It's worked against every case I've tried it so far. On Mon, Apr 8, 2019 at 18:42 Mike Bayer <[email protected]> wrote:
> On Mon, Apr 8, 2019 at 3:25 PM Sherif Fanous <[email protected]> > wrote: > > > > 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() > > OK does that work though? The json_each() thing is not fully > supported and there is a recipe at > https://github.com/sqlalchemy/sqlalchemy/issues/3566#issuecomment-441931331 > I've had users using for years against PostgreSQL. > > We do want to add better support for functions you SELECT from but > it's a long term TODO. > > > > > > > > > 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. > > -- > 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 a topic in the > Google Groups "sqlalchemy" group. > To unsubscribe from this topic, visit > https://groups.google.com/d/topic/sqlalchemy/oujGTawONOI/unsubscribe. > To unsubscribe from this group and all its topics, 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. > -- 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.
