@fallncrlss could you please share the SQLAlchemy model definitions you used for the code examples?

@fallncrlss could you please share the SQLAlchemy model definitions you used for the code examples?

In my case, due to some limitations of business logic, I should define SQLAlchemy BQ models dynamically like the example below:

from sqlalchemy import create_engine, Table, MetaData


engine = create_engine('BQ_CONNECTION_URL', pool_size=POOL_SIZE)
SomeTable = Table('some_table_name', MetaData(bind=engine), autoload=True)

@fallncrlss please share the code you used to define Mock and AnotherMock (or whatever you actually named them). I think I know what's going on, but being able to reproduce what you did exactly would help to be sure and reduce the chance of wasting everyone's time.

There are two issues.

1. Currently, record/struct data are exposed to SQLAlchemy as JSON. So, to access your record fields, you need to use mapping syntax.

   For example, given a table containing structs with an object_id field, to select records with an object id of 'x':

   select([mock_structs], mock_structs.c.object['object_id']=='x')

2. To use unnest, you have to use alias, rather than label:

   subquery = session.query(
       func.unnest(another_mock.c.objects).alias('another_mock_objects')
   ).subquery()
   

   However, there's a bug in handling of unnest: unnest isn't handled correctly #283

@fallncrlss please share the code you used to define Mock and AnotherMock (or whatever you actually named them). I think I know what's going on, but being able to reproduce what you did exactly would help to be sure and reduce the chance of wasting everyone's time.

Mock and AnotherMock model schemas define directly in BigQuery, and in the code I define them dynamically as in the example above using Table and the table names.

@fallncrlss please share the code you used to define Mock and AnotherMock (or whatever you actually named them). I think I know what's going on, but being able to reproduce what you did exactly would help to be sure and reduce the chance of wasting everyone's time.

Mock and AnotherMock model schemas define directly in BigQuery, and in the code I define them dynamically as in the example above using Table and the table names.

OK, fair enough. Then I think I reproduced your scenario.

More info on what I said about using alias:

Some references invoking functions like unnest:

• alias, https://docs.sqlalchemy.org/en/14/core/functions.html#sqlalchemy.sql.functions.FunctionElement.alias

  Note, in particular, the need to tack on .column, which is kind of weird.

  An alternative...

• column_valued , https://docs.sqlalchemy.org/en/14/core/functions.html#sqlalchemy.sql.functions.FunctionElement.column_valued

See: https://docs.sqlalchemy.org/en/14/tutorial/data_select.html#tutorial-functions-table-valued

1. Currently, record/struct data are exposed to SQLAlchemy as JSON.  So, to access your record fields, you need to use mapping syntax.

Well, that doesn't work either. :( #293

@fallncrlss see

for an example of how to do what you want to do with sqlalchemy-bigquery 1.2.0 or later.

Note that dot notation now works if the name doesn't conflict with an internal name, so:

subquery.c.another_mock_objects.object_id == mock_table.c.mock_id

and if there's a chance of a conflict, you can use upper case to disambiguate:

subquery.c.another_mock_objects.OBJECT_ID == mock_table.c.mock_id