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!
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.
