jsonb_agg()

Collects all the input values, including nulls, into a JSON array. Values are converted to JSON as per to_json or to_jsonb.

Signature

jsonb_agg ( anyelement ORDER BY input_sort_columns ) → jsonb

Examples

SELECT jsonb_agg(product ORDER BY id) FROM orders;

                            jsonb_agg                             
------------------------------------------------------------------
 ["keyboard", "monitor", "keyboard", "mouse", "monitor", "cable"]
(1 row)

Run against PostgreSQL 18.6 on 2026-09-13. This is the output we got, not the documentation’s.

In other engines

PostgreSQL	jsonb_agg
MySQL	JSON_ARRAYAGG(x)
SQL Server	JSON_ARRAYAGG(x) on SQL Server 2025, else FOR JSON
Oracle	JSON_ARRAYAGG(x)
SQLite	json_group_array(x)

PostgreSQL is alone in distinguishing json from jsonb — one keeps your whitespace and key order, the other normalises and indexes. Choosing json when you meant jsonb is a decision you discover months later, when you try to add a GIN index.

Want to run this against your own PostgreSQL server from an iPhone, iPad or Mac? These are ours — every connection goes straight from the device to your database, with no account and no server of ours in between.

Postgres & FriendsPostgreSQL and the open-source engines around it

Signature and description adapted from the PostgreSQL manual, used under the PostgreSQL License. PostgreSQL is a registered trademark of the PostgreSQL Community Association of Canada. This page is not affiliated with the PostgreSQL Global Development Group.