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

2
votes

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?

2
sqljsonpostgresqlpattern-matching

2 Answers

3
votes

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:

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"}
]
1
votes

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.