Insert element of UUID type in nested array of Object through Postgresql query

Question

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 :)

Could you please post Full JSON and desired output JSON

Akhilesh Mishra
– Akhilesh Mishra

2020-06-05 07:35:19 +00:00
Commented Jun 5, 2020 at 7:35 — Akhilesh Mishra
– Akhilesh Mishra, Commented Jun 5, 2020 at 7:35
Sure! I have added my expected output.

Ashish Khokhariya
– Ashish Khokhariya

2020-06-05 07:41:14 +00:00
Commented Jun 5, 2020 at 7:41 — Ashish Khokhariya
– Ashish Khokhariya, Commented Jun 5, 2020 at 7:41

Akhilesh Mishra · Accepted Answer · 2020-06-05 10:57:28Z

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

Thanks Akhilesh :) It worked but i want to generate every time Unique UUID for each Object.
you should mention this in your question. however i have updated my answer

Collectives™ on Stack Overflow

Insert element of UUID type in nested array of Object through Postgresql query

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related