Selecting for a Jsonb array contains regex match

Question

Given a data structure as follows:

{"single":"someText", "many":["text1", text2"]}

I can query a regex on single with

WHERE JsonBColumn ->> 'single' ~ '^some.*'

And I can query a contains match on the Array with

WHERE JsonBColumn -> 'many' ? 'text2'

What I would like to do is to do a contains match with a regex on the JArray

WHERE JsonBColumn -> 'many' {Something} '.*2$'

score 6 · Answer 1 · answered Apr 24 '17 at 21:27

6

I found that it is also possible to convert the entire JSONB array to a plain text string and simply perform the regular expression on that. A side effect though is that a search on something like

xt 1", "text

would end up matching.

This approach isn't as clean since it doesn't search each element individually but it gets the job done with a visually simpler statement.

    WHERE JsonBColumn ->>'many'  ~ 'text2'

answered Apr 24 '17 at 21:27

user7916123

61
1
1

Keep in mind, that `JsonBColumn->>'many'` returns the array as text, i.e. `["text2"]` and this is the value you are searching on. Regex like `^text2` won't find anything. – fracz Dec 10 '21 at 12:31

score 2 · Accepted Answer · edited May 23 '17 at 12:25

Use jsonb_array_elements_text() in lateral join.

with the_data(id, jsonbcolumn) as (
    values
        (1, '{"single":"someText", "many": ["text1", "text2"]}'::jsonb)
    )

select distinct on (id) d.*
from 
    the_data d, 
    jsonb_array_elements_text(jsonbcolumn->'many') many(elem)
where elem ~ '^text.*';

 id |                    jsonbcolumn                     
----+----------------------------------------------------
  1 | {"many": ["text1", "text2"], "single": "someText"}
(1 row)

See also this answer.

If the feature is used frequently, you may want to write your own function:

create or replace function jsonb_array_regex_like(json_array jsonb, pattern text)
returns boolean language sql as $$
    select bool_or(elem ~ pattern)
    from jsonb_array_elements_text(json_array) arr(elem)
$$;

The function definitely simplifies the code:

with the_data(id, jsonbcolumn) as (
    values
        (1, '{"single":"someText", "many": ["text1", "text2"]}'::jsonb)
    )

select *
from the_data
where jsonb_array_regex_like(jsonbcolumn->'many', '^text.*');

Selecting for a Jsonb array contains regex match

2 Answers2

Linked