Как проверить содержится ли хотя бы один элемент массива в другом массиве jsonb

Question

Дан массив [5, 6, 16, 33, 36] и имеются следующие строки в таблице в БД:

{"share": {"users": ["demo"], "groups": [14, 59, 33, 9]}}
{"share": {"users": ["demo", "system"], "groups": [1]}}
{"share": {"users": ["system"], "groups": [14, 59, 33, 9]}}
{"share": {"users": ["test": [5]}}

Данные строки хранятся в столбце params, тип - jsonb.

Ожидаемый результат - если в groups есть хотя бы 1 совпадение с любым числом из массива, то такая строка подходит в результирующий набор.

Результат должен быть следующий:

{"share": {"users": ["demo"], "groups": [14, 59, 33, 9]}}
{"share": {"users": ["system"], "groups": [14, 59, 33, 9]}}
{"share": {"users": ["test": [5]}}

в 1 строке есть совпадение по элементу 33
в 3 строке есть совпадение по элементу 33
в 4 строке есть совпадение по элементу 5

Я написал реализацию с помощью запроса

SELECT d.params -> 'share' #> '{groups}' @> '[5, 6, 16, 33, 36]', d.params FROM data_d d

Но по всем строчкам получается false, так как насколько я понимаю ожидается что все элементы массива будут содержаться в поле.

Answer 1

select d.params from d
where d.params @? '$.share.groups[*] ? (@ == 33 || @ == 36)'

прямое обращение через стандартный jsonpath, но у него нет синтаксиса сравнения со списком значений, нужно описать в jsonpath выражении каждый элемент через логическое или.

select d.params from d where 
  array(select jsonb_array_elements_text(d.params->'share'->'groups')::int) 
  && array[5, 6, 16, 33, 36];

либо вот так сконвертировать в postgres массив, для которого уже есть подходящий оператор "у массивов есть хотя бы один общий элемент"

Answer 2

На мой взгряд Ваша проблема в том, что вы используете оператор @>,он проверяет вхождение всего массива целиком. А Вам надо проверить наличие хотя бы одного совпадения. Можно использовать оператор ANY для сравнения элементов массива. Вот пример запроса с использованием ANY:

SELECT params
FROM data_d
WHERE EXISTS (
  SELECT 1
  FROM jsonb_array_elements(params -> 'share' -> 'groups') AS group
  WHERE group::int = ANY('{5, 6, 16, 33, 36}'::int[])
);

Этот запрос использует jsonb_array_elements для развертывания массива groups в отдельные элементы, а затем сравнивает каждый элемент с любым элементом из заданного массива. Если хотя бы одно совпадение найдено, то строка будет включена в результат.

БЛОГ НА HUSL

Как проверить содержится ли хотя бы один элемент массива в другом массиве jsonb

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