CODEWITHSUNDEEP
google-bigquery
Dylan Douglas
Dylan Douglas

Reputation: 128

Adding an ORDER BY statement to a query without flattening results leads to "Cannot query the cross product of repeated fields"

Query: 
    "SELECT * FROM [table] ORDER BY id DESC LIMIT 10"
    AllowLargeResults = true
    FlattenResults = false

table schema:
    [
        {
            "name": "id",
            "type": "STRING",
            "mode": "NULLABLE"
        },
        {
            "name": "repeated_field_1",
            "type": "STRING",
            "mode": "REPEATED"
        },
        {
            "name": "repeated_field_2",
            "type": "STRING",
            "mode": "REPEATED"
        }
    ]

The query "SELECT * FROM [table] LIMIT 10" works just fine. I get this error when I add an order by clause, even though the order by does not mention either repeated field.

Is there any way to make this work?

Upvotes: 3

Views: 235

Answers (1)

Danny Kitt
Danny Kitt

Reputation: 3251

The ORDER BY clause causes BigQuery to automatically flatten the output of a query, causing your query to attempt to generate a cross product of repeated_field_1 and repeated_field_2.

If you don't care about preserving the repeatedness of the fields, you could explicitly FLATTEN both of them, which will cause your query to generate the cross product that the original query is complaining about.

SELECT *
FROM FLATTEN(FLATTEN([table], repeated_field_1), repeated_field_2)
ORDER BY id DESC
LIMIT 10

Other than that, I don't have a good workaround for your query to both ORDER BY and also output repeated fields.

See also: BigQuery flattens result when selecting into table with GROUP BY even with “noflatten_results” flag on

Upvotes: 2

Related Questions