SQLAlchemy Postgres query is key exists in JSON

Question

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

are you using psycopg2?

jmunsch
– jmunsch

2017-06-06 18:34:24 +00:00
Commented Jun 6, 2017 at 18:34 — jmunsch
– jmunsch, Commented Jun 6, 2017 at 18:34
Yes, using psycopg2

PepperoniPizza
– PepperoniPizza

2017-06-06 18:35:16 +00:00
Commented Jun 6, 2017 at 18:35 — PepperoniPizza
– PepperoniPizza, Commented Jun 6, 2017 at 18:35

jmunsch · Accepted Answer · 2017-06-06 18:45:50Z

1

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:

answered Jun 6, 2017 at 18:45

jmunsch

24.3k12 gold badges102 silver badges120 bronze badges

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

Comments

snakecharmerb · Accepted Answer · 2024-04-25 16:43:21Z

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},)

Collectives™ on Stack Overflow

SQLAlchemy Postgres query is key exists in JSON

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related