只返回json_extract_array后的第一行

Question

我正试图在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)

我试图将balancesSAFE_OFFSEt(0)添加到json_extract_scalar中，但BigQuery不允许这样做。

Answer 1

考虑使用如下的jsonpath，而不是取消json数组的嵌套。

，但仅为每个数组的第一行。

SELECT _airbyte_ab_id,
       JSON_VALUE(_airbyte_data, '$.balances[0].total_balance.value') AS value,
       JSON_VALUE(_airbyte_data, '$.balances[0].total_balance.currency_code') AS currency,
  FROM (
    -- your sample data
  )

查询结果

​

​