Effectively searching through entire 1 level nested JSONB [closed]

Question

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.

Answer 1

Update: In Postgres 12 or later consider an SQL/JSON path expression. Related:

• Query JSON array of obejcts against multiple values

---

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
  LANGUAGE sql IMMUTABLE PARALLEL SAFE AS
$func$
SELECT string_agg(value, '|') FROM jsonb_each_text($1)
$func$;

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

Answer 2

For large volumes of data, perhaps you can cut down on the cost of fulltext search by adding a fulltext GIN index, either for the whole json (to_tsvector(jsonb_column::text)) or the output of a function that would extract and concatenates all of its relevant values.

Then you could use the @@ ts_query() operator to obtain a reduced subset of likely matches, and search within that subset using ILIKE or ~.