I’m trying to unpack json inside BigQuery. The data contains an array with multiple columns and rows. I’m looking for a way to unpack this to have all the columns, but only the first row of each array. The table should be flat in the end.

I can solve it like this, but then I end up with an struct. What would be the easiest way to completely flatten the table?

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)

I tried to add the balances[SAFE_OFFSEt(0)] inside the json_extract_scalar, but this isn’t allowed by BigQuery.

>Solution :

Consider using a jsonpath like below instead of unnesting a json array.

but only the first row of each array.

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
  )

Query results