在 BigQuery 中查询多个重复字段 [英] Querying multiple repeated fields in BigQuery

问题描述

我有一个包含多个非嵌套重复字段的架构.

I have a schema that contains multiple repeated fields which are not nested.

我正在尝试查询交叉产品，但出现错误:无法查询重复字段的叉积..."

I'm trying to query the cross product, but I get an error: "Cannot query the cross product of repeated fields..."

如果我只查询 2 个重复字段，我可以将其中一个展平.然而，我对查询超过 2 个重复字段很感兴趣，我无法理解 FLATTEN 语法如何支持这一点.

If I query only 2 repeated fields, I can flatten one of them. Yet, I'm interested in querying more that 2 repeated fields, and I can't understand how FLATTEN syntax supports this.

例如说表结构是:a1，记录(重复):a1.b1，整数a2，记录(重复):a2.b1，整数a3，记录(重复):a3.b1，整数

For example, say the table structure is: a1, record (repeated) : a1.b1, integer a2, record (repeated) : a2.b1, integer a3, record (repeated) : a3.b1, integer

我要查询:从选项卡中选择 (*)

I want to query: select (*) from tab

推荐答案

您可以在 FLATTEN 中使用嵌套的子选择.它需要在 select 语句周围添加一个额外的括号.(不幸的是，语法有点丑陋).例如

You can use a nested subselect inside the FLATTEN. It requires an extra paren around the select statement. (the syntax is kind of ugly, unfortunately). e.g.

SELECT
  ...
FROM (
    FLATTEN((
      SELECT
        ...
      FROM (
          FLATTEN((
            SELECT
              ...
            FROM
              table),
            f1)
          )
        ),
      f2)
    )
  )

这篇关于在 BigQuery 中查询多个重复字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！