CODEWITHSUNDEEP
pythonjsonpostgresqlpsycopg2
PepperoniPizza
PepperoniPizza

Reputation: 9112

SQLAlchemy Postgres query is key exists in JSON

I have a model Definition:

class Definition:
    meta = Column(MutableDict.as_mutable(JSON))

It stores any JSON so I want to query if a given key exists in this field.

I need something like:

defs = db.query(Definition).filter(
    Definition.meta.has_key('translation')).all()

MySQL for example has a function named json_contains and I could use it:

defs = db.query(Definition).filter(
    func.json_contains(
        Definition.meta, 'translation') == 1).all()

How can I achieve this in PostgreSQL

Upvotes: 1

Views: 970

Answers (2)

snakecharmerb
snakecharmerb

Reputation: 55759

has_key() - given as a hypothetical example in the question - exists and is the correct method to call. It will generate PostgreSQL's ? operator.

import sqlalchemy as sa
from sqlalchemy.dialects.postgresql import JSONB

engine = sa.create_engine('postgresql+psycopg2:///test', echo=True)

tbl = sa.Table(
        't44397358',
        sa.MetaData(),
        sa.Column('c', JSONB),
        )
tbl.drop(engine, checkfirst=True)
tbl.create(engine)

values = [{'c': v} for v in [{'a': 1}, {'a': 2}, {'b': 3}, {'c': {'a': 2}}]]

with engine.begin() as conn:
    conn.execute(tbl.insert(), values)

with engine.connect() as conn:
    rows = conn.execute(sa.select(tbl).where(tbl.c.c.has_key('a')))
    for row in rows:
        print(row)

Output

024-04-25 17:30:12,749 INFO sqlalchemy.engine.Engine SELECT t44397358.c 
FROM t44397358 
WHERE t44397358.c ? %(c_1)s
2024-04-25 17:30:12,749 INFO sqlalchemy.engine.Engine [generated in 0.00036s] {'c_1': 'a'}
({'a': 1},)
({'a': 2},)

Upvotes: 0

jmunsch
jmunsch

Reputation: 24089

Whenever I am unsure of how to use the sqlalchemy bits, I hack a version together, and go from there:

json_select = '''SELECT jdoc->'key_name1', jdoc->'translation'
FROM Definition 
WHERE jdoc @> '{"translation": 1, "key_name1": "hello there"}'
;
'''

print([x for x in db.engine.execute(json_select)])

note:

psycopg2 changed in version 2.5.4: added jsonb support.

Referenced:

Upvotes: 1

Related Questions