0

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]}}
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.