Current JSON
{
"layout":"dynamicReport1",
"templateType":"DYNAMIC_REPORTS",
"containers":{
"fieldsContainer":[
{
"fieldName":"role_id",
"displayName":"Role",
"fieldType":"text",
"isHidden":true,
"index":0,
"queryForParam":"select name as \"role_id\" from um_role_master where id=#role_id#",
"queryIdForParam":476
},
{
"fieldName":"course_id",
"displayName":"Course",
"fieldType":"text",
"isHidden":true,
"index":1,
"queryForParam":"select course_name as course_id from tr_course_master where course_id=#course_id#",
"queryIdForParam":477
},
{
"fieldName":"location_id",
"displayName":"Location",
"fieldType":"text",
"isHidden":true,
"index":2,
"queryForParam":"select name as location_id from location_master where id = #location_id#",
"queryIdForParam":478
}
]
}
}
Hierarchy is like
containers -> fieldContainer -> object
Above is my json config and i want to add queryUUIDForParam: random UUID to each Object through query.
How i can insert ?
I tried to get updated config by this query but it throws error:
select config::jsonb || ('{"queryUUIDForParam":' || cast(uuid as text) || '}')::jsonb
error :
SQL Error [22P02]: ERROR: invalid input syntax for type json Detail: Token "5574ff23" is invalid. Where: JSON data, line 1: {"queryUUIDForParam":5574ff23...
My expected output is to add "queryUUIDForParam" element in Object Like. I want an element with UUID value to be appended.This UUId value is generated using random function.
{
"layout":"dynamicReport1",
"templateType":"DYNAMIC_REPORTS",
"containers":{
"fieldsContainer":[
{
"fieldName":"role_id",
"displayName":"Role",
"fieldType":"text",
"isHidden":true,
"index":0,
"queryForParam":"select name as \"role_id\" from um_role_master where id=#role_id#",
"queryIdForParam":476,
"queryUUIDForParam":"1ea99f17-6965-4a0d-8d31-22b8777b9c62"
},
{
"fieldName":"course_id",
"displayName":"Course",
"fieldType":"text",
"isHidden":true,
"index":1,
"queryForParam":"select course_name as course_id from tr_course_master where course_id=#course_id#",
"queryIdForParam":477,
"queryUUIDForParam":"3ea99f17-6965-4a0d-8d31-22b8777b9c62"
},
{
"fieldName":"location_id",
"displayName":"Location",
"fieldType":"text",
"isHidden":true,
"index":2,
"queryForParam":"select name as location_id from location_master where id = #location_id#",
"queryIdForParam":478,
"queryUUIDForParam":"9ea99f17-6965-4a0d-8d31-22b8777b9c62"
}
]
}
}
Thanks in advance :)
Try This:
with cte as (select jsonb_array_elements(jsonb_extract_path(config, 'containers','fieldsContainer')::jsonb) "objects" from example),
final_array as (
select jsonb_build_array(d) "array_data" from (select array_agg(objects::jsonb || jsonb_build_object('queryUUIDForParam',(select uuid_generate_v4()))) "fieldsContainer" from cte )d)
select jsonb_set(
config::jsonb,
'{containers,fieldsContainer}', (f.array_data),false)
from example, final_array f;
in case you want different uuid for each object
with cte as (select uuid_generate_v4() "uuid_",jsonb_array_elements(jsonb_extract_path(config, 'containers','fieldsContainer')::jsonb) "objects" from example),
final_array as (
select jsonb_build_array(d) "array_data" from (select array_agg(objects::jsonb || jsonb_build_object('queryUUIDForParam',uuid_)) "fieldsContainer" from cte )d)
select jsonb_set(
config::jsonb,
'{containers,fieldsContainer}', (f.array_data),false)
from example, final_array f;
Note: I have used Inbuilt function of Postgres to generate the UUID. Please run following statement before using it
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
DEMO on DB-Fiddle