Search a JSON array for an object containing a value matching a pattern

Question

I have a DB with a jsonb column where each row essentially holds an array of name value pairs. Example for a single jsonb value:

[
    {"name":"foo", "value":"bar"},
    {"name":"biz", "value":"baz"},
    {"name":"beep", "value":"boop"}
]

How would I query for rows that contain a partial value? I.e., find rows with the JSON object key value ilike '%ba%'?

I know that I can use SELECT * FROM tbl WHERE jsoncol @> '[{"value":"bar"}]' to find rows where the JSON is that specific value, but how would I query for rows containing a pattern?

Answer 1

Postgres 12 or newer

Use an SQL/JSON path expression, where you can test with like_regex:

SELECT *
FROM   tbl t
WHERE  t.jsoncol @? '$[*].value ? (@ like_regex "ba" flag "i")';

Only add flag "i" for case insensitive matching.

Can an index. See:

• Find rows containing a key in a JSONB array of records

Postgres 11 or older

There are no built in jsonb operators nor any indexes supporting this kind of filter directly (yet).

I suggest an EXISTS semi-join:

SELECT t.*
FROM   tbl t
WHERE  EXISTS (
   SELECT FROM jsonb_array_elements(t.jsoncol) elem
   WHERE  elem->>'value' LIKE '%ba%'
   );

It avoids redundant evaluations and the final DISTINCT step you would need to get distinct rows with a plain CROSS JOIN.

If this still isn't fast enough, a way more sophisticated specialized solution for the given type of query would be to extract a concatenated string of unique values (with a delimiter that won't interfere with your search patterns) per row in an IMMUTABLE function, build a trigram GIN index on the functional expression and use the same expression in your queries.

Related:

• Search for nested values in jsonb array with greater operator
• Create Postgres JSONB Index on Array Sub-Object

Aside, if your jsonb values really look like the example, you could trim a lot of noise and just store:

[
   {"foo":"bar"},
   {"biz":"baz"},
   {"beep":"boop"}
]

Answer 2

You can use the function jsonb_array_elements() in a lateral join and use its result value in the WHERE clause:

select distinct t.* 
from my_table t
cross join jsonb_array_elements(jsoncol)
where value->>'value' like '%ba%'

Please, read How to query jsonb arrays with IN operator for notes about distinct and performance.