Unable compare value from records array

[elsiniestra]

I need to create a join of 2 BQ tables where one of them has an id field that has to be compared with the record id in the repeated field (array of records) of another one. I'm struggling with SQLAlchemy implementation of the SQL query that works fine. Tried to solve this problem in different ways, but nothing was successful, most relevant and similar to sql-like solution below. Probably, there are some troubles with sqlalchemy interpretation of SQL queries or result fetch either, aren't?

Environment details

• OS type and version: MacOS, 12.0 beta
• Python version: 3.9.1
• pip version: 21.1.2
• google-cloud-bigquery version: 2.22.1

Steps to reproduce

1. Create the BQ table Mock with fields:

   • name: mock_id | type: STRING | mode: NULLABLE

2. Create the BQ table AnotherMock with fields:

   • name: objects | type: RECORD | mode: REPEATED
     • name: object_id | type: STRING | mode: NULLABLE

3. Run code below (It gonna fail).

4. Run SQL below (it gonna success).

SQL example

SELECT *
FROM db.dataset.mock mock
INNER JOIN (SELECT another_mock_objects FROM db.dataset.another_mock, UNNEST(objects) as another_mock_objects) another_mock
ON mock.mock_id = another_mock.another_mock_objects.object_id

Code example

 subquery = session.query(
    func.unnest(another_mock_model.c.objects).label('another_mock_objects')
).subquery()

session.execute(
    select([]) \
        .select_from(
            mock_model
                .join(
                    another_mock_model,
                    subquery.c.another_mock_objects.object_id == mock_model.c.mock_id,
                )
        )
)

Stack trace

AttributeError: 'Comparator' object has no attribute 'object_id'

[jimfulton]

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

[elsiniestra]

@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)

[jimfulton]

@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.

[jimfulton]

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

[elsiniestra]

@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.

[jimfulton]

@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.

[jimfulton]

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