Search code examples
arraysjsonpostgresqljsonb

Postgres search JSONB array containing objects for specific value


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

Solution

  • 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