BigQuery中解析JSON：提取数组首行并完全扁平化数据表

我在BigQuery中解析JSON数据，其中包含一个多列多行的数组。我需要将数组展开获取所有列，但只保留每个数组的第一行，最终得到完全扁平化的表。

我试过下面的写法，但结果会返回一个结构体。想知道把表完全扁平化的最简单方法是什么？

SELECT
  _airbyte_ab_id,
  ARRAY(
  SELECT
    AS STRUCT
    JSON_EXTRACT_SCALAR(balances, "$.total_balance.value") as value,
    JSON_EXTRACT_SCALAR(balances, "$.total_balance.currency_code") as currency
  FROM
    UNNEST(JSON_EXTRACT_ARRAY(_airbyte_data, "$.balances")) AS balances )[SAFE_OFFSET(0)] AS balances
FROM
 (select "1" as _airbyte_ab_id, JSON '{"balances":[{"total_balance":{"value":"1.68","currency_code":"EUR"},"withheld_balance":{"value":"0.00","currency_code":"EUR"},"currency":"EUR","available_balance":{"value":"1.68","currency_code":"EUR"},"primary":true},{"total_balance":{"value":"0.00","currency_code":"USD"},"withheld_balance":{"value":"0.00","currency_code":"USD"},"currency":"USD","available_balance":{"value":"0.00","currency_code":"USD"}}],"account_id":"VVULCKAL8HAHG","last_refresh_time":"2022-11-25T01:29:59Z","as_of_time":"2022-03-06T00:00:00+00:00"}' as _airbyte_data)

我尝试在JSON_EXTRACT_SCALAR中添加balances[SAFE_OFFSET(0)]，但BigQuery不允许这样操作。

方法1：先提取数组首元素再解析字段 #

直接通过[SAFE_OFFSET(0)]取出数组的第一个元素，再针对这个元素解析所需字段，避免生成结构体：

SELECT
  _airbyte_ab_id,
  JSON_EXTRACT_SCALAR(first_balance, "$.total_balance.value") AS value,
  JSON_EXTRACT_SCALAR(first_balance, "$.total_balance.currency_code") AS currency,
  -- 可按需添加其他字段，比如可用余额、币种等
  JSON_EXTRACT_SCALAR(first_balance, "$.available_balance.value") AS available_value,
  JSON_EXTRACT_SCALAR(first_balance, "$.currency") AS currency_code
FROM
  (SELECT 
    _airbyte_ab_id,
    -- 取出balances数组的第一个元素
    JSON_EXTRACT_ARRAY(_airbyte_data, "$.balances")[SAFE_OFFSET(0)] AS first_balance
   FROM
    (SELECT "1" AS _airbyte_ab_id, JSON '{"balances":[{"total_balance":{"value":"1.68","currency_code":"EUR"},"withheld_balance":{"value":"0.00","currency_code":"EUR"},"currency":"EUR","available_balance":{"value":"1.68","currency_code":"EUR"},"primary":true},{"total_balance":{"value":"0.00","currency_code":"USD"},"withheld_balance":{"value":"0.00","currency_code":"USD"},"currency":"USD","available_balance":{"value":"0.00","currency_code":"USD"}}],"account_id":"VVULCKAL8HAHG","last_refresh_time":"2022-11-25T01:29:59Z","as_of_time":"2022-03-06T00:00:00+00:00"}' AS _airbyte_data))

方法2：UNNEST后过滤首行 #

如果需要处理多组数据（每个_airbyte_ab_id对应一个数组），可以用UNNEST展开数组，再通过窗口函数筛选出每个分组的第一行：

SELECT
  _airbyte_ab_id,
  JSON_EXTRACT_SCALAR(balances, "$.total_balance.value") AS value,
  JSON_EXTRACT_SCALAR(balances, "$.total_balance.currency_code") AS currency
FROM
  (SELECT "1" AS _airbyte_ab_id, JSON '{"balances":[{"total_balance":{"value":"1.68","currency_code":"EUR"},"withheld_balance":{"value":"0.00","currency_code":"EUR"},"currency":"EUR","available_balance":{"value":"1.68","currency_code":"EUR"},"primary":true},{"total_balance":{"value":"0.00","currency_code":"USD"},"withheld_balance":{"value":"0.00","currency_code":"USD"},"currency":"USD","available_balance":{"value":"0.00","currency_code":"USD"}}],"account_id":"VVULCKAL8HAHG","last_refresh_time":"2022-11-25T01:29:59Z","as_of_time":"2022-03-06T00:00:00+00:00"}' AS _airbyte_data),
  UNNEST(JSON_EXTRACT_ARRAY(_airbyte_data, "$.balances")) AS balances WITH OFFSET AS pos
QUALIFY ROW_NUMBER() OVER(PARTITION BY _airbyte_ab_id ORDER BY pos) = 1

这两种方法都能直接生成扁平化的表结构，无需嵌套结构体。

内容的提问来源于stack exchange，提问作者volderette