google-bigquery - 在不展平结果的情况下向查询添加 ORDER BY 语句会导致 "Cannot query the cross product of repeated fields"

Question

Query:     "SELECT * FROM [table] ORDER BY id DESC LIMIT 10"    AllowLargeResults = true    FlattenResults = falsetable schema:    [        {            "name": "id",            "type": "STRING",            "mode": "NULLABLE"        },        {            "name": "repeated_field_1",            "type": "STRING",            "mode": "REPEATED"        },        {            "name": "repeated_field_2",            "type": "STRING",            "mode": "REPEATED"        }    ]

查询“SELECT * FROM [table] LIMIT 10”工作得很好。我在添加 order by 子句时收到此错误，即使 order by 未提及任何重复字段。

有什么方法可以让它工作吗？

Answer 1

ORDER BY 子句会导致 BigQuery 自动展平查询的输出，从而导致您的查询尝试生成 repeated_field_1 和 repeated_field_2 的叉积。

如果您不关心保留字段的重复性，您可以显式地FLATTEN 两者，这将导致您的查询生成原始查询提示的叉积。

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

除此之外，对于您对 ORDER BY 和输出重复字段的查询，我没有很好的解决方法。

另请参阅:BigQuery flattens result when selecting into table with GROUP BY even with “noflatten_results” flag on