Oracle解析CLOB列存储的JSON数据提取指定字段的SQL实现问题

原有写法错误原因 #

• 拼写错误：两份尝试代码都将JSON字段Name错写为Namne，路径匹配失效导致返回无数据
• 逻辑错误：第一种写法使用NESTED PATH会把每个属性拆分为独立行，无法实现一行对应一条JSON记录、不同属性转为不同列的需求；第二种写法JSON路径不符合实际结构，试图从Attributes数组层级直接取不存在的字段，自然返回空结果

正确可运行SQL（适配Oracle 12c及以上支持JSON_TABLE的版本） #

SELECT 
    j.ClassId,
    j.ID,
    j.HREF,
    j.HPRECISION,
    j.HMETHOD
FROM importitem d,
JSON_TABLE(
    d.JSON_DATA, '$'
    COLUMNS (
        ClassId NUMBER PATH '$.ClassId',
        -- 通过JSON路径过滤器匹配对应Name的属性取值
        ID VARCHAR2(100) PATH '$.Attributes[?(@.Name=="ID")].Value',
        HREF VARCHAR2(100) PATH '$.Attributes[?(@.Name=="HREF")].Value',
        HPRECISION VARCHAR2(100) PATH '$.Attributes[?(@.Name=="HPRECISION")].Value',
        HMETHOD VARCHAR2(100) PATH '$.Attributes[?(@.Name=="HMETHOD")].Value'
    )
) j;

如果需要返回数值类型的字段，可直接将对应字段的VARCHAR2(100)改为NUMBER即可。
如果需要避免非法JSON格式导致整条查询报错，可在路径后添加NULL ON ERROR参数，碰到解析失败时对应字段返回空值：

SELECT 
    j.ClassId,
    j.ID,
    j.HREF,
    j.HPRECISION,
    j.HMETHOD
FROM importitem d,
JSON_TABLE(
    d.JSON_DATA, '$'
    COLUMNS (
        ClassId NUMBER PATH '$.ClassId' NULL ON ERROR,
        ID VARCHAR2(100) PATH '$.Attributes[?(@.Name=="ID")].Value' NULL ON ERROR,
        HREF VARCHAR2(100) PATH '$.Attributes[?(@.Name=="HREF")].Value' NULL ON ERROR,
        HPRECISION VARCHAR2(100) PATH '$.Attributes[?(@.Name=="HPRECISION")].Value' NULL ON ERROR,
        HMETHOD VARCHAR2(100) PATH '$.Attributes[?(@.Name=="HMETHOD")].Value' NULL ON ERROR
    )
) j;

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