Поиск элемента в jsonb_array_elements (2D-массив)
У меня есть таблица с 2 столбцами R (id int, dat jsonb). Столбец b jsonb состоит из двумерного массива [][]. Например :
id| dat
1 | {"name":"a","numbers":[[1,2],[3,4],[5,6],[1,3]]}
2 | {"age":5,"numbers":[[1,1]]}
3 | {"numbers":[[5,6],[6,7]]}
Я пытаюсь найти все идентификаторы, содержащие определенное число в одном из этих подмассивов. Я использовал 2 решения и хочу понять, почему первое не работает:
1)
select * from R
where exists (
select from jsonb_array_elements(R.dat->'numbers')->>0 first,jsonb_array_elements(range.data->'numbers')->>1 second where first::decimal= 1 and second::decimal= 1
);
ERROR: syntax error at or near "->>"
LINE 3: ...t from jsonb_array_elements(R.dat->'numbers')->>0 first,j...
SELECT *
FROM R
WHERE EXISTS (
SELECT FROM jsonb_array_elements(R.dat-> 'numbers') subarray
WHERE (subarray->>0)::decimal = 1 and (subarray->>1)::decimal = 1
);
Кроме того, я видел, что индекс джина не обрабатывает этот оператор, так что здесь поможет какой-либо индекс?
Ответы 1
Ваш первый запрос вызывает ошибку, потому что вы можете использовать только таблицы (не выражения значений) в предложении FROM.
Вы можете сделать второй запрос немного проще:
select *
from r
where exists (
select from jsonb_array_elements(dat->'numbers') subarray
where subarray = '[1,1]'
);
или используя функцию в боковом соединении:
select r.*
from r
cross join jsonb_array_elements(dat->'numbers')
where value = '[1,1]';
Нет индекса, который мог бы поддерживать эти запросы из-за использования jsonb_array_elements().
У вас может возникнуть соблазн использовать оператор сдерживания @> следующим образом:
select *
from r
where dat->'numbers' @> '[[1,1]]'::jsonb
id | dat
----+------------------------------------------------------------
1 | {"name": "a", "numbers": [[1, 2], [3, 4], [5, 6], [1, 3]]}
2 | {"age": 5, "numbers": [[1, 1]]}
(2 rows)
К сожалению, как видите, это не работает так, как вы могли ожидать. Использование оператора на массивах немного сложно, так как он работает следующим образом: array1 @> array2 истинно, если для каждого элемента j из array2 существует i в array1 такое, что i @> j. Следовательно, согласно документация:
the order of array elements is not significant when doing a containment match, and duplicate array elements are effectively considered only once.