BigQuery中JSON字符串BLOB修改指定字段后生成合法JSON方案咨询

最优实现方案 #

你遇到的转义问题本质是JSON_EXTRACT返回的是字符串类型的JSON片段，而非BigQuery原生JSON类型，在调用TO_JSON_STRING时会被识别为普通字符串做转义处理。目前BigQuery提供了原生JSON操作函数可以直接修改指定字段，无需拆解所有键，适配复杂嵌套、多键的JSON场景：

核心思路

1. 用PARSE_JSON将存储的JSON字符串转为BigQuery原生JSON类型
2. 调用JSON_SET函数直接修改指定路径$.b的值，其余字段保持不变
3. 最后将修改后的JSON类型转回字符串即可

示例代码

SELECT
  TO_JSON_STRING(
    JSON_SET(
      PARSE_JSON(COL_NAME),
      "$.b",
      TO_JSON(MD5(JSON_EXTRACT_SCALAR(COL_NAME, "$.b")))
    )
  ) AS modified_json
FROM
  dataset.TABLE

方案优势

• 无需感知其他字段的结构、嵌套层级，即使后续JSON新增字段也不需要修改查询逻辑，可扩展性极强
• 性能远高于逐字段拆解组装的方案，适合生产环境大流量场景

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