Contents
json_populate_recordset()
A function expanding a JSON object array to a set of rows
json_populate_recordset()
is a system function expanding the top-level JSON array of objects to a set of rows.
json_populate_recordset()
was added in PostgreSQL 9.3.
Usage
json_populate_recordset (base
anyelemen
t,from_json
json
) → setofanyelement
The base
parameter must be a composite type whose column names correspond to JSON fields.
Change history
- PostgreSQL 9.3
- added (commit a570c98d)
Examples
Basic usage example for json_populate_recordset()
:
postgres=# CREATE TYPE foobar AS (id INT, val TEXT); CREATE TYPE postgres=# SELECT * FROM json_populate_recordset(NULL::foobar, '[{"id":1,"val":"foo"}, {"id":2,"val":"bar"}]'); id | val ----+----- 1 | foo 2 | bar (2 rows)
NULL
values will be emitted if the provided JSON object does not contain a field matching the expected column name:
postgres=# SELECT id IS NULL, id, val IS NULL, val FROM json_populate_recordset(NULL::foobar, '[{"a":1,"val":"foo"}, {"id":2,"b":"bar"}]'); ?column? | id | ?column? | val ----------+----+----------+----- t | | f | foo f | 2 | t | (2 rows)
An ERROR will be raised if the JSON object contains values which cannot be cast to the expected column data type:
postgres=# SELECT * FROM json_populate_recordset(NULL::foobar, '[{"id":"baz","val":"foo"}, {"id":2,"val":"bar"}]'); ERROR: invalid input syntax for type integer: "baz"
References
- PostgreSQL documentation: JSON Processing Functions
Categories
See also
jsonb_populate_recordset(), json_populate_record(), json_to_recordset()