Re: [HACKERS] Why no jsonb_exists_path()?
Josh,
On Tue, Jun 9, 2015 at 9:16 PM, Josh Berkus j...@agliodbs.com wrote:
Dmitry, Alexander:
I'm noticing a feature gap for JSONB operators; we have no way to do this:
jsonb_col ? ARRAY['key1','key2','key3']
What documents do you expect to match this operator?
Such syntax can be interpreted in very different semantics. Checking keys
only at top level, any level, sequence starting from top level ... etc.
'{key1: value1, key2: value2, key3: value3}'
'{key1:{ key2: {key3: value}}}'
'{key1: value1}
'[{key1: value1}, {key2: value2}, {key3: value3}]'
--
Alexander Korotkov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
[HACKERS] Why no jsonb_exists_path()?
Dmitry, Alexander: I'm noticing a feature gap for JSONB operators; we have no way to do this: jsonb_col ? ARRAY['key1','key2','key3'] ... that is, there is no way for us to check for key existence in an indexable fashion. Given that @ already can check the whole path including the value, is there some challenge to stopping just short of the value I'm not seeing? Or is this just a didn't get to it yet issue? I'm trying to estimate the difficulty of creating this operator in an extension (obviously we're not doing it for 9.5), and maybe for 9.6. -- 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] Why no jsonb_exists_path()?
Josh Berkus j...@agliodbs.com writes: I'm noticing a feature gap for JSONB operators; we have no way to do this: jsonb_col ? ARRAY['key1','key2','key3'] ... that is, there is no way for us to check for key existence in an indexable fashion. Given that @ already can check the whole path including the value, is there some challenge to stopping just short of the value I'm not seeing? Or is this just a didn't get to it yet issue? Hm, well, the jsonb_path_ops opclass couldn't do it, because what it indexes is hashes that include the value. I suppose jsonb_ops could look for entries that match all of the keys and then see if the ordering is correct. 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] Why no jsonb_exists_path()?
On 06/09/2015 05:30 PM, Andrew Dunstan wrote: On 06/09/2015 02:40 PM, Tom Lane wrote: Josh Berkus j...@agliodbs.com writes: I'm noticing a feature gap for JSONB operators; we have no way to do this: jsonb_col ? ARRAY['key1','key2','key3'] ... that is, there is no way for us to check for key existence in an indexable fashion. Given that @ already can check the whole path including the value, is there some challenge to stopping just short of the value I'm not seeing? Or is this just a didn't get to it yet issue? Hm, well, the jsonb_path_ops opclass couldn't do it, because what it indexes is hashes that include the value. I suppose jsonb_ops could look for entries that match all of the keys and then see if the ordering is correct. It looks to me like we'd need to index all paths in a document, or possibly hashes of all paths in a document. I don't think anything we have now will help much, unless my understanding is way off. On second thought, yes, we could possibly check the index against the path elements and then recheck. 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] Why no jsonb_exists_path()?
On 06/09/2015 02:40 PM, Tom Lane wrote: Josh Berkus j...@agliodbs.com writes: I'm noticing a feature gap for JSONB operators; we have no way to do this: jsonb_col ? ARRAY['key1','key2','key3'] ... that is, there is no way for us to check for key existence in an indexable fashion. Given that @ already can check the whole path including the value, is there some challenge to stopping just short of the value I'm not seeing? Or is this just a didn't get to it yet issue? Hm, well, the jsonb_path_ops opclass couldn't do it, because what it indexes is hashes that include the value. I suppose jsonb_ops could look for entries that match all of the keys and then see if the ordering is correct. It looks to me like we'd need to index all paths in a document, or possibly hashes of all paths in a document. I don't think anything we have now will help much, unless my understanding is way off. 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
