I can create JSON objects using jsonb_build_object the way I want them. E.g.
SELECT jsonb_build_object('id', id) FROM (SELECT generate_series(1,3) id) objects;
results in
jsonb_build_object
------------------
{"id": 1}
{"id": 2}
{"id": 3}
But when I want to add them to an array, they are wrapped in an additional object, using the column name as key:
SELECT jsonb_build_object(
'foo', 'bar',
'collection', jsonb_agg(collection)
)
FROM (
SELECT jsonb_build_object('id', id)
FROM (
SELECT generate_series(1,3) id
) objects
) collection;
results in
{"foo": "bar", "collection": [{"jsonb_build_object": {"id": 1}}, {"jsonb_build_object": {"id": 2}}, {"jsonb_build_object": {"id": 3}}]}
How can I get
{"foo": "bar", "collection": [{"id": 1}, {"id": 2}, {"id": 3}]}
instead?
Use jsonb_agg(collection.jsonb_build_object). You can use aliases too, but the point is that collection refers to the entire row, which has a (single) jsonb_build_object named (by default) column, which is the JSON you want to aggregate.
With simplifying and aliases, you query can be:
SELECT jsonb_build_object(
'foo', 'bar',
'collection', jsonb_agg(js)
)
FROM generate_series(1,3) id
CROSS JOIN LATERAL jsonb_build_object('id', id) js;
Notes:
LATERAL is implicit, I just wrote it for clarityFROM clause creates a table & a column alias too, with the same name. So it is equivalent to jsonb_build_object('id', id) AS js(js)