将JSON解析为Oracle SQL

问题描述

我有一个CLOB数据类型的表。该列的值实际上是JSON数据。

{"ClassId":32000,"Attributes":
    [
        {"Name":"ID","Value":"4548"},
        {"Name":"HREF","Value":"-1"},
        {"Name":"HPRECISION","Value":"5"},
        {"Name":"HMETHOD","Value":"96"},
        {"Name":"GEO-METHOD","Value":"96"},
        {"Name":"GEO-PRECISION","Value":"5"},
        {"Name":"GEO-VISIBILITY","Value":"0"}
    ]
}
{"ClassId":30074,"Attributes":
        [
            {"Name":"ID","Value":"265794"},
            {"Name":"HREF","Value":"-1"},
            {"Name":"HPRECISION","Value":"5"},
            {"Name":"HMETHOD","Value":"100"},
            {"Name":"GEO-METHOD","Value":"90"},
            {"Name":"GEO-PRECISION","Value":"5"},
            {"Name":"GEO-VISIBILITY","Value":"0"}
        ]
    }

如果我选择表格，它将显示如下

我需要分析数据，以便可以获得HMETHODE和HPRECISION的输出。

Classid ID      HREF HPRECISION HMETHOD 
30074   265794  -1    5          96     
32000   4548    -1    5          100    

或可能是如下所示的输出

select ClassId, NAME, VALUE 
from importitem D, 
JSON_TABLE
(
  D.JSON_DATA, '$' COLUMNS
  (
    ClassId NUMBER path '$.ClassId',
    NESTED path '$.Attributes[*]' COLUMNS
    (
    NAME NUMBER PATH '$.Namne',
    Value NUMBER PATH '$.Value'
    )
  )
) J ;

或者可能是这样，我已经尝试了两种不同的方法。

SELECT *
FROM   JSON_TABLE(
         '{"ClassId":30074,
           "Attributes":
            [
              {"Name":"ID","Value":"356605"},
              {"Name":"HREF","Value":"-1"},
              {"Name":"HPRECISION","Value":"5"},
              {"Name":"HMETHOD","Value":"96"},
              {"Name":"GEO-METHOD","Value":"96"},
              {"Name":"GEO-PRECISION","Value":"5"},
              {"Name":"GEO-VISIBILITY","Value":"0"}
            ]
        }',
         '$.Attributes'
         COLUMNS
           ID NUMBER PATH '$.Namne.Value',
           HREF NUMBER PATH '$.Namne.Value',
           HPRECISION NUMBER PATH  '$.Namne.Value',
           HMETHOD NUMBER PATH '$.Namne.Value'
);

推荐答案

我认为，您JSON数据必须是这种格式：

{"ClassId":32000,"Attributes":
    [
        {"ID":4548},
        {"HREF":-1},
        {"HPRECISION":5},
        {"HMETHOD":96},
        {"GEO-METHOD":96},
        {"GEO-PRECISION":5},
        {"GEO-VISIBILITY":0}
    ]
}

SELECT CLASSID, ATRB_ID, HREF, HPRECISION, HMETHOD, GEOMETHOD, GEOPRECISION, GEOVISIBILITY
FROM TABLE2 T1
    CROSS JOIN 
    JSON_TABLE(T1.JSON_DATA, '$' COLUMNS 
        (
            CLASSID INTEGER PATH '$.ClassId', 
            ATRB_ID INTEGER PATH '$.Attributes.ID', 
            HREF INTEGER PATH '$.Attributes.HREF', 
            HPRECISION INTEGER PATH '$.Attributes.HPRECISION', 
            HMETHOD INTEGER PATH '$.Attributes.HMETHOD', 
            GEOMETHOD INTEGER PATH '$.Attributes."GEO-METHOD"', 
            GEOPRECISION INTEGER PATH '$.Attributes."GEO-PRECISION"', 
            GEOVISIBILITY INTEGER PATH '$.Attributes."GEO-VISIBILITY"' 
        )
    )

但是，如果您只使用此格式的JSON：

{"ClassId":32000,"Attributes":
    [
        {"Name":"ID","Value":"4548"},
        {"Name":"HREF","Value":"-1"},
        {"Name":"HPRECISION","Value":"5"},
        {"Name":"HMETHOD","Value":"96"},
        {"Name":"GEO-METHOD","Value":"96"},
        {"Name":"GEO-PRECISION","Value":"5"},
        {"Name":"GEO-VISIBILITY","Value":"0"}
    ]
}

因此，我们可以编写查询：

SELECT CLASSID, ATRB_ID, HREF, HPRECISION, HMETHOD, GEOMETHOD, GEOPRECISION, GEOVISIBILITY
FROM TABLE1 T1
    CROSS JOIN 
    JSON_TABLE(T1.JSON_DATA, '$[*]' COLUMNS 
        (
            CLASSID INTEGER PATH '$.ClassId', 
            ATRB_ID VARCHAR2(100) PATH '$.Attributes[0]."Value"', 
            HREF VARCHAR2(100) PATH '$.Attributes[1]."Value"', 
            HPRECISION VARCHAR2(100) PATH '$.Attributes[2]."Value"', 
            HMETHOD VARCHAR2(100) PATH '$.Attributes[3]."Value"', 
            GEOMETHOD VARCHAR2(100) PATH '$.Attributes[4]."Value"', 
            GEOPRECISION VARCHAR2(100) PATH '$.Attributes[5]."Value"', 
            GEOVISIBILITY VARCHAR2(100) PATH '$.Attributes[6]."Value"' 
        )
    )

结果为：