Effectively searching through entire 1 level nested JSONB Let's say we need to check if a jsonb column contains a particular value matching by a substring in any of the value (non-nested, only first level).

How does one effectively optimize a query to search entire JSONB column for each value?

Is there some good alternative to doing ILIKE %val% on jsonb datatype casted 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 go about optimizing a query like that when in need to search for pattern %val% in records containing different keys configuration in a column? Is there a better alternative to extracting each key-value pair to text and performing an ILIKE/POSIX search?

Mainly, I'm looking for a different alternative to unpacking a whole jsonb field into separate key rows with their values as text.
For pattern-matching, there is currently (pg 10) no better way with standard operators and indexes than to unnest values and test each individually. At least, use an efficient EXISTS query:

SELECT *
FROM   tbl
WHERE  EXISTS (SELECT FROM jsonb_each_text(jsonb_column) WHERE value ~* 'val');


A specialized solution for this particular use case: concatenate all string values per row with an IMMUTABLE function and create a trigram GIN index on the expression. You need the additional module pg_trgm installed. Read this first if you are not familiar:


How is LIKE implemented?


Function:

CREATE OR REPLACE FUNCTION f_all_value_string(jsonb)
  RETURNS text AS
$func$
SELECT string_agg(value, '|') FROM jsonb_each_text($1)
$func$  LANGUAGE sql IMMUTABLE;


Chose a separator that never interferes with your patterns - '|' in the example.

Index:

CREATE INDEX tbl_all_value_string_trigram_idx ON tbl
USING GIN (f_all_value_string(jsonb_column) gin_trgm_ops); 


Query (matching the index):

SELECT * FROM tbl WHERE f_all_value_string(jsonb_column) ~* 'val';


Related:


Search for nested values in jsonb array with greater operator
Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL