Re: [BUGS] BUG #10728: json_to_recordset with nested json objects NULLs columns

On Mon, Jun 23, 2014 at 8:43 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> * Nested json arrays are a bit more problematic.  What I'd ideally like
> is to spit them out in a form that would be successfully parsable as a SQL
> array of the appropriate element type.  Unfortunately, I think that that
> ship has sailed because json_populate_recordset failed to do that in 9.3.
> What we should probably do is define this the same as the nested object
> case, ie, we spit it out in *json* array format, meaning you can insert it
> into a text or json/jsonb field of the result record.  Maybe sometime in
> the future we can add a json-array-to-SQL-array converter function, but
> these functions won't do that.

Not quite following your logic here.  9.3 gave an error for an
internally nested array:

postgres=# create type foo as(a int, b int[]);
postgres=# select * from json_populate_recordset(null::foo, '[{"a": 1,
"b": [1,2,3]},{"a": 1, "b": [1,2,3]}]');
ERROR:  cannot call json_populate_recordset on a nested object

With your proposal this would still fail?  TBH, I'd rather this
function fail as above than implement a behavior we couldn't take back
later.

merlin