Postgres ищет массив JSONB, содержащий объекты для определенного значения

У меня есть столбец jsonb под названием «global_settings» в моей таблице пользователей. Здесь пользователи могут настроить ключ под названием «псевдонимы», который содержит массив объектов формы:

[{"email":"[email protected]","active":true},{"email":"[email protected]","active":true}]

Я бы хотел найти всех пользователей, чтобы узнать, есть ли у них псевдоним для определенного адреса электронной почты, например «[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]"'

select * from users where team_id = 1 and (global_settings->'aliases')::jsonb ? '[email protected]'

Однако это возвращает всех пользователей в команде 1, у которых настроены «псевдонимы», но, конечно же, не позволяет мне найти конкретные электронные письма:

select * from users where team_id = 1 and (global_settings->'aliases')::jsonb is not null

Есть идеи, как я могу искать пользователей с определенными псевдонимами электронной почты?

РЕДАКТИРОВАТЬ:

Вдохновленный ответом Вао ниже (спасибо!), Я придумал это, чтобы получить полную строку пользователя, содержащую конкретное электронное письмо:

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

postgresql arrays json jsonb
person BryanP    schedule 30.10.2017    source источник

Ответы (1)


arrow_upward
1
arrow_downward 

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)

здесь я использую jsonb_array_elements для отмены вложенности массива и bit_or (который возвращает истину, если хотя бы один в группе истинен), чтобы проверить, существует ли такое значение

person Vao Tsun    schedule 30.10.2017