google-bigquery - 从数组中删除空值/空值

Question

我正在努力理解 BigQuery 中的数组和结构。当我在标准 SQL 中运行此查询时：

with t1 as (
  select 1 as id, [1,2] as orders
  union all
  select 2 as id, null as orders
)
select
  id,
  orders
from t1
order by 1

我在 json 中得到这个结果：

[
  {
    "id": "1",
    "orders": [
      "1",
      "2"
    ]
  },
  {
    "id": "2",
    "orders": []
  }
]

我想删除以删除 id = 2 的订单值，以便我得到：

[
  {
    "id": "1",
    "orders": [
      "1",
      "2"
    ]
  },
  {
    "id": "2"
  }
]

我怎样才能做到这一点？我是否需要添加另一个 CTE 来删除空值，如何？

标签： google-bigquery