Thread: Re: [BUGS] BUG #10728: json_to_recordset with nested json objects NULLs columns
Michael Paquier <michael.paquier@gmail.com> writes:
> Digging more into that, I have found the issue and a fix for it. It happens
> that populate_recordset_object_start, which is used to initialize the
> process for the population of the record, is taken *each* time a json
> object is found, re-creating every time the hash table for the parsing
> process, hence removing from PopulateRecordsetState all the entries already
> parsed and creating the problem reported by Matti. The fix I am proposing
> to fix this issue is rather simple: simply bypass the creation of the hash
> table if lex_level > 1 as we are in presence of a nested object and rely on
> the existing hash table.
Yes, this code is clearly not handling the nested-objects case correctly.
I had written a fix more or less equivalent to yours last night.
However, it seems to me that these functions (json[b]_to_record[set]) are
handling the nested-json-objects case in a fairly brain-dead fashion to
start with. I would like to propose that we should think about getting
rid of the use_json_as_text flag arguments altogether. What purpose do
they serve? If we're going to the trouble of parsing the nested JSON
objects anyway, why don't we just reconstruct from that data?
(IOW, we probably actually should have nested hash tables in this case.
I suspect that the current bug arose from incompletely-written logic
to do it like that.)
Since we've already decided to force an initdb for 9.4beta2, it's not
quite too late to revisit this API, and I think it needs revisiting.
regards, tom lane
Re: [BUGS] BUG #10728: json_to_recordset with nested json objects NULLs columns
From
Merlin Moncure
Date:
On Mon, Jun 23, 2014 at 10:43 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Michael Paquier <michael.paquier@gmail.com> writes: >> Digging more into that, I have found the issue and a fix for it. It happens >> that populate_recordset_object_start, which is used to initialize the >> process for the population of the record, is taken *each* time a json >> object is found, re-creating every time the hash table for the parsing >> process, hence removing from PopulateRecordsetState all the entries already >> parsed and creating the problem reported by Matti. The fix I am proposing >> to fix this issue is rather simple: simply bypass the creation of the hash >> table if lex_level > 1 as we are in presence of a nested object and rely on >> the existing hash table. > > Yes, this code is clearly not handling the nested-objects case correctly. > I had written a fix more or less equivalent to yours last night. > > However, it seems to me that these functions (json[b]_to_record[set]) are > handling the nested-json-objects case in a fairly brain-dead fashion to > start with. I would like to propose that we should think about getting > rid of the use_json_as_text flag arguments altogether. What purpose do > they serve? If we're going to the trouble of parsing the nested JSON > objects anyway, why don't we just reconstruct from that data? I think they should be removed. (I called this out in the feature level review: http://www.postgresql.org/message-id/CAHyXU0wqadCJk7MMkeARuuY05VrD=AXDn6wDceMtuWo5p4CUiA@mail.gmail.com). AIUI, the flag was introduced as a workaround to try and deal with mapping nested structures. Text variant 'json' flags have had them. merlin
Re: [BUGS] BUG #10728: json_to_recordset with nested json objects NULLs columns
From
Andrew Dunstan
Date:
On 06/23/2014 11:43 AM, Tom Lane wrote: > Michael Paquier <michael.paquier@gmail.com> writes: >> Digging more into that, I have found the issue and a fix for it. It happens >> that populate_recordset_object_start, which is used to initialize the >> process for the population of the record, is taken *each* time a json >> object is found, re-creating every time the hash table for the parsing >> process, hence removing from PopulateRecordsetState all the entries already >> parsed and creating the problem reported by Matti. The fix I am proposing >> to fix this issue is rather simple: simply bypass the creation of the hash >> table if lex_level > 1 as we are in presence of a nested object and rely on >> the existing hash table. > Yes, this code is clearly not handling the nested-objects case correctly. > I had written a fix more or less equivalent to yours last night. > > However, it seems to me that these functions (json[b]_to_record[set]) are > handling the nested-json-objects case in a fairly brain-dead fashion to > start with. I would like to propose that we should think about getting > rid of the use_json_as_text flag arguments altogether. What purpose do > they serve? If we're going to the trouble of parsing the nested JSON > objects anyway, why don't we just reconstruct from that data? > > (IOW, we probably actually should have nested hash tables in this case. > I suspect that the current bug arose from incompletely-written logic > to do it like that.) > > Since we've already decided to force an initdb for 9.4beta2, it's not > quite too late to revisit this API, and I think it needs revisiting. > > Looks like we have some problems in this whole area, not just the new function, so we need to fix 9.3 also :-( IIRC, originally, the intention was to disallow nested json objects, but the use_json_as_text was put in as a possibly less drastic possibility. If we get rid of it our only recourse is to error out if we encounter nested json. I was probably remiss in not considering the likelihood of a json target field. I currently don't have lots of time to devote to this, sadly, but Michael's patch looks like a good minimal fix. cheers andrew
Andrew Dunstan <andrew@dunslane.net> writes:
> On 06/23/2014 11:43 AM, Tom Lane wrote:
>> However, it seems to me that these functions (json[b]_to_record[set]) are
>> handling the nested-json-objects case in a fairly brain-dead fashion to
>> start with. I would like to propose that we should think about getting
>> rid of the use_json_as_text flag arguments altogether. What purpose do
>> they serve? If we're going to the trouble of parsing the nested JSON
>> objects anyway, why don't we just reconstruct from that data?
> Looks like we have some problems in this whole area, not just the new
> function, so we need to fix 9.3 also :-(
> IIRC, originally, the intention was to disallow nested json objects, but
> the use_json_as_text was put in as a possibly less drastic possibility.
> If we get rid of it our only recourse is to error out if we encounter
> nested json. I was probably remiss in not considering the likelihood of
> a json target field.
> I currently don't have lots of time to devote to this, sadly, but
> Michael's patch looks like a good minimal fix.
I can spend some time on it over the next couple of days. I take it you
don't have a problem with the concept of doing recursive processing,
as long as it doesn't add much complication?
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.
regards, tom lane
Re: [BUGS] BUG #10728: json_to_recordset with nested json objects NULLs columns
From
Andrew Dunstan
Date:
On 06/23/2014 07:34 PM, Tom Lane wrote:
> Andrew Dunstan <andrew@dunslane.net> writes:
>> On 06/23/2014 11:43 AM, Tom Lane wrote:
>>> However, it seems to me that these functions (json[b]_to_record[set]) are
>>> handling the nested-json-objects case in a fairly brain-dead fashion to
>>> start with. I would like to propose that we should think about getting
>>> rid of the use_json_as_text flag arguments altogether. What purpose do
>>> they serve? If we're going to the trouble of parsing the nested JSON
>>> objects anyway, why don't we just reconstruct from that data?
>> Looks like we have some problems in this whole area, not just the new
>> function, so we need to fix 9.3 also :-(
>> IIRC, originally, the intention was to disallow nested json objects, but
>> the use_json_as_text was put in as a possibly less drastic possibility.
>> If we get rid of it our only recourse is to error out if we encounter
>> nested json. I was probably remiss in not considering the likelihood of
>> a json target field.
>> I currently don't have lots of time to devote to this, sadly, but
>> Michael's patch looks like a good minimal fix.
> I can spend some time on it over the next couple of days. I take it you
> don't have a problem with the concept of doing recursive processing,
> as long as it doesn't add much complication?
>
> 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:
andrew=# create type yyy as (a int, b json, c int, d int); CREATE TYPE andrew=# select * from
json_populate_recordset(null::yyy,'[ {"a":2,"c":3,"b":{"z":4}, "d":6} ] ',true) x; a | b | c | d
---+---------+---+--- | {"z":4} | | 6 (1 row)
I don't have any problem with recursive processing, but I'm not sure I
understand how it will work. If you post a patch I will be able to look
it over, though.
cheers
andrew
>
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.
* 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.
From a user's standpoint this just boils down to (a) fix the bug with
mishandling of the hash tables, and (b) get rid of the gratuitous
error report.
regards, tom lane
Re: [BUGS] BUG #10728: json_to_recordset with nested json objects NULLs columns
From
Michael Paquier
Date:
On Tue, Jun 24, 2014 at 10:43 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
-- >> I'm not following your comment about 9.3. The json[b]_to_record[set]Ah, I see the problem.
>> 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:
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
Re: [BUGS] BUG #10728: json_to_recordset with nested json objects NULLs columns
From
Michael Paquier
Date:
On Tue, Jun 24, 2014 at 9:34 AM, Andrew Dunstan <andrew@dunslane.net> wrote:
-- This problem is also manifest in json_populate_recordset, which also uses the function in question, and is in 9.3:
On 06/23/2014 07:34 PM, Tom Lane wrote:Andrew Dunstan <andrew@dunslane.net> writes:On 06/23/2014 11:43 AM, Tom Lane wrote:I can spend some time on it over the next couple of days. I take it youHowever, it seems to me that these functions (json[b]_to_record[set]) areLooks like we have some problems in this whole area, not just the new
handling the nested-json-objects case in a fairly brain-dead fashion to
start with. I would like to propose that we should think about getting
rid of the use_json_as_text flag arguments altogether. What purpose do
they serve? If we're going to the trouble of parsing the nested JSON
objects anyway, why don't we just reconstruct from that data?
function, so we need to fix 9.3 also :-(
IIRC, originally, the intention was to disallow nested json objects, but
the use_json_as_text was put in as a possibly less drastic possibility.
If we get rid of it our only recourse is to error out if we encounter
nested json. I was probably remiss in not considering the likelihood of
a json target field.
I currently don't have lots of time to devote to this, sadly, but
Michael's patch looks like a good minimal fix.
don't have a problem with the concept of doing recursive processing,
as long as it doesn't add much complication?
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.
andrew=# create type yyy as (a int, b json, c int, d int);
CREATE TYPE
andrew=# select * from json_populate_recordset(null::yyy, '[',true) x;
{"a":2,"c":3,"b":{"z":4}, "d":6}
]
a | b | c | d
---+---------+---+---
| {"z":4} | | 6
(1 row)
Yeah, the somewhat-backpatchable patch I sent fixes that as well. I wouldn't mind writing a more complete patch with new regression tests and tutti-quanti for 9.3 and 9.4master, but it seems that Tom is already on it.
Michael
Re: [BUGS] BUG #10728: json_to_recordset with nested json objects NULLs columns
From
Merlin Moncure
Date:
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
Re: [BUGS] BUG #10728: json_to_recordset with nested json objects NULLs columns
From
Andrew Dunstan
Date:
On 06/23/2014 09:43 PM, Tom Lane 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. > > * 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. > > >From a user's standpoint this just boils down to (a) fix the bug with > mishandling of the hash tables, and (b) get rid of the gratuitous > error report. > > The big problem is that we have been ignoring the result type when constructing the hash, even though the info is available. There is some sense in this in that the field might not even be present in the result type. And it works except for structured types like records, arrays and json. Even if we don't have a nested value, the functions will do the wrong thing for a scalar string destined for a json field (it will be de-escaped, when it should not be). w.r.t. json arrays, I think you're chasing a chimera, since they are heterogenous, unlike SQL arrays. w.r.t. the use_json_as_text argument, yes, it has a default, but the default is false. Ignoring it seems to be more than just deprecating it. I agree it's a mess, though :-( cheers andrew
Re: [BUGS] BUG #10728: json_to_recordset with nested json objects NULLs columns
From
Merlin Moncure
Date:
On Tue, Jun 24, 2014 at 9:08 AM, Andrew Dunstan <andrew@dunslane.net> wrote: > w.r.t. json arrays, I think you're chasing a chimera, since they are > heterogenous, unlike SQL arrays. But, there are many useful cases where the json is known to be well formed, right? Or do you mean that the difficulties stem from simply validating the type? Basically, I'm wondering if SELECT to_json(foo_t[]) is ever going to be able to be reversed by: SELECT array(json[b]_populate_recordset(null::foo_t[]), '...'::json[b]) ...where foo_t is some arbitrarily complex nested type. even simpler (although not necessarily faster) would be: SELECT from_json(null::foo_t[], ',,,'); or even SELECT '...'::foo_t[]::json::foo_t[]; My basic gripe with the json[b] APIs is that there is no convenient deserialization reverse of to_json. Tom's proposal AIUI, in particular having internal json arrays force to json, would foreclose the last two cases from ever being possible. merlin
Merlin Moncure <mmoncure@gmail.com> writes:
> 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
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
Andrew Dunstan <andrew@dunslane.net> writes:
>>> I currently don't have lots of time to devote to this, sadly, but
>>> Michael's patch looks like a good minimal fix.
> This problem is also manifest in json_populate_recordset, which also
> uses the function in question, and is in 9.3:
I've pushed this patch back through 9.3, along with a fix to ensure that
json_populate_record destroys the hashtable it creates. I want to do some
more work on this code, but this much is indubitably a bug fix.
regards, tom lane
I wrote:
> * 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.
Here's a draft patch that gets rid of the use_json_as_text flag argument
altogether for all the functions newly added in 9.4. The pre-existing
json_populate_record(set) functions still have such an argument, but
it's ignored and undocumented (not that it was well documented before).
The behavior is as if the flag had been specified as true, which AFAICS
is the only useful case.
This does not do anything about the question of possibly printing
JSON arrays in SQL array syntax when the target record field is of
array type. While I think that's definitely do-able, it would require
some significant rewriting of the code, which is probably not something
to be doing at this point for 9.4. I think it's something we can add
as a new feature in 9.5 or later, because it changes the behavior only
in cases that are currently guaranteed-to-fail, so there's not really
any backwards compatibility problem IMO.
BTW, depending on how hard we want to work, one could imagine also
printing JSON objects in SQL record format if the target type is record
rather than JSON, and then making this happen recursively for nested
arrays/composites. Again, any attempt to do that today would fail with
a syntax error from record_in, so there's no backwards compatibility
problem.
regards, tom lane
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index f4754588ae7594061969da7f4eb76302a33e040b..f8701e1771fea33bf17b568c86732580a7557d0f 100644
*** a/doc/src/sgml/func.sgml
--- b/doc/src/sgml/func.sgml
*************** table2-mapping
*** 10560,10567 ****
</entry>
</row>
<row>
! <entry><para><literal>json_populate_record(base anyelement, from_json json, [, use_json_as_text
bool=false])</literal>
! </para><para><literal>jsonb_populate_record(base anyelement, from_json jsonb, [, use_json_as_text
bool=false])</literal>
</para></entry>
<entry><type>anyelement</type></entry>
<entry>
--- 10560,10567 ----
</entry>
</row>
<row>
! <entry><para><literal>json_populate_record(base anyelement, from_json json)</literal>
! </para><para><literal>jsonb_populate_record(base anyelement, from_json jsonb)</literal>
</para></entry>
<entry><type>anyelement</type></entry>
<entry>
*************** table2-mapping
*** 10579,10586 ****
</entry>
</row>
<row>
! <entry><para><literal>json_populate_recordset(base anyelement, from_json json, [, use_json_as_text
bool=false])</literal>
! </para><para><literal>jsonb_populate_recordset(base anyelement, from_json jsonb, [, use_json_as_text
bool=false])</literal>
</para></entry>
<entry><type>setof anyelement</type></entry>
<entry>
--- 10579,10586 ----
</entry>
</row>
<row>
! <entry><para><literal>json_populate_recordset(base anyelement, from_json json)</literal>
! </para><para><literal>jsonb_populate_recordset(base anyelement, from_json jsonb)</literal>
</para></entry>
<entry><type>setof anyelement</type></entry>
<entry>
*************** table2-mapping
*** 10653,10670 ****
<entry><literal>number</literal></entry>
</row>
<row>
! <entry><para><literal>json_to_record(json [, nested_as_text bool=false])</literal>
! </para><para><literal>jsonb_to_record(jsonb [, nested_as_text bool=false])</literal>
</para></entry>
<entry><type>record</type></entry>
<entry>
Builds an arbitrary record from a JSON object (see note below). As
with all functions returning <type>record</>, the caller must
explicitly define the structure of the record with an <literal>AS</>
! clause. If <replaceable>nested_as_text</> is true, the function
! coerces nested complex elements to text.
</entry>
! <entry><literal>select * from json_to_record('{"a":1,"b":[1,2,3],"c":"bar"}',true) as x(a int, b text, d text)
</literal></entry>
<entry>
<programlisting>
a | b | d
--- 10653,10669 ----
<entry><literal>number</literal></entry>
</row>
<row>
! <entry><para><literal>json_to_record(json)</literal>
! </para><para><literal>jsonb_to_record(jsonb)</literal>
</para></entry>
<entry><type>record</type></entry>
<entry>
Builds an arbitrary record from a JSON object (see note below). As
with all functions returning <type>record</>, the caller must
explicitly define the structure of the record with an <literal>AS</>
! clause.
</entry>
! <entry><literal>select * from json_to_record('{"a":1,"b":[1,2,3],"c":"bar"}') as x(a int, b text, d text)
</literal></entry>
<entry>
<programlisting>
a | b | d
*************** table2-mapping
*** 10674,10691 ****
</entry>
</row>
<row>
! <entry><para><literal>json_to_recordset(json [, nested_as_text bool=false])</literal>
! </para><para><literal>jsonb_to_recordset(jsonb [, nested_as_text bool=false])</literal>
</para></entry>
<entry><type>setof record</type></entry>
<entry>
Builds an arbitrary set of records from a JSON array of objects (see
note below). As with all functions returning <type>record</>, the
caller must explicitly define the structure of the record with
! an <literal>AS</> clause. <replaceable>nested_as_text</> works as
! with <function>json_to_record</>.
</entry>
! <entry><literal>select * from json_to_recordset('[{"a":1,"b":"foo"},{"a":"2","c":"bar"}]',true) as x(a int, b
text);</literal></entry>
<entry>
<programlisting>
a | b
--- 10673,10689 ----
</entry>
</row>
<row>
! <entry><para><literal>json_to_recordset(json)</literal>
! </para><para><literal>jsonb_to_recordset(jsonb)</literal>
</para></entry>
<entry><type>setof record</type></entry>
<entry>
Builds an arbitrary set of records from a JSON array of objects (see
note below). As with all functions returning <type>record</>, the
caller must explicitly define the structure of the record with
! an <literal>AS</> clause.
</entry>
! <entry><literal>select * from json_to_recordset('[{"a":1,"b":"foo"},{"a":"2","c":"bar"}]') as x(a int, b
text);</literal></entry>
<entry>
<programlisting>
a | b
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 793a92b76aafdeba75bc43dad88e82ededac251b..1bde175d4566ae9ac36f783c3d06c7ef73b52fcc 100644
*** a/src/backend/catalog/system_views.sql
--- b/src/backend/catalog/system_views.sql
*************** CREATE OR REPLACE FUNCTION
*** 817,854 ****
pg_start_backup(label text, fast boolean DEFAULT false)
RETURNS pg_lsn STRICT VOLATILE LANGUAGE internal AS 'pg_start_backup';
CREATE OR REPLACE FUNCTION
json_populate_record(base anyelement, from_json json, use_json_as_text boolean DEFAULT false)
RETURNS anyelement LANGUAGE internal STABLE AS 'json_populate_record';
CREATE OR REPLACE FUNCTION
json_populate_recordset(base anyelement, from_json json, use_json_as_text boolean DEFAULT false)
RETURNS SETOF anyelement LANGUAGE internal STABLE ROWS 100 AS 'json_populate_recordset';
- CREATE OR REPLACE FUNCTION
- jsonb_populate_record(base anyelement, from_json jsonb, use_json_as_text boolean DEFAULT false)
- RETURNS anyelement LANGUAGE internal STABLE AS 'jsonb_populate_record';
-
- CREATE OR REPLACE FUNCTION
- jsonb_populate_recordset(base anyelement, from_json jsonb, use_json_as_text boolean DEFAULT false)
- RETURNS SETOF anyelement LANGUAGE internal STABLE ROWS 100 AS 'jsonb_populate_recordset';
-
- CREATE OR REPLACE FUNCTION
- json_to_record(from_json json, nested_as_text boolean DEFAULT false)
- RETURNS record LANGUAGE internal STABLE AS 'json_to_record';
-
- CREATE OR REPLACE FUNCTION
- json_to_recordset(from_json json, nested_as_text boolean DEFAULT false)
- RETURNS SETOF record LANGUAGE internal STABLE ROWS 100 AS 'json_to_recordset';
-
- CREATE OR REPLACE FUNCTION
- jsonb_to_record(from_json jsonb, nested_as_text boolean DEFAULT false)
- RETURNS record LANGUAGE internal STABLE AS 'jsonb_to_record';
-
- CREATE OR REPLACE FUNCTION
- jsonb_to_recordset(from_json jsonb, nested_as_text boolean DEFAULT false)
- RETURNS SETOF record LANGUAGE internal STABLE ROWS 100 AS 'jsonb_to_recordset';
-
CREATE OR REPLACE FUNCTION pg_logical_slot_get_changes(
IN slot_name name, IN upto_lsn pg_lsn, IN upto_nchanges int, VARIADIC options text[] DEFAULT '{}',
OUT location pg_lsn, OUT xid xid, OUT data text)
--- 817,832 ----
pg_start_backup(label text, fast boolean DEFAULT false)
RETURNS pg_lsn STRICT VOLATILE LANGUAGE internal AS 'pg_start_backup';
+ -- legacy definition for compatibility with 9.3
CREATE OR REPLACE FUNCTION
json_populate_record(base anyelement, from_json json, use_json_as_text boolean DEFAULT false)
RETURNS anyelement LANGUAGE internal STABLE AS 'json_populate_record';
+ -- legacy definition for compatibility with 9.3
CREATE OR REPLACE FUNCTION
json_populate_recordset(base anyelement, from_json json, use_json_as_text boolean DEFAULT false)
RETURNS SETOF anyelement LANGUAGE internal STABLE ROWS 100 AS 'json_populate_recordset';
CREATE OR REPLACE FUNCTION pg_logical_slot_get_changes(
IN slot_name name, IN upto_lsn pg_lsn, IN upto_nchanges int, VARIADIC options text[] DEFAULT '{}',
OUT location pg_lsn, OUT xid xid, OUT data text)
diff --git a/src/backend/utils/adt/jsonfuncs.c b/src/backend/utils/adt/jsonfuncs.c
index bd1241b620fa17a61a45a441da48f9d769585d31..6c16a953dd3fb9c0bcb903a4b17c1fb75bd2a807 100644
*** a/src/backend/utils/adt/jsonfuncs.c
--- b/src/backend/utils/adt/jsonfuncs.c
*************** static void elements_array_element_end(v
*** 85,92 ****
static void elements_scalar(void *state, char *token, JsonTokenType tokentype);
/* turn a json object into a hash table */
! static HTAB *get_json_object_as_hash(text *json, const char *funcname,
! bool use_json_as_text);
/* common worker for populate_record and to_record */
static Datum populate_record_worker(FunctionCallInfo fcinfo, const char *funcname,
--- 85,91 ----
static void elements_scalar(void *state, char *token, JsonTokenType tokentype);
/* turn a json object into a hash table */
! static HTAB *get_json_object_as_hash(text *json, const char *funcname);
/* common worker for populate_record and to_record */
static Datum populate_record_worker(FunctionCallInfo fcinfo, const char *funcname,
*************** typedef struct JhashState
*** 198,204 ****
HTAB *hash;
char *saved_scalar;
char *save_json_start;
- bool use_json_as_text;
} JHashState;
/* hashtable element */
--- 197,202 ----
*************** typedef struct PopulateRecordsetState
*** 235,241 ****
HTAB *json_hash;
char *saved_scalar;
char *save_json_start;
- bool use_json_as_text;
Tuplestorestate *tuple_store;
TupleDesc ret_tdesc;
HeapTupleHeader rec;
--- 233,238 ----
*************** populate_record_worker(FunctionCallInfo
*** 1989,1995 ****
Oid jtype = get_fn_expr_argtype(fcinfo->flinfo, json_arg_num);
text *json;
Jsonb *jb = NULL;
- bool use_json_as_text;
HTAB *json_hash = NULL;
HeapTupleHeader rec = NULL;
Oid tupType = InvalidOid;
--- 1986,1991 ----
*************** populate_record_worker(FunctionCallInfo
*** 2005,2013 ****
Assert(jtype == JSONOID || jtype == JSONBOID);
- use_json_as_text = PG_ARGISNULL(json_arg_num + 1) ? false :
- PG_GETARG_BOOL(json_arg_num + 1);
-
if (have_record_arg)
{
Oid argtype = get_fn_expr_argtype(fcinfo->flinfo, 0);
--- 2001,2006 ----
*************** populate_record_worker(FunctionCallInfo
*** 2065,2071 ****
/* just get the text */
json = PG_GETARG_TEXT_P(json_arg_num);
! json_hash = get_json_object_as_hash(json, funcname, use_json_as_text);
/*
* if the input json is empty, we can only skip the rest if we were
--- 2058,2064 ----
/* just get the text */
json = PG_GETARG_TEXT_P(json_arg_num);
! json_hash = get_json_object_as_hash(json, funcname);
/*
* if the input json is empty, we can only skip the rest if we were
*************** populate_record_worker(FunctionCallInfo
*** 2227,2236 ****
else if (v->type == jbvNumeric)
s = DatumGetCString(DirectFunctionCall1(numeric_out,
PointerGetDatum(v->val.numeric)));
- else if (!use_json_as_text)
- ereport(ERROR,
- (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
- errmsg("cannot populate with a nested object unless use_json_as_text is true")));
else if (v->type == jbvBinary)
s = JsonbToCString(NULL, (JsonbContainer *) v->val.binary.data, v->val.binary.len);
else
--- 2220,2225 ----
*************** populate_record_worker(FunctionCallInfo
*** 2258,2272 ****
* get_json_object_as_hash
*
* decompose a json object into a hash table.
- *
- * Currently doesn't allow anything but a flat object. Should this
- * change?
- *
- * funcname argument allows caller to pass in its name for use in
- * error messages.
*/
static HTAB *
! get_json_object_as_hash(text *json, const char *funcname, bool use_json_as_text)
{
HASHCTL ctl;
HTAB *tab;
--- 2247,2255 ----
* get_json_object_as_hash
*
* decompose a json object into a hash table.
*/
static HTAB *
! get_json_object_as_hash(text *json, const char *funcname)
{
HASHCTL ctl;
HTAB *tab;
*************** get_json_object_as_hash(text *json, cons
*** 2289,2295 ****
state->function_name = funcname;
state->hash = tab;
state->lex = lex;
- state->use_json_as_text = use_json_as_text;
sem->semstate = (void *) state;
sem->array_start = hash_array_start;
--- 2272,2277 ----
*************** hash_object_field_start(void *state, cha
*** 2313,2323 ****
if (_state->lex->token_type == JSON_TOKEN_ARRAY_START ||
_state->lex->token_type == JSON_TOKEN_OBJECT_START)
{
! if (!_state->use_json_as_text)
! ereport(ERROR,
! (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
! errmsg("cannot call %s on a nested object",
! _state->function_name)));
_state->save_json_start = _state->lex->token_start;
}
else
--- 2295,2301 ----
if (_state->lex->token_type == JSON_TOKEN_ARRAY_START ||
_state->lex->token_type == JSON_TOKEN_OBJECT_START)
{
! /* remember start position of the whole text of the subobject */
_state->save_json_start = _state->lex->token_start;
}
else
*************** make_row_from_rec_and_jsonb(Jsonb *eleme
*** 2535,2544 ****
else if (v->type == jbvNumeric)
s = DatumGetCString(DirectFunctionCall1(numeric_out,
PointerGetDatum(v->val.numeric)));
- else if (!state->use_json_as_text)
- ereport(ERROR,
- (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
- errmsg("cannot populate with a nested object unless use_json_as_text is true")));
else if (v->type == jbvBinary)
s = JsonbToCString(NULL, (JsonbContainer *) v->val.binary.data, v->val.binary.len);
else
--- 2513,2518 ----
*************** populate_recordset_worker(FunctionCallIn
*** 2565,2571 ****
{
int json_arg_num = have_record_arg ? 1 : 0;
Oid jtype = get_fn_expr_argtype(fcinfo->flinfo, json_arg_num);
- bool use_json_as_text;
ReturnSetInfo *rsi;
MemoryContext old_cxt;
Oid tupType;
--- 2539,2544 ----
*************** populate_recordset_worker(FunctionCallIn
*** 2576,2583 ****
int ncolumns;
PopulateRecordsetState *state;
- use_json_as_text = PG_ARGISNULL(json_arg_num + 1) ? false : PG_GETARG_BOOL(json_arg_num + 1);
-
if (have_record_arg)
{
Oid argtype = get_fn_expr_argtype(fcinfo->flinfo, 0);
--- 2549,2554 ----
*************** populate_recordset_worker(FunctionCallIn
*** 2667,2673 ****
state->function_name = funcname;
state->my_extra = my_extra;
state->rec = rec;
- state->use_json_as_text = use_json_as_text;
state->fn_mcxt = fcinfo->flinfo->fn_mcxt;
if (jtype == JSONOID)
--- 2638,2643 ----
*************** populate_recordset_object_start(void *st
*** 2749,2764 ****
errmsg("cannot call %s on an object",
_state->function_name)));
! /* Nested objects, if allowed, require no special processing */
if (lex_level > 1)
- {
- if (!_state->use_json_as_text)
- ereport(ERROR,
- (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
- errmsg("cannot call %s with nested objects",
- _state->function_name)));
return;
- }
/* Object at level 1: set up a new hash table for this object */
memset(&ctl, 0, sizeof(ctl));
--- 2719,2727 ----
errmsg("cannot call %s on an object",
_state->function_name)));
! /* Nested objects require no special processing */
if (lex_level > 1)
return;
/* Object at level 1: set up a new hash table for this object */
memset(&ctl, 0, sizeof(ctl));
*************** populate_recordset_array_element_start(v
*** 2903,2915 ****
static void
populate_recordset_array_start(void *state)
{
! PopulateRecordsetState *_state = (PopulateRecordsetState *) state;
!
! if (_state->lex->lex_level != 0 && !_state->use_json_as_text)
! ereport(ERROR,
! (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
! errmsg("cannot call %s with nested arrays",
! _state->function_name)));
}
static void
--- 2866,2872 ----
static void
populate_recordset_array_start(void *state)
{
! /* nothing to do */
}
static void
*************** populate_recordset_object_field_start(vo
*** 2938,2948 ****
if (_state->lex->token_type == JSON_TOKEN_ARRAY_START ||
_state->lex->token_type == JSON_TOKEN_OBJECT_START)
{
- if (!_state->use_json_as_text)
- ereport(ERROR,
- (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
- errmsg("cannot call %s on a nested object",
- _state->function_name)));
_state->save_json_start = _state->lex->token_start;
}
else
--- 2895,2900 ----
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index 762ce6ca09c16c32f113130fd6a47b33621ffbff..43d11e26e9f47438266f792acb461799a2a6ed94 100644
*** a/src/include/catalog/pg_proc.h
--- b/src/include/catalog/pg_proc.h
*************** DATA(insert OID = 3960 ( json_populate_
*** 4250,4258 ****
DESCR("get record fields from a json object");
DATA(insert OID = 3961 ( json_populate_recordset PGNSP PGUID 12 1 100 0 0 f f f f f t s 3 0 2283 "2283 114 16"
_null__null_ _null_ _null_ json_populate_recordset _null_ _null_ _null_ ));
DESCR("get set of records with fields from a json array of objects");
! DATA(insert OID = 3204 ( json_to_record PGNSP PGUID 12 1 0 0 0 f f f f f f s 2 0 2249 "114 16" _null_ _null_
_null__null_ json_to_record _null_ _null_ _null_ ));
DESCR("get record fields from a json object");
! DATA(insert OID = 3205 ( json_to_recordset PGNSP PGUID 12 1 100 0 0 f f f f f t s 2 0 2249 "114 16" _null_ _null_
_null__null_ json_to_recordset _null_ _null_ _null_ ));
DESCR("get set of records with fields from a json array of objects");
DATA(insert OID = 3968 ( json_typeof PGNSP PGUID 12 1 0 0 0 f f f f t f i 1 0 25 "114" _null_ _null_
_null__null_ json_typeof _null_ _null_ _null_ ));
DESCR("get the type of a json value");
--- 4250,4258 ----
DESCR("get record fields from a json object");
DATA(insert OID = 3961 ( json_populate_recordset PGNSP PGUID 12 1 100 0 0 f f f f f t s 3 0 2283 "2283 114 16"
_null__null_ _null_ _null_ json_populate_recordset _null_ _null_ _null_ ));
DESCR("get set of records with fields from a json array of objects");
! DATA(insert OID = 3204 ( json_to_record PGNSP PGUID 12 1 0 0 0 f f f f t f s 1 0 2249 "114" _null_ _null_
_null__null_ json_to_record _null_ _null_ _null_ ));
DESCR("get record fields from a json object");
! DATA(insert OID = 3205 ( json_to_recordset PGNSP PGUID 12 1 100 0 0 f f f f f t s 1 0 2249 "114" _null_
_null__null_ _null_ json_to_recordset _null_ _null_ _null_ ));
DESCR("get set of records with fields from a json array of objects");
DATA(insert OID = 3968 ( json_typeof PGNSP PGUID 12 1 0 0 0 f f f f t f i 1 0 25 "114" _null_ _null_
_null__null_ json_typeof _null_ _null_ _null_ ));
DESCR("get the type of a json value");
*************** DATA(insert OID = 3208 ( jsonb_each
*** 4609,4621 ****
DESCR("key value pairs of a jsonb object");
DATA(insert OID = 3932 ( jsonb_each_text PGNSP PGUID 12 1 100 0 0 f f f f t t i 1 0 2249 "3802"
"{3802,25,25}""{i,o,o}" "{from_json,key,value}" _null_ jsonb_each_text _null_ _null_ _null_ ));
DESCR("key value pairs of a jsonb object");
! DATA(insert OID = 3209 ( jsonb_populate_record PGNSP PGUID 12 1 0 0 0 f f f f f f s 3 0 2283 "2283 3802 16"
_null__null_ _null_ _null_ jsonb_populate_record _null_ _null_ _null_ ));
DESCR("get record fields from a jsonb object");
! DATA(insert OID = 3475 ( jsonb_populate_recordset PGNSP PGUID 12 1 100 0 0 f f f f f t s 3 0 2283 "2283 3802 16"
_null__null_ _null_ _null_ jsonb_populate_recordset _null_ _null_ _null_ ));
DESCR("get set of records with fields from a jsonb array of objects");
! DATA(insert OID = 3490 ( jsonb_to_record PGNSP PGUID 12 1 0 0 0 f f f f f f s 2 0 2249 "3802 16" _null_ _null_
_null__null_ jsonb_to_record _null_ _null_ _null_ ));
DESCR("get record fields from a json object");
! DATA(insert OID = 3491 ( jsonb_to_recordset PGNSP PGUID 12 1 100 0 0 f f f f f t s 2 0 2249 "3802 16" _null_ _null_
_null__null_ jsonb_to_recordset _null_ _null_ _null_ ));
DESCR("get set of records with fields from a json array of objects");
DATA(insert OID = 3210 ( jsonb_typeof PGNSP PGUID 12 1 0 0 0 f f f f t f i 1 0 25 "3802" _null_
_null__null_ _null_ jsonb_typeof _null_ _null_ _null_ ));
DESCR("get the type of a jsonb value");
--- 4609,4621 ----
DESCR("key value pairs of a jsonb object");
DATA(insert OID = 3932 ( jsonb_each_text PGNSP PGUID 12 1 100 0 0 f f f f t t i 1 0 2249 "3802"
"{3802,25,25}""{i,o,o}" "{from_json,key,value}" _null_ jsonb_each_text _null_ _null_ _null_ ));
DESCR("key value pairs of a jsonb object");
! DATA(insert OID = 3209 ( jsonb_populate_record PGNSP PGUID 12 1 0 0 0 f f f f f f s 2 0 2283 "2283 3802" _null_
_null__null_ _null_ jsonb_populate_record _null_ _null_ _null_ ));
DESCR("get record fields from a jsonb object");
! DATA(insert OID = 3475 ( jsonb_populate_recordset PGNSP PGUID 12 1 100 0 0 f f f f f t s 2 0 2283 "2283 3802"
_null__null_ _null_ _null_ jsonb_populate_recordset _null_ _null_ _null_ ));
DESCR("get set of records with fields from a jsonb array of objects");
! DATA(insert OID = 3490 ( jsonb_to_record PGNSP PGUID 12 1 0 0 0 f f f f t f s 1 0 2249 "3802" _null_
_null__null_ _null_ jsonb_to_record _null_ _null_ _null_ ));
DESCR("get record fields from a json object");
! DATA(insert OID = 3491 ( jsonb_to_recordset PGNSP PGUID 12 1 100 0 0 f f f f f t s 1 0 2249 "3802" _null_
_null__null_ _null_ jsonb_to_recordset _null_ _null_ _null_ ));
DESCR("get set of records with fields from a json array of objects");
DATA(insert OID = 3210 ( jsonb_typeof PGNSP PGUID 12 1 0 0 0 f f f f t f i 1 0 25 "3802" _null_
_null__null_ _null_ jsonb_typeof _null_ _null_ _null_ ));
DESCR("get the type of a jsonb value");
diff --git a/src/test/regress/expected/json.out b/src/test/regress/expected/json.out
index d1e32a19a52891dee8e2f5aaf500b40006fc11b2..99036a23ca865d20539aeb9db88636a17a00d645 100644
*** a/src/test/regress/expected/json.out
--- b/src/test/regress/expected/json.out
*************** select * from json_populate_record(row('
*** 943,1020 ****
blurfl | 3 | Mon Dec 31 15:30:56 2012
(1 row)
! select * from json_populate_record(null::jpop,'{"a":"blurfl","x":43.2}', true) q;
a | b | c
--------+---+---
blurfl | |
(1 row)
! select * from json_populate_record(row('x',3,'2012-12-31 15:30:56')::jpop,'{"a":"blurfl","x":43.2}', true) q;
a | b | c
--------+---+--------------------------
blurfl | 3 | Mon Dec 31 15:30:56 2012
(1 row)
! select * from json_populate_record(null::jpop,'{"a":[100,200,false],"x":43.2}', true) q;
a | b | c
-----------------+---+---
[100,200,false] | |
(1 row)
! select * from json_populate_record(row('x',3,'2012-12-31 15:30:56')::jpop,'{"a":[100,200,false],"x":43.2}', true) q;
a | b | c
-----------------+---+--------------------------
[100,200,false] | 3 | Mon Dec 31 15:30:56 2012
(1 row)
! select * from json_populate_record(row('x',3,'2012-12-31 15:30:56')::jpop,'{"c":[100,200,false],"x":43.2}', true) q;
ERROR: invalid input syntax for type timestamp: "[100,200,false]"
-- populate_recordset
! select * from json_populate_recordset(null::jpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]',false)
q;
a | b | c
--------+---+--------------------------
blurfl | |
| 3 | Fri Jan 20 10:42:53 2012
(2 rows)
! select * from json_populate_recordset(row('def',99,null)::jpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20
10:42:53"}]',false)q;
a | b | c
--------+----+--------------------------
blurfl | 99 |
def | 3 | Fri Jan 20 10:42:53 2012
(2 rows)
! select * from json_populate_recordset(null::jpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]',true)
q;
a | b | c
--------+---+--------------------------
blurfl | |
| 3 | Fri Jan 20 10:42:53 2012
(2 rows)
! select * from json_populate_recordset(row('def',99,null)::jpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20
10:42:53"}]',true)q;
a | b | c
--------+----+--------------------------
blurfl | 99 |
def | 3 | Fri Jan 20 10:42:53 2012
(2 rows)
! select * from
json_populate_recordset(row('def',99,null)::jpop,'[{"a":[100,200,300],"x":43.2},{"a":{"z":true},"b":3,"c":"2012-01-20
10:42:53"}]',true)q;
a | b | c
---------------+----+--------------------------
[100,200,300] | 99 |
{"z":true} | 3 | Fri Jan 20 10:42:53 2012
(2 rows)
! select * from
json_populate_recordset(row('def',99,null)::jpop,'[{"c":[100,200,300],"x":43.2},{"a":{"z":true},"b":3,"c":"2012-01-20
10:42:53"}]',true)q;
ERROR: invalid input syntax for type timestamp: "[100,200,300]"
create type jpop2 as (a int, b json, c int, d int);
! select * from json_populate_recordset(null::jpop2, '[{"a":2,"c":3,"b":{"z":4},"d":6}]',true) q;
a | b | c | d
---+---------+---+---
2 | {"z":4} | 3 | 6
(1 row)
- -- using the default use_json_as_text argument
select * from json_populate_recordset(null::jpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]') q;
a | b | c
--------+---+--------------------------
--- 943,1019 ----
blurfl | 3 | Mon Dec 31 15:30:56 2012
(1 row)
! select * from json_populate_record(null::jpop,'{"a":"blurfl","x":43.2}') q;
a | b | c
--------+---+---
blurfl | |
(1 row)
! select * from json_populate_record(row('x',3,'2012-12-31 15:30:56')::jpop,'{"a":"blurfl","x":43.2}') q;
a | b | c
--------+---+--------------------------
blurfl | 3 | Mon Dec 31 15:30:56 2012
(1 row)
! select * from json_populate_record(null::jpop,'{"a":[100,200,false],"x":43.2}') q;
a | b | c
-----------------+---+---
[100,200,false] | |
(1 row)
! select * from json_populate_record(row('x',3,'2012-12-31 15:30:56')::jpop,'{"a":[100,200,false],"x":43.2}') q;
a | b | c
-----------------+---+--------------------------
[100,200,false] | 3 | Mon Dec 31 15:30:56 2012
(1 row)
! select * from json_populate_record(row('x',3,'2012-12-31 15:30:56')::jpop,'{"c":[100,200,false],"x":43.2}') q;
ERROR: invalid input syntax for type timestamp: "[100,200,false]"
-- populate_recordset
! select * from json_populate_recordset(null::jpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]') q;
a | b | c
--------+---+--------------------------
blurfl | |
| 3 | Fri Jan 20 10:42:53 2012
(2 rows)
! select * from json_populate_recordset(row('def',99,null)::jpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20
10:42:53"}]')q;
a | b | c
--------+----+--------------------------
blurfl | 99 |
def | 3 | Fri Jan 20 10:42:53 2012
(2 rows)
! select * from json_populate_recordset(null::jpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]') q;
a | b | c
--------+---+--------------------------
blurfl | |
| 3 | Fri Jan 20 10:42:53 2012
(2 rows)
! select * from json_populate_recordset(row('def',99,null)::jpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20
10:42:53"}]')q;
a | b | c
--------+----+--------------------------
blurfl | 99 |
def | 3 | Fri Jan 20 10:42:53 2012
(2 rows)
! select * from
json_populate_recordset(row('def',99,null)::jpop,'[{"a":[100,200,300],"x":43.2},{"a":{"z":true},"b":3,"c":"2012-01-20
10:42:53"}]')q;
a | b | c
---------------+----+--------------------------
[100,200,300] | 99 |
{"z":true} | 3 | Fri Jan 20 10:42:53 2012
(2 rows)
! select * from
json_populate_recordset(row('def',99,null)::jpop,'[{"c":[100,200,300],"x":43.2},{"a":{"z":true},"b":3,"c":"2012-01-20
10:42:53"}]')q;
ERROR: invalid input syntax for type timestamp: "[100,200,300]"
create type jpop2 as (a int, b json, c int, d int);
! select * from json_populate_recordset(null::jpop2, '[{"a":2,"c":3,"b":{"z":4},"d":6}]') q;
a | b | c | d
---+---------+---+---
2 | {"z":4} | 3 | 6
(1 row)
select * from json_populate_recordset(null::jpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]') q;
a | b | c
--------+---+--------------------------
*************** select * from json_populate_recordset(ro
*** 1030,1038 ****
(2 rows)
select * from
json_populate_recordset(row('def',99,null)::jpop,'[{"a":[100,200,300],"x":43.2},{"a":{"z":true},"b":3,"c":"2012-01-20
10:42:53"}]')q;
! ERROR: cannot call json_populate_recordset on a nested object
! select * from
json_populate_recordset(row('def',99,null)::jpop,'[{"c":[100,200,300],"x":43.2},{"a":{"z":true},"b":3,"c":"2012-01-20
10:42:53"}]')q;
! ERROR: cannot call json_populate_recordset on a nested object
-- handling of unicode surrogate pairs
select json '{ "a": "\ud83d\ude04\ud83d\udc36" }' -> 'a' as correct_in_utf8;
correct_in_utf8
--- 1029,1040 ----
(2 rows)
select * from
json_populate_recordset(row('def',99,null)::jpop,'[{"a":[100,200,300],"x":43.2},{"a":{"z":true},"b":3,"c":"2012-01-20
10:42:53"}]')q;
! a | b | c
! ---------------+----+--------------------------
! [100,200,300] | 99 |
! {"z":true} | 3 | Fri Jan 20 10:42:53 2012
! (2 rows)
!
-- handling of unicode surrogate pairs
select json '{ "a": "\ud83d\ude04\ud83d\udc36" }' -> 'a' as correct_in_utf8;
correct_in_utf8
*************** ERROR: null value not allowed for objec
*** 1215,1228 ****
select json_object('{a,b,"","d e f"}','{1,2,3,"a b c"}');
ERROR: empty value not allowed for object key
-- json_to_record and json_to_recordset
! select * from json_to_record('{"a":1,"b":"foo","c":"bar"}',true)
as x(a int, b text, d text);
a | b | d
---+-----+---
1 | foo |
(1 row)
! select * from json_to_recordset('[{"a":1,"b":"foo","d":false},{"a":2,"b":"bar","c":true}]',false)
as x(a int, b text, c boolean);
a | b | c
---+-----+---
--- 1217,1230 ----
select json_object('{a,b,"","d e f"}','{1,2,3,"a b c"}');
ERROR: empty value not allowed for object key
-- json_to_record and json_to_recordset
! select * from json_to_record('{"a":1,"b":"foo","c":"bar"}')
as x(a int, b text, d text);
a | b | d
---+-----+---
1 | foo |
(1 row)
! select * from json_to_recordset('[{"a":1,"b":"foo","d":false},{"a":2,"b":"bar","c":true}]')
as x(a int, b text, c boolean);
a | b | c
---+-----+---
*************** select * from json_to_recordset('[{"a":1
*** 1230,1236 ****
2 | bar | t
(2 rows)
! select * from json_to_recordset('[{"a":1,"b":{"d":"foo"},"c":true},{"a":2,"c":false,"b":{"d":"bar"}}]', true)
as x(a int, b json, c boolean);
a | b | c
---+-------------+---
--- 1232,1238 ----
2 | bar | t
(2 rows)
! select * from json_to_recordset('[{"a":1,"b":{"d":"foo"},"c":true},{"a":2,"c":false,"b":{"d":"bar"}}]')
as x(a int, b json, c boolean);
a | b | c
---+-------------+---
diff --git a/src/test/regress/expected/json_1.out b/src/test/regress/expected/json_1.out
index 93cb693b2fbdec2bab654fbfdaf0e26d50cde51e..e74aabec8a1d3016d7fd483d9bbed30f7ed5deb9 100644
*** a/src/test/regress/expected/json_1.out
--- b/src/test/regress/expected/json_1.out
*************** select * from json_populate_record(row('
*** 943,1020 ****
blurfl | 3 | Mon Dec 31 15:30:56 2012
(1 row)
! select * from json_populate_record(null::jpop,'{"a":"blurfl","x":43.2}', true) q;
a | b | c
--------+---+---
blurfl | |
(1 row)
! select * from json_populate_record(row('x',3,'2012-12-31 15:30:56')::jpop,'{"a":"blurfl","x":43.2}', true) q;
a | b | c
--------+---+--------------------------
blurfl | 3 | Mon Dec 31 15:30:56 2012
(1 row)
! select * from json_populate_record(null::jpop,'{"a":[100,200,false],"x":43.2}', true) q;
a | b | c
-----------------+---+---
[100,200,false] | |
(1 row)
! select * from json_populate_record(row('x',3,'2012-12-31 15:30:56')::jpop,'{"a":[100,200,false],"x":43.2}', true) q;
a | b | c
-----------------+---+--------------------------
[100,200,false] | 3 | Mon Dec 31 15:30:56 2012
(1 row)
! select * from json_populate_record(row('x',3,'2012-12-31 15:30:56')::jpop,'{"c":[100,200,false],"x":43.2}', true) q;
ERROR: invalid input syntax for type timestamp: "[100,200,false]"
-- populate_recordset
! select * from json_populate_recordset(null::jpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]',false)
q;
a | b | c
--------+---+--------------------------
blurfl | |
| 3 | Fri Jan 20 10:42:53 2012
(2 rows)
! select * from json_populate_recordset(row('def',99,null)::jpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20
10:42:53"}]',false)q;
a | b | c
--------+----+--------------------------
blurfl | 99 |
def | 3 | Fri Jan 20 10:42:53 2012
(2 rows)
! select * from json_populate_recordset(null::jpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]',true)
q;
a | b | c
--------+---+--------------------------
blurfl | |
| 3 | Fri Jan 20 10:42:53 2012
(2 rows)
! select * from json_populate_recordset(row('def',99,null)::jpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20
10:42:53"}]',true)q;
a | b | c
--------+----+--------------------------
blurfl | 99 |
def | 3 | Fri Jan 20 10:42:53 2012
(2 rows)
! select * from
json_populate_recordset(row('def',99,null)::jpop,'[{"a":[100,200,300],"x":43.2},{"a":{"z":true},"b":3,"c":"2012-01-20
10:42:53"}]',true)q;
a | b | c
---------------+----+--------------------------
[100,200,300] | 99 |
{"z":true} | 3 | Fri Jan 20 10:42:53 2012
(2 rows)
! select * from
json_populate_recordset(row('def',99,null)::jpop,'[{"c":[100,200,300],"x":43.2},{"a":{"z":true},"b":3,"c":"2012-01-20
10:42:53"}]',true)q;
ERROR: invalid input syntax for type timestamp: "[100,200,300]"
create type jpop2 as (a int, b json, c int, d int);
! select * from json_populate_recordset(null::jpop2, '[{"a":2,"c":3,"b":{"z":4},"d":6}]',true) q;
a | b | c | d
---+---------+---+---
2 | {"z":4} | 3 | 6
(1 row)
- -- using the default use_json_as_text argument
select * from json_populate_recordset(null::jpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]') q;
a | b | c
--------+---+--------------------------
--- 943,1019 ----
blurfl | 3 | Mon Dec 31 15:30:56 2012
(1 row)
! select * from json_populate_record(null::jpop,'{"a":"blurfl","x":43.2}') q;
a | b | c
--------+---+---
blurfl | |
(1 row)
! select * from json_populate_record(row('x',3,'2012-12-31 15:30:56')::jpop,'{"a":"blurfl","x":43.2}') q;
a | b | c
--------+---+--------------------------
blurfl | 3 | Mon Dec 31 15:30:56 2012
(1 row)
! select * from json_populate_record(null::jpop,'{"a":[100,200,false],"x":43.2}') q;
a | b | c
-----------------+---+---
[100,200,false] | |
(1 row)
! select * from json_populate_record(row('x',3,'2012-12-31 15:30:56')::jpop,'{"a":[100,200,false],"x":43.2}') q;
a | b | c
-----------------+---+--------------------------
[100,200,false] | 3 | Mon Dec 31 15:30:56 2012
(1 row)
! select * from json_populate_record(row('x',3,'2012-12-31 15:30:56')::jpop,'{"c":[100,200,false],"x":43.2}') q;
ERROR: invalid input syntax for type timestamp: "[100,200,false]"
-- populate_recordset
! select * from json_populate_recordset(null::jpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]') q;
a | b | c
--------+---+--------------------------
blurfl | |
| 3 | Fri Jan 20 10:42:53 2012
(2 rows)
! select * from json_populate_recordset(row('def',99,null)::jpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20
10:42:53"}]')q;
a | b | c
--------+----+--------------------------
blurfl | 99 |
def | 3 | Fri Jan 20 10:42:53 2012
(2 rows)
! select * from json_populate_recordset(null::jpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]') q;
a | b | c
--------+---+--------------------------
blurfl | |
| 3 | Fri Jan 20 10:42:53 2012
(2 rows)
! select * from json_populate_recordset(row('def',99,null)::jpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20
10:42:53"}]')q;
a | b | c
--------+----+--------------------------
blurfl | 99 |
def | 3 | Fri Jan 20 10:42:53 2012
(2 rows)
! select * from
json_populate_recordset(row('def',99,null)::jpop,'[{"a":[100,200,300],"x":43.2},{"a":{"z":true},"b":3,"c":"2012-01-20
10:42:53"}]')q;
a | b | c
---------------+----+--------------------------
[100,200,300] | 99 |
{"z":true} | 3 | Fri Jan 20 10:42:53 2012
(2 rows)
! select * from
json_populate_recordset(row('def',99,null)::jpop,'[{"c":[100,200,300],"x":43.2},{"a":{"z":true},"b":3,"c":"2012-01-20
10:42:53"}]')q;
ERROR: invalid input syntax for type timestamp: "[100,200,300]"
create type jpop2 as (a int, b json, c int, d int);
! select * from json_populate_recordset(null::jpop2, '[{"a":2,"c":3,"b":{"z":4},"d":6}]') q;
a | b | c | d
---+---------+---+---
2 | {"z":4} | 3 | 6
(1 row)
select * from json_populate_recordset(null::jpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]') q;
a | b | c
--------+---+--------------------------
*************** select * from json_populate_recordset(ro
*** 1030,1038 ****
(2 rows)
select * from
json_populate_recordset(row('def',99,null)::jpop,'[{"a":[100,200,300],"x":43.2},{"a":{"z":true},"b":3,"c":"2012-01-20
10:42:53"}]')q;
! ERROR: cannot call json_populate_recordset on a nested object
! select * from
json_populate_recordset(row('def',99,null)::jpop,'[{"c":[100,200,300],"x":43.2},{"a":{"z":true},"b":3,"c":"2012-01-20
10:42:53"}]')q;
! ERROR: cannot call json_populate_recordset on a nested object
-- handling of unicode surrogate pairs
select json '{ "a": "\ud83d\ude04\ud83d\udc36" }' -> 'a' as correct_in_utf8;
ERROR: invalid input syntax for type json
--- 1029,1040 ----
(2 rows)
select * from
json_populate_recordset(row('def',99,null)::jpop,'[{"a":[100,200,300],"x":43.2},{"a":{"z":true},"b":3,"c":"2012-01-20
10:42:53"}]')q;
! a | b | c
! ---------------+----+--------------------------
! [100,200,300] | 99 |
! {"z":true} | 3 | Fri Jan 20 10:42:53 2012
! (2 rows)
!
-- handling of unicode surrogate pairs
select json '{ "a": "\ud83d\ude04\ud83d\udc36" }' -> 'a' as correct_in_utf8;
ERROR: invalid input syntax for type json
*************** ERROR: null value not allowed for objec
*** 1211,1224 ****
select json_object('{a,b,"","d e f"}','{1,2,3,"a b c"}');
ERROR: empty value not allowed for object key
-- json_to_record and json_to_recordset
! select * from json_to_record('{"a":1,"b":"foo","c":"bar"}',true)
as x(a int, b text, d text);
a | b | d
---+-----+---
1 | foo |
(1 row)
! select * from json_to_recordset('[{"a":1,"b":"foo","d":false},{"a":2,"b":"bar","c":true}]',false)
as x(a int, b text, c boolean);
a | b | c
---+-----+---
--- 1213,1226 ----
select json_object('{a,b,"","d e f"}','{1,2,3,"a b c"}');
ERROR: empty value not allowed for object key
-- json_to_record and json_to_recordset
! select * from json_to_record('{"a":1,"b":"foo","c":"bar"}')
as x(a int, b text, d text);
a | b | d
---+-----+---
1 | foo |
(1 row)
! select * from json_to_recordset('[{"a":1,"b":"foo","d":false},{"a":2,"b":"bar","c":true}]')
as x(a int, b text, c boolean);
a | b | c
---+-----+---
*************** select * from json_to_recordset('[{"a":1
*** 1226,1232 ****
2 | bar | t
(2 rows)
! select * from json_to_recordset('[{"a":1,"b":{"d":"foo"},"c":true},{"a":2,"c":false,"b":{"d":"bar"}}]', true)
as x(a int, b json, c boolean);
a | b | c
---+-------------+---
--- 1228,1234 ----
2 | bar | t
(2 rows)
! select * from json_to_recordset('[{"a":1,"b":{"d":"foo"},"c":true},{"a":2,"c":false,"b":{"d":"bar"}}]')
as x(a int, b json, c boolean);
a | b | c
---+-------------+---
diff --git a/src/test/regress/expected/jsonb.out b/src/test/regress/expected/jsonb.out
index 6bc789de293df8c187f6be5ac4bf8c3b7f5b06c4..c1cc1a9dbec4c2f28a4794f953c293be2b056d66 100644
*** a/src/test/regress/expected/jsonb.out
--- b/src/test/regress/expected/jsonb.out
*************** SELECT * FROM jsonb_populate_record(row(
*** 1297,1367 ****
blurfl | 3 | Mon Dec 31 15:30:56 2012
(1 row)
! SELECT * FROM jsonb_populate_record(NULL::jbpop,'{"a":"blurfl","x":43.2}', true) q;
a | b | c
--------+---+---
blurfl | |
(1 row)
! SELECT * FROM jsonb_populate_record(row('x',3,'2012-12-31 15:30:56')::jbpop,'{"a":"blurfl","x":43.2}', true) q;
a | b | c
--------+---+--------------------------
blurfl | 3 | Mon Dec 31 15:30:56 2012
(1 row)
! SELECT * FROM jsonb_populate_record(NULL::jbpop,'{"a":[100,200,false],"x":43.2}', true) q;
a | b | c
-------------------+---+---
[100, 200, false] | |
(1 row)
! SELECT * FROM jsonb_populate_record(row('x',3,'2012-12-31 15:30:56')::jbpop,'{"a":[100,200,false],"x":43.2}', true)
q;
a | b | c
-------------------+---+--------------------------
[100, 200, false] | 3 | Mon Dec 31 15:30:56 2012
(1 row)
! SELECT * FROM jsonb_populate_record(row('x',3,'2012-12-31 15:30:56')::jbpop,'{"c":[100,200,false],"x":43.2}', true)
q;
ERROR: invalid input syntax for type timestamp: "[100, 200, false]"
-- populate_recordset
! SELECT * FROM jsonb_populate_recordset(NULL::jbpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20
10:42:53"}]',false)q;
a | b | c
--------+---+--------------------------
blurfl | |
| 3 | Fri Jan 20 10:42:53 2012
(2 rows)
! SELECT * FROM jsonb_populate_recordset(row('def',99,NULL)::jbpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20
10:42:53"}]',false)q;
a | b | c
--------+----+--------------------------
blurfl | 99 |
def | 3 | Fri Jan 20 10:42:53 2012
(2 rows)
! SELECT * FROM jsonb_populate_recordset(NULL::jbpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20
10:42:53"}]',true)q;
a | b | c
--------+---+--------------------------
blurfl | |
| 3 | Fri Jan 20 10:42:53 2012
(2 rows)
! SELECT * FROM jsonb_populate_recordset(row('def',99,NULL)::jbpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20
10:42:53"}]',true)q;
a | b | c
--------+----+--------------------------
blurfl | 99 |
def | 3 | Fri Jan 20 10:42:53 2012
(2 rows)
! SELECT * FROM
jsonb_populate_recordset(row('def',99,NULL)::jbpop,'[{"a":[100,200,300],"x":43.2},{"a":{"z":true},"b":3,"c":"2012-01-20
10:42:53"}]',true)q;
a | b | c
-----------------+----+--------------------------
[100, 200, 300] | 99 |
{"z": true} | 3 | Fri Jan 20 10:42:53 2012
(2 rows)
! SELECT * FROM
jsonb_populate_recordset(row('def',99,NULL)::jbpop,'[{"c":[100,200,300],"x":43.2},{"a":{"z":true},"b":3,"c":"2012-01-20
10:42:53"}]',true)q;
ERROR: invalid input syntax for type timestamp: "[100, 200, 300]"
- -- using the default use_json_as_text argument
SELECT * FROM jsonb_populate_recordset(NULL::jbpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]') q;
a | b | c
--------+---+--------------------------
--- 1297,1366 ----
blurfl | 3 | Mon Dec 31 15:30:56 2012
(1 row)
! SELECT * FROM jsonb_populate_record(NULL::jbpop,'{"a":"blurfl","x":43.2}') q;
a | b | c
--------+---+---
blurfl | |
(1 row)
! SELECT * FROM jsonb_populate_record(row('x',3,'2012-12-31 15:30:56')::jbpop,'{"a":"blurfl","x":43.2}') q;
a | b | c
--------+---+--------------------------
blurfl | 3 | Mon Dec 31 15:30:56 2012
(1 row)
! SELECT * FROM jsonb_populate_record(NULL::jbpop,'{"a":[100,200,false],"x":43.2}') q;
a | b | c
-------------------+---+---
[100, 200, false] | |
(1 row)
! SELECT * FROM jsonb_populate_record(row('x',3,'2012-12-31 15:30:56')::jbpop,'{"a":[100,200,false],"x":43.2}') q;
a | b | c
-------------------+---+--------------------------
[100, 200, false] | 3 | Mon Dec 31 15:30:56 2012
(1 row)
! SELECT * FROM jsonb_populate_record(row('x',3,'2012-12-31 15:30:56')::jbpop,'{"c":[100,200,false],"x":43.2}') q;
ERROR: invalid input syntax for type timestamp: "[100, 200, false]"
-- populate_recordset
! SELECT * FROM jsonb_populate_recordset(NULL::jbpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]') q;
a | b | c
--------+---+--------------------------
blurfl | |
| 3 | Fri Jan 20 10:42:53 2012
(2 rows)
! SELECT * FROM jsonb_populate_recordset(row('def',99,NULL)::jbpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20
10:42:53"}]')q;
a | b | c
--------+----+--------------------------
blurfl | 99 |
def | 3 | Fri Jan 20 10:42:53 2012
(2 rows)
! SELECT * FROM jsonb_populate_recordset(NULL::jbpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]') q;
a | b | c
--------+---+--------------------------
blurfl | |
| 3 | Fri Jan 20 10:42:53 2012
(2 rows)
! SELECT * FROM jsonb_populate_recordset(row('def',99,NULL)::jbpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20
10:42:53"}]')q;
a | b | c
--------+----+--------------------------
blurfl | 99 |
def | 3 | Fri Jan 20 10:42:53 2012
(2 rows)
! SELECT * FROM
jsonb_populate_recordset(row('def',99,NULL)::jbpop,'[{"a":[100,200,300],"x":43.2},{"a":{"z":true},"b":3,"c":"2012-01-20
10:42:53"}]')q;
a | b | c
-----------------+----+--------------------------
[100, 200, 300] | 99 |
{"z": true} | 3 | Fri Jan 20 10:42:53 2012
(2 rows)
! SELECT * FROM
jsonb_populate_recordset(row('def',99,NULL)::jbpop,'[{"c":[100,200,300],"x":43.2},{"a":{"z":true},"b":3,"c":"2012-01-20
10:42:53"}]')q;
ERROR: invalid input syntax for type timestamp: "[100, 200, 300]"
SELECT * FROM jsonb_populate_recordset(NULL::jbpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]') q;
a | b | c
--------+---+--------------------------
*************** SELECT * FROM jsonb_populate_recordset(r
*** 1377,1385 ****
(2 rows)
SELECT * FROM
jsonb_populate_recordset(row('def',99,NULL)::jbpop,'[{"a":[100,200,300],"x":43.2},{"a":{"z":true},"b":3,"c":"2012-01-20
10:42:53"}]')q;
! ERROR: cannot populate with a nested object unless use_json_as_text is true
! SELECT * FROM
jsonb_populate_recordset(row('def',99,NULL)::jbpop,'[{"c":[100,200,300],"x":43.2},{"a":{"z":true},"b":3,"c":"2012-01-20
10:42:53"}]')q;
! ERROR: cannot populate with a nested object unless use_json_as_text is true
-- handling of unicode surrogate pairs
SELECT octet_length((jsonb '{ "a": "\ud83d\ude04\ud83d\udc36" }' -> 'a')::text) AS correct_in_utf8;
correct_in_utf8
--- 1376,1387 ----
(2 rows)
SELECT * FROM
jsonb_populate_recordset(row('def',99,NULL)::jbpop,'[{"a":[100,200,300],"x":43.2},{"a":{"z":true},"b":3,"c":"2012-01-20
10:42:53"}]')q;
! a | b | c
! -----------------+----+--------------------------
! [100, 200, 300] | 99 |
! {"z": true} | 3 | Fri Jan 20 10:42:53 2012
! (2 rows)
!
-- handling of unicode surrogate pairs
SELECT octet_length((jsonb '{ "a": "\ud83d\ude04\ud83d\udc36" }' -> 'a')::text) AS correct_in_utf8;
correct_in_utf8
*************** SELECT jsonb '{ "a": "null \u0000 escap
*** 1431,1444 ****
(1 row)
-- jsonb_to_record and jsonb_to_recordset
! select * from jsonb_to_record('{"a":1,"b":"foo","c":"bar"}',true)
as x(a int, b text, d text);
a | b | d
---+-----+---
1 | foo |
(1 row)
! select * from jsonb_to_recordset('[{"a":1,"b":"foo","d":false},{"a":2,"b":"bar","c":true}]',false)
as x(a int, b text, c boolean);
a | b | c
---+-----+---
--- 1433,1446 ----
(1 row)
-- jsonb_to_record and jsonb_to_recordset
! select * from jsonb_to_record('{"a":1,"b":"foo","c":"bar"}')
as x(a int, b text, d text);
a | b | d
---+-----+---
1 | foo |
(1 row)
! select * from jsonb_to_recordset('[{"a":1,"b":"foo","d":false},{"a":2,"b":"bar","c":true}]')
as x(a int, b text, c boolean);
a | b | c
---+-----+---
diff --git a/src/test/regress/expected/jsonb_1.out b/src/test/regress/expected/jsonb_1.out
index 0c861d3b2940db051cf0c9040c7f86fe735fa92d..249f5758442d6277322bc9809d5dcc5d2ce06c5f 100644
*** a/src/test/regress/expected/jsonb_1.out
--- b/src/test/regress/expected/jsonb_1.out
*************** SELECT * FROM jsonb_populate_record(row(
*** 1297,1367 ****
blurfl | 3 | Mon Dec 31 15:30:56 2012
(1 row)
! SELECT * FROM jsonb_populate_record(NULL::jbpop,'{"a":"blurfl","x":43.2}', true) q;
a | b | c
--------+---+---
blurfl | |
(1 row)
! SELECT * FROM jsonb_populate_record(row('x',3,'2012-12-31 15:30:56')::jbpop,'{"a":"blurfl","x":43.2}', true) q;
a | b | c
--------+---+--------------------------
blurfl | 3 | Mon Dec 31 15:30:56 2012
(1 row)
! SELECT * FROM jsonb_populate_record(NULL::jbpop,'{"a":[100,200,false],"x":43.2}', true) q;
a | b | c
-------------------+---+---
[100, 200, false] | |
(1 row)
! SELECT * FROM jsonb_populate_record(row('x',3,'2012-12-31 15:30:56')::jbpop,'{"a":[100,200,false],"x":43.2}', true)
q;
a | b | c
-------------------+---+--------------------------
[100, 200, false] | 3 | Mon Dec 31 15:30:56 2012
(1 row)
! SELECT * FROM jsonb_populate_record(row('x',3,'2012-12-31 15:30:56')::jbpop,'{"c":[100,200,false],"x":43.2}', true)
q;
ERROR: invalid input syntax for type timestamp: "[100, 200, false]"
-- populate_recordset
! SELECT * FROM jsonb_populate_recordset(NULL::jbpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20
10:42:53"}]',false)q;
a | b | c
--------+---+--------------------------
blurfl | |
| 3 | Fri Jan 20 10:42:53 2012
(2 rows)
! SELECT * FROM jsonb_populate_recordset(row('def',99,NULL)::jbpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20
10:42:53"}]',false)q;
a | b | c
--------+----+--------------------------
blurfl | 99 |
def | 3 | Fri Jan 20 10:42:53 2012
(2 rows)
! SELECT * FROM jsonb_populate_recordset(NULL::jbpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20
10:42:53"}]',true)q;
a | b | c
--------+---+--------------------------
blurfl | |
| 3 | Fri Jan 20 10:42:53 2012
(2 rows)
! SELECT * FROM jsonb_populate_recordset(row('def',99,NULL)::jbpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20
10:42:53"}]',true)q;
a | b | c
--------+----+--------------------------
blurfl | 99 |
def | 3 | Fri Jan 20 10:42:53 2012
(2 rows)
! SELECT * FROM
jsonb_populate_recordset(row('def',99,NULL)::jbpop,'[{"a":[100,200,300],"x":43.2},{"a":{"z":true},"b":3,"c":"2012-01-20
10:42:53"}]',true)q;
a | b | c
-----------------+----+--------------------------
[100, 200, 300] | 99 |
{"z": true} | 3 | Fri Jan 20 10:42:53 2012
(2 rows)
! SELECT * FROM
jsonb_populate_recordset(row('def',99,NULL)::jbpop,'[{"c":[100,200,300],"x":43.2},{"a":{"z":true},"b":3,"c":"2012-01-20
10:42:53"}]',true)q;
ERROR: invalid input syntax for type timestamp: "[100, 200, 300]"
- -- using the default use_json_as_text argument
SELECT * FROM jsonb_populate_recordset(NULL::jbpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]') q;
a | b | c
--------+---+--------------------------
--- 1297,1366 ----
blurfl | 3 | Mon Dec 31 15:30:56 2012
(1 row)
! SELECT * FROM jsonb_populate_record(NULL::jbpop,'{"a":"blurfl","x":43.2}') q;
a | b | c
--------+---+---
blurfl | |
(1 row)
! SELECT * FROM jsonb_populate_record(row('x',3,'2012-12-31 15:30:56')::jbpop,'{"a":"blurfl","x":43.2}') q;
a | b | c
--------+---+--------------------------
blurfl | 3 | Mon Dec 31 15:30:56 2012
(1 row)
! SELECT * FROM jsonb_populate_record(NULL::jbpop,'{"a":[100,200,false],"x":43.2}') q;
a | b | c
-------------------+---+---
[100, 200, false] | |
(1 row)
! SELECT * FROM jsonb_populate_record(row('x',3,'2012-12-31 15:30:56')::jbpop,'{"a":[100,200,false],"x":43.2}') q;
a | b | c
-------------------+---+--------------------------
[100, 200, false] | 3 | Mon Dec 31 15:30:56 2012
(1 row)
! SELECT * FROM jsonb_populate_record(row('x',3,'2012-12-31 15:30:56')::jbpop,'{"c":[100,200,false],"x":43.2}') q;
ERROR: invalid input syntax for type timestamp: "[100, 200, false]"
-- populate_recordset
! SELECT * FROM jsonb_populate_recordset(NULL::jbpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]') q;
a | b | c
--------+---+--------------------------
blurfl | |
| 3 | Fri Jan 20 10:42:53 2012
(2 rows)
! SELECT * FROM jsonb_populate_recordset(row('def',99,NULL)::jbpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20
10:42:53"}]')q;
a | b | c
--------+----+--------------------------
blurfl | 99 |
def | 3 | Fri Jan 20 10:42:53 2012
(2 rows)
! SELECT * FROM jsonb_populate_recordset(NULL::jbpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]') q;
a | b | c
--------+---+--------------------------
blurfl | |
| 3 | Fri Jan 20 10:42:53 2012
(2 rows)
! SELECT * FROM jsonb_populate_recordset(row('def',99,NULL)::jbpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20
10:42:53"}]')q;
a | b | c
--------+----+--------------------------
blurfl | 99 |
def | 3 | Fri Jan 20 10:42:53 2012
(2 rows)
! SELECT * FROM
jsonb_populate_recordset(row('def',99,NULL)::jbpop,'[{"a":[100,200,300],"x":43.2},{"a":{"z":true},"b":3,"c":"2012-01-20
10:42:53"}]')q;
a | b | c
-----------------+----+--------------------------
[100, 200, 300] | 99 |
{"z": true} | 3 | Fri Jan 20 10:42:53 2012
(2 rows)
! SELECT * FROM
jsonb_populate_recordset(row('def',99,NULL)::jbpop,'[{"c":[100,200,300],"x":43.2},{"a":{"z":true},"b":3,"c":"2012-01-20
10:42:53"}]')q;
ERROR: invalid input syntax for type timestamp: "[100, 200, 300]"
SELECT * FROM jsonb_populate_recordset(NULL::jbpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]') q;
a | b | c
--------+---+--------------------------
*************** SELECT * FROM jsonb_populate_recordset(r
*** 1377,1385 ****
(2 rows)
SELECT * FROM
jsonb_populate_recordset(row('def',99,NULL)::jbpop,'[{"a":[100,200,300],"x":43.2},{"a":{"z":true},"b":3,"c":"2012-01-20
10:42:53"}]')q;
! ERROR: cannot populate with a nested object unless use_json_as_text is true
! SELECT * FROM
jsonb_populate_recordset(row('def',99,NULL)::jbpop,'[{"c":[100,200,300],"x":43.2},{"a":{"z":true},"b":3,"c":"2012-01-20
10:42:53"}]')q;
! ERROR: cannot populate with a nested object unless use_json_as_text is true
-- handling of unicode surrogate pairs
SELECT octet_length((jsonb '{ "a": "\ud83d\ude04\ud83d\udc36" }' -> 'a')::text) AS correct_in_utf8;
ERROR: invalid input syntax for type json
--- 1376,1387 ----
(2 rows)
SELECT * FROM
jsonb_populate_recordset(row('def',99,NULL)::jbpop,'[{"a":[100,200,300],"x":43.2},{"a":{"z":true},"b":3,"c":"2012-01-20
10:42:53"}]')q;
! a | b | c
! -----------------+----+--------------------------
! [100, 200, 300] | 99 |
! {"z": true} | 3 | Fri Jan 20 10:42:53 2012
! (2 rows)
!
-- handling of unicode surrogate pairs
SELECT octet_length((jsonb '{ "a": "\ud83d\ude04\ud83d\udc36" }' -> 'a')::text) AS correct_in_utf8;
ERROR: invalid input syntax for type json
*************** SELECT jsonb '{ "a": "null \u0000 escap
*** 1431,1444 ****
(1 row)
-- jsonb_to_record and jsonb_to_recordset
! select * from jsonb_to_record('{"a":1,"b":"foo","c":"bar"}',true)
as x(a int, b text, d text);
a | b | d
---+-----+---
1 | foo |
(1 row)
! select * from jsonb_to_recordset('[{"a":1,"b":"foo","d":false},{"a":2,"b":"bar","c":true}]',false)
as x(a int, b text, c boolean);
a | b | c
---+-----+---
--- 1433,1446 ----
(1 row)
-- jsonb_to_record and jsonb_to_recordset
! select * from jsonb_to_record('{"a":1,"b":"foo","c":"bar"}')
as x(a int, b text, d text);
a | b | d
---+-----+---
1 | foo |
(1 row)
! select * from jsonb_to_recordset('[{"a":1,"b":"foo","d":false},{"a":2,"b":"bar","c":true}]')
as x(a int, b text, c boolean);
a | b | c
---+-----+---
diff --git a/src/test/regress/sql/json.sql b/src/test/regress/sql/json.sql
index bc8bb62978155f672b5ab4fafd69d7cbd74869e1..3215b61a5a81100546ed415f15ddebfb8020b3db 100644
*** a/src/test/regress/sql/json.sql
--- b/src/test/regress/sql/json.sql
*************** create type jpop as (a text, b int, c ti
*** 309,339 ****
select * from json_populate_record(null::jpop,'{"a":"blurfl","x":43.2}') q;
select * from json_populate_record(row('x',3,'2012-12-31 15:30:56')::jpop,'{"a":"blurfl","x":43.2}') q;
! select * from json_populate_record(null::jpop,'{"a":"blurfl","x":43.2}', true) q;
! select * from json_populate_record(row('x',3,'2012-12-31 15:30:56')::jpop,'{"a":"blurfl","x":43.2}', true) q;
! select * from json_populate_record(null::jpop,'{"a":[100,200,false],"x":43.2}', true) q;
! select * from json_populate_record(row('x',3,'2012-12-31 15:30:56')::jpop,'{"a":[100,200,false],"x":43.2}', true) q;
! select * from json_populate_record(row('x',3,'2012-12-31 15:30:56')::jpop,'{"c":[100,200,false],"x":43.2}', true) q;
-- populate_recordset
! select * from json_populate_recordset(null::jpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]',false)
q;
! select * from json_populate_recordset(row('def',99,null)::jpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20
10:42:53"}]',false)q;
! select * from json_populate_recordset(null::jpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]',true)
q;
! select * from json_populate_recordset(row('def',99,null)::jpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20
10:42:53"}]',true)q;
! select * from
json_populate_recordset(row('def',99,null)::jpop,'[{"a":[100,200,300],"x":43.2},{"a":{"z":true},"b":3,"c":"2012-01-20
10:42:53"}]',true)q;
! select * from
json_populate_recordset(row('def',99,null)::jpop,'[{"c":[100,200,300],"x":43.2},{"a":{"z":true},"b":3,"c":"2012-01-20
10:42:53"}]',true)q;
create type jpop2 as (a int, b json, c int, d int);
! select * from json_populate_recordset(null::jpop2, '[{"a":2,"c":3,"b":{"z":4},"d":6}]',true) q;
!
! -- using the default use_json_as_text argument
select * from json_populate_recordset(null::jpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]') q;
select * from json_populate_recordset(row('def',99,null)::jpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20
10:42:53"}]')q;
select * from
json_populate_recordset(row('def',99,null)::jpop,'[{"a":[100,200,300],"x":43.2},{"a":{"z":true},"b":3,"c":"2012-01-20
10:42:53"}]')q;
- select * from
json_populate_recordset(row('def',99,null)::jpop,'[{"c":[100,200,300],"x":43.2},{"a":{"z":true},"b":3,"c":"2012-01-20
10:42:53"}]')q;
-- handling of unicode surrogate pairs
--- 309,336 ----
select * from json_populate_record(null::jpop,'{"a":"blurfl","x":43.2}') q;
select * from json_populate_record(row('x',3,'2012-12-31 15:30:56')::jpop,'{"a":"blurfl","x":43.2}') q;
! select * from json_populate_record(null::jpop,'{"a":"blurfl","x":43.2}') q;
! select * from json_populate_record(row('x',3,'2012-12-31 15:30:56')::jpop,'{"a":"blurfl","x":43.2}') q;
! select * from json_populate_record(null::jpop,'{"a":[100,200,false],"x":43.2}') q;
! select * from json_populate_record(row('x',3,'2012-12-31 15:30:56')::jpop,'{"a":[100,200,false],"x":43.2}') q;
! select * from json_populate_record(row('x',3,'2012-12-31 15:30:56')::jpop,'{"c":[100,200,false],"x":43.2}') q;
-- populate_recordset
! select * from json_populate_recordset(null::jpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]') q;
! select * from json_populate_recordset(row('def',99,null)::jpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20
10:42:53"}]')q;
! select * from json_populate_recordset(null::jpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]') q;
! select * from json_populate_recordset(row('def',99,null)::jpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20
10:42:53"}]')q;
! select * from
json_populate_recordset(row('def',99,null)::jpop,'[{"a":[100,200,300],"x":43.2},{"a":{"z":true},"b":3,"c":"2012-01-20
10:42:53"}]')q;
! select * from
json_populate_recordset(row('def',99,null)::jpop,'[{"c":[100,200,300],"x":43.2},{"a":{"z":true},"b":3,"c":"2012-01-20
10:42:53"}]')q;
create type jpop2 as (a int, b json, c int, d int);
! select * from json_populate_recordset(null::jpop2, '[{"a":2,"c":3,"b":{"z":4},"d":6}]') q;
select * from json_populate_recordset(null::jpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]') q;
select * from json_populate_recordset(row('def',99,null)::jpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20
10:42:53"}]')q;
select * from
json_populate_recordset(row('def',99,null)::jpop,'[{"a":[100,200,300],"x":43.2},{"a":{"z":true},"b":3,"c":"2012-01-20
10:42:53"}]')q;
-- handling of unicode surrogate pairs
*************** select json_object('{a,b,"","d e f"}','{
*** 445,455 ****
-- json_to_record and json_to_recordset
! select * from json_to_record('{"a":1,"b":"foo","c":"bar"}',true)
as x(a int, b text, d text);
! select * from json_to_recordset('[{"a":1,"b":"foo","d":false},{"a":2,"b":"bar","c":true}]',false)
as x(a int, b text, c boolean);
! select * from json_to_recordset('[{"a":1,"b":{"d":"foo"},"c":true},{"a":2,"c":false,"b":{"d":"bar"}}]', true)
as x(a int, b json, c boolean);
--- 442,452 ----
-- json_to_record and json_to_recordset
! select * from json_to_record('{"a":1,"b":"foo","c":"bar"}')
as x(a int, b text, d text);
! select * from json_to_recordset('[{"a":1,"b":"foo","d":false},{"a":2,"b":"bar","c":true}]')
as x(a int, b text, c boolean);
! select * from json_to_recordset('[{"a":1,"b":{"d":"foo"},"c":true},{"a":2,"c":false,"b":{"d":"bar"}}]')
as x(a int, b json, c boolean);
diff --git a/src/test/regress/sql/jsonb.sql b/src/test/regress/sql/jsonb.sql
index 7527925b2cb1ea98a73065b0fa004b5fd78c1033..187a8e8ccc99473303dffddb624046d921d6187d 100644
*** a/src/test/regress/sql/jsonb.sql
--- b/src/test/regress/sql/jsonb.sql
*************** CREATE TYPE jbpop AS (a text, b int, c t
*** 285,313 ****
SELECT * FROM jsonb_populate_record(NULL::jbpop,'{"a":"blurfl","x":43.2}') q;
SELECT * FROM jsonb_populate_record(row('x',3,'2012-12-31 15:30:56')::jbpop,'{"a":"blurfl","x":43.2}') q;
! SELECT * FROM jsonb_populate_record(NULL::jbpop,'{"a":"blurfl","x":43.2}', true) q;
! SELECT * FROM jsonb_populate_record(row('x',3,'2012-12-31 15:30:56')::jbpop,'{"a":"blurfl","x":43.2}', true) q;
! SELECT * FROM jsonb_populate_record(NULL::jbpop,'{"a":[100,200,false],"x":43.2}', true) q;
! SELECT * FROM jsonb_populate_record(row('x',3,'2012-12-31 15:30:56')::jbpop,'{"a":[100,200,false],"x":43.2}', true)
q;
! SELECT * FROM jsonb_populate_record(row('x',3,'2012-12-31 15:30:56')::jbpop,'{"c":[100,200,false],"x":43.2}', true)
q;
-- populate_recordset
! SELECT * FROM jsonb_populate_recordset(NULL::jbpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20
10:42:53"}]',false)q;
! SELECT * FROM jsonb_populate_recordset(row('def',99,NULL)::jbpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20
10:42:53"}]',false)q;
! SELECT * FROM jsonb_populate_recordset(NULL::jbpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20
10:42:53"}]',true)q;
! SELECT * FROM jsonb_populate_recordset(row('def',99,NULL)::jbpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20
10:42:53"}]',true)q;
! SELECT * FROM
jsonb_populate_recordset(row('def',99,NULL)::jbpop,'[{"a":[100,200,300],"x":43.2},{"a":{"z":true},"b":3,"c":"2012-01-20
10:42:53"}]',true)q;
! SELECT * FROM
jsonb_populate_recordset(row('def',99,NULL)::jbpop,'[{"c":[100,200,300],"x":43.2},{"a":{"z":true},"b":3,"c":"2012-01-20
10:42:53"}]',true)q;
!
! -- using the default use_json_as_text argument
SELECT * FROM jsonb_populate_recordset(NULL::jbpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]') q;
SELECT * FROM jsonb_populate_recordset(row('def',99,NULL)::jbpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20
10:42:53"}]')q;
SELECT * FROM
jsonb_populate_recordset(row('def',99,NULL)::jbpop,'[{"a":[100,200,300],"x":43.2},{"a":{"z":true},"b":3,"c":"2012-01-20
10:42:53"}]')q;
SELECT * FROM
jsonb_populate_recordset(row('def',99,NULL)::jbpop,'[{"c":[100,200,300],"x":43.2},{"a":{"z":true},"b":3,"c":"2012-01-20
10:42:53"}]')q;
-- handling of unicode surrogate pairs
SELECT octet_length((jsonb '{ "a": "\ud83d\ude04\ud83d\udc36" }' -> 'a')::text) AS correct_in_utf8;
SELECT jsonb '{ "a": "\ud83d\ud83d" }' -> 'a'; -- 2 high surrogates in a row
SELECT jsonb '{ "a": "\ude04\ud83d" }' -> 'a'; -- surrogates in wrong order
--- 285,311 ----
SELECT * FROM jsonb_populate_record(NULL::jbpop,'{"a":"blurfl","x":43.2}') q;
SELECT * FROM jsonb_populate_record(row('x',3,'2012-12-31 15:30:56')::jbpop,'{"a":"blurfl","x":43.2}') q;
! SELECT * FROM jsonb_populate_record(NULL::jbpop,'{"a":"blurfl","x":43.2}') q;
! SELECT * FROM jsonb_populate_record(row('x',3,'2012-12-31 15:30:56')::jbpop,'{"a":"blurfl","x":43.2}') q;
! SELECT * FROM jsonb_populate_record(NULL::jbpop,'{"a":[100,200,false],"x":43.2}') q;
! SELECT * FROM jsonb_populate_record(row('x',3,'2012-12-31 15:30:56')::jbpop,'{"a":[100,200,false],"x":43.2}') q;
! SELECT * FROM jsonb_populate_record(row('x',3,'2012-12-31 15:30:56')::jbpop,'{"c":[100,200,false],"x":43.2}') q;
-- populate_recordset
! SELECT * FROM jsonb_populate_recordset(NULL::jbpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]') q;
! SELECT * FROM jsonb_populate_recordset(row('def',99,NULL)::jbpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20
10:42:53"}]')q;
SELECT * FROM jsonb_populate_recordset(NULL::jbpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]') q;
SELECT * FROM jsonb_populate_recordset(row('def',99,NULL)::jbpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20
10:42:53"}]')q;
SELECT * FROM
jsonb_populate_recordset(row('def',99,NULL)::jbpop,'[{"a":[100,200,300],"x":43.2},{"a":{"z":true},"b":3,"c":"2012-01-20
10:42:53"}]')q;
SELECT * FROM
jsonb_populate_recordset(row('def',99,NULL)::jbpop,'[{"c":[100,200,300],"x":43.2},{"a":{"z":true},"b":3,"c":"2012-01-20
10:42:53"}]')q;
+ SELECT * FROM jsonb_populate_recordset(NULL::jbpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]') q;
+ SELECT * FROM jsonb_populate_recordset(row('def',99,NULL)::jbpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20
10:42:53"}]')q;
+ SELECT * FROM
jsonb_populate_recordset(row('def',99,NULL)::jbpop,'[{"a":[100,200,300],"x":43.2},{"a":{"z":true},"b":3,"c":"2012-01-20
10:42:53"}]')q;
-- handling of unicode surrogate pairs
+
SELECT octet_length((jsonb '{ "a": "\ud83d\ude04\ud83d\udc36" }' -> 'a')::text) AS correct_in_utf8;
SELECT jsonb '{ "a": "\ud83d\ud83d" }' -> 'a'; -- 2 high surrogates in a row
SELECT jsonb '{ "a": "\ude04\ud83d" }' -> 'a'; -- surrogates in wrong order
*************** SELECT jsonb '{ "a": "null \u0000 escap
*** 321,330 ****
-- jsonb_to_record and jsonb_to_recordset
! select * from jsonb_to_record('{"a":1,"b":"foo","c":"bar"}',true)
as x(a int, b text, d text);
! select * from jsonb_to_recordset('[{"a":1,"b":"foo","d":false},{"a":2,"b":"bar","c":true}]',false)
as x(a int, b text, c boolean);
-- indexing
--- 319,328 ----
-- jsonb_to_record and jsonb_to_recordset
! select * from jsonb_to_record('{"a":1,"b":"foo","c":"bar"}')
as x(a int, b text, d text);
! select * from jsonb_to_recordset('[{"a":1,"b":"foo","d":false},{"a":2,"b":"bar","c":true}]')
as x(a int, b text, c boolean);
-- indexing