Dynamically generate a nested json

[Rushabh Shah]

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