PostgreSQL: Using json_agg() aggregate table data into a JSON formatted array

Recently, PostgreSQL introduced JSON data type which is very powerful to manipulate JSON formatted data.
I have published a couple of articles on JSON Data Type.
You can start from here.

In this post, I am showing aggregation of table data into a JSON formatted array using json_agg().

Our requirement is to aggregate non json formatted data into JSON array and even some time also required to aggregate JSON formatted data.

I have prepared a small demonstration using json_agg().

First, create a table with sample JSON formatted data:

Aggregates JSON formatted data:

Aggregates non JSON formatted data:

Anvesh Patel

Leave a Reply

2 Comments on "PostgreSQL: Using json_agg() aggregate table data into a JSON formatted array"

Notify of
avatar
Sort by:   newest | oldest | most voted
eri
Guest

what is the equivalent in mysql?

wpDiscuz