Extract a record's field
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)
Comments (8)
-
repo owner -
reporter 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. -
repo owner 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()
-
reporter 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 (gotAttributeError
on it) -
repo owner .cast
should be available in recent 1.0.x releases -
repo owner - changed status to duplicate
Duplicate of #2450.
-
repo owner - changed status to open
changing dupe target
-
repo owner - changed status to duplicate
Duplicate of #3566.
- Log in to comment
this works:
resolved?