Postgresql – Postgres JSON output using specific column as JSON key

jsonpostgresql

I have data in Postgres (10.4) in the form of 

id | my_json
-----------------------------------
1  | {"a": 1, "b": 2, "c": [1,2,3]}
2  | {"a": 3, "b": 5, "c": [2,3,4]}

I can output the data as JSON like this:

WITH l AS (SELECT id, my_json as data FROM table)
SELECT to_json(l) FROM l;

and i get:

{"id": 1, "data": {"a": 1, "b": 2, "c": [1,2,3]}}
{"id": 2, "data": {"a": 3, "b": 5, "c": [2,3,4]}}

But is there a way to output the data, keyed by the id?

Like this:

{"1": {"a": 1, "b": 2, "c": [1,2,3]}}
{"2": {"a": 3, "b": 5, "c": [2,3,4]}}

Best Answer

You can use json_build_object function which allows you to calculate keys or values. 

WITH l AS (SELECT id, my_json as data FROM table)
SELECT json_build_object(id, data) FROM l;

The result will be like; 

|                        json_build_object |
|------------------------------------------|
| {"1" : {"a": 1, "b": 2, "c": [1, 2, 3]}} |
| {"2" : {"a": 1, "b": 2, "c": [1, 2, 3]}} |

Here is sample fiddle

Related Question