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$;