Query SQL Server JSON in SQLAlchemy

Question

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.

I was not able to find it at first, I was searching for mssql not sql server. I found it once someone edited this question. Thanks. — Robert Riley
– Robert Riley, Commented Aug 18, 2021 at 12:59

Gord Thompson · Accepted Answer · 2021-08-16 22:27:50Z

1

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"]',)]

answered Aug 16, 2021 at 22:27

Gord Thompson

125k39 gold badges252 silver badges458 bronze badges

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

2 Comments

Robert Riley Over a year ago

Interesting, I ended up accessing the json by query(Table.field['emails']) for a similar result.

Gord Thompson Over a year ago

Thanks for that. It renders the exact same SQL as noted in the answer. TIL.

Collectives™ on Stack Overflow

Query SQL Server JSON in SQLAlchemy

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related