在BigQuery中展平JSON字符串并处理空值与单引号问题

针对你处理非标准JSON字符串的需求（替换None为null、移除自由文本中的单引号并转为合法JSON），可以用更简洁精准的正则替换替代多层临时符替换，避免潜在的误处理问题：

WITH cte AS (
  SELECT "[{'a': '123', 'b': None, 'freetext': \"I'm data\" }]" AS a 
)
SELECT
  JSON_EXTRACT_SCALAR(value, '$.a') AS a,
  JSON_EXTRACT_SCALAR(value, '$.b') AS b,
  JSON_EXTRACT_SCALAR(value, '$.freetext') AS freetext
FROM cte,
  UNNEST(
    JSON_EXTRACT_ARRAY(
      -- 步骤3：将单引号包裹的键转为双引号，生成合法JSON
      REGEXP_REPLACE(
        -- 步骤2：替换Python风格的None为JSON的null
        REPLACE(
          -- 步骤1：移除双引号包裹的自由文本中的单引号
          REGEXP_REPLACE(a, r'"([^"]*)''([^"]*)"', r'"\1\2"'),
          'None', 'null'
        ),
        r"'([^']+)'", r'"\1"'
      )
    )
  ) AS value;

优化说明 #

1. 移除自由文本中的单引号：
   用REGEXP_REPLACE(a, r'"([^"]*)''([^"]*)"', r'"\1\2"')精准匹配双引号包裹内容里的单引号并移除。BigQuery中字符串字面量的单引号需用两个单引号转义，因此正则里的''实际匹配单个单引号。

2. 替换None为null：
   直接用REPLACE完成简单字符串替换，比正则更高效。

3. 转为合法JSON格式：
   用REGEXP_REPLACE(..., r"'([^']+)'", r'"\1"')将所有单引号包裹的键（如'a'）转为双引号包裹的标准JSON键名，确保JSON函数能正确解析。

原方案的潜在问题 #

原方案使用临时替换符!~!存在风险：如果原字符串中本身包含该字符，会被误删除导致数据错误。正则替换则更精准，只处理目标字符。

处理多单引号的扩展 #

如果自由文本中有多个单引号（如"I'm don't data"），可以用全局正则替换一次性移除所有双引号内的单引号：

REGEXP_REPLACE(a, r'(?<=")([^"]*)''([^"]*)(?=")', r'\1\2')

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