將JSON解析為Oracle SQL-魔扣目錄

本文介紹了將JSON解析為Oracle SQL的處理方法，對大家解決問題具有一定的參考價值，需要的朋友們下面隨著小編來一起學習吧！

問題描述

我有一個CLOB數(shù)據(jù)類型的表。該列的值實際上是JSON數(shù)據(jù)。

{"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"}
        ]
    }

如果我選擇表格，它將顯示如下

我需要分析數(shù)據(jù)，以便可以獲得HMETHODE和HPRECISION的輸出。

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

或可能是如下所示的輸出

我嘗試了幾種方法，但要么出錯，要么找不到數(shù)據(jù)。如果有人可以修復此SQL，那將非常有幫助。

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數(shù)據(jù)必須是這種格式：

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

這是正確的格式，因為在此格式中，JSON有一個鍵(ID、HREF、HPRECISION…)并且該密鑰的整數(shù)值為(4548，-1，5)。在本例中，我們的查詢將為：

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"' 
        )
    )

結果為：