jsonb_to_recordset() and json_to_recordset()
Purpose: Convert a homogeneous JSON array of JSON objects into the equivalent set of SQL records.
Signature For the jsonb
variant:
input value: jsonb
return value: SETOF record
Notes: The function jsonb_to_recordset()
bears the same relationship to jsonb_to_record()
as jsonb_populate_recordset()
bears to jsonb_populate_record()
.
Therefore, the DO
block that demonstrated the functionality of jsonb_populate_recordset()
can be extended to demonstrate the functionality of jsonb_to_recordset()
as well and to show that their results are identical. See the "Record and array comparison" note in the account of jsonb_populate_recordset()
.
create type t as (a int, b int);
do $body$
declare
array_of_objects constant jsonb :=
'[
{"a": 1, "b": 2},
{"b": 4, "a": 3},
{"a": 5, "c": 6},
{"b": 7, "d": 8},
{"c": 9, "d": 0}
]';
rows_1 t[];
rows_2 t[];
expected_rows constant t[] :=
array[
( 1, 2)::t,
( 3, 4)::t,
( 5, null)::t,
(null, 7)::t,
(null, null)::t
];
row t;
n int := 0;
begin
for row in (
select a, b
from jsonb_populate_recordset(null::t, array_of_objects)
)
loop
n := n + 1;
rows_1[n] := row;
end loop;
n := 0;
for row in (
select a, b
from jsonb_to_recordset(array_of_objects)
as on_the_fly(a int, b int)
)
loop
n := n + 1;
rows_2[n] := row;
end loop;
assert
(rows_1 = expected_rows) and
(rows_2 = expected_rows),
'unexpected';
end;
$body$;
The same considerations apply here as do for to jsonb_to_record()
if the target record
data type has a non-primitive field. The jsonb_to_recordset()
syntax variant therefore has no practical advantage over the jsonb_populate_recordset()
variant.