如何在BigQuery中提取复杂JSON结构数据生成独立列？

问题描述 #

我有如下格式的非标准嵌套结构数据：

{aa_validation: null 
 propensity_overlap: {auc pscore overlap: 0.5993614555297898 
                      auc pscore treated: 1.000000000000001 
                      auc pscore control: 1.0000000000000004
                      auc pscore ROC: 0.7524618788923345} 
 feature_balance: {% features with post matching SMD < 0.1: 100.0 
                   % features with post matching SMD < 0.25: 100.0 
                   % features with SMD improved after matching: 84.21052631578947 
                   % features with SMD not significantly worsened: 100.0}}

希望在BigQuery中将每个键对应的值单独作为一列，得到类似如下的结果：

auc pscore overlap | auc pscore treated | auc pscore control | auc pscore ROC | % features with post matching SMD < 0.1 | % features with post matching SMD < 0.25 | % features with SMD improved after matching | % features with SMD not significantly worsened
-------------------|-------------------|-------------------|----------------|----------------------------------------|----------------------------------------|---------------------------------------------|------------------------------------------------
0.5993614555297898 | 1.000000000000001 | 1.0000000000000004| 0.7524618788923345 | 100.0 | 100.0 | 84.21052631578947 | 100.0

尝试用REGEX_EXTRACT但没成功，求实现方法。

解决方案 #

方法1：转换为标准JSON后解析 #

原始数据是非标准JSON格式（键无引号、包含特殊符号），先通过字符串替换转为标准JSON，再用BigQuery的JSON函数提取字段：

WITH raw_data AS (
  SELECT '''{aa_validation: null 
 propensity_overlap: {auc pscore overlap: 0.5993614555297898 
                      auc pscore treated: 1.000000000000001 
                      auc pscore control: 1.0000000000000004
                      auc pscore ROC: 0.7524618788923345} 
 feature_balance: {% features with post matching SMD < 0.1: 100.0 
                   % features with post matching SMD < 0.25: 100.0 
                   % features with SMD improved after matching: 84.21052631578947 
                   % features with SMD not significantly worsened: 100.0}}''' AS raw_str
),
standardized_json AS (
  SELECT
    REGEXP_REPLACE(
      REGEXP_REPLACE(
        REGEXP_REPLACE(raw_str, r'(\w+|\%[^:]+)(?=\:)','"$1"'), -- 给所有键添加双引号
        r'<', '\u003C' -- 转义小于号，避免JSON解析报错
      ),
      r'null', 'null' -- 保留null的JSON格式
    ) AS json_str
  FROM raw_data
)
SELECT
  JSON_EXTRACT_SCALAR(json_str, '$.aa_validation') AS aa_validation,
  JSON_EXTRACT_SCALAR(json_str, '$.propensity_overlap."auc pscore overlap"') AS `auc pscore overlap`,
  JSON_EXTRACT_SCALAR(json_str, '$.propensity_overlap."auc pscore treated"') AS `auc pscore treated`,
  JSON_EXTRACT_SCALAR(json_str, '$.propensity_overlap."auc pscore control"') AS `auc pscore control`,
  JSON_EXTRACT_SCALAR(json_str, '$.propensity_overlap."auc pscore ROC"') AS `auc pscore ROC`,
  JSON_EXTRACT_SCALAR(json_str, '$.feature_balance."% features with post matching SMD < 0.1"') AS `% features with post matching SMD < 0.1`,
  JSON_EXTRACT_SCALAR(json_str, '$.feature_balance."% features with post matching SMD < 0.25"') AS `% features with post matching SMD < 0.25`,
  JSON_EXTRACT_SCALAR(json_str, '$.feature_balance."% features with SMD improved after matching"') AS `% features with SMD improved after matching`,
  JSON_EXTRACT_SCALAR(json_str, '$.feature_balance."% features with SMD not significantly worsened"') AS `% features with SMD not significantly worsened`
FROM standardized_json;

方法2：直接用正则提取单个字段 #

如果不想转JSON，也可以针对每个字段单独写正则表达式提取：

WITH raw_data AS (
  SELECT '''{aa_validation: null 
 propensity_overlap: {auc pscore overlap: 0.5993614555297898 
                      auc pscore treated: 1.000000000000001 
                      auc pscore control: 1.0000000000000004
                      auc pscore ROC: 0.7524618788923345} 
 feature_balance: {% features with post matching SMD < 0.1: 100.0 
                   % features with post matching SMD < 0.25: 100.0 
                   % features with SMD improved after matching: 84.21052631578947 
                   % features with SMD not significantly worsened: 100.0}}''' AS raw_str
)
SELECT
  REGEXP_EXTRACT(raw_str, r'aa_validation:\s*(null)') AS aa_validation,
  REGEXP_EXTRACT(raw_str, r'auc pscore overlap:\s*([\d\.]+)') AS `auc pscore overlap`,
  REGEXP_EXTRACT(raw_str, r'auc pscore treated:\s*([\d\.]+)') AS `auc pscore treated`,
  REGEXP_EXTRACT(raw_str, r'auc pscore control:\s*([\d\.]+)') AS `auc pscore control`,
  REGEXP_EXTRACT(raw_str, r'auc pscore ROC:\s*([\d\.]+)') AS `auc pscore ROC`,
  REGEXP_EXTRACT(raw_str, r'% features with post matching SMD < 0.1:\s*([\d\.]+)') AS `% features with post matching SMD < 0.1`,
  REGEXP_EXTRACT(raw_str, r'% features with post matching SMD < 0.25:\s*([\d\.]+)') AS `% features with post matching SMD < 0.25`,
  REGEXP_EXTRACT(raw_str, r'% features with SMD improved after matching:\s*([\d\.]+)') AS `% features with SMD improved after matching`,
  REGEXP_EXTRACT(raw_str, r'% features with SMD not significantly worsened:\s*([\d\.]+)') AS `% features with SMD not significantly worsened`
FROM raw_data;

内容的提问来源于stack exchange，提问作者Kshitij Yadav