Pattern matching on jsonb key / value

Question

I am using PostgreSQL 9.4. There is a jsonb column in my table:

 CREATE TABLE "PreStage".transaction ( transaction_id serial NOT NULL, transaction jsonb CONSTRAINT pk_transaction PRIMARY KEY (transaction_id) ); CREATE INDEX idxgin ON "PreStage".transaction USING gin (transaction); 

I store transactions in terms of key / value in a JSONB column. One of the requirements is to search for the client name from the key value, so I run a query, for example:

 SELECT transaction as data FROM "PreStage".transaction WHERE transaction->>('HCP_FST_NM') ilike ('%neer%'); 

Whatever I do, it seems like the query does not like the GIN index. How can I force a query to use a GIN index to search for case-insensitive patterns?

I tried changing the jsonb column to text by indexing it with gin_trgm_ops , then find the desired text, then convert the result to json , and then search for the desired key / value. This approach does not seem to work.

Answer 1

By default, the GIN index class operator jsonb_ops does not allow matching full text by value. Details:

• What is the correct index for querying structures in arrays in Postgres jsonb?

The best indexing strategy depends on your overall situation. There are many options. To just cover the one key that you presented, you can use the trigram functional index . You have already tested gin_trgm_ops , so you are already familiar with the pg_trgm add-on . For those who are not:

• PostgreSQL LIKE query performance changes

After installing the module:

 CREATE INDEX idxgin ON "PreStage".transaction USING gin ((transaction->>'HCP_FST_NM') gin_trgm_ops); 

Then this request is supported:

 SELECT transaction AS data FROM "PreStage".transaction WHERE transaction->>'HCP_FST_NM' ILIKE '%neer%'; 

I also removed the unnecessary parentheses.

Depending on the unknown details, there are various options for optimizing index coverage.

For example, if many rows don’t have the “HCP_FST_NM” key at all, make a partial index to exclude irrelevant rows and keep the index small:

 CREATE INDEX idxgin ON "PreStage".transaction USING gin ((transaction->>'HCP_FST_NM') gin_trgm_ops) WHERE transaction ? 'HCP_FST_NM'; 

? being the jsonb containment jsonb .
And add the same predicate to every query that this index should use:

 SELECT transaction AS data FROM "PreStage".transaction WHERE transaction->>'HCP_FST_NM' ILIKE '%neer%' AND transaction ? 'HCP_FST_NM'; -- even if that seems redundant.