BigQuery嵌套字段:ARRAY类型的列单位不能在SELECT DISTINCT中使用 [英] BigQuery nested fields: column units of type ARRAY cannot be used in SELECT DISTINCT

问题描述

我想在BigQuery中选择表的唯一行，但是出现以下错误:"ARRAY类型的列单位不能在SELECT DISTINCT中使用".

I want to select unique rows of a table in BigQuery but I get the following error: "Column units of type ARRAY cannot be used in SELECT DISTINCT".

我的查询是

SELECT DISTINCT * from <table>

表架构

  {
    "mode": "NULLABLE",
    "name": "company_name",
    "type": "STRING"
  },
  {
    "mode": "NULLABLE",
    "name": "vat_number",
    "type": "STRING"
  },
  {
    "fields": [
      {
        "mode": "NULLABLE",
        "name": "name",
        "type": "STRING"
      }
    ],
    "mode": "REPEATED",
    "name": "industry",
    "type": "RECORD"
  }

如何在BigQuery中选择带有嵌套字段的表的不同行?

How can I select distinct rows of a table with nested fields in BigQuery?

推荐答案

您可以使用ANY_VALUE函数选择GROUP BY中的嵌套字段值(假设该值对于整个组都是相同的):

You can use the ANY_VALUE function to select the nested field values in a GROUP BY (assuming this value is the same for the entire group):

SELECT
  company_name,
  vat_number,
  ANY_VALUE(industry) AS industry
FROM
  <table>
GROUP BY
  company_name,
  vat_number

这篇关于BigQuery嵌套字段:ARRAY类型的列单位不能在SELECT DISTINCT中使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！