Extract a record's field

[Andrey Semenov]

I need to construct a query like

SELECT (jsonb_each('{"a": 1, "b": 2}')).key

jsonb_each() returns a RECORD type which in turn has it's own fields named key and value. Yes, in PostgreSQL it MUST be also wrapped into braces, because of extraction and executing precedence (or it will try to select a key column from the jsonb_each() column - that leads to SQL engine error raised). It's not obvious how to query those fields while constructing a query (or make a dot-notated reference to a custom sub-field when this capability is provided by an engine).

Also, dialects.util.dottedgetter does not work with func.* (returns None if called against)

[Mike Bayer]

this works:

from sqlalchemy import func, select, literal, column

from sqlalchemy.dialects import postgresql

stmt = select([column('key')]).select_from(
    func.jsonb_each(
        literal({"a": 1, "b": 2}, type_=postgresql.JSON)
    )
)

from sqlalchemy import create_engine
e = create_engine("postgresql://scott:tiger@localhost/test", echo='debug')

print e.execute(stmt).fetchall()

2015-10-19 09:49:50,610 INFO sqlalchemy.engine.base.Engine SELECT key 
FROM jsonb_each(%(param_1)s)
2015-10-19 09:49:50,610 INFO sqlalchemy.engine.base.Engine {'param_1': '{"a": 1, "b": 2}'}
2015-10-19 09:49:50,613 DEBUG sqlalchemy.engine.base.Engine Col ('key',)
2015-10-19 09:49:50,613 DEBUG sqlalchemy.engine.base.Engine Row (u'a',)
2015-10-19 09:49:50,613 DEBUG sqlalchemy.engine.base.Engine Row (u'b',)
[(u'a',), (u'b',)]

resolved?

[Andrey Semenov]

Well, the actual case is to build such a query:

                    select
                        (jsonb_each(contents)).key::int,
                        (jsonb_each(contents)).value::varchar::int,
                        id,
                        ts_spawn
                    from assets_transactions

so I need to make a table-like expression from an sqlalchemy.sql.functions.* expression which in turn is a table-like expression from which I can query an arbitrary column. Your example still does not make it obvious on making arbitrary dot-notated fields access.

[Mike Bayer]

I've extended #2450 to tackle the issue of adding a builtin for PG's composite types in general, not just reflecting them. A RECORD type would then be trivial.

in this case, the approach below illustrates first a total brute force approach, and the second refines this with a more succinct approach. In all cases completely public API is used with no need to use custom compilation; an example of doing similar things with totally custom compilation is at https://bitbucket.org/zzzeek/sqlalchemy/wiki/UsageRecipes/PGArrayIndex.. As always, the text() construct is available if you just need to emit a query that is DB-specific and doesn't need compositional features.

The query emitted at the end is:

SELECT assets_transactions.id, ((jsonb_each(assets_transactions.contents)) . key) :: int AS anon_1, (((jsonb_each(assets_transactions.contents)) . value) :: varchar) :: int AS anon_2 
FROM assets_transactions

from sqlalchemy import *
from sqlalchemy.dialects import postgresql
from sqlalchemy.sql.expression import Grouping

m = MetaData()

assets_transactions = Table(
    'assets_transactions', m,
    Column('id', Integer, primary_key=True),
    Column('contents', postgresql.JSONB)

)

e = create_engine("postgresql://scott:tiger@localhost/test", echo=True)

m.drop_all(e)
m.create_all(e)

e.execute(
    assets_transactions.insert(), [
        {"contents": {"1": 1, "4": 2}},
        {"contents": {"2": 2, "5": 3}},
        {"contents": {"3": 3, "6": 4}},
    ]
)

# 1. total brute force
print e.execute(
    select([
        assets_transactions.c.id,
        (Grouping(
            func.jsonb_each(assets_transactions.c.contents)
        ).op(".")(literal_column("key"))).cast(Integer),
        (Grouping(
            func.jsonb_each(assets_transactions.c.contents)
        ).op(".")(literal_column("value"))).cast(String).cast(Integer)
    ]).select_from(assets_transactions)
).fetchall()

# 2. build a function w/ the access built in
from sqlalchemy.sql import functions


class jsonb_each(functions.GenericFunction):

    def __getitem__(self, key):
        expr = Grouping(self).op(".")(column(key, String))

        if key == 'value':
            expr = (expr.op("::")(literal_column("varchar")))
        expr = expr.op("::")(literal_column("int"))
        return expr


print e.execute(
    select([
        assets_transactions.c.id,
        jsonb_each(assets_transactions.c.contents)['key'],
        jsonb_each(assets_transactions.c.contents)['value'],
    ]).select_from(assets_transactions)
).fetchall()

[Andrey Semenov]

Tried both approaches. Will stand on the second for now:

class JSONB_EACH_RECORD(GenericFunction):
    def __init__(self, *args, **kwargs):
        super(JSONB_EACH_RECORD, self).__init__(*args, **kwargs)
        self.name = 'jsonb_each'

    def __getitem__(self, item):
        return Grouping(self).op('.')(column(item, String))


q = Session.query(
    AssetTransaction.mission_id.label('id'),
    AssetTransaction.ts_spawn.label('ts_spawn'),
    cast(JSONB_EACH_RECORD(AssetTransaction.contents)['key'], Integer).label('asset_id'),
    cast(cast(JSONB_EACH_RECORD(AssetTransaction.contents)['value'], String), Integer).label('amount'),
)

Also, it may be an outdated SQLA version issue, but Grouping object has no .cast attribute (got AttributeError on it)

[Mike Bayer]

.cast should be available in recent 1.0.x releases

[Mike Bayer]

Duplicate of #2450.

[Mike Bayer]

changing dupe target

[Mike Bayer]

Duplicate of #3566.