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.

  • 1
    Please Edit your question and add the CREATE TABLE statements for the tables in question adding the desired output. Use formatted text please, no screen shots Commented Apr 1, 2018 at 19:31
  • Starting the pattern with '%' creates an un-anchored search pattern. There is no way around indexing this without a trigram index. Not sure what you want though from this. Commented Apr 1, 2018 at 19:48

2 Answers 2


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

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:

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:


CREATE OR REPLACE FUNCTION f_all_value_string(jsonb)
  RETURNS text
SELECT string_agg(value, '|') FROM jsonb_each_text($1)

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


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';



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 ~.

Not the answer you're looking for? Browse other questions tagged or ask your own question.