I have a jsonb column called "global_settings" in my users table. In here, users can configure a key called "aliases" which contains an array of objects of the form:
[{"email":"[email protected]","active":true},{"email":"[email protected]","active":true}]
What I'd like to do is search all users to see if one has an alias for a specific email, such as "[email protected]". One of my users has this in his aliases array as described above so I can test the search.
I tried these queries, but they don't return any results:
select * from users where team_id = 1 and (global_settings->'aliases')::jsonb @> '"[email protected]"'
select * from users where team_id = 1 and (global_settings->'aliases')::jsonb ? '"[email protected]"'
select * from users where team_id = 1 and (global_settings->'aliases')::jsonb ? '[email protected]'
This however, does return all users on team 1 that have "aliases" configured, but of course doesn't allow me to find specific emails:
select * from users where team_id = 1 and (global_settings->'aliases')::jsonb is not null
Any ideas how I can search for users with specific email aliases?
EDIT:
With inspiration from Vao's answer below (Thank you!), I came up with this to get the full user row that contains a specific email:
select * from users as u
inner join (select id,jsonb_array_elements(
(global_settings->'aliases')::jsonb)->>'email' = '[email protected]'
as alias from users
where team_id = 1) as u2
on u2.alias = true and u.id = u2.id
with users(team_id,global_settings) as (values(1,'{"aliases":[{"email":"[email protected]","active":true},{"email":"[email protected]","active":true}]}'::jsonb)
)
, e as (select *,(jsonb_array_elements(global_settings->'aliases')->>'email' = '[email protected]')::int from users where team_id = 1)
select *, bit_or(int4) from e group by team_id,global_settings,int4 having bit_or(int4)::boolean;
team_id | global_settings | int4 | bit_or
---------+-----------------------------------------------------------------------------------------------------------+------+--------
1 | {"aliases": [{"email": "[email protected]", "active": true}, {"email": "[email protected]", "active": true}]} | 1 | 1
(1 row)
here I use jsonb_array_elements
to unnest array and bit_or
(which returns true if at least one in group is true) to check if such value exists