How do I define a record type for JSON in postgres
postgresql
Solution
The 2nd notice is the one of interest in the doc as it explains how missing fields/values are handled
Note: In json_populate_record, json_populate_recordset, json_to_record and json_to_recordset, type coercion from the JSON is "best effort" and may not result in desired values for some types. JSON keys are matched to identical column names in the target row type. JSON fields that do not appear in the target row type will be omitted from the output, and target columns that do not match any JSON field will simply be NULL.
`json_populate_recordset` maps the name of the json object to the column name in the table given as first argument.
create table public.test (a int, b text);
select * from json_populate_recordset(null::public.test, '[{"a":1,"b":"b2"},{"a":3,"b":"b4"}]');
a | b
---+----
1 | b2
3 | b4
(2 rows)
--Wrong column name:
select * from json_populate_recordset(null::public.test, '[{"a":1,"c":"c2"},{"a":3,"c":"c4"}]');
a | b
---+---
1 |
3 |
(2 rows)
--Wrong datatype:
select * from json_populate_recordset(null::public.test, '[{"a":1.1,"b":22},{"a":3.1,"b":44}]');
ERROR: invalid input syntax for integer: "1.1"
Alternatively, instead of using the column name/type from an existing table, you can define the columns on the fly
select * from json_to_recordset('[{"a":1,"b":"foo"},{"a":"2","c":"bar"}]') as x(a int, b text);
a | b
---+-----
1 | foo
2 |
(2 rows)
--> note that default type cast occurs ("2" is mapped to 2), missing fields are ignored (b, in second record) as well as fields not defined (c)
Problem
Looking at postgres documentation for JSON functions (https://www.postgresql.org/docs/9.6/static/functions-json.html), there is a section I don't understand about expanding a JSON object into a set of rows. The docs give a sample use of this function: `json_populate_recordset(base anyelement, from_json json)` as `select * from json_populate_recordset(null::myrowtype, '[{"a":1,"b":2},{"a":3,"b":4}]')` But I'm not sure what that first argument `(null::myrowtype)` is -- a table definition? The description of this function is: Expands the outermost array of objects in from_json to a set of rows whose columns match the record type defined by base (see note below). None of the notes at the bottom seemed relevant. I'm hoping for a better explanation with sample code to understand it all.