PostgreSQL: json_agg() Example
July 27, 2015
I’ve already posted a few times about the usefulness of the jsonb field type for PostgreSQL 9.4+. A related function I love is json_agg(). It allows you to aggregate grouped jsonb fields into a valid json array. Below is a quick example on how to use it:
Example Database
1 Table: user_attributes
2 +--------------+-------------------+-----------------------+
3 | id [integer] | user_id [integer] | attribute [jsonb] |
4 +--------------+-------------------+-----------------------+
5 | 1 | 1 | {"first_name":"John"} |
6 | 2 | 1 | {"last_name":"Doe"} |
7 +--------------+-------------------+-----------------------+SQL
1 SELECT user_id, json_agg(attribute) AS attributes
2 FROM test
3 GROUP BY user_idRESULT
1 +-------------------+-------------------------------------------------+
2 | user_id [integer] | attributes [jsonb] |
3 +-------------------+-------------------------------------------------+
4 | 1 | [{"first_name": "John"}, {"last_name": "Doe"}] |
5 +-------------------+-------------------------------------------------+