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

jsonpostgresql

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:

["email1@foo.com", "email2@foo.com", "email3@foo.com"]

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!

Best Answer

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.