Re: [HACKERS] BUG #10728: json_to_recordset with nested json objects NULLs columns - Mailing list pgsql-bugs

From Michael Paquier
Subject Re: [HACKERS] BUG #10728: json_to_recordset with nested json objects NULLs columns
Date
Msg-id CAB7nPqSfxZj2qU155tzpvQ7yDWaNwL9jOuXUBqEf7aR_7TLwyw@mail.gmail.com
Whole thread Raw
In response to Re: [HACKERS] BUG #10728: json_to_recordset with nested json objects NULLs columns  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-bugs



On Tue, Jun 24, 2014 at 10:43 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Andrew Dunstan <andrew@dunslane.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

pgsql-bugs by date:

Previous
From: Tom Lane
Date:
Subject: Re: [HACKERS] BUG #10728: json_to_recordset with nested json objects NULLs columns
Next
From: Michael Paquier
Date:
Subject: Re: [HACKERS] BUG #10728: json_to_recordset with nested json objects NULLs columns