René's URL Explorer Experiment

{"@context":"https://schema.org","@type":"DiscussionForumPosting","headline":"Unable compare value from records array","articleBody":"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?\r\n\r\n#### Environment details\r\n\r\n  - OS type and version: MacOS, 12.0 beta\r\n  - Python version: 3.9.1\r\n  - pip version: 21.1.2\r\n  - `google-cloud-bigquery` version: 2.22.1\r\n\r\n#### Steps to reproduce\r\n\r\n 1. Create the BQ table `Mock` with fields:\r\n    - name: `mock_id` | type: `STRING` | mode: `NULLABLE`\r\n  \r\n 2. Create the BQ table `AnotherMock` with fields:\r\n    - name: `objects` | type: `RECORD` | mode: `REPEATED`\r\n      - name: `object_id` | type: `STRING` | mode: `NULLABLE`\r\n 3. Run code below (It gonna fail).\r\n 4. Run SQL below (it gonna success).\r\n\r\n#### SQL example\r\n```sql\r\nSELECT *\r\nFROM db.dataset.mock mock\r\nINNER JOIN (SELECT another_mock_objects FROM db.dataset.another_mock, UNNEST(objects) as another_mock_objects) another_mock\r\nON mock.mock_id = another_mock.another_mock_objects.object_id\r\n```\r\n\r\n\r\n#### Code example\r\n\r\n```python\r\n subquery = session.query(\r\n    func.unnest(another_mock_model.c.objects).label('another_mock_objects')\r\n).subquery()\r\n\r\nsession.execute(\r\n    select([]) \\\r\n        .select_from(\r\n            mock_model\r\n                .join(\r\n                    another_mock_model,\r\n                    subquery.c.another_mock_objects.object_id == mock_model.c.mock_id,\r\n                )\r\n        )\r\n)\r\n```\r\n\r\n#### Stack trace\r\n`AttributeError: 'Comparator' object has no attribute 'object_id'`\r\n\r\n","author":{"url":"https://github.com/elsiniestra","@type":"Person","name":"elsiniestra"},"datePublished":"2021-07-29T20:18:36.000Z","interactionStatistic":{"@type":"InteractionCounter","interactionType":"https://schema.org/CommentAction","userInteractionCount":9},"url":"https://github.com/233/python-bigquery-sqlalchemy/issues/233"}