jsonb nested values and indexes - Mailing list pgsql-hackers

From Pavel Stehule
Subject jsonb nested values and indexes
Date
Msg-id CAFj8pRARjJ2f8ta4VqNZDAv8gyVLXEUcmzyc317Kz8=eTU-TuQ@mail.gmail.com
Whole thread Raw
List pgsql-hackers
Hello

I don't know a doc about jsonb indexes

"But in jsonb_path_ops, each index item is a hash of both the value and the key(s) leading to it; for example to index {"foo": {"bar": "baz"}}, a single index item would be created incorporating all three of foo, bar, and baz into the hash value. Thus a containment query looking for this structure would result in an extremely specific index search; but there is no way at all to find out whether foo appears as a key. On the other hand, a jsonb_ops index would create three index items representing foo, bar, and baz separately;"

What keys I can search on this jsonb?

postgres=# create table x(a jsonb);
CREATE TABLE
postgres=# insert into x values('{"foo": {"bar": "baz"}}');
INSERT 0 1

postgres=# select * from x where a ? 'foo';
            a           
-------------------------
 {"foo": {"bar": "baz"}}
(1 row)

postgres=# select * from x where a ? 'bar';
 a
---
(0 rows)

postgres=# select * from x where a ? 'baz';
 a
---
(0 rows)


???

Regards

Pavel

pgsql-hackers by date:

Previous
From: Bruce Momjian
Date:
Subject: Re: pg_upgrade fails: Mismatch of relation OID in database 8.4 -> 9.3
Next
From: Andrew Dunstan
Date:
Subject: jsonb inequality operators