indexing pattern on jsonb column

Question

I want to improve performance of my queries using GIN index on jsonb column for pattern matching

For example, I have a table defined as:

CREATE TABLE my_table (
  uuid text,
  doc jsonb
);

In every row of table doc has path {A,B}. B is an object, possibly empty. There is a one more optional element in path: it can be absent, can be C, D etc.

Can I create a single index for pattern matching that will be used in all cases listed below?

SELECT doc#>>'{A,B}'
FROM my_table
WHERE doc#>>'{A,B}' ILIKE '%example%';

SELECT doc#>>'{A,B,C}'
FROM my_table
WHERE doc#>>'{A,B,C}' ILIKE '%example%';

SELECT doc#>>'{A,B,D}'
FROM my_table
WHERE doc#>>'{A,B,D}' ILIKE '%example%';

I've tried to create index as:

CREATE INDEX my_index
ON my_table
USING GIN ((doc#>>'{A,B}') gin_trgm_ops)

Unfortunately, it works only for queries with path {A,B}, but not {A,B,C}

Answer 1

One index might work for all the cases, but you do have to write the query in an unnatural way for ones that don't exactly match the index expression:

SELECT doc#>>'{A,B,C}'
FROM my_table
WHERE doc#>>'{A,B}' ILIKE '%example%' AND doc#>>'{A,B,C}' ILIKE '%example%';

doc#>>'{A,B,C}' ILIKE '%example%' usually implies that doc#>>'{A,B}' ILIKE '%example%', but that is not something PostgreSQL will reason out for you. It also isn't perfectly true in all cases, characters that require escaping in JSON will be escaped in doc#>>'{A,B}' but not in doc#>>'{A,B,C}' if the end result of one is stringified JSON object while the other is just a plain string.

select '{"0":{"a":"b\"c"}}'::jsonb #>> '{0}';
---------------
 {"a": "b\"c"}

select '{"0":{"a":"b\"c"}}'::jsonb #>> '{0,a}';
----------
 b"c