Question 202097 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – converting records to the desired json format in postgres 14

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?

Tags:

3

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
  3. 0

    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);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search