Thread: 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

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

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

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