converting records to the desired json format in postgres 14

Question

I have data in the following form

select t.name_field, t.value_field 
from 
(
    values 
        ('name_1', 'val_fld_1'), 
        ('name_2', null), 
        ('name_3', 'val_fld__3')
) as t(name_field,value_field);

From this data, I need to get a json string of the following form:

{"name_1" : [{"value" : "val_fld_1", "seq" : 1}], "name_2" : [{"value" : "", "seq" : 1}], "name_3" : [{"value" : "val_fld__3", "seq" : 1}]}

I am trying to do it like this:

select array_agg(json_build_object(t.name_field, json_build_array(json_build_object('value',  t.value_field)))) as my_test
from 
(
    values 
        ('name_1', 'val_fld_1'), 
        ('name_2', null), 
        ('name_3', 'val_fld__3')
) as t(name_field,value_field);

but it doesn't give me the desired result. How do I achieve the desired json format?

what seq means ? i guess you want to get a sequential number 1,2 and 3 ? — jmvcollaborator, Jul 06 '23 at 18:41

score 1 · Accepted Answer · answered Jul 06 '23 at 19:06

I guess it should work:

SELECT json_object_agg(t.name_field, json_build_array(json_build_object('value', t.value_field, 'seq', 1))) AS my_test
FROM 
(
    VALUES 
        ('name_1', 'val_fld_1'), 
        ('name_2', null), 
        ('name_3', 'val_fld__3')
) AS t(name_field, value_field);

more about json functions:
https://www.postgresql.org/docs/current/functions-json.html#FUNCTIONS-JSON-CREATION-TABLE

score 1 · Answer 2 · answered Jul 06 '23 at 19:22

Just do

select  ('{'||
string_agg(concat('"',t.name_field,'" : [{"value" : "',t.value_field,'", "seq" : 1}]'), ',')
    ||'}')::json
from 
(
    values 
        ('name_1', 'val_fld_1'), 
        ('name_2', null), 
        ('name_3', 'val_fld__3')
) as t(name_field,value_field);

to assemble your JSON as expected.

Working sample here

score 1 · Answer 3 · answered Jul 06 '23 at 19:31

first fix the val_fld_3 value i guess you wanted one underscore :)

You can nest queries to get the inner array, and using aggregate and build array as:

Notice the Coalesce to return empty string instead of null.

Fiddle available here

SELECT json_object_agg(t.name_field,
json_build_array(json_build_object('value', coalesce(t.value_field,''), 'seq', 1))) AS res
FROM 
(
    VALUES 
        ('name_1', 'val_fld_1'), ('name_2', null),('name_3', 'val_fld_3')
) AS t(name_field, value_field);

converting records to the desired json format in postgres 14

3 Answers3