Postgresql and jsonb: perform a like over all values of an array

Question

Jsonb seems great but I can't figure out how to perform a partial search (like) over an array. In my case it's an array of email:

["[email protected]", "[email protected]", "[email protected]"]

I want to be able to get a record that has an email like "email2".

I tried the following

SELECT * FROM users WHERE jsonb_array_elements(emails) like 'email2';

But it does not work (obviously).

If somebody can help... Thanks!

Answer 1

You can use the following query

SELECT 
 id_user 
FROM
 users
 JOIN LATERAL jsonb_array_elements(emails) AS x(email) ON true
WHERE
 email::text like '%email%' ;

jsonb_array_elements() returns a SET OF jsonb objects. You join them to user_id using a JOIN LATERAL. Then use a WHERE clause where you convert these jsonb objects to text, and then use your like '%email%'.

You can check a simple setup at dbfiddle.

NOTE: If you do not want repeated values: GROUP BY.

• McNets: You can use jsonb_array_elements_text to avoid the cast.