在BigQuery中动态展平含不同键数的JSON列报错求助

我的数据表结构如下：

我尝试用动态识别JSON键并展平为SQL表的BigQuery脚本，但运行时出现错误：
Query error: SyntaxError: Expected property name or '}' in JSON at position 2 (line 1 column 3) at undefined line 1, columns 2-3 at [24:3]

原脚本代码：

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));
''';

create temp table temp_table as (
 select offset, key, value, id
 from your_table t, 
 unnest([struct(extract_all_leaves(payload) as leaves)]),
 unnest(extract_keys(leaves)) key with offset
join unnest(extract_values(leaves)) value with offset
using(offset)  
);   

execute immediate (select '''
select * from (select * except(offset) from temp_table)
 pivot (any_value(value) for replace(key, '.', '__') in (''' || keys_list || '''
 ))'''
from (select string_agg('"' || replace(key, '.', '__') || '"', ',' order by offset) keys_list from (
select key, min(offset) as offset from temp_table group by key
))
);

报错原因及修正方案 #

1. JSON格式不兼容：keywords列用单引号'包裹JSON属性，但标准JSON要求双引号"，BigQuery的JSON.parse无法解析单引号格式的JSON，这是报错核心原因。
2. 字段名不匹配：原脚本用了payload字段，但数据表中对应字段是keywords，需替换。
3. 数组处理缺失：原脚本未处理keywords是JSON数组的情况，直接解析整个数组会导致结构不符合预期。

修正后的完整脚本：

-- 替换单引号为双引号，将非标准JSON转为标准格式
create temp function fix_json(input string) returns string language js as """
return input.replace(/'/g, '"');
""";

-- 提取JSON对象的键
create temp function extract_keys(input string) returns array<string> language js as """
return Object.keys(JSON.parse(input));
""";

-- 提取JSON对象的值
create temp function extract_values(input string) returns array<string> language js as """
return Object.values(JSON.parse(input));
""";

-- 扁平化嵌套JSON
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' && obj[key] !== null){
      flattenObj(obj[key], propName, res);
    } else {
      res[propName] = obj[key];
    }
  }
  return JSON.stringify(res);
}
return flattenObj(JSON.parse(input));
''';

-- 处理数组中的每个JSON对象，生成中间表
create temp table temp_table as (
  select 
    id,
    offset,
    key,
    value
  from your_table t,
    -- 修复JSON格式并转为数组
    unnest(JSON_ARRAY(fix_json(keywords))) as json_item,
    -- 扁平化每个JSON对象
    unnest([struct(extract_all_leaves(json_item) as leaves)]),
    -- 提取键和值
    unnest(extract_keys(leaves)) key with offset
  join unnest(extract_values(leaves)) value with offset
  using(offset)
);

-- 动态生成PIVOT语句，展平为宽表
execute immediate (
  select '''
    select * from (
      select id, key, value from temp_table
    )
    pivot (
      any_value(value) for replace(key, '.', '__') in (''' || keys_list || '''
      )
    )
  '''
  from (
    select string_agg('"' || replace(key, '.', '__') || '"', ',' order by min_offset) as keys_list
    from (
      select key, min(offset) as min_offset from temp_table group by key
    )
  )
);

关键修正说明 #

• fix_json函数：将字段中的单引号替换为双引号，确保JSON格式符合标准，避免解析报错。
• 处理JSON数组：使用JSON_ARRAY(fix_json(keywords))将修复后的字符串转为JSON数组，再通过unnest遍历每个元素。
• 字段名替换：将原脚本中的payload替换为实际字段名keywords。
• 空值判断：在扁平化函数中增加obj[key] !== null的判断，避免解析null值时出错。

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