[SQLAlchemy Core] JSONB in dynamic select

[Maciek Olko]

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.

Regards,

Maciej

[Mike Bayer]

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.
> To post to this group, send email to sqlal...@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

[Maciek Olko]

Here is what I have for now:
sa.func.string_agg(sa.select([('jsonb_array_elements')['{}'].astext]).select_from(sa.select([
sa.func.jsonb_array_elements(c.acc_licence_plates)])), ' / ')

But it's wrong.

Regards,

Maciej

[Maciek Olko]

Thank you very much!