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.

closed as unclear what you're asking by Evan Carroll, mustaccio, Marco, McNets, Erik Darling Apr 7 '18 at 1:34

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.


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