时间戳

首页 > 解决方案 > Google BigQuery 中的嵌套字段

sql - Google BigQuery 中的嵌套字段

问题描述

我正在尝试取消嵌套一些嵌套字段并在 Google BigQuery 中进行子查询。

这工作正常。

SELECT id, 
custom_field_options
FROM `project.database.ticket_fields`

'custom_field_options' 是一个嵌套字段，这给我带来了一些问题。我有“custom_field_options.id”和“custom_field_options.name”以及其他几个嵌套字段。

这也很好用。

SELECT ticket_fields.id,
ticket_fields.raw_title,
ticket_custom_fields.name AS ticket_custom_fields_name,
ticket_custom_fields.raw_name AS ticket_custom_fields_raw_name,
ticket_custom_fields.value AS ticket_custom_fields_value,
ticket_fields.created_at
FROM `project.database.ticket_fields` AS ticket_fields
LEFT JOIN UNNEST(custom_field_options) AS ticket_custom_fields
GROUP BY 1,2,3,4,5,6

单独运行时，这两个查询都运行良好。现在，我正在尝试将第一个查询作为第二个查询的子查询运行，以从该查询中拉回所有 id，并在此基础上运行下面的查询。

SELECT ticket_fields.id,
ticket_fields.raw_title,
ticket_custom_fields.name AS ticket_custom_fields_name,
ticket_custom_fields.raw_name AS ticket_custom_fields_raw_name,
ticket_custom_fields.value AS ticket_custom_fields_value,
ticket_fields.created_at
FROM `project.database.ticket_fields` AS ticket_fields
LEFT JOIN UNNEST(custom_field_options) AS ticket_custom_fields
  WHERE id IN (SELECT id, 
  custom_field_options
  FROM `project.database.ticket_fields`)
GROUP BY 1,2,3,4,5,6

标签： sqlgoogle-bigquery

解决方案

您可以限定id列名以使引用明确：

SELECT ticket_fields.id,
ticket_fields.raw_title,
ticket_custom_fields.name AS ticket_custom_fields_name,
ticket_custom_fields.raw_name AS ticket_custom_fields_raw_name,
ticket_custom_fields.value AS ticket_custom_fields_value,
ticket_fields.created_at
FROM `project.database.ticket_fields` AS ticket_fields
LEFT JOIN UNNEST(custom_field_options) AS ticket_custom_fields
  WHERE ticket_fields.id IN (SELECT id, 
  custom_field_options
  FROM `project.database.ticket_fields`)
GROUP BY 1,2,3,4,5,6

（我假设这ticket_fields.id是id您要搜索的）

推荐阅读