Dynamically generate a nested json - Mailing list pgsql-general

From Rushabh Shah
Subject Dynamically generate a nested json
Date
Msg-id CADY9CuB-YKBD7hKy=8pL1gip7vJ3aBe=Vw8FQhVG7sY4Bf-w5A@mail.gmail.com
Whole thread Raw
List pgsql-general
Hi,

I want to dynamically generate a nested json file. I have written a function for it in PL/PGSQL that accepts 3 arrays. First one is an array of all json fields, second one is an array of all json fields with columns from tables present in db, third one mentions the type for all the fields inside the json file. 

This what I have so for that is working:

declare outputs text; 
 begin 
 outputs = ''; 
 for i in 1 .. array_upper(fieldtype, 1) loop 
 select case 
 when lower(fieldtype[i]) = 'field' then (outputs || '' || jsonb_build_object( fname[i], tcolumn[i] )::text)::text 

when lower(fieldtype[i]) = 'json object' then (outputs || '' || jsonb_build_object( fname[i], jsonb_build_object() )::text)::text 

 when lower(fieldtype[i]) = 'json array' then (outputs || '' || json_build_array( fname[i], json_build_array() )::text)::text 

 else 'It is not field, object or an array'::text 
end case into outputs 
 from tblname; 
end loop; 
 return outputs; 
end;

So, not for example the input for my function is: 
fname: [‘passenger’, ‘firstname’, ‘lastname’, ‘address’, ‘city’, ‘state’, ‘country’]
tcolumn: [,’pass.fname’, ‘pass.lname’, , ‘address.city’, ‘address.state’, ‘address.country’]
ftype: [‘json object’, ‘field’, ‘field’, ‘json array’, ‘field’, ‘field’, ‘field’]

This is what I want my output to look like:
{
  passenger: {
       “firstname”: “john”,
       “lastname”: “smith”,
       “address”: [
         {
           “city”: “Houston”,
           “state”: “Texas”,
           “country”: “USA”
         }
        ]
    }
}

But currently I am having difficulty adding firstname inside passenger json object.

I know that I need to again loop through the json field names array to go to next one inside jsonb_build_object() function to get the fields and arrays inside but that would make my function very big. This is what I need some assistance with. 

Thanks for all the help. 

Regards,
Rushabh

pgsql-general by date:

Previous
From: Adrian Klaver
Date:
Subject: Re: suppress notices from inside a stored a plpgqsl function
Next
From: H
Date:
Subject: Installing extension temporal_tables for PG13