Re: jsonb existence queries are misimplemented by jsonb_ops

Peter Geoghegan <pg(at)heroku(dot)com> writes:
> On Wed, May 7, 2014 at 1:47 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> No, wait, containment *doesn't* look into sub-objects:
>>
>> regression=# select * from j where f1 @> '{"foo": {"bar": "baz"}}';
>> f1
>> -------------------------
>> {"foo": {"bar": "baz"}}
>> (1 row)
>>
>> regression=# select * from j where f1 @> '{"bar": "baz"}';
>> f1
>> ----
>> (0 rows)

> Yes it does. It's just not intended to work like that. You have to
> "give a path" to what you're looking for.

Right, so the top-level item in the query has to be at top level in the
searched-for row. This means that we *could* distinguish top-level from
non-top-level keys in the index items, and still be able to generate
correct index probes for a containment search (since we could similarly
mark the generated keys as to whether they came from top level in the
query object).

So the question is whether that's a good idea or not. It seems like it is
a good idea for the current existence operator, and about a wash for the
current containment operator, but perhaps a bad idea for other definitions
of containment.

In any case, something here needs to change. Thoughts?

regards, tom lane