Searching partial value on array field of jsonb column using PostgreSQL 12

Question

I have a table that contains a jsonb field called data. This field can contain arbitrary data. One of them could be emails. Like;

{
  ...
  "emails": ["[email protected]", "[email protected]"],
  ...
}

So, email field is Array. Normally if it would a string i could easily use ILIKE or ~* on this data, however, being an array makes me think twice. Because it still works.

The query i ran:

select * from test_tbl where "data" ->> 'emails' ILIKE '%spenc%';
-- OR --
select * from test_tbl where "data" ->> 'emails' ~* 'spenc';

See also: https://rextester.com/FFQ83366

Is it correct and safe to use ilike and/or ~* operator on array element? Or is there a better way to do this job?

Please note that, i need partial search because client can give only partial search query. So, searching exact email address is not an option for me (can be but it shouldn't be limited to)

I'm using PostgreSQL 12.2

score 5 · Accepted Answer · 2020-04-24 21:00:27Z

5

You need to unnest the array:

select t.*
from test_tbl t
where exists (select *
              from jsonb_array_elements_text(t.data -> 'emails') as t(email)
              where t.email like '%spence%');

Alternatively you can use a JSON path expression

select *
from test_tbl t
where data @? '$.emails[*] ? (@ like_regex ".*spenc.*")'

Online example

edited Apr 24, 2020 at 21:00

answered Apr 24, 2020 at 20:36

user330315

Sign up to request clarification or add additional context in comments.

1 Comment

Dennis Over a year ago

I choose second example.

Collectives™ on Stack Overflow

Searching partial value on array field of jsonb column using PostgreSQL 12

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related