时间戳

首页 > 解决方案 > 如何将旧版 SQL 请求移动到 BigQuery 中带有嵌套字段的标准 SQL？

sql - 如何将旧版 SQL 请求移动到 BigQuery 中带有嵌套字段的标准 SQL？

问题描述

我有像这样非常简单的遗留 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。我错在哪里以及如何解决？

标签： sqlgoogle-cloud-platformgoogle-bigquery

解决方案

下面是 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

推荐阅读