Using Postgres @> Operator and json_build_object in SQLAlchemy

Question

I have a query using several Postgres-specific operations:

SELECT 
    a.row_id, 
    a.name
FROM
    a 
JOIN
    b
ON
    b.json_record @> json_build_object('path', json_build_object('to', a.name))::jsonb

My understanding is that the @> operator acts as a comparison, but the comparison methods for JSONB in the SQLAlchemy docs reference only keys, not values.

https://docs.sqlalchemy.org/en/latest/dialects/postgresql.html#sqlalchemy.dialects.postgresql.JSONB.Comparator

I'm not clear on how I could design this query through SQLAlchemy short of using a raw query.

Edit 1

Based on this answer, I gave the below a try.

session \
   .query(A_Table) \
   .join(
      B_Table.json_record.contains({
         'path': {
            'to': A_Table.name
         }
      })
   )

It, however, resulted in an error from the line 'to': A_Table.name:

AttributeError: Neither 'BinaryExpression' object nor 'Comparator' object has an attribute 'selectable'
sqlalchemy/orm/query.py", line 2206, in join
from_joinpoint=from_joinpoint,
File "<string>", line 2, in _join

So I instead attempted

session \
   .query(A_Table) \
   .filter(
      B_Table.json_record.contains({
         'path': {
            'to': A_Table.name
         }
      })
   )

Which at least resulted in a different error, this one with some SQL generation from SQLAlchemy:

sqlalchemy.exc.StatementError: (builtins.TypeError) 
Object of type 'InstrumentedAttribute' is not JSON serializable 
[SQL: 'SELECT a.row_id AS a_id, a.name AS a_name FROM a, b 
WHERE b.json_record @> %(json_record_1)s'] [parameters: [{}]]

This SQL is close to what I was aiming for, and may be acceptable, but the example provided in the answer presumes I know the value ahead of time, when what I want to do is a comparison against the row value. I typically would do:

.filter([a.name == b.json_record['path']['to'].astext])

But I'm also trying to leverage an optimization from a gin index on this JSONB column, which leaves me needing the @> operator.

Edit 2

Based on the answer from Ilja Everilä, I was able to track down the SQLAlchemy method implemented in the source code, and using the sql-json method was able to get the SQL almost there.

session \
   .query(A_Table) \
   .join(
      B_Table.json_record.contains({
         json_sql({'path': json_sql({
            'to': A_Table.name
         }
      })
   )

Giving me the SQL:

SELECT 
    a.row_id, 
    a.name
FROM
    a 
JOIN
    b
ON
    b.json_record @> json_build_object('path', json_build_object('to', a.name))

The problem with this output is that instead of:

json_build_object(..., json_build_object(...))

Valid Postgres syntax should be:

json_build_object(..., json_build_object(...))::jsonb

Both the answer's and the source code's approach relies on the _FunctionGenerator, which can build the function, but it's not clear how something can get appended to the end of the method during the compile when going that route.

Edit 3

NVM - the answer's author pointed out jsonb_build_object(...) would fit with this model w/o the flag.

Related, somewhat: stackoverflow.com/questions/39460387/…. Operator @> is contains() in SQLAlchemy for JSONB columns. Are you using the ORM or Core? — Ilja Everilä
– Ilja Everilä, Commented Feb 12, 2019 at 17:14
@IljaEverilä - gave it a go. Looks close, but I need to be able to do column value comparisons, not pass in a value from memory/code. Any ideas? Using ORM. — getglad
– getglad, Commented Feb 12, 2019 at 19:23
An aside: the <expr>::jsonb is Postgresql specific syntax for a type cast, not a flag (unless I misinterpret the use of the word here). You could replace it with the SQL standard CAST(<expr> AS jsonb). SQLAlchemy has multiple ways to produce a cast expression, for example the cast() construct. — Ilja Everilä
– Ilja Everilä, Commented Feb 13, 2019 at 19:20

Ilja Everilä · Accepted Answer · 2019-02-13 06:22:55Z

4

The linked Q/A handles the case of using literal values, as you've noticed. The solution is to combine using contains() in SQLA and jsonb_build_object() in Postgresql as you were trying before:

session.query(A_Table) \
    .filter(
        B_Table.json_record.contains(
            func.jsonb_build_object( 
                'path',
                func.jsonb_build_object('to', A_Table.name)
            )
        )
    )

My understanding is that the @> operator acts as a comparison, but the comparison methods for JSONB in the SQLAlchemy docs reference only keys, not values.

The SQLAlchemy documentation for JSONB.Comparator.contains() seems to be a bit poorly written. Compare

Boolean expression. Test if keys (or array) are a superset of/contained the keys of the argument jsonb expression.

to Postgresql documentation for @>:

Does the left JSON value contain the right JSON path/value entries at the top level?

You could hide the details of building jsonb in a helper function:

def build_jsonb(obj):
    if isinstance(obj, dict):
        pairs = [(k, build_jsonb(v)) for k, v in obj.items()]
        return func.jsonb_build_object(*[arg for p in pairs for arg in p])

    elif isinstance(obj, list):
        return func.jsonb_build_array(*[build_jsonb(v) for v in obj])

    else:
        return obj

and then use it in the original query:

session.query(A_Table) \
    .filter(
        B_Table.json_record.contains(
            build_jsonb({'path': {'to': A_Table.name}})))

If you wish to use the explicit JOIN syntax:

session.query(A_Table).\
    join(B_Table, B_Table.json_record.contains(
        build_jsonb({'path': {'to': A_Table.name}})))

edited Feb 13, 2019 at 6:22

answered Feb 13, 2019 at 5:50

Ilja Everilä

53.4k9 gold badges137 silver badges141 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

getglad Over a year ago

Thanks for this! See my second edit - there is a similar built in method in sqlalchemy-utils, but neither method appends ::jsonb to the json_build_object

Ilja Everilä Over a year ago

There's no need for the cast, if you just use jsonb_build_object, as build_jsonb does. If you must use the sqlalchemy-utils version, add the cast explicitly: json_sql(...).cast(JSONB).

getglad Over a year ago

Ah - I missed the diff btwn jsonb_build_object vs json_build_object there - excellent!

Collectives™ on Stack Overflow

Using Postgres @> Operator and json_build_object in SQLAlchemy

Edit 1

Edit 2

Edit 3

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Edit 1

Edit 2

Edit 3

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related