On Thu, Jun 14, 2018 at 11:25 AM, Maciek Olko <maciej.o...@gmail.com> 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+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.
--
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.
