Effectively search all 1 level nested JSONB in ​​Postgres

Question

Let's say we need to check if the jsonb column contains a specific value corresponding to a substring in any of the values ​​(not nested, only the first level).

How to efficiently optimize a query to search the entire JSONB column (that means each key) for a value?

Is there any good alternative to doing ILIKE %val% in jsonb datatype discarded to text?

 jsonb_each_text(jsonb_column) ILIKE '%val%' 

As an example, consider this data:

 SELECT '{"col1": "somevalue", "col2": 5.5, "col3": 2016-01-01, "col4": "othervalue", "col5": "yet_another_value"}'::JSONB 

How would you like to optimize such a query when you needed to look for the %val% pattern in records containing different key configurations for different rows in the jsonb column?

I know that searching with the previous and next % sign is inefficient, so he searches for the best way, but finds it with difficulty. In addition, indexing all fields explicitly is not an option, since they are different for each type of record and will create a huge set of indexes.

Is there a better alternative to extracting each key-value pair into text and doing an ILIKE / POSIX search?

Answer 1

If you know that you only need to request a few known keys, you can simply index these expressions.

This is too simple but self explanatory example:

 create table foo as SELECT '{"col1": "somevalue", "col2": 5.5, "col3": "2016-01-01", "col4": "othervalue", "col5": "yet_another_value"}'::JSONB as bar; create index pickfoo1 on foo ((bar #>> '{col1}')); create index pickfoo2 on foo ((bar #>> '{col2}')); 

This is a basic idea, even it is not useful for similar queries, but you can do more things (depending on your needs).

For example: if you only need a case-insensitive match, that would be enough:

 -- Create index over lowered value: create index pickfoo1 on foo (lower(bar #>> '{col1}')); create index pickfoo2 on foo (lower(bar #>> '{col2}')); -- Check that it matches: select * from foo where lower(bar #>> '{col1}') = lower('soMEvaLUe'); 

NOTE. This is just an example: if you follow the explanation of the previous choice, you will see that postgres actually performs a sequential scan instead of using an index. But this is because we are testing on a single row table, which is not ordinary. But I'm sure you can test it with a larger table; -)

While huge tables, even such as queries, should use the index if the flirt plug does not appear at the beginning of the line (but this is not a jsonb question, but a question of the btree indices themselves).

If you need to optimize queries, for example:

 select * from foo where bar #>> '{col1}' ilike '%MEvaL%'; 

... then you should use GIN or GIST indexes instead.