CODEWITHSUNDEEP
sqlpostgresqljsonb
Dennis
Dennis

Reputation: 1975

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

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

Upvotes: 0

Views: 2075

Answers (1)

user330315
user330315

Reputation:

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

Upvotes: 5

Related Questions