jsonb_agg()

Purpose: This is an aggregate function. (Aggregate functions compute a single result from a SETOF input SQL values.) It creates a JSON array whose values are the JSON representations of the aggregated SQL values.

Signature:

input value:       SETOF anyelement
return value:      jsonb

Notes: The syntax "order by column1 nulls first" within the parentheses of the aggregate function is not specific to jsonb_agg(). Rather, it is a generic feature of aggregate functions. The same syntax is used with the array_agg()` function for SQL arrays.

create type rt as (key1 int, key2 text);

do $body$
declare
  agg jsonb;

  expected_agg constant jsonb not null := '[
      {"key1": null, "key2": "ant"},
      {"key1": 1,    "key2": "dog"},
      {"key1": 2,    "key2": "cat"},
      {"key1": 4,    "key2": null },
      {"key1": 5,    "key2": "ant"}
    ]'::jsonb;
begin
  with tab as (
    values
      (5::int,    'ant'::text),
      (2::int,    'cat'::text),
      (null::int, 'ant'::text),
      (1::int,    'dog'::text),
      (4::int,     null::text))
  select
  json_agg((column1, column2)::rt order by column1 nulls first)
  into strict agg
  from tab;

  assert (agg = expected_agg), 'unexpected';
end;
$body$;

You can also aggregate SQL arrays into a ragged JSON array of JSON arrays like this:

do $body$
declare
  agg jsonb not null := '"?"';

  expected_agg constant jsonb not null := '[
      ["a", "b", "c"],
      ["d"          ],
      ["e", "f"     ]
    ]'::jsonb;
begin
  with tab as (
    values
      (array['a', 'b', 'c']::text[]),
      (array['d'          ]::text[]),
      (array['e', 'f'     ]::text[]))
  select
  json_agg((column1)::text[] order by column1 nulls first)
  into strict agg
  from tab;

  assert (agg = expected_agg), 'unexpected';
end;
$body$;