Как правильно найти пересечение двух jsonb массивов в postgres?

Question

[mShpakov]

Есть jsonb колонка вида [20, 56]
И есть json массив для запроса [0, 20]
Есть у постгреса оператор &&
Если сделать запрос вида column && [0, 20]

То отвалится ошибка
[42883] ERROR: operator does not exist: jsonb && unknown Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.

Answer 1

[Melkij]

Есть у постгреса оператор &&

"есть оператор" - это сказано немного сильно преждевременно. Например, на моей тестовой 9.6 таких операторов 8 штук.
Потому что нет отдельно операторов. Операторы определены только неразрывно с типами данных своих операндов (двух или одного для префиксных и постфиксных операторов)

select oprkind, l.typname, oprname, r.typname from pg_operator join pg_type l on oprleft = l.oid join pg_type r on oprright = r.oid where oprname = '&&';
 oprkind |  typname  | oprname |  typname  
---------+-----------+---------+-----------
 b       | box       | &&      | box
 b       | polygon   | &&      | polygon
 b       | tinterval | &&      | tinterval
 b       | circle    | &&      | circle
 b       | inet      | &&      | inet
 b       | tsquery   | &&      | tsquery
 b       | anyarray  | &&      | anyarray
 b       | anyrange  | &&      | anyrange

И jsonb здесь явно отсутствует.

Значит берём CREATE OPERATOR в руки, пара хранимок - и вот у вас новый оператор готов. Это PostgreSQL, он так умеет и позволяет делать.

Плюс посмотреть в релевантный jsonb раздел документации: https://www.postgresql.org/docs/current/static/fun...
Но с операциями на массивами в json там немного печально и неудобно.