当前行包含多个值时，BigQuery SQL JSON返回其他行

Question

我有一个表，看起来像

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

keya，keyb我可以轻松地使用JSON_EXTRACT_SCALAR轻松提取单个值，但是对于keyc < /code>我想返回多个值并更改密钥名称，因此最终输出看起来像这样：

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

我知道我可以使用Unnest和JSON__EXTRACT多个值，并创建其他但不确定如何组合它们以将关键列名称调整为出色地？

Answer 1

甚至更通用的方法

create temp function  extract_keys(input string) returns array<string> language js as """
  return Object.keys(JSON.parse(input));
  """;
create temp function  extract_values(input string) returns array<string> language js as """
  return Object.values(JSON.parse(input));
  """;
create temp function extract_all_leaves(input string) returns string language js as '''
  function flattenObj(obj, parent = '', res = {}){
    for(let key in obj){
        let propName = parent ? parent + '.' + key : key;
        if(typeof obj[key] == 'object'){
            flattenObj(obj[key], propName, res);
        } else {
            res[propName] = obj[key];
        }
    }
    return JSON.stringify(res);
  }
  return flattenObj(JSON.parse(input));
  ''';
select col || replace(replace(key, 'value', ''), '.', '-') as col, value, 
from your_table,
unnest([struct(extract_all_leaves(data) as json)]),
unnest(extract_keys(json)) key with offset
join unnest(extract_values(json)) value with offset
using(offset)    

如果应用于您的问题中的样本数据，

- 输出为

这种方法的好处是它非常通用，因此可以处理JSON中的任何层次的嵌套，

例如以下数据/表

输出为

Answer 2

尝试一下：

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;