Thank you very much!
W dniu piątek, 15 czerwca 2018 02:43:08 UTC+2 użytkownik Mike Bayer napisał:
>
> On Thu, Jun 14, 2018 at 11:25 AM, Maciek Olko <macie...@gmail.com
> <javascript:>> wrote:
> > Hello,
> > I'm having difficulty in expressing such PostgreSQL code in SQLAlchemy:
> >
> > select string_agg(test::jsonb#>>'{}', ' / ') from (select
> > jsonb_array_elements('["raz","dwa"]') as test) as foo;
> >
> > At the end I'd like to have reference to real JSON column value (which
> is
> > guaranteed to be JSON array) instead of JSON ["raz", "dwa"].
> >
> > Especially problematic is accessing the dynamic field in JSON-ic way.
> >
> > Does anyone have already encounter such or similar query? I'd be
> grateful
> > for any help.
>
> you can of course express the query in terms of the raw SQL, however
> the example below illustrates the results of both the raw SQL as well
> as the SQLAlchemy Core EL version:
>
>
> from sqlalchemy import *
> from sqlalchemy.dialects.postgresql import JSON, JSONB
>
> foo = select([
> func.jsonb_array_elements(
> literal(["raz", "dwa"], JSON),
> type_=String
> ).label('test')
> ]).alias('foo')
>
> stmt = select([
> func.string_agg(
> cast(foo.c.test, JSONB)[()].astext,
> " / "
> )
> ])
>
> e = create_engine("postgresql://scott:tiger@localhost/test", echo='debug')
>
> print(
> e.execute(
> """select string_agg(test::jsonb#>>'{}', ' / ') from """
> """(select jsonb_array_elements('["raz","dwa"]') as test) as
> foo"""
> ).fetchall()
> )
>
> print(e.execute(stmt).fetchall())
>
>
> both return the row ('raz / dwa',).
>
>
>
>
>
> >
> > Regards,
> > Maciej
> >
> > --
> > 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 sqlalchemy+...@googlegroups.com <javascript:>.
> > To post to this group, send email to sqlal...@googlegroups.com
> <javascript:>.
> > 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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
