Query SQL Server JSON in SQLAlchemy

user_9986939 0
Viewed 157

I'm trying to create this query with SQLAlchemy:

SELECT JSON_QUERY(json_field, '$.emails') FROM table

Where the field looks like:

{"emails": ["email1","email2"...]}

I've got something like:

session.query(func.json_query(json_field, '$.emails').all()

But it looks like json_query isn't in func. Any help would be appreciated.

sqlalchemy sql-server python json
1 Answers

it looks like json_query isn't in func

.func() does indeed render json_query. For a test table named [json_test] …

id  json_field
--  -------------------------------  
 1  {"emails": ["email1","email2"]}
 2  {"emails": ["email3","email4"]}

… this works for me:

class JsonTest(Base):
    __tablename__ = "json_test"
    id = sa.Column(sa.Integer, primary_key=True, autoincrement=False)
    json_field = sa.Column(sa.JSON)


with sa.orm.Session(engine) as session:
    qry = session.query(sa.func.json_query(JsonTest.json_field, "$.emails"))
    """SQL emitted:
    2021-08-16 16:24:13,203 INFO sqlalchemy.engine.Engine SELECT json_query(json_test.json_field, ?) AS json_query_1 
    FROM json_test
    2021-08-16 16:24:13,203 INFO sqlalchemy.engine.Engine [generated in 0.00039s] ('$.emails',)
    """
    results = session.execute(qry).fetchall()
    print(results)
    # [('["email1","email2"]',), ('["email3","email4"]',)]
user_21443900
Related