SQLAlchemy: how to filter with dictionary value?-CodePudding

I have a database table like this:

 ------------------------------------------------------------- 
| id | res_id    | path                   | overrides         |
|-------------------------------------------------------------|
| 1  | res_1     | res_1                  | {"enabled": True} |
| 2  | res_1.1   | res_1.res_1.1          | {"enabled": False}|
| 3  | res_1.2   | res_1.res_1.2          | {"enabled": False}|
| 4  | res_1.1.1 | res_1.res_1.1.res_1.1.1| {"enabled": False}|
 -------------------------------------------------------------

The overrides is for setting a breaking point to inheritance. So, if I query the res_1.1.1m I'd normally get also resources 1 & 2, because of the inheritance. But now, there is "enabled": True in 1 and it doesn't inherit anywhere, so the query would return only 2 & 4.

The question is, how can I filter out objects where the overrides is "enabled": True? I've tried e.g. this:
.filter(MyModel.overrides["enabled"] is not True), this doesn't seem to work.

I've also tried using dictionary methods get(), values() and keys(), but they give me an error, e.g. AttributeError: Neither 'InstrumentedAttribute' object nor 'Comparator' object associated with MyModel.overrides has an attribute 'get'.

The field overrides in a JSONB -field.

CodePudding user response：

There is two things here you're missing.

Firstly, .filter(col is not True) cannot be compiled by SQLAlchemy, I recommend you use .filter(col == False) or .filter(col.is_(False)).

Then to access the value inside the the JSONB dict, you need to cast it to a SQL type, for instance with .as_boolean().

Putting both of those together, we get:

stmt = (
    select(MyModel)
    .filter(MyModel.overrides["enabled"].as_boolean() == False)
)

Full script demo:

import sqlalchemy as sa
from sqlalchemy.dialects import postgresql

metadata_obj = sa.MetaData()

t = sa.Table(
    "t72632977",
    metadata_obj,
    sa.Column("id", sa.Integer, primary_key=True),
    sa.Column("res_id", sa.String),
    sa.Column("path", sa.String),
    sa.Column("overrides", postgresql.JSONB),
)

engine = sa.create_engine(
    "postgresql psycopg2://postgres:postgres@localhost:5432/postgres",
    echo=True,
    future=True,
)

metadata_obj.create_all(engine)

with engine.begin() as con:
    con.execute(
        sa.insert(t),
        [
            {
                "res_id": "res_1",
                "path": "res_1",
                "overrides": {"enabled": True},
            },
            {
                "res_id": "res_1.1",
                "path": "res_1.res_1.1",
                "overrides": {"enabled": False},
            },
            {
                "res_id": "res_1.2",
                "path": "res_1.res_1.2",
                "overrides": {"enabled": False},
            },
            {
                "res_id": "res_1.1.1",
                "path": "res_1.res_1.1.res_1.1.1",
                "overrides": {"enabled": False},
            },
        ],
    )

with engine.connect() as con:
    r = con.execute(
        sa.select(t).filter(t.c.overrides["enabled"].as_boolean() == False)
    )
    print(r.all())

# emits
# SELECT t72632977.id, t72632977.res_id, t72632977.path, t72632977.overrides 
# FROM t72632977 
# WHERE CAST((t72632977.overrides ->> %(overrides_1)s) AS BOOLEAN) = false

# returns
# [
#     (2, 'res_1.1', 'res_1.res_1.1', {'enabled': False}),
#     (3, 'res_1.2', 'res_1.res_1.2' , {'enabled': False}),
#     (4, 'res_1.1.1', 'res_1.res_1.1.res_1.1.1', {'enabled': False}),
# ]