jsonb_array_elements() and json_array_elements()

Purpose: Transform the JSON values of a JSON array into a SQL table of (i.e., SETOF) jsonb values.

Signature: For the jsonb variant:

input value:       jsonb
return value:      SETOF jsonb

Notes: Each function in this pair requires that the supplied JSON value is an array. They are the counterparts, for an array, to jsonb_populate_recordset() for a JSON object.

Notice that the JSON value null becomes a genuine SQL NULL. However, SQL array comparison and record comparison use IS NOT DISTINCT FROM semantics, and not the semantics that the comparison of scalars uses. So the simple ASSERT that elements = expected_elements is TRUE is sufficient. See the section Operators for comparing two arrays.

do $body$
declare
  j_array constant jsonb := '["cat", "dog house", 42, true, {"x": 17}, null]';
  j jsonb;

  elements jsonb[];
  expected_elements constant jsonb[] :=
    array[
      '"cat"'::jsonb,
      '"dog house"'::jsonb,
      '42'::jsonb,
      'true'::jsonb,
      '{"x": 17}'::jsonb,
      'null'::jsonb
    ];

  n int := 0;
begin
  for j in (select jsonb_array_elements(j_array)) loop
    n := n + 1;
    elements[n] := j;
  end loop;

  assert
    elements = expected_elements,
  'unexpected';
end;
$body$;