如何将遗留sql请求移动到bigquery中带有嵌套字段的标准sql中？

neekobn8 于 2021-07-24 发布在 Java

关注(0)|答案(1)|浏览(370)

我有非常简单的遗留sql结构

SELECT
  report_date,
  campaign.bundleId,
  campaign.countryCode,
  campaign.stageNum
FROM
  [...records_20200708],
  [...records_20200707]

我试着把它转换成标准sql

SELECT
  report_date,
  (select bundleId from unnest(campaign)) as campaign_bundleId,
  (select countryCode from unnest(campaign)) as campaign_countryCode,
  (select stageNum from unnest(campaign)) as campaign_stageNum

FROM
  `...records_*`

它不起作用。我得到一个错误： Scalar subquery produced more than one element . 我哪里错了，怎么纠正？

sql google-bigquery google-cloud-platform

来源：https://stackoverflow.com/questions/62794726/how-to-move-legacy-sql-request-into-standard-sql-with-nested-fields-in-bigquery

1条答案

按热度按时间

3yhwsihp1#

下面是bigquery标准sql来处理您收到的错误消息 Scalar subquery produced more than one element -只需添加 ARRAY 所以bq知道如何处理这些 more than one element 输入输出

SELECT
  report_date,
  ARRAY(SELECT bundleId FROM UNNEST(campaign)) AS campaign_bundleId,
  ARRAY(SELECT countryCode FROM UNNEST(campaign)) AS campaign_countryCode,
  ARRAY(SELECT stageNum FROM UNNEST(campaign)) AS campaign_stageNum

FROM
  `...records_*`

或者，取决于预期的输出-您可以使用下面的

SELECT
  report_date,
  c.bundleId AS campaign_bundleId,
  c.countryCode AS campaign_countryCode,
  c.stageNum AS campaign_stageNum

FROM
  `...records_*`,
UNNEST(campaign) AS c

赞(0）回复(0）举报 2021-07-24

我来回答

如何将遗留sql请求移动到bigquery中带有嵌套字段的标准sql中？

1条答案

相关问题

热门标签

最新问答