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

On Tue, Jun 24, 2014 at 10:43 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
> > On 06/23/2014 07:34 PM, Tom Lane wrote:
> >> I'm not following your comment about 9.3. The json[b]_to_record[set]
> >> functions are new in 9.4, which is what makes me feel it's not too
> >> late to redefine their behavior. But changing behavior of stuff that
> >> was in 9.3 seems a lot more debatable.
>
> > This problem is also manifest in json_populate_recordset, which also
> > uses the function in question, and is in 9.3:
>
> Ah, I see the problem.
>
> Here is a first cut suggestion:
>
> * Get rid of the use_json_as_text flag argument for the new functions.
> In json_populate_record(set), ignore its value and deprecate using it.
> (The fact that it already had a default makes that easier.) The
> behavior should always be as below.
>
Agreed. This simplifies the interface of the existing functions and will
need a mention in the release notes of 9.3.

> * For nested json objects, we'll spit those out in json textual format,
> which means they'll successfully convert to either text or json/jsonb.
> Compared to the old behavior of json_populate_recordset, this just means
> that we don't throw an error anymore regardless of the flag value,
> which seems ok (though maybe not something to backpatch into 9.3).
>
* 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.
>
Just a question (lack of coffee): do those two points implicitly mean that
we do not parse the nested json objects, pass them as simple text to the
hash table, and bypass the creation of fresh hash tables with lex_level > 1
in populate_recordset_object_start.
--
Michael