如何在dbt和BigQuery中优化JSON转义字符的替换操作

我有如下JSON数据：

{"payload":"{\"custom\":{\"a\":{\"hs.dl\":\"hs:\\\/\\/categories\\/Z2lkOi8vc2hvcGlmeS9NZW51SXRlbS81NDM2Nzk0NDczODI=\",\"hs.image\":\"https:\\\/\\/cms-highstreetapp.imgix.net\\/denham\\/2023\\/08\\/0657f839-0045-49b0-ba89-fbde3c74f519\\/montage20230818-1-kzy7bm.jpg\",\"hs.body\":\"Reworked in the colours of the season.\",\"hs.video_720\":null,\"hs.thumbnail\":\"https:\\\/\\/cms-highstreetapp.imgix.net\\/denham\\/2023\\/08\\/0657f839-0045-49b0-ba89-fbde3c74f519\\/montage20230818-1-kzy7bm.jpg\",\"hs.title\":\"Free Move Chinos\",\"hs.video_480\":null,\"hs.id\":\"HS_STUDIO-91428\"},\"i\":\"31d443e3-1874-4deb-b475-ac10c850451d\"},\"aps\":{\"mutable-content\":1,\"alert\":\"Free Move Chinos - Reworked in the colours of the season.\",\"category\":\"category\",\"sound\":\"default\"}}"}

目前我用多层REPLACE处理转义字符和键名：

REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(value, 'hs.', ''), '\\\"', '\"'), '\"{', "{"), '}\"', "}"), '\\/', "/"), '\\n', ' ') AS value

想知道在BigQuery和dbt里有没有更高效的实现方式？

另外我写了一个用于替换的临时JS函数，但因为引号等字符转义问题报错，求修复：

CREATE TEMP FUNCTION customReplace(value STRING)
RETURNS STRING
LANGUAGE js AS """
  try {
    value = value.replace(/hs\./g, '')
                 .replace(/\\\"/g, '\"')
                 .replace(/\"\{/g, '{')
                 .replace(/\}"/g, '}')
                 .replace(/\\\\//g, '/')
                 .replace(/\\n/g, ' ');
    return value;
  } catch (e) {
    return null;
  }
""";

一、更优的JSON处理方式（避免多层REPLACE） #

原始数据的payload字段是字符串化的JSON，直接用字符串替换容易破坏JSON结构，更稳妥的方式是先解析JSON，再处理键名和内容，最后重新序列化：

BigQuery 原生SQL实现 #

WITH raw_data AS (
  SELECT '{"payload":"{\"custom\":{\"a\":{\"hs.dl\":\"hs:\\\/\\/categories\\/Z2lkOi8vc2hvcGlmeS9NZW51SXRlbS81NDM2Nzk0NDczODI=\",\"hs.image\":\"https:\\\/\\/cms-highstreetapp.imgix.net\\/denham\\/2023\\/08\\/0657f839-0045-49b0-ba89-fbde3c74f519\\/montage20230818-1-kzy7bm.jpg\",\"hs.body\":\"Reworked in the colours of the season.\",\"hs.video_720\":null,\"hs.thumbnail\":\"https:\\\/\\/cms-highstreetapp.imgix.net\\/denham\\/2023\\/08\\/0657f839-0045-49b0-ba89-fbde3c74f519\\/montage20230818-1-kzy7bm.jpg\",\"hs.title\":\"Free Move Chinos\",\"hs.video_480\":null,\"hs.id\":\"HS_STUDIO-91428\"},\"i\":\"31d443e3-1874-4deb-b475-ac10c850451d\"},\"aps\":{\"mutable-content\":1,\"alert\":\"Free Move Chinos - Reworked in the colours of the season.\",\"category\":\"category\",\"sound\":\"default\"}}"}' AS value
)
SELECT
  -- 1. 解析外层JSON，取出payload字符串
  PARSE_JSON(value) AS outer_json,
  -- 2. 解析payload为JSON对象
  PARSE_JSON(PARSE_JSON(value).payload) AS parsed_payload,
  -- 3. 处理custom.a下的键名，去掉hs.前缀，同时处理URL转义
  JSON(
    SELECT AS STRUCT
      (SELECT AS STRUCT
         REPLACE(k, 'hs.', '') AS key,
         -- 替换URL中的转义斜杠
         REPLACE(v, '\\/', '/') AS value
       FROM UNNEST(JSON_EXTRACT_ARRAY(PARSE_JSON(PARSE_JSON(value).payload).custom.a, '$')) AS kv
       WITH OFFSET
       PIVOT ANY_VALUE(kv.value) FOR kv.key IN ('hs.dl', 'hs.image', 'hs.body', 'hs.video_720', 'hs.thumbnail', 'hs.title', 'hs.video_480', 'hs.id')) AS a,
      PARSE_JSON(PARSE_JSON(value).payload).custom.i AS i
    ) AS cleaned_custom,
  -- 4. 组装最终的JSON字符串
  TO_JSON_STRING(
    STRUCT(
      JSON(STRUCT(cleaned_custom AS custom, PARSE_JSON(PARSE_JSON(value).payload).aps AS aps)) AS payload
    )
  ) AS final_value
