如何在BigQuery中从非标准JSON字符串提取content值

可以通过REGEXP_REPLACE将非标准格式的字符串转换为标准JSON，再用JSON_EXTRACT_SCALAR提取content值。具体SQL语句如下：

CREATE OR REPLACE VIEW `your-project.your-dataset.your-view` AS
SELECT
  -- 按需转为整数类型的话，可套一层SAFE_CAST(...) AS INT64
  JSON_EXTRACT_SCALAR(
    REGEXP_REPLACE(
      REGEXP_REPLACE(your_column, r'(\w+):', r'"$1":'), -- 给键名添加双引号
      r':([^,\}\d]+)', r':"$1"' -- 给非数字类型的字符串值添加双引号
    ),
    '$.content'
  ) AS content_value
FROM `your-project.your-dataset.your-table`;

步骤说明 #

1. 第一次REGEXP_REPLACE：匹配字符串中的键名（比如type、content），给它们加上双引号，将{type:System.Int32,content:202104}转换为{"type":System.Int32,"content":202104}。
2. 第二次REGEXP_REPLACE：匹配冒号后不属于数字、逗号或右大括号的内容（比如System.Int32），给这类字符串值加上双引号，最终得到标准JSON格式{"type":"System.Int32","content":202104}。
3. JSON_EXTRACT_SCALAR直接提取content字段的值，返回结果为字符串类型；如果需要整数类型，可在外层嵌套SAFE_CAST(...) AS INT64。

如果你的非标准字符串有其他变种（比如值包含特殊字符），可以微调正则表达式适配，但针对你给出的示例格式，上述语句完全适用。

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