Exasol中JSON_EXTRACT含短划线日期键的路径转义问题排查

问题根源 #

• 非标准JSON格式：Progress_history列存储的是单引号包裹键值的伪JSON，而Exasol的JSON_EXTRACT要求标准JSON（键必须用双引号包裹），这是第二个错误的直接原因。
• JSON路径转义错误：含短划线的特殊字符键，在Exasol的JSON路径中未正确转义，导致路径解析失败。

解决方案 #

步骤1：修正JSON格式 #

先将单引号替换为双引号，把非标准JSON转为Exasol可识别的标准格式：

SELECT
    kpi,
    REPLACE(progress_history, '''', '"') AS standard_json
FROM kpis

步骤2：正确解析含短划线的键 #

对于带短划线的键，在JSON路径中用双引号包裹键名，同时在SQL字符串中用双引号转义（两个双引号表示一个双引号），完整SQL如下：

SELECT
    kpi,
    progress_history,
    json_extract(
        REPLACE(progress_history, '''', '"'), -- 先转换为标准JSON
        '$."2020-01-01" LOG ON ERROR', -- 正确路径：双引号包裹带短划线的键
        '$.error()'
    )
    emits(
        "2020-01-01" INT,
        error_column VARCHAR(2000000)
    )
FROM kpis

可选：批量解析所有日期键 #

如果需要提取所有日期对应的数值，用JSON_TABLE展开键值对更灵活：

SELECT
    k.kpi,
    j.date_key,
    j.progress_value
FROM kpis k
JOIN JSON_TABLE(
    REPLACE(k.progress_history, '''', '"'),
    '$.*' COLUMNS (
        date_key VARCHAR(10) PATH '$',
        progress_value INT PATH '$'
    )
) j

错误说明 #

• 第一个错误是因为路径格式错误，Exasol无法识别$.""2020-这类无效路径，正确格式应为$."键名"。
• 第二个错误是原始数据用单引号包裹键，不符合JSON规范，导致JSON解析函数报错。

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