[sqlalchemy] [SQLAlchemy Core] JSONB in dynamic select

Discussion:

Maciek Olko

2018-06-14 15:25:46 UTC

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

Mike Bayer

2018-06-15 00:43:01 UTC

Permalink

Post by Maciek Olko
Hello,
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:***@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',).

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

Maciek Olko

2018-06-16 19:43:54 UTC

Permalink

Thank you very much!

Post by Maciek Olko

Post by Maciek Olko
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

Post by Maciek Olko
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
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,
" / "
)
])
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',).

Groups

Post by Maciek Olko
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send

an
<javascript:>.

Post by Maciek Olko
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Maciek Olko

2018-06-15 14:35:06 UTC

Permalink

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