build_json_object issue with nested JSON

Question

I need to get the following JSON object as an output:

"{"table" : "test", "column 1" : {"pos" : 1, "name" : "col1", "type" : "integer"}, 
"column 2" : {"pos" : 2, "name" : "col2", "type" : "date"}}"

When I use

build_json_object('table','test', 'column 1', build_json_object('pos',1,'name','col1','type','integer'), 'column 2', build_json_object('pos',2,'name','col2','type','date'));

it throws an error: ERROR: function build_json_object(unknown,integer, unknown,unknown, unknown,unknown) does not exist

when I use

build_json_object('table','test', 'column 1',('pos',1,'name','col1','type','integer'), 'column 2', ('pos',2,'name','col2','type','date'));

it just produces the wrong result:

"{"table" : "test", "column 1" : {"f1":"pos", "f2": 1, "f3":"name", "f4": "col1", "f5":"type", "f6": "integer"}, 
    "column 2" : {"f1":"pos", "f2": 2, "f3":"name", "f4": "col2", "f5":"type", "f6": "date"}}"

How to generate nested JSON object with build_json_object function?

klin · Accepted Answer · 2016-07-13 21:31:26Z

15

A hint - build the expression step by step starting from the most nested objects. Use proper indents. Note that the expression is somehow similar to the result. (I've wrapped the main expression with jsonb_pretty() to get a nice output):

select 
    jsonb_pretty(
        json_build_object(
            'table', 'test',
            'column 1', json_build_object('pos', 1, 'name', 'col1', 'type', 'integer'),
            'column 2', json_build_object('pos', 2, 'name', 'col2', 'type', 'date')
        )::jsonb
    );


       jsonb_pretty        
---------------------------
 {                        +
     "table": "test",     +
     "column 1": {        +
         "pos": 1,        +
         "name": "col1",  +
         "type": "integer"+
     },                   +
     "column 2": {        +
         "pos": 2,        +
         "name": "col2",  +
         "type": "date"   +
     }                    +
 }
(1 row)

edited Jul 13, 2016 at 21:31

answered Jul 13, 2016 at 21:25

klin

123k15 gold badges240 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

MaterialGirl Over a year ago

Great, it works! Thanks a lot. Looks like changing build_json_object to json_build_object really matters.

klin Over a year ago

Only now I've noticed that your first attempt was ok, with the exception of the function name. This is because in principle I don't read texts that don't fit on the page ;) Use indents!!

Lost Odinson · Accepted Answer · 2018-08-10 19:48:32Z

1

You can nest json objects in constructs via SQL like this

SELECT jsonb_pretty(
    data || json_build_object(
        'field', sub.field, 
        'construct', json_build_object(
            'nested', json_build_object(
                'key', 'value', 
                'moved_data', data -> 'nuke_me' -> moved_data
            ),
            'other_nested_key', json_build_object(
                '0', 2136 
            )                       

        )
    ) :: JSONB 
    #- '{nuke_me,moved_data}' 
    #- '{nuke_all_of_me}'
)
AS data FROM table AS sub
ORDER BY "created_at" DESC

https://www.postgresql.org/docs/9.6/static/functions-json.html

answered Aug 10, 2018 at 19:48

Lost Odinson

4081 gold badge5 silver badges14 bronze badges

Collectives™ on Stack Overflow

build_json_object issue with nested JSON

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related