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.