如何在BigQuery的REGEXP_REPLACE中处理匹配值并完成替换？

我尝试使用BigQuery字符串函数处理从Firestore流式传输到BigQuery的JSON字符串（以字符串格式存储），将Epoch秒级时间戳替换为时间戳字符串。数据中每个JSON内的时间戳数量不固定，无法通过多次替换完成，需一次性处理可变数量的时间戳。

示例数据：

{
    "createdAtTimestamp": {
        "_seconds": 1670337132,
        "_nanoseconds": 863000000
    },
    "phoneNo": "+44970000000",
    "phoneVerified": false,
    "updatedAtTimestamp": {
        "_seconds": 1672754379,
        "_nanoseconds": 112000000
    },
    "timestamp3": {
        "_seconds": 1669127206,
        "_nanoseconds": 112000000
    },
    "timestamp4": {
        "_seconds": 1672909833,
        "_nanoseconds": 112000000
    },
    "PIN": "$2b$10$gsgfdgfsdfdf"
}

我已实现以下查询（仅提取匹配项，未完成替换）：

SELECT
REGEXP_REPLACE(data,ARRAY_TO_STRING(REGEXP_EXTRACT_ALL(data,'"_seconds":([0-9]+)'),'|'),'\\0')
FROM 
  `tableXYZ` 

尝试使用如下语句处理，但执行报错，无法处理正则匹配到的\0值：

SELECT
REGEXP_REPLACE(data,ARRAY_TO_STRING(REGEXP_EXTRACT_ALL(data,'"_seconds":([0-9]+)'),'|'),cast(FORMAT_TIMESTAMP("%Y-%m-%dT%X",TIMESTAMP_SECONDS(cast('\\0' as bigint))) as string) )
FROM 
  `tableXYZ` 

请问在BigQuery SQL中是否有可行的实现方法？

方法一：使用JSON解析与重构（推荐，避免正则处理JSON的风险） #

这种方法先将字符串解析为JSON对象，遍历所有字段并转换时间戳对象，最后重新序列化为JSON字符串，比正则更可靠：

CREATE TEMP FUNCTION convert_firestore_timestamps(json_str STRING)
RETURNS STRING
LANGUAGE js AS """
  const obj = JSON.parse(json_str);
  for (const key in obj) {
    const value = obj[key];
    // 判断是否为Firestore时间戳对象
    if (typeof value === 'object' && value !== null && '_seconds' in value && '_nanoseconds' in value) {
      // 转换为ISO格式时间戳字符串
      const timestamp = new Date(value._seconds * 1000 + value._nanoseconds / 1000000);
      obj[key] = timestamp.toISOString();
    }
  }
  return JSON.stringify(obj);
""";

SELECT convert_firestore_timestamps(data) AS processed_json
FROM `tableXYZ`;

方法二：使用正则与动态替换（适合简单场景） #

如果必须用字符串正则处理，可以结合REGEXP_EXTRACT_ALL生成匹配项和替换值的映射，再通过循环替换实现：

WITH raw_data AS (
  SELECT data FROM `tableXYZ`
),
matches AS (
  SELECT
    data,
    REGEXP_EXTRACT_ALL(data, '"_seconds":([0-9]+)') AS second_values,
    ARRAY(
      SELECT FORMAT_TIMESTAMP("%Y-%m-%dT%X", TIMESTAMP_SECONDS(CAST(val AS INT64)))
      FROM UNNEST(REGEXP_EXTRACT_ALL(data, '"_seconds":([0-9]+)')) val
    ) AS timestamp_strings
  FROM raw_data
),
recursive_replace AS (
  SELECT
    data,
    second_values,
    timestamp_strings,
    0 AS idx,
    data AS processed_data
  FROM matches
  UNION ALL
  SELECT
    data,
    second_values,
    timestamp_strings,
    idx + 1,
    REGEXP_REPLACE(processed_data, '"_seconds":' || second_values[idx], '"_seconds":' || QUOTE(timestamp_strings[idx]))
  FROM recursive_replace
  WHERE idx < ARRAY_LENGTH(second_values)
)
SELECT processed_data
FROM recursive_replace
WHERE idx = ARRAY_LENGTH(second_values);

说明 #

• 方法一使用JavaScript UDF处理JSON对象，能精准识别Firestore时间戳结构，避免正则匹配错误（比如JSON字符串中包含类似"_seconds"的内容）。
• 方法二通过递归替换实现，但依赖正则匹配的准确性，若JSON结构复杂（如嵌套对象）可能失效。

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