BigQuery SQL JSON Returning additional rows when current row contains multiple values

Question

I have a table that looks like this

keyA           | data:{"value":false}}
keyB           | data:{"value":3}}
keyC           | data:{"value":{"paid":10,"unpaid":20}}} 

For keyA,keyB I can easily extract a single value with JSON_EXTRACT_SCALAR, but for keyC I would like to return multiple values and change the key name, so the final output looks like this:

keyA           | false
keyB           | 3
keyC-paid      | 10
keyD-unpaid    | 20

I know I can use UNNEST and JSON_EXTRACT multiple values and create additional but unsure how to combine them to adjust the key column name as well?

Answer 1

Try this one:

WITH sample AS (
  SELECT 'keyA' AS col, '{"value":false}' AS data
   UNION ALL
  SELECT 'keyB' AS col, '{"value":3}' AS data
   UNION ALL
  SELECT 'keyC' AS col, '{"value":{"paid":10,"unpaid":20}}' AS data
)
SELECT col || IFNULL('-' || k, '') AS col,
       IFNULL(v, JSON_VALUE(data, '$.value')) AS data
  FROM (
    SELECT col, data, 
           `bqutil.fn.json_extract_keys`(JSON_QUERY(data, '$.value')) AS keys,
           `bqutil.fn.json_extract_values`(JSON_QUERY(data, '$.value')) AS vals
      FROM sample
  ) LEFT JOIN UNNEST(keys) k WITH OFFSET ki 
    LEFT JOIN UNNEST(vals) v WITH OFFSET vi ON ki = vi;