Guess I had to write this post to figure out the answer!!!!
Here's the query in ORM
session.query(func.json_extract(text('value'), '$.text').label(
'channel_name')).select_from(Channel, func.json_each(Channel.json,
'$.display_names')).filter(func.json_extract(Channel.json, '$.number') == 1
).all()
On Monday, April 8, 2019 at 3:03:22 PM UTC-4, Sherif Fanous wrote:
>
> Hello,
>
> I'm storing JSON documents in one of the columns of an SQLite table. The
> following query works fine if executed from the SQLite CLI
>
> SELECT json_extract(value, '$.text') as channel_name
> FROM channel, json_each(channel.json, '$.display_names')
> WHERE json_extract(channel.json, '$.number') == 1
>
> I've been trying to write this query using the ORM (session.query()) but
> am not able to wrap my head around how to do and after numerous failed
> attempts I've reverted to using Core and calling execute on a connection
> resource procured from the engine
>
> connection.execute("SELECT json_extract(value, '$.text') AS channel_name "
> "FROM channel, json_each(channel.json,
> '$.display_names') "
> "WHERE json_extract(channel.json, '$.number') == 1")
>
> Is it possible to write this query using the ORM?
>
> Thanks
>
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