FROM raw_data

dbt 中的实现（结合BigQuery） #

在dbt模型中可以直接写SQL，也可以封装成宏复用：

{{ config(materialized='view') }}

WITH source_data AS (
  SELECT value FROM {{ ref('your_source_table') }}
)
SELECT
  TO_JSON_STRING(
    STRUCT(
      JSON(
        STRUCT(
          (SELECT AS STRUCT
             REPLACE(k, 'hs.', '') AS key,
             REPLACE(v, '\\/', '/') AS value
           FROM UNNEST(JSON_EXTRACT_ARRAY(PARSE_JSON(PARSE_JSON(value).payload).custom.a, '$')) AS kv
           WITH OFFSET
           PIVOT ANY_VALUE(kv.value) FOR kv.key IN ('hs.dl', 'hs.image', 'hs.body', 'hs.video_720', 'hs.thumbnail', 'hs.title', 'hs.video_480', 'hs.id')) AS a,
          PARSE_JSON(PARSE_JSON(value).payload).custom.i AS i
        ) AS custom,
        PARSE_JSON(PARSE_JSON(value).payload).aps AS aps
      ) AS payload
    )
  ) AS value
FROM source_data

这种方式的优势：

• 不会破坏JSON结构，避免字符串替换导致的格式错误
• 逻辑清晰，分层处理不同层级的JSON内容
• BigQuery原生JSON函数性能优于多层字符串替换

二、修复JS临时函数的报错问题 #

你的JS函数报错是因为正则表达式中的转义字符处理错误，在BigQuery的JS字符串中，反斜杠需要双重转义，同时正则里的特殊字符也要正确处理：

CREATE TEMP FUNCTION customReplace(value STRING)
RETURNS STRING
LANGUAGE js AS """
  try {
    value = value.replace(/hs\\./g, '')
                 .replace(/\\\\"/g, '"')
                 .replace(/"\{/g, '{')
                 .replace(/\}"/g, '}')
                 .replace(/\\\\\\//g, '/')
                 .replace(/\\n/g, ' ');
    return value;
  } catch (e) {
    return null;
  }
""";

修复点说明： #

1. /hs\./g 改为 /hs\\./g：在JS字符串中，反斜杠需要转义，所以\.要写成\\.
2. /\\\"/g 改为 /\\\\"/g：要匹配\"，需要在JS字符串中转义成\\\\"（BigQuery先解析一层转义，JS再解析一层）
3. /\\\\//g 改为 /\\\\\\//g：要匹配\/，需要转义成\\\\\\/，最终JS正则里是\/

不过仍推荐用第一种JSON解析的方式，字符串替换容易出现边界情况（比如值里刚好包含hs.或者转义字符）。

内容的提问来源于stack exchange，提问作者Mc.Lover