PostgreSQL поиск по массиву объектов в JSON

Автор вопроса: Jeid

Как сделать правильный SQL запрос для поиска по JSON полю Пример таблицы table1

id	sections
1	[{"key":"xxx", "name":"Name One"},{"key":"yyy", "name":"Name Two"}]
2	[{"key":"zzz", "name":"Name Tre"},{"key":"xxx", "name":"Name One"}]
3	[{"key":"aaa", "name":"Name For"},{"key":"zzz", "name":"Name Tre"}]

Необходимо сделать запрос типа

SELECT id FROM table1 WHERE sections->key IN('xxx','zzz')

Соответственно должно вернуть 1,2,3

Помимо этого было бы плюсом, как организовать корректный relation в Laravel по этому JSON полю.

Источник

Ответы (2 шт):

Автор решения: Akina

WITH cte AS (
  SELECT id, (json_array_elements(sections))->>'key' value
  FROM test
  )
SELECT DISTINCT id
FROM cte
WHERE value IN ('xxx', 'zzz');

→ Ссылка

Автор решения: Jeid

Придумал вот такой вариант:

SELECT id FROM table1 WHERE  sections @? '$[*].key ? (@ == "xxx" || @ == "zzz")';

UPD: и вот такой вариант

SELECT id FROM table1, jsonb_array_elements(sections) AS sec WHERE sec->>'key' IN('xxx', 'zzz');

→ Ссылка