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

Merlin Moncure <mmoncure(at)gmail(dot)com> writes:
> On Mon, Jun 23, 2014 at 8:43 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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

Yeah, that's the default behavior, with use_json_as_text false.
However, consider what happens with use_json_as_text true:

regression=# select * from json_populate_recordset(null::foo, '[{"a": 1,
"b": [1,2,3]},{"a": 1, "b": [1,2,3]}]', true);
ERROR: missing "]" in array dimensions

That case is certainly useless, but suppose somebody had done

regression=# create type foo2 as(a int, b json);
CREATE TYPE
regression=# select * from json_populate_recordset(null::foo2, '[{"a": 1,
"b": [1,2,3]},{"a": 1, "b": [1,2,3]}]', true);
a | b
---+---------
1 | [1,2,3]
1 | [1,2,3]
(2 rows)

or even just

regression=# create type foo3 as(a int, b text);
CREATE TYPE
regression=# select * from json_populate_recordset(null::foo3, '[{"a": 1,
"b": [1,2,3]},{"a": 1, "b": [1,2,3]}]', true);
a | b
---+---------
1 | [1,2,3]
1 | [1,2,3]
(2 rows)

Since these cases work and do something arguably useful, I doubt we
can break them.

However, I don't see anything wrong with changing the behavior in
cases that currently throw an error, since presumably no application
is depending on them. Perhaps Andrew's comment about looking at the
target type info yields a way forward, ie, we could output in SQL-array
format if the target is an array, or in JSON-array format if the target
is json. Multiply-nested cases might be a pain to get right though.

regards, tom lane