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?

Answer 1

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