Convert all json rows in a table into a single json array

I have a table where each row has a json column named data_object. I want to get the data_object for each row and create a json array that represents all rows in the table.

Example:

id | data_object
----------------
1  | { "someKey": "someValue", etc. }
2  | { "someKey": "someOtherValue", etc. }

Desired Result:

[
  { "someKey": "someValue", etc. },
  { "someKey": "someOtherValue", etc. }
]

Some of the keys per row are known (a standard set), but some of the the keys per row will vary, I just want a json array that represents all rows in the table for the data_object json column.

I appreciate your help in advance!

CodePudding user response：

You can use

select array_agg(data_object) from table_name;

CodePudding user response：

jsonb_agg is what you're looking for:

SELECT jsonb_agg(data_object) FROM mytable

Demo: db<>fiddle