sqlalchemy: Add missing json/jsonb operators in PostgreSQL

Since the json/jsonb support in SQLAlchemy was added PostgreSQL has added some new operators, like @@ and @? in v12 https://www.postgresql.org/docs/12/datatype-json.html

It would be nice to add the missing operators to the relevant types.

One thing to finalize are the names of these operators, that are not immediately clear from the postgresql docs

First raised in https://github.com/sqlalchemy/sqlalchemy/discussions/7146

About this issue

Original URL
State: closed
Created 3 years ago
Comments: 15 (14 by maintainers)

Most upvoted comments

Guilherme Martins Crocetti has proposed a fix for this issue in the main branch:

Implement missing #-, @? and @@ Postgres’ JSONB operators. https://gerrit.sqlalchemy.org/c/sqlalchemy/sqlalchemy/+/4336

sqla-tester on Jan 3, 2023

it seems not

import sqlalchemy as sa
from sqlalchemy.dialects import postgresql

tt = sa.Table('tt', sa.MetaData(), sa.Column('col', postgresql.JSONB))

sub = tt.c.col - 'x'
concat = tt.c.col + 'x'

print(sub.compile(dialect=postgresql.dialect()))
print(concat.compile(dialect=postgresql.dialect()))

prints

tt.col - %(col_1)s
tt.col || %(col_1)s

CaselIT on Oct 6, 2021

ah ha! the code has this string: " -> " and I was searching for "->" and '->' (no leading/trailing whitespace).

I’ll recheck the source and update the table above

jvanasco on Oct 6, 2021

These are jsonpath operators. They are defined in the docs in table 9.45

Table 9.45. Additional jsonb Operators

Operator	Right Operand Type	Description	Example
@?	jsonpath	Does JSON path return any item for the specified JSON value?	‘{“a”:[1,2,3,4,5]}’::jsonb @? ‘$.a[*] ? (@ > 2)’
@@	jsonpath	Returns the result of JSON path predicate check for the specified JSON value. Only the first item of the result is taken into account. If the result is not Boolean, then null is returned.	‘{“a”:[1,2,3,4,5]}’::jsonb @@ ‘$.a[*] > 2’

There is a note a bit down the page that hints at potential names, as they seem to be shorthand for corollary functions with some presets :

Note The jsonb_path_exists, jsonb_path_match, jsonb_path_query, jsonb_path_query_array, and jsonb_path_query_first functions have optional vars and silent arguments.

If the vars argument is specified, it provides an object containing named variables to be substituted into a jsonpath expression.

If the silent argument is specified and has the true value, these functions suppress the same errors as the @? and @@ operators.

jvanasco on Oct 5, 2021