如何通过JSON_TABLE访问转义引号内的字段？

问题本质 #

你遇到的核心问题是：current_review_id的字段值并非嵌套JSON对象，而是经过转义的JSON格式字符串（比如"{\"value\": \"xxx\"}"）。直接用$.current_review_id.value这类路径会失效，因为JSON_TABLE只会把它当成普通字符串处理，无法识别内部的JSON结构。

解决方案：先转字符串为JSON对象，再解析 #

需要先将转义后的字符串转换为合法的JSON对象，再用常规路径提取内部字段。以下是主流数据库的实现示例：

MySQL 示例

SELECT
  -- 解析普通嵌套JSON字段
  jt.num_records,
  -- 解析转义JSON字符串的内部字段
  JSON_VALUE(JSON_UNQUOTE(jt.current_review_id), '$.value') AS review_id,
  JSON_VALUE(JSON_UNQUOTE(jt.current_review_id), '$.recorded_timestamp') AS review_timestamp
FROM your_table,
     JSON_TABLE(
       your_json_column,
       '$' COLUMNS (
         num_records INT PATH '$.run_summary.num_records',
         current_review_id VARCHAR(4000) PATH '$.current_review_id'
       )
     ) AS jt;

也可以直接在JSON_TABLE内部完成转换：

SELECT
  jt.num_records,
  jt.review_id,
  jt.review_timestamp
FROM your_table,
     JSON_TABLE(
       your_json_column,
       '$' COLUMNS (
         num_records INT PATH '$.run_summary.num_records',
         -- 先去除转义并转为JSON，再提取内部字段
         review_id VARCHAR(36) PATH 'JSON_UNQUOTE($.current_review_id).value',
         review_timestamp BIGINT PATH 'JSON_UNQUOTE($.current_review_id).recorded_timestamp'
       )
     ) AS jt;

Oracle 示例

Oracle需用JSON.parse()将字符串转为JSON对象：

SELECT
  jt.num_records,
  jt.review_id,
  jt.review_timestamp
FROM your_table,
     JSON_TABLE(
       your_json_column,
       '$' COLUMNS (
         num_records NUMBER PATH '$.run_summary.num_records',
         review_id VARCHAR2(36) PATH 'JSON.parse($.current_review_id).value',
         review_timestamp NUMBER PATH 'JSON.parse($.current_review_id).recorded_timestamp'
       )
     ) AS jt;

PostgreSQL 示例

PostgreSQL用jsonb_parse_text()处理转义字符串：

SELECT
  jt.num_records,
  (jsonb_extract_path_text(jsonb_parse_text(jt.current_review_id), 'value')) AS review_id,
  (jsonb_extract_path_text(jsonb_parse_text(jt.current_review_id), 'recorded_timestamp')) AS review_timestamp
FROM your_table,
     json_table(
       your_json_column,
       '$' COLUMNS (
         num_records INT PATH '$.run_summary.num_records',
         current_review_id TEXT PATH '$.current_review_id'
       )
     ) AS jt;

注意事项 #

• 提前校验转义字符串的合法性：可以用数据库的JSON校验函数（如MySQL的JSON_VALID(JSON_UNQUOTE(current_review_id))）过滤无效数据，避免解析报错。
• 字段长度适配：根据实际数据调整VARCHAR、TEXT的长度，防止截断。

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