Postgresql – How to filter on multiple values for a given set of keys using JSONB, and an index

jsonpostgresql

Suppose we have a table (named rel_1) with an info field of type JSONB. The content of this field is client specific and may be changed every once in a while, but let's assume it to be of the form {"ki":"vi"} / i=0->N.

Now suppose we want to search for specific rows that have k0="foo" AND k1="bar". From the documentation, One can simply use the containment @> operator like so:

SELECT * FROM rel_1 WHERE info @> '{"k0":"foo", "k1":"bar"}';

While being elegant and working perfectly fine, it can also make use of a GIN index. It's also agnostic to what actual data for info might be. As long as the clients use the same keys for writes and reads, the DB side can completely ignore it and still do a fine job at optimizing requests using the index.

The only problem is that this approach fails if we change the requirement a little bit so that multiple values are allowed for a given key: (k0="foo" OR k0="bar") AND k1="baz".

Is there any way to achieve this while retaining the elegance and efficiency of the first solution?

Best Answer

I see 2 rather straight forward approaches depending on how many values we're looking for.

The first option is to look for a document containing the single valued keys (in this example: where info @> '{"k1":"bar"}'; and wrap that with additional filtering for all the other values: info->>k0 in <the values>

The 2nd option would be to create full documents for the various options, in your example it means creating 2 documents to search by. If the number of values is small them it's not too much of a problem and the index will be used.

Related Solutions

Postgresql – How does postgres use the schema search path when making indexes

Given the following setup:

regress=> CREATE SCHEMA A;
CREATE SCHEMA
regress=> CREATE SCHEMA B;
CREATE SCHEMA
regress=> SET search_path = B, public;
SET
regress=> CREATE TABLE bar(email text);
CREATE TABLE
CREATE UNIQUE INDEX index_bar_on_email ON bar USING btree (email);
CREATE INDEX

I cannot reproduce the problem you report in PostgreSQL 9.2:

regress=> SET search_path = A, B;
SET
regress=> CREATE TABLE bar(email text);
CREATE TABLE
regress=> CREATE UNIQUE INDEX index_bar_on_email ON bar USING btree (email);
CREATE INDEX

However, rather than using the search_path, it's safer to use explicit schema-qualification. For example, I'd re-write the above as:

regress=> RESET search_path;
RESET
regress=> SHOW search_path ;
  search_path   
----------------
 "$user",public
(1 row)

CREATE TABLE B.bar(email text);
CREATE UNIQUE INDEX b.index_bar_on_email ON b.bar USING btree (email);
CREATE TABLE A.bar(email text);
CREATE UNIQUE INDEX index_bar_on_email ON A.bar USING btree (email);

The indexes are automatically created in the schema of their associated table; see:

regress=> \di B.
                  List of relations
 Schema |        Name        | Type  | Owner | Table 
--------+--------------------+-------+-------+-------
 b      | index_bar_on_email | index | craig | bar
(1 row)

regress=> \di A.
                  List of relations
 Schema |        Name        | Type  | Owner | Table 
--------+--------------------+-------+-------+-------
 a      | index_bar_on_email | index | craig | bar
(1 row)

Update based on question change:

Yes, what you've shown does look like a Rails adapter issue. It's checking to see whether the index exists in any schema. It should be checking to see whether the first table of the given name in the search_path has the named index.

I would write the query differently. I'd leave off the join on pg_class entirely, instead using a cast to regclass to handle search_path resolution for me. I'd use the resulting oid to search for the index. Compare original, then updated, below. Note that the updated query does require search_path to be set first.

regress=> SELECT DISTINCT i.relname, d.indisunique, d.indkey, t.oid, am.amname
 FROM pg_class t, pg_class i, pg_index d, pg_am am
 WHERE i.relkind = 'i'
 AND d.indexrelid = i.oid
 AND d.indisprimary = 'f'
 AND t.oid = d.indrelid
 AND t.relname = 'bar'
 AND i.relnamespace IN (SELECT oid FROM pg_namespace WHERE nspname IN ('b','a') )
 AND i.relam = am.oid
 ORDER BY i.relname
;
      relname       | indisunique | indkey |  oid  | amname 
--------------------+-------------+--------+-------+--------
 index_bar_on_email | t           | 1      | 28585 | btree
 index_bar_on_email | t           | 1      | 28592 | btree
(2 rows)

regress=> SELECT DISTINCT i.relname, d.indisunique, d.indkey, 'bar'::regclass::oid, am.amname
 FROM pg_class i, pg_index d, pg_am am
 WHERE i.relkind = 'i'
 AND d.indexrelid = i.oid
 AND d.indisprimary = 'f'
 AND 'bar'::regclass = d.indrelid
 AND i.relam = am.oid
 ORDER BY i.relname                                                              
;
      relname       | indisunique | indkey | regclass | amname 
--------------------+-------------+--------+----------+--------
 index_bar_on_email | t           | 1      | 28585    | btree
(1 row)

PostgreSQL – Nullable Columns vs JSONB for Storing User Profile

You can use "best of both worlds". Implement every regular column you know of as dedicated column (with exact proper data type, possibly with NOT NULL / CHECK / PK / FK etc. constraints to enforce detailed rules, default values and comment for documentation) and add a "catch-all" jsonb column for very rare cases / later additions / highly volatile attributes that do not need any of the features mentioned above.

To be clear: "highly volatile" does not mean the value is updated a lot, which would clearly speak against any document type like jsonb, making updates more complex and expensive. It means the attribute per se (on the meta level) changes a lot (different names, rare and and pops in and out of existence over time or similar).

Most people underestimate how cheap NULL storage really is:

Best Answer

Related Solutions

Postgresql – How does postgres use the schema search path when making indexes

PostgreSQL – Nullable Columns vs JSONB for Storing User Profile

Related Question