Re: [HACKERS] jsonb and nested hstore
On 01/30/2014 01:03 PM, Hannu Krosing wrote:
On 01/30/2014 06:45 PM, Andrew Dunstan wrote:
On 01/30/2014 12:34 PM, Merlin Moncure wrote:
On Thu, Jan 30, 2014 at 9:50 AM, Andrew Dunstan and...@dunslane.net
wrote:
Now, if we're agreed on that, I then also wonder if the 'as_text'
argument needs to exist at all for the populate functions except for
backwards compatibility on the json side (not jsonb). For
non-complex
structures it does best effort casting anyways so the flag is moot.
Well, I could certainly look at making the populate_record{set} and
to_record{set} logic handle types that are arrays or composites
inside the
record. It might not be terribly hard to do - not sure.
A quick analysis suggests that this is fixable with fairly minimal
disturbance in the jsonb case.
As row_to_json() works with arbitrarily complex nested types (for
example row having a field
of type array of another (table)type containing arrays of third type) it
would be really nice if
you can get the result back into that row without too much hassle.
and it should be ok to treat json as source type and require it to be
translated to jsonb
for more complex operations
Might be possible.
In the json case it would probably involve
reparsing the inner json. That's probably doable, because the
routines are
all reentrant, but not likely to be terribly efficient. It will also
be a
deal more work.
Right. Also the text json functions are already in the wild anyways
-- that's not in the scope of this patch so if they need to be fixed
that could be done later.
ISTM then the right course of action is to point jsonb 'populate'
variants at hstore implementation, not the text json one and remove
the 'as text' argument. Being able to ditch that argument is the main
reason why I think this should be handled now (not forcing hstore
dependency to handle complex json is gravy).
We can't reference any hstore code in jsonb. There is no guarantee
that hstore will even be loaded.
We'd have to move that code from hstore to jsonb_support.c and then
make hstore refer to it.
Or just copy it and leave hstore alone - the code duplication is not
terribly huge
here and hstore might still want to develop independently.
We have gone to great deal of trouble to make jsonb and nested hstore
more or less incarnations of the same thing. The new hstore relies
heavily on the new jsonb. So what you're suggesting is the opposite of
what's been developed these last months.
cheers
andrew
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] jsonb and nested hstore
Andrew Dunstan and...@dunslane.net writes: On 01/30/2014 01:03 PM, Hannu Krosing wrote: On 01/30/2014 06:45 PM, Andrew Dunstan wrote: We'd have to move that code from hstore to jsonb_support.c and then make hstore refer to it. Or just copy it and leave hstore alone - the code duplication is not terribly huge here and hstore might still want to develop independently. We have gone to great deal of trouble to make jsonb and nested hstore more or less incarnations of the same thing. The new hstore relies heavily on the new jsonb. So what you're suggesting is the opposite of what's been developed these last months. If so, why would you be resistant to pushing more code out of hstore and into jsonb? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] jsonb and nested hstore
On 01/30/2014 01:50 PM, Tom Lane wrote: Andrew Dunstan and...@dunslane.net writes: On 01/30/2014 01:03 PM, Hannu Krosing wrote: On 01/30/2014 06:45 PM, Andrew Dunstan wrote: We'd have to move that code from hstore to jsonb_support.c and then make hstore refer to it. Or just copy it and leave hstore alone - the code duplication is not terribly huge here and hstore might still want to develop independently. We have gone to great deal of trouble to make jsonb and nested hstore more or less incarnations of the same thing. The new hstore relies heavily on the new jsonb. So what you're suggesting is the opposite of what's been developed these last months. If so, why would you be resistant to pushing more code out of hstore and into jsonb? I'm not. Above I suggested exactly that. I was simply opposed to Hannu's suggestion that instead of making hstore refer to the adopted code we maintain two copies of code that does essentially the same thing. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] jsonb and nested hstore - small docpatch
On Thu, January 30, 2014 20:07, Andrew Dunstan wrote: Updated patches for both pieces. Included is some tidying done by [ nested-hstore-9.patch.gz ] Here is a small doc-patch to Table F-6. hstore Operators It corrects its booleans in the 'Result' column ( t and f instead of true and false ). Thanks, Erik Rijkers -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] jsonb and nested hstore - small docpatch
On Thu, January 30, 2014 23:15, Erik Rijkers wrote:
On Thu, January 30, 2014 20:07, Andrew Dunstan wrote:
Updated patches for both pieces. Included is some tidying done by
[ nested-hstore-9.patch.gz ]
Here is a small doc-patch to Table F-6. hstore Operators
It corrects its booleans in the 'Result' column ( t and f instead of true
and false ).
I mean, here it is...
--- doc/src/sgml/hstore.sgml.orig 2014-01-30 22:39:52.970474354 +0100
+++ doc/src/sgml/hstore.sgml 2014-01-30 22:57:27.630698633 +0100
@@ -286,7 +286,7 @@
entrytypeboolean//entry
entryget boolean value for key (literalNULL/ if not boolean or not present)/entry
entryliteral'a =gt; 42.0, b =gt; true'::hstore ?gt; 'b'/literal/entry
- entryliteraltrue/literal/entry
+ entryliteralt/literal/entry
/row
row
@@ -294,7 +294,7 @@
entrytypeboolean//entry
entryget boolean value for array index (literalNULL/ if not boolean or not present)/entry
entryliteral'[false,null,44]'::hstore ?gt; 0/literal/entry
- entryliteralfalse/literal/entry
+ entryliteralf/literal/entry
/row
row
@@ -318,7 +318,7 @@
entrytypeboolean//entry
entryget boolean value for key path (literalNULL/ if not boolean or not present)/entry
entryliteral'foo =gt; {bar =gt; true}'::hstore #?gt; '{foo,bar}'/literal/entry
- entryliteraltrue/literal/entry
+ entryliteralt/literal/entry
/row
row
@@ -366,7 +366,7 @@
entrytypeboolean//entry
entrydoes typehstore/ contain key?/entry
entryliteral'a=gt;1'::hstore ? 'a'/literal/entry
- entryliteraltrue/literal/entry
+ entryliteralt/literal/entry
/row
row
@@ -374,7 +374,7 @@
entrytypeboolean//entry
entrydoes typehstore/ contain array index?/entry
entryliteral'[a,b,c]'::hstore ? 2/literal/entry
- entryliteraltrue/literal/entry
+ entryliteralt/literal/entry
/row
row
@@ -382,7 +382,7 @@
entrytypeboolean//entry
entrydoes typehstore/ contain key path?/entry
entryliteral'[1, 2, {foo=gt;hi}]'::hstore #? '{2,foo}'/literal/entry
- entryliteraltrue/literal/entry
+ entryliteralt/literal/entry
/row
row
@@ -390,7 +390,7 @@
entrytypeboolean//entry
entrydoes typehstore/ contain all specified keys?/entry
entryliteral'a=gt;1,b=gt;2'::hstore ?amp; ARRAY['a','b']/literal/entry
- entryliteraltrue/literal/entry
+ entryliteralt/literal/entry
/row
row
@@ -398,7 +398,7 @@
entrytypeboolean//entry
entrydoes typehstore/ contain any of the specified keys?/entry
entryliteral'a=gt;1,b=gt;2'::hstore ?| ARRAY['b','c']/literal/entry
- entryliteraltrue/literal/entry
+ entryliteralt/literal/entry
/row
row
@@ -406,7 +406,7 @@
entrytypeboolean//entry
entrydoes left operand contain right?/entry
entryliteral'a=gt;b, b=gt;1, c=gt;NULL'::hstore @gt; 'b=gt;1'/literal/entry
- entryliteraltrue/literal/entry
+ entryliteralt/literal/entry
/row
row
@@ -414,7 +414,7 @@
entrytypeboolean//entry
entryis left operand contained in right?/entry
entryliteral'a=gt;c'::hstore lt;@ 'a=gt;b, b=gt;1, c=gt;NULL'/literal/entry
- entryliteralfalse/literal/entry
+ entryliteralf/literal/entry
/row
row
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] jsonb and nested hstore
On Thu, Jan 30, 2014 at 1:07 PM, Andrew Dunstan and...@dunslane.net wrote:
On 01/29/2014 04:56 PM, Andrew Dunstan wrote:
On 01/29/2014 01:03 PM, Andrew Dunstan wrote:
On 01/27/2014 10:43 PM, Andrew Dunstan wrote:
On 01/26/2014 05:42 PM, Andrew Dunstan wrote:
Here is the latest set of patches for nested hstore and jsonb.
Because it's so large I've broken this into two patches and compressed
them. The jsonb patch should work standalone. The nested hstore patch
depends on it.
All the jsonb functions now use the jsonb API - there is no more
turning jsonb into text and reparsing it.
At this stage I'm going to be starting cleanup on the jsonb code
(indentation, error messages, comments etc.) as well get getting up some
jsonb docs.
Here is an update of the jsonb part of this. Charges:
* there is now documentation for jsonb
* most uses of elog() in json_funcs.c are replaced by ereport().
* indentation fixes and other tidying.
No changes in functionality.
Further update of jsonb portion.
Only change in functionality is the addition of casts between jsonb and
json.
The other changes are the merge with the new json functions code, and
rearrangement of the docs changes to make them less ugly. Essentially I
moved the indexterm tags right out of the table as is done in some other
parts pf the docs. That makes the entry tags much clearer to read.
Updated to apply cleanly after recent commits.
Updated patches for both pieces. Included is some tidying done by Teodor,
and fixes for remaining whitespace issues. This now passes git diff --check
master cleanly for me.
Something seems off:
postgres=# create type z as (a int, b int[]);
CREATE TYPE
postgres=# create type y as (a int, b z[]);
CREATE TYPE
postgres=# create type x as (a int, b y[]);
CREATE TYPE
-- test a complicated construction
postgres=# select row(1, array[row(1, array[row(1, array[1,2])::z])::y])::x;
row
-
(1,{(1,\\{(1,{1,2})}\\)})
postgres=# select hstore(row(1, array[row(1, array[row(1,
array[1,2])::z])::y])::x);
hstore
--
a=1,
b={\(1,\\\{\\(1,\\{1,2}\\)\\}\\\)\}
here, the output escaping has leaked into the internal array
structures. istm we should have a json expressing the internal
structure. It does (weirdly) map back however:
postgres=# select populate_record(null::x, hstore(row(1, array[row(1,
array[row(1, array[1,2])::z])::y])::x));
populate_record
-
(1,{(1,\\{(1,{1,2})}\\)})
OTOH, if I go via json route:
postgres=# select row_to_json(row(1, array[row(1, array[row(1,
array[1,2])::z])::y])::x);
row_to_json
---
{a:1,b:[{a:1,b:[{a:1,b:[1,2]}]}]}
so far, so good. let's push to hstore:
postgres=# select row_to_json(row(1, array[row(1, array[row(1,
array[1,2])::z])::y])::x)::jsonb::hstore;
row_to_json
---
a=1, b=[{a=1, b=[{a=1, b=[1, 2]}]}]
this ISTM is the 'right' behavior. but what if we bring it back to
record object?
postgres=# select populate_record(null::x, row_to_json(row(1,
array[row(1, array[row(1, array[1,2])::z])::y])::x)::jsonb::hstore);
ERROR: malformed array literal: {{a=1, b={{a=1, b={1, 2}
yikes. The situation as I read it is that (notwithstanding my comments
upthread) there is no clean way to slide rowtypes to/from hstore and
jsonb while preserving structure. IMO, the above query should work
and the populate function record above should return the internally
structured row object, not the text escaped version.
merlin
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] jsonb and nested hstore
On 01/30/2014 07:21 PM, Merlin Moncure wrote:
Something seems off:
postgres=# create type z as (a int, b int[]);
CREATE TYPE
postgres=# create type y as (a int, b z[]);
CREATE TYPE
postgres=# create type x as (a int, b y[]);
CREATE TYPE
-- test a complicated construction
postgres=# select row(1, array[row(1, array[row(1, array[1,2])::z])::y])::x;
row
-
(1,{(1,\\{(1,{1,2})}\\)})
postgres=# select hstore(row(1, array[row(1, array[row(1,
array[1,2])::z])::y])::x);
hstore
--
a=1,
b={\(1,\\\{\\(1,\\{1,2}\\)\\}\\\)\}
here, the output escaping has leaked into the internal array
structures. istm we should have a json expressing the internal
structure.
What has this to do with json at all? It's clearly a failure in the
hstore() function.
It does (weirdly) map back however:
postgres=# select populate_record(null::x, hstore(row(1, array[row(1,
array[row(1, array[1,2])::z])::y])::x));
populate_record
-
(1,{(1,\\{(1,{1,2})}\\)})
OTOH, if I go via json route:
postgres=# select row_to_json(row(1, array[row(1, array[row(1,
array[1,2])::z])::y])::x);
row_to_json
---
{a:1,b:[{a:1,b:[{a:1,b:[1,2]}]}]}
so far, so good. let's push to hstore:
postgres=# select row_to_json(row(1, array[row(1, array[row(1,
array[1,2])::z])::y])::x)::jsonb::hstore;
row_to_json
---
a=1, b=[{a=1, b=[{a=1, b=[1, 2]}]}]
this ISTM is the 'right' behavior. but what if we bring it back to
record object?
postgres=# select populate_record(null::x, row_to_json(row(1,
array[row(1, array[row(1, array[1,2])::z])::y])::x)::jsonb::hstore);
ERROR: malformed array literal: {{a=1, b={{a=1, b={1, 2}
yikes. The situation as I read it is that (notwithstanding my comments
upthread) there is no clean way to slide rowtypes to/from hstore and
jsonb while preserving structure. IMO, the above query should work
and the populate function record above should return the internally
structured row object, not the text escaped version.
And this is a failure in populate_record().
I think we possibly need to say that handling of nested composites and
arrays is an area that needs further work. OTOH, the refusal of
json_populate_record() and json_populate_recordset() to handle these in
9.3 has not generated a flood of complaints, so I don't think it's a
tragedy, just a limitation, which should be documented if it's not
already. (And of course hstore hasn't handled nested anything before now.)
Meanwhile, maybe Teodor can fix the two hstore bugs shown here.
cheers
andrew
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] jsonb and nested hstore
On Thu, Jan 30, 2014 at 4:52 PM, Andrew Dunstan and...@dunslane.net wrote:
On 01/30/2014 07:21 PM, Merlin Moncure wrote:
postgres=# select hstore(row(1, array[row(1, array[row(1,
array[1,2])::z])::y])::x);
hstore
--
a=1,
b={\(1,\\\{\\(1,\\{1,2}\\)\\}\\\)\}
here, the output escaping has leaked into the internal array
structures. istm we should have a json expressing the internal
structure.
What has this to do with json at all? It's clearly a failure in the hstore()
function.
yeah -- meant to say 'hstore' there. Also I'm not sure that it's
'wrong'; it's just doing what it always did. That brings up another
point: are there any interesting cases of compatibility breakage? I'm
inclined not to care about this particular case though...
array[row(1, array[row(1, array[1,2])::z])::y])::x)::jsonb::hstore);
ERROR: malformed array literal: {{a=1, b={{a=1, b={1,
2}
yikes. The situation as I read it is that (notwithstanding my comments
upthread) there is no clean way to slide rowtypes to/from hstore and
jsonb while preserving structure. IMO, the above query should work
and the populate function record above should return the internally
structured row object, not the text escaped version.
And this is a failure in populate_record().
I think we possibly need to say that handling of nested composites and
arrays is an area that needs further work. OTOH, the refusal of
json_populate_record() and json_populate_recordset() to handle these in 9.3
has not generated a flood of complaints, so I don't think it's a tragedy,
just a limitation, which should be documented if it's not already. (And of
course hstore hasn't handled nested anything before now.)
Meanwhile, maybe Teodor can fix the two hstore bugs shown here.
While not a flood, there certainly have been complaints. See
http://postgresql.1045698.n5.nabble.com/Best-way-to-populate-nested-composite-type-from-JSON-td5770566.html
http://osdir.com/ml/postgresql-pgsql-general/2014-01/msg00205.html
But, if we had to drop this in the interests of time I'd rather see
the behavior cauterized off so that it errored out 'not supported' (as
json_populate does) that attempt to implement the wrong behavior.
merlin
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] jsonb and nested hstore
On Wed, Jan 29, 2014 at 12:03 PM, Andrew Dunstan and...@dunslane.net wrote: Only change in functionality is the addition of casts between jsonb and json. The other changes are the merge with the new json functions code, and rearrangement of the docs changes to make them less ugly. Essentially I moved the indexterm tags right out of the table as is done in some other parts pf the docs. That makes the entry tags much clearer to read. I think the opening paragraphs contrasting json/jsonb be needs refinement. json is going to be slightly faster than jsonb for input *and* output. For example, in one application I store fairly large json objects containing pre-compiled static polygon data that is simply flipped up to google maps. This case will likely be pessimal for jsonb. For the next paragaph, I'd like to expand it a bit on 'specialized needs' and boil it down to specific uses cases. Basically, json will likely be more compact in most cases and slightly faster for input/output; jsonb would be preferred in any context where processing, or searching or extensive server side parsing is employed. If you agree, I'd be happy to do that... merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] jsonb and nested hstore
On 01/29/2014 12:46 PM, Merlin Moncure wrote: I think the opening paragraphs contrasting json/jsonb be needs refinement. json is going to be slightly faster than jsonb for input *and* output. For example, in one application I store fairly large json objects containing pre-compiled static polygon data that is simply flipped up to google maps. This case will likely be pessimal for jsonb. For the next paragaph, I'd like to expand it a bit on 'specialized needs' and boil it down to specific uses cases. Basically, json will likely be more compact in most cases and slightly faster for input/output; jsonb would be preferred in any context where processing, or searching or extensive server side parsing is employed. If you agree, I'd be happy to do that... Please take a stab at it, I'll be happy to revise it. I was working on doing a two-column table comparison chart; I still think that's the best way to go. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] jsonb and nested hstore
On Wed, Jan 29, 2014 at 3:56 PM, Andrew Dunstan and...@dunslane.net wrote:
On 01/29/2014 01:03 PM, Andrew Dunstan wrote:
On 01/27/2014 10:43 PM, Andrew Dunstan wrote:
On 01/26/2014 05:42 PM, Andrew Dunstan wrote:
Here is the latest set of patches for nested hstore and jsonb.
Because it's so large I've broken this into two patches and compressed
them. The jsonb patch should work standalone. The nested hstore patch
depends on it.
All the jsonb functions now use the jsonb API - there is no more turning
jsonb into text and reparsing it.
At this stage I'm going to be starting cleanup on the jsonb code
(indentation, error messages, comments etc.) as well get getting up some
jsonb docs.
Here is an update of the jsonb part of this. Charges:
* there is now documentation for jsonb
* most uses of elog() in json_funcs.c are replaced by ereport().
* indentation fixes and other tidying.
No changes in functionality.
Further update of jsonb portion.
Only change in functionality is the addition of casts between jsonb and
json.
The other changes are the merge with the new json functions code, and
rearrangement of the docs changes to make them less ugly. Essentially I
moved the indexterm tags right out of the table as is done in some other
parts pf the docs. That makes the entry tags much clearer to read.
Updated to apply cleanly after recent commits.
ok, great. This is really fabulous. So far most everything feels
natural and good.
I see something odd in terms of the jsonb use case coverage. One of
the major headaches with json deserialization presently is that
there's no easy way to easily move a complex (record- or array-
containing) json structure into a row object. For example,
create table bar(a int, b int[]);
postgres=# select jsonb_populate_record(null::bar, '{a: 1, b:
[1,2]}'::jsonb, false);
ERROR: cannot populate with a nested object unless use_json_as_text is true
If find the use_json_as_text argument here to be pretty useless
(unlike in the json_build to_record variants where it least provides
some hope for an escape hatch) for handling this since it will just
continue to fail:
postgres=# select jsonb_populate_record(null::bar, '{a: 1, b:
[1,2]}'::jsonb, true);
ERROR: missing ] in array dimensions
OTOH, the nested hstore handles this no questions asked:
postgres=# select * from populate_record(null::bar, 'a=1,
b={1,2}'::hstore);
a | b
---+---
1 | {1,2}
So, if you need to convert a complex json to a row type, the only
effective way to do that is like this:
postgres=# select* from populate_record(null::bar, '{a: 1, b:
[1,2]}'::json::hstore);
a | b
---+---
1 | {1,2}
Not a big deal really. But it makes me wonder (now that we have the
internal capability of properly mapping to a record) why *both* the
json/jsonb populate record variants shouldn't point to what the nested
hstore behavior is when the 'as_text' flag is false. That would
demolish the error and remove the dependency on hstore in order to do
effective rowtype mapping. In an ideal world the json_build
'to_record' variants would behave similarly I think although there's
no existing hstore analog so I'm assuming it's a non-trival amount of
work.
Now, if we're agreed on that, I then also wonder if the 'as_text'
argument needs to exist at all for the populate functions except for
backwards compatibility on the json side (not jsonb). For non-complex
structures it does best effort casting anyways so the flag is moot.
merlin
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] jsonb and nested hstore
On 01/29/2014 05:37 PM, Merlin Moncure wrote:
On Wed, Jan 29, 2014 at 3:56 PM, Andrew Dunstan and...@dunslane.net wrote:
On 01/29/2014 01:03 PM, Andrew Dunstan wrote:
On 01/27/2014 10:43 PM, Andrew Dunstan wrote:
On 01/26/2014 05:42 PM, Andrew Dunstan wrote:
Here is the latest set of patches for nested hstore and jsonb.
Because it's so large I've broken this into two patches and compressed
them. The jsonb patch should work standalone. The nested hstore patch
depends on it.
All the jsonb functions now use the jsonb API - there is no more turning
jsonb into text and reparsing it.
At this stage I'm going to be starting cleanup on the jsonb code
(indentation, error messages, comments etc.) as well get getting up some
jsonb docs.
Here is an update of the jsonb part of this. Charges:
* there is now documentation for jsonb
* most uses of elog() in json_funcs.c are replaced by ereport().
* indentation fixes and other tidying.
No changes in functionality.
Further update of jsonb portion.
Only change in functionality is the addition of casts between jsonb and
json.
The other changes are the merge with the new json functions code, and
rearrangement of the docs changes to make them less ugly. Essentially I
moved the indexterm tags right out of the table as is done in some other
parts pf the docs. That makes the entry tags much clearer to read.
Updated to apply cleanly after recent commits.
ok, great. This is really fabulous. So far most everything feels
natural and good.
I see something odd in terms of the jsonb use case coverage. One of
the major headaches with json deserialization presently is that
there's no easy way to easily move a complex (record- or array-
containing) json structure into a row object. For example,
create table bar(a int, b int[]);
postgres=# select jsonb_populate_record(null::bar, '{a: 1, b:
[1,2]}'::jsonb, false);
ERROR: cannot populate with a nested object unless use_json_as_text is true
If find the use_json_as_text argument here to be pretty useless
(unlike in the json_build to_record variants where it least provides
some hope for an escape hatch) for handling this since it will just
continue to fail:
postgres=# select jsonb_populate_record(null::bar, '{a: 1, b:
[1,2]}'::jsonb, true);
ERROR: missing ] in array dimensions
OTOH, the nested hstore handles this no questions asked:
postgres=# select * from populate_record(null::bar, 'a=1,
b={1,2}'::hstore);
a | b
---+---
1 | {1,2}
So, if you need to convert a complex json to a row type, the only
effective way to do that is like this:
postgres=# select* from populate_record(null::bar, '{a: 1, b:
[1,2]}'::json::hstore);
a | b
---+---
1 | {1,2}
Not a big deal really. But it makes me wonder (now that we have the
internal capability of properly mapping to a record) why *both* the
json/jsonb populate record variants shouldn't point to what the nested
hstore behavior is when the 'as_text' flag is false. That would
demolish the error and remove the dependency on hstore in order to do
effective rowtype mapping. In an ideal world the json_build
'to_record' variants would behave similarly I think although there's
no existing hstore analog so I'm assuming it's a non-trival amount of
work.
Now, if we're agreed on that, I then also wonder if the 'as_text'
argument needs to exist at all for the populate functions except for
backwards compatibility on the json side (not jsonb). For non-complex
structures it does best effort casting anyways so the flag is moot.
Well, I could certainly look at making the populate_record{set} and
to_record{set} logic handle types that are arrays or composites inside
the record. It might not be terribly hard to do - not sure.
cheers
andrew
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] jsonb and nested hstore
On 01/29/2014 02:37 PM, Merlin Moncure wrote:
create table bar(a int, b int[]);
postgres=# select jsonb_populate_record(null::bar, '{a: 1, b:
[1,2]}'::jsonb, false);
ERROR: cannot populate with a nested object unless use_json_as_text is true
Hmmm. What about just making any impossibly complex objects type JSON?
--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] jsonb and nested hstore
On Mon, Jan 27, 2014 at 9:43 PM, Andrew Dunstan and...@dunslane.net wrote: On 01/26/2014 05:42 PM, Andrew Dunstan wrote: Here is the latest set of patches for nested hstore and jsonb. Because it's so large I've broken this into two patches and compressed them. The jsonb patch should work standalone. The nested hstore patch depends on it. All the jsonb functions now use the jsonb API - there is no more turning jsonb into text and reparsing it. At this stage I'm going to be starting cleanup on the jsonb code (indentation, error messages, comments etc.) as well get getting up some jsonb docs. Here is an update of the jsonb part of this. Charges: * there is now documentation for jsonb * most uses of elog() in json_funcs.c are replaced by ereport(). * indentation fixes and other tidying. No changes in functionality. Don't have time to fire it up this morning, but a quick scan of the patch turned up a few minor things: * see a comment typo, line 290 'jsonn': * line 332: 'bogus input' -- is this up to error reporting standards? How about value 'x' must be one of array, object, numeric, string, bool? * line 357: jsonb's key could be only a string prefer non possessive: jsonb keys must be a string * line 374, 389: ditto 332 * line 513: is panic appropriate here? * line 599: ditto * line 730: odd phrasing in comment, also commenting on this function is a little light * line 807: slightly prefer 'with respect to' * line 888: another PANIC: these maybe correct, seems odd to halt server on corrupted datum though* * line 1150: hm, is the jsonb internal hash structure documented? Aside: why didn't we use standard hash table (performance maybe)? * line 1805-6: poor phrasing. How about: it will order and make unique the hash keys. Otherwise we believe that pushed keys are ordered and unique. (Don't like verbed 'unqiue'). * line 1860: no break here: merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] jsonb and nested hstore
On 01/28/2014 09:38 AM, Merlin Moncure wrote: On Mon, Jan 27, 2014 at 9:43 PM, Andrew Dunstan and...@dunslane.net wrote: On 01/26/2014 05:42 PM, Andrew Dunstan wrote: Here is the latest set of patches for nested hstore and jsonb. Because it's so large I've broken this into two patches and compressed them. The jsonb patch should work standalone. The nested hstore patch depends on it. All the jsonb functions now use the jsonb API - there is no more turning jsonb into text and reparsing it. At this stage I'm going to be starting cleanup on the jsonb code (indentation, error messages, comments etc.) as well get getting up some jsonb docs. Here is an update of the jsonb part of this. Charges: * there is now documentation for jsonb * most uses of elog() in json_funcs.c are replaced by ereport(). * indentation fixes and other tidying. No changes in functionality. Don't have time to fire it up this morning, but a quick scan of the patch turned up a few minor things: * see a comment typo, line 290 'jsonn': * line 332: 'bogus input' -- is this up to error reporting standards? How about value 'x' must be one of array, object, numeric, string, bool? * line 357: jsonb's key could be only a string prefer non possessive: jsonb keys must be a string * line 374, 389: ditto 332 * line 513: is panic appropriate here? * line 599: ditto * line 730: odd phrasing in comment, also commenting on this function is a little light * line 807: slightly prefer 'with respect to' * line 888: another PANIC: these maybe correct, seems odd to halt server on corrupted datum though* * line 1150: hm, is the jsonb internal hash structure documented? Aside: why didn't we use standard hash table (performance maybe)? * line 1805-6: poor phrasing. How about: it will order and make unique the hash keys. Otherwise we believe that pushed keys are ordered and unique. (Don't like verbed 'unqiue'). * line 1860: no break here: Looks like this review is against jsonb-5, not jsonb-6. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] jsonb and nested hstore
Looks like this review is against jsonb-5, not jsonb-6. oh yep -- shoot, sorry for the noise. merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] jsonb and nested hstore
Andrew Dunstan wrote: para +There are two JSON data types: typejson/type and typejsonb/type. +Both accept identical sets of values as input. The difference is primarily +a matter of efficiency. The typejson/type data type stores an exact +copy of the the input text, and the processing functions have to reparse +it to precess it, while the typejsonb/type is stored in a decomposed +form that makes it slightly less efficient to input but very much faster +to process, since it never needs reparsing. + /para typo precess duplicated word of the the input + /indextermindexterm + primaryjsonb_each/primary + /indextermparaliteraljson_each(json)/literal + /paraparaliteraljsonb_each(jsonb)/literal + /para/entry This SGML nesting is odd and hard to read. Please place opening tags in separate lines (or at least not immediately following a closing tag). I am not sure whether the mentions of jsonb_each vs. json_each there are correct or typos. This also occurs in other places. Expands the object in replaceablefrom_json/replaceable to a row whose columns match the record type defined by base. Conversion will be best effort; columns in base with no corresponding key in replaceablefrom_json/replaceable - will be left null. If a column is specified more than once, the last value is used. + will be left null. When processing typejson/type, if a column is + specified more than once, the last value is used. Maybe you also need to specify what happens with jsonb? diff --git a/src/backend/utils/adt/jsonb.c b/src/backend/utils/adt/jsonb.c new file mode 100644 index 000..107ebf0 --- /dev/null +++ b/src/backend/utils/adt/jsonb.c @@ -0,0 +1,544 @@ +/*- + * + * jsonb.c + * I/O for jsonb type + * + * Portions Copyright (c) 1996-2013, PostgreSQL Global Development Group 2014. Why Portions, if we don't attribute any portion to UCB? + * NOTE. JSONB type is designed to be binary compatible with hstore. + * + * src/backend/utils/adt/jsonb_support.c Typo'ed name here. +#include postgres.h + +#include libpq/pqformat.h +#include utils/builtins.h +#include utils/json.h +#include utils/jsonapi.h +#include utils/jsonb.h Misplaced prototype? +static void recvJsonb(StringInfo buf, JsonbValue *v, uint32 level, uint32 header); Not sure about the jsonb_1.out file. Is that only due to encoding differences? What happens if you run it in a completely different encoding than whatever you tested with? (I would assume Latin-9 and UTF8) If it fails, then I think you'll end up ripping those tests out, so probably the _1.out file will have no value at all. I also wonder if it'd be better to have one large .sql file that produces the same output in all platforms that tests most of the common stuff, so that tests that changes output in different platforms can have smaller alternative expected files. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] jsonb and nested hstore
On 01/28/2014 10:50 AM, Alvaro Herrera wrote: + /indextermindexterm + primaryjsonb_each/primary + /indextermparaliteraljson_each(json)/literal + /paraparaliteraljsonb_each(jsonb)/literal + /para/entry This SGML nesting is odd and hard to read. Please place opening tags in separate lines (or at least not immediately following a closing tag). I am not sure whether the mentions of jsonb_each vs. json_each there are correct or typos. This also occurs in other places. As I understand it, an entry tag can only contain block-level elements like para if there are no inline elements (including white space). If that's not correct I'll change it, but that's what I read here: http://oreilly.com/openbook/docbook/book/entry.html cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] jsonb and nested hstore
Andrew Dunstan and...@dunslane.net writes: On 01/28/2014 10:50 AM, Alvaro Herrera wrote: + /indextermindexterm + primaryjsonb_each/primary + /indextermparaliteraljson_each(json)/literal + /paraparaliteraljsonb_each(jsonb)/literal + /para/entry This SGML nesting is odd and hard to read. Please place opening tags in separate lines (or at least not immediately following a closing tag). I am not sure whether the mentions of jsonb_each vs. json_each there are correct or typos. This also occurs in other places. As I understand it, an entry tag can only contain block-level elements like para if there are no inline elements (including white space). Practically every existing use of indexterm is freer than this in its use of whitespace. It sounds to me like maybe you are trying to put the indexterm inside something it shouldn't go inside of. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] jsonb and nested hstore
On 01/28/2014 11:09 AM, Tom Lane wrote: Andrew Dunstan and...@dunslane.net writes: On 01/28/2014 10:50 AM, Alvaro Herrera wrote: + /indextermindexterm + primaryjsonb_each/primary + /indextermparaliteraljson_each(json)/literal + /paraparaliteraljsonb_each(jsonb)/literal + /para/entry This SGML nesting is odd and hard to read. Please place opening tags in separate lines (or at least not immediately following a closing tag). I am not sure whether the mentions of jsonb_each vs. json_each there are correct or typos. This also occurs in other places. As I understand it, an entry tag can only contain block-level elements like para if there are no inline elements (including white space). Practically every existing use of indexterm is freer than this in its use of whitespace. It sounds to me like maybe you are trying to put the indexterm inside something it shouldn't go inside of. The problem is not the indexterm element, it's the space that might exist outside it. Are we using block level elements like para inside entry elements anywhere else? If not, then your observation is not relevant. If there are no block level elements then AIUI we can space things out how we like inside the entry element. If you can show me how else legally to get a line break inside an entry element I'm very interested. I tried several things before I found this way of making it work. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] jsonb and nested hstore
Tom Lane wrote: Andrew Dunstan and...@dunslane.net writes: On 01/28/2014 10:50 AM, Alvaro Herrera wrote: + /indextermindexterm + primaryjsonb_each/primary + /indextermparaliteraljson_each(json)/literal + /paraparaliteraljsonb_each(jsonb)/literal + /para/entry This SGML nesting is odd and hard to read. Please place opening tags in separate lines (or at least not immediately following a closing tag). I am not sure whether the mentions of jsonb_each vs. json_each there are correct or typos. This also occurs in other places. As I understand it, an entry tag can only contain block-level elements like para if there are no inline elements (including white space). Practically every existing use of indexterm is freer than this in its use of whitespace. It sounds to me like maybe you are trying to put the indexterm inside something it shouldn't go inside of. FWIW I was just talking about formatting of the SGML source so that it is easier to read. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] jsonb and nested hstore
Alvaro Herrera alvhe...@2ndquadrant.com writes: Tom Lane wrote: Practically every existing use of indexterm is freer than this in its use of whitespace. It sounds to me like maybe you are trying to put the indexterm inside something it shouldn't go inside of. FWIW I was just talking about formatting of the SGML source so that it is easier to read. Yeah, me too. I'm just suggesting that maybe Andrew needs to move the indexterm so that he can format it more readably. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] jsonb and nested hstore
On 01/28/2014 11:27 AM, Tom Lane wrote: Alvaro Herrera alvhe...@2ndquadrant.com writes: Tom Lane wrote: Practically every existing use of indexterm is freer than this in its use of whitespace. It sounds to me like maybe you are trying to put the indexterm inside something it shouldn't go inside of. FWIW I was just talking about formatting of the SGML source so that it is easier to read. Yeah, me too. I'm just suggesting that maybe Andrew needs to move the indexterm so that he can format it more readably. Hmm. Maybe I could put them inside the para elements. So we'd have: entrypara indexterm /indexterm para text /parapara indexterm /indexterm para text /para/entry cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] jsonb and nested hstore
Andrew Dunstan and...@dunslane.net writes: The problem is not the indexterm element, it's the space that might exist outside it. Are we using block level elements like para inside entry elements anywhere else? Probably not, and I wonder why you're trying to. Whole paras inside a table entry (this is a table no?) don't sound like they are going to lead to nice-looking results. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] jsonb and nested hstore
On 01/28/2014 11:29 AM, Tom Lane wrote: Andrew Dunstan and...@dunslane.net writes: The problem is not the indexterm element, it's the space that might exist outside it. Are we using block level elements like para inside entry elements anywhere else? Probably not, and I wonder why you're trying to. Whole paras inside a table entry (this is a table no?) don't sound like they are going to lead to nice-looking results. See http://developer.postgresql.org/~adunstan/functions-json.html cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] jsonb and nested hstore
On Tue, Jan 28, 2014 at 10:46 AM, Andrew Dunstan and...@dunslane.net wrote: On 01/28/2014 11:29 AM, Tom Lane wrote: Andrew Dunstan and...@dunslane.net writes: The problem is not the indexterm element, it's the space that might exist outside it. Are we using block level elements like para inside entry elements anywhere else? Probably not, and I wonder why you're trying to. Whole paras inside a table entry (this is a table no?) don't sound like they are going to lead to nice-looking results. See http://developer.postgresql.org/~adunstan/functions-json.html yeah. note: I think the json documentation needs *major* overhaul. too much is going in inside the function listings where there really should be a big breakout discussing the big picture of json/jsonb with examples of various use cases. I want to give it a shot but unfortunately can not commit to do that by the end of the 'fest. merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] jsonb and nested hstore
On 01/28/2014 09:58 AM, Merlin Moncure wrote: yeah. note: I think the json documentation needs *major* overhaul. too much is going in inside the function listings where there really should be a big breakout discussing the big picture of json/jsonb with examples of various use cases. I want to give it a shot but unfortunately can not commit to do that by the end of the 'fest. FWIW, I've promised Andrew that I'll overhaul this by the end of beta. Given that we have all of beta for doc refinements. In addition to this, the JSON vs JSONB datatype page really needs expansion and clarification. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] jsonb and nested hstore
On Tue, Jan 28, 2014 at 12:09 PM, Josh Berkus j...@agliodbs.com wrote: On 01/28/2014 09:58 AM, Merlin Moncure wrote: yeah. note: I think the json documentation needs *major* overhaul. too much is going in inside the function listings where there really should be a big breakout discussing the big picture of json/jsonb with examples of various use cases. I want to give it a shot but unfortunately can not commit to do that by the end of the 'fest. FWIW, I've promised Andrew that I'll overhaul this by the end of beta. Given that we have all of beta for doc refinements. In addition to this, the JSON vs JSONB datatype page really needs expansion and clarification. right: exactly. I'd be happy to help (such as I can) ...I wanted to see if jsonb to make it in on this 'fest (doc issues notwithstanding); it hasn't been formally reviewed yet AFAICT. So my thinking here is to get docs to minimum acceptable standards in the short term and focus on the structural code issues for the 'fest (if jsonb slips then it's moot obviously). merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] jsonb and nested hstore
On 01/28/2014 10:29 AM, Merlin Moncure wrote: In addition to this, the JSON vs JSONB datatype page really needs expansion and clarification. right: exactly. I'd be happy to help (such as I can) ...I wanted to see if jsonb to make it in on this 'fest (doc issues notwithstanding); it hasn't been formally reviewed yet AFAICT. So my thinking here is to get docs to minimum acceptable standards in the short term and focus on the structural code issues for the 'fest (if jsonb slips then it's moot obviously). Well, having reviewed the docs before Andrew sent them in, I felt they already *were* minimum acceptable. Certainly they're as complete as the original JSON docs were. Or is this just about whitespace and line breaks? -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] jsonb and nested hstore
Josh Berkus escribió: Or is this just about whitespace and line breaks? If the docs are going to be rehauled, please ignore my whitespace comments. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] jsonb and nested hstore
On 01/28/2014 10:56 AM, Alvaro Herrera wrote: Josh Berkus escribió: Or is this just about whitespace and line breaks? If the docs are going to be rehauled, please ignore my whitespace comments. I'm sure you'll find plenty to criticize in my version. ;-) -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
