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?

Answer 1

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)