BigQuery nested fields: column units of type ARRAY cannot be used in SELECT DISTINCT

Question

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".

My query is

SELECT DISTINCT * from <table>

Table schema

  {
    "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"
  }

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

Answer 1

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