jsonb_agg: Avoid objects wrapped with "jsonb_build_object"

Question

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?

This is a duplicate of stackoverflow.com/q/30521196 , sorry! — hfs
– hfs, Commented Apr 20, 2017 at 10:48

pozs · Accepted Answer · 2017-04-20 10:46:58Z

6

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 clarity
aliasing like this in the FROM 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)

answered Apr 20, 2017 at 10:46

pozs

36.6k5 gold badges61 silver badges66 bronze badges

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

3 Comments

Bergi Over a year ago

Creating a table and column alias at the same time is very interesting, thanks! Where can I find more about this? The docs don't seem to mention it.

pozs Over a year ago

@Bergi see postgresql.org/docs/current/… -- If column aliases are not supplied, then for a function returning a base data type, the column name is also the same as the function name. For a function returning a composite type, the result columns get the names of the individual attributes of the type. -- although it is only implied that when you are aliasing the function name, the default column name changes with it.

Bergi Over a year ago

Ah. I guess I was more confused by referring to the table_alias element in the syntax as a "function name" :-) I thought using fn(…) AS t would create a table t with a column fn.

Collectives™ on Stack Overflow

jsonb_agg: Avoid objects wrapped with "jsonb_build_object"

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related