Why no jsonb_exists_path()?

Josh Berkus <josh@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

On 06/09/2015 02:40 PM, Tom Lane wrote:

Josh Berkus <josh@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

On 06/09/2015 05:30 PM, Andrew Dunstan wrote:

On 06/09/2015 02:40 PM, Tom Lane wrote:

Josh Berkus <josh@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

Josh,

On Tue, Jun 9, 2015 at 9:16 PM, Josh Berkus <josh@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