[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-general
Subject:    Dynamically generate a nested json
From:       Rushabh Shah <rushabh1007 () gmail ! com>
Date:       2023-11-27 22:32:24
Message-ID: CADY9CuB-YKBD7hKy=8pL1gip7vJ3aBe=Vw8FQhVG7sY4Bf-w5A () mail ! gmail ! com
[Download RAW message or body]

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

[Attachment #3 (text/html)]

<div><meta charset="UTF-8"><span \
style="color:rgb(49,49,49);font-family:-apple-system,&quot;Helvetica \
Neue&quot;;font-size:16px;font-style:normal;font-variant-caps:normal;font-weight:400;l \
etter-spacing:normal;text-align:start;text-indent:0px;text-transform:none;white-space: \
normal;word-spacing:1px;text-decoration:none;display:inline!important;float:none">Hi,</span><div \
dir="auto" style="color:rgb(49,49,49);font-family:-apple-system,&quot;Helvetica \
Neue&quot;;font-size:16px;font-style:normal;font-variant-caps:normal;font-weight:400;l \
etter-spacing:normal;text-align:start;text-indent:0px;text-transform:none;white-space:normal;word-spacing:1px;text-decoration:none"><br></div><div \
dir="auto" style="color:rgb(49,49,49);font-family:-apple-system,&quot;Helvetica \
Neue&quot;;font-size:1rem;font-style:normal;font-variant-caps:normal;font-weight:400;l \
etter-spacing:normal;text-align:start;text-indent:0px;text-transform:none;white-space:normal;word-spacing:1px;text-decoration:none">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.  </div><div dir="auto" \
style="color:rgb(49,49,49);font-family:-apple-system,&quot;Helvetica \
Neue&quot;;font-size:16px;font-style:normal;font-variant-caps:normal;font-weight:400;l \
etter-spacing:normal;text-align:start;text-indent:0px;text-transform:none;white-space:normal;word-spacing:1px;text-decoration:none"><br></div><div \
dir="auto" style="color:rgb(49,49,49);font-family:-apple-system,&quot;Helvetica \
Neue&quot;;font-size:1rem;font-style:normal;font-variant-caps:normal;font-weight:400;l \
etter-spacing:normal;text-align:start;text-indent:0px;text-transform:none;white-space:normal;word-spacing:1px;text-decoration:none">This \
what I have so for that is working:</div><div dir="auto" \
style="color:rgb(49,49,49);font-family:-apple-system,&quot;Helvetica \
Neue&quot;;font-size:16px;font-style:normal;font-variant-caps:normal;font-weight:400;l \
etter-spacing:normal;text-align:start;text-indent:0px;text-transform:none;white-space:normal;word-spacing:1px;text-decoration:none"><br></div><div \
dir="auto" style="color:rgb(49,49,49);font-family:-apple-system,&quot;Helvetica \
Neue&quot;;font-size:16px;font-style:normal;font-variant-caps:normal;font-weight:400;l \
etter-spacing:normal;text-align:start;text-indent:0px;text-transform:none;white-space:normal;word-spacing:1px;text-decoration:none"><div \
style="font-size:1rem">declare outputs text;  </div><div dir="auto" \
style="font-size:1rem">  begin  </div><div dir="auto" style="font-size:1rem">  \
outputs = &#39;&#39;;  </div><div dir="auto" style="font-size:1rem">  for i in 1 .. \
array_upper(fieldtype, 1) loop  </div><div dir="auto" style="font-size:1rem">  select \
case  </div><div dir="auto" style="font-size:1rem">  when lower(fieldtype[i]) = \
&#39;field&#39; then (outputs || &#39;&#39; || jsonb_build_object( fname[i], \
tcolumn[i] )::text)::text  </div><div dir="auto"><br></div><div dir="auto" \
style="font-size:1rem">when lower(fieldtype[i]) = &#39;json object&#39; then (outputs \
|| &#39;&#39; || jsonb_build_object( fname[i], jsonb_build_object() )::text)::text  \
</div><div dir="auto"><br></div><div dir="auto" style="font-size:1rem">  when \
lower(fieldtype[i]) = &#39;json array&#39; then (outputs || &#39;&#39; || \
json_build_array( fname[i], json_build_array() )::text)::text  </div><div \
dir="auto"><br></div><div dir="auto" style="font-size:1rem">  else &#39;It is not \
field, object or an array&#39;::text  </div><div dir="auto" \
style="font-size:1rem">end case into outputs  </div><div dir="auto" \
style="font-size:1rem">  from tblname;  </div><div dir="auto" \
style="font-size:1rem">end loop;  </div><div dir="auto" style="font-size:1rem">  \
return outputs;  </div><div dir="auto" \
style="font-size:1rem">end;<br></div><br></div><div dir="auto" \
style="color:rgb(49,49,49);font-family:-apple-system,&quot;Helvetica \
Neue&quot;;font-size:1rem;font-style:normal;font-variant-caps:normal;font-weight:400;l \
etter-spacing:normal;text-align:start;text-indent:0px;text-transform:none;white-space:normal;word-spacing:1px;text-decoration:none">So, \
not for example the input for my function is:  </div><div dir="auto" \
style="color:rgb(49,49,49);font-family:-apple-system,&quot;Helvetica \
Neue&quot;;font-size:1rem;font-style:normal;font-variant-caps:normal;font-weight:400;l \
etter-spacing:normal;text-align:start;text-indent:0px;text-transform:none;white-space:normal;word-spacing:1px;text-decoration:none">fname: \
[‘passenger', ‘firstname', ‘lastname', ‘address', ‘city', ‘state', \
‘country']</div><div dir="auto" \
style="color:rgb(49,49,49);font-family:-apple-system,&quot;Helvetica \
Neue&quot;;font-size:1rem;font-style:normal;font-variant-caps:normal;font-weight:400;l \
etter-spacing:normal;text-align:start;text-indent:0px;text-transform:none;white-space:normal;word-spacing:1px;text-decoration:none">tcolumn: \
[,'pass.fname', ‘pass.lname', , ‘address.city', ‘address.state', \
‘address.country']</div><div dir="auto" \
style="color:rgb(49,49,49);font-family:-apple-system,&quot;Helvetica \
Neue&quot;;font-size:1rem;font-style:normal;font-variant-caps:normal;font-weight:400;l \
etter-spacing:normal;text-align:start;text-indent:0px;text-transform:none;white-space:normal;word-spacing:1px;text-decoration:none">ftype: \
[‘json object', ‘field', ‘field', ‘json array', ‘field', ‘field', \
‘field']</div><div dir="auto" \
style="color:rgb(49,49,49);font-family:-apple-system,&quot;Helvetica \
Neue&quot;;font-size:16px;font-style:normal;font-variant-caps:normal;font-weight:400;l \
etter-spacing:normal;text-align:start;text-indent:0px;text-transform:none;white-space:normal;word-spacing:1px;text-decoration:none"><br></div><div \
dir="auto" style="color:rgb(49,49,49);font-family:-apple-system,&quot;Helvetica \
Neue&quot;;font-size:1rem;font-style:normal;font-variant-caps:normal;font-weight:400;l \
etter-spacing:normal;text-align:start;text-indent:0px;text-transform:none;white-space:normal;word-spacing:1px;text-decoration:none">This \
is what I want my output to look like:</div><div dir="auto" \
style="color:rgb(49,49,49);font-family:-apple-system,&quot;Helvetica \
Neue&quot;;font-size:1rem;font-style:normal;font-variant-caps:normal;font-weight:400;l \
etter-spacing:normal;text-align:start;text-indent:0px;text-transform:none;white-space:normal;word-spacing:1px;text-decoration:none">{</div><div \
dir="auto" style="color:rgb(49,49,49);font-family:-apple-system,&quot;Helvetica \
Neue&quot;;font-size:1rem;font-style:normal;font-variant-caps:normal;font-weight:400;l \
etter-spacing:normal;text-align:start;text-indent:0px;text-transform:none;white-space:normal;word-spacing:1px;text-decoration:none"> \
passenger: {</div><div dir="auto" \
style="color:rgb(49,49,49);font-family:-apple-system,&quot;Helvetica \
Neue&quot;;font-size:1rem;font-style:normal;font-variant-caps:normal;font-weight:400;l \
etter-spacing:normal;text-align:start;text-indent:0px;text-transform:none;white-space:normal;word-spacing:1px;text-decoration:none"> \
"firstname": "john",</div><div dir="auto" \
style="color:rgb(49,49,49);font-family:-apple-system,&quot;Helvetica \
Neue&quot;;font-size:1rem;font-style:normal;font-variant-caps:normal;font-weight:400;l \
etter-spacing:normal;text-align:start;text-indent:0px;text-transform:none;white-space:normal;word-spacing:1px;text-decoration:none"> \
"lastname": "smith",</div><div dir="auto" \
style="color:rgb(49,49,49);font-family:-apple-system,&quot;Helvetica \
Neue&quot;;font-size:1rem;font-style:normal;font-variant-caps:normal;font-weight:400;l \
etter-spacing:normal;text-align:start;text-indent:0px;text-transform:none;white-space:normal;word-spacing:1px;text-decoration:none"> \
"address": [</div><div dir="auto" \
style="color:rgb(49,49,49);font-family:-apple-system,&quot;Helvetica \
Neue&quot;;font-size:1rem;font-style:normal;font-variant-caps:normal;font-weight:400;l \
etter-spacing:normal;text-align:start;text-indent:0px;text-transform:none;white-space:normal;word-spacing:1px;text-decoration:none"> \
{</div><div dir="auto" \
style="color:rgb(49,49,49);font-family:-apple-system,&quot;Helvetica \
Neue&quot;;font-size:1rem;font-style:normal;font-variant-caps:normal;font-weight:400;l \
etter-spacing:normal;text-align:start;text-indent:0px;text-transform:none;white-space:normal;word-spacing:1px;text-decoration:none"> \
"city": "Houston",</div><div dir="auto" \
style="color:rgb(49,49,49);font-family:-apple-system,&quot;Helvetica \
Neue&quot;;font-size:1rem;font-style:normal;font-variant-caps:normal;font-weight:400;l \
etter-spacing:normal;text-align:start;text-indent:0px;text-transform:none;white-space:normal;word-spacing:1px;text-decoration:none"> \
"state": "Texas",</div><div dir="auto" \
style="color:rgb(49,49,49);font-family:-apple-system,&quot;Helvetica \
Neue&quot;;font-size:1rem;font-style:normal;font-variant-caps:normal;font-weight:400;l \
etter-spacing:normal;text-align:start;text-indent:0px;text-transform:none;white-space:normal;word-spacing:1px;text-decoration:none"> \
"country": "USA"</div><div dir="auto" \
style="color:rgb(49,49,49);font-family:-apple-system,&quot;Helvetica \
Neue&quot;;font-size:1rem;font-style:normal;font-variant-caps:normal;font-weight:400;l \
etter-spacing:normal;text-align:start;text-indent:0px;text-transform:none;white-space:normal;word-spacing:1px;text-decoration:none"> \
}</div><div dir="auto" \
style="color:rgb(49,49,49);font-family:-apple-system,&quot;Helvetica \
Neue&quot;;font-size:1rem;font-style:normal;font-variant-caps:normal;font-weight:400;l \
etter-spacing:normal;text-align:start;text-indent:0px;text-transform:none;white-space:normal;word-spacing:1px;text-decoration:none"> \
]</div><div dir="auto" \
style="color:rgb(49,49,49);font-family:-apple-system,&quot;Helvetica \
Neue&quot;;font-size:1rem;font-style:normal;font-variant-caps:normal;font-weight:400;l \
etter-spacing:normal;text-align:start;text-indent:0px;text-transform:none;white-space:normal;word-spacing:1px;text-decoration:none"> \
}</div><div dir="auto" \
style="color:rgb(49,49,49);font-family:-apple-system,&quot;Helvetica \
Neue&quot;;font-size:1rem;font-style:normal;font-variant-caps:normal;font-weight:400;l \
etter-spacing:normal;text-align:start;text-indent:0px;text-transform:none;white-space:normal;word-spacing:1px;text-decoration:none">}</div><div \
dir="auto" style="color:rgb(49,49,49);font-family:-apple-system,&quot;Helvetica \
Neue&quot;;font-size:16px;font-style:normal;font-variant-caps:normal;font-weight:400;l \
etter-spacing:normal;text-align:start;text-indent:0px;text-transform:none;white-space:normal;word-spacing:1px;text-decoration:none"><br></div><div \
dir="auto" style="color:rgb(49,49,49);font-family:-apple-system,&quot;Helvetica \
Neue&quot;;font-size:1rem;font-style:normal;font-variant-caps:normal;font-weight:400;l \
etter-spacing:normal;text-align:start;text-indent:0px;text-transform:none;white-space:normal;word-spacing:1px;text-decoration:none">But \
currently I am having difficulty adding firstname inside passenger json \
object.</div><div dir="auto" \
style="color:rgb(49,49,49);font-family:-apple-system,&quot;Helvetica \
Neue&quot;;font-size:16px;font-style:normal;font-variant-caps:normal;font-weight:400;l \
etter-spacing:normal;text-align:start;text-indent:0px;text-transform:none;white-space:normal;word-spacing:1px;text-decoration:none"><br></div><div \
dir="auto" style="color:rgb(49,49,49);font-family:-apple-system,&quot;Helvetica \
Neue&quot;;font-size:1rem;font-style:normal;font-variant-caps:normal;font-weight:400;l \
etter-spacing:normal;text-align:start;text-indent:0px;text-transform:none;white-space:normal;word-spacing:1px;text-decoration:none">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.  \
</div><div dir="auto" \
style="color:rgb(49,49,49);font-family:-apple-system,&quot;Helvetica \
Neue&quot;;font-size:16px;font-style:normal;font-variant-caps:normal;font-weight:400;l \
etter-spacing:normal;text-align:start;text-indent:0px;text-transform:none;white-space:normal;word-spacing:1px;text-decoration:none"><br></div><div \
dir="auto" style="color:rgb(49,49,49);font-family:-apple-system,&quot;Helvetica \
Neue&quot;;font-size:1rem;font-style:normal;font-variant-caps:normal;font-weight:400;l \
etter-spacing:normal;text-align:start;text-indent:0px;text-transform:none;white-space:normal;word-spacing:1px;text-decoration:none">Thanks \
for all the help.  </div><br class="Apple-interchange-newline"></div><div \
dir="auto">Regards,</div><div dir="auto">Rushabh</div>



[prev in list] [next in list] [prev in thread] [next in thread]