I have a table which has CLOB data type. Value of the column is actually JSON data.

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

If I select the table table it will show like this

I need to parse the data so that I can get the output of HMETHODE and HPRECISION.

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

or may be the output will be like below

I was trying several method but getting either error or no data found. If anyone can fix this sql it would be very helpful.

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 ;

Or may be like this, i have tried two different way.

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

CodePudding user response：

I think that, you JSON data must be in that this format:

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

This is the correct format because in this format JSON has a KEYS (ID, HREF, HPRECISION...) and this KEYS has an integer values as (4548, -1, 5). In this case, then our query will be as:

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

But, if you JSON only in this format:

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

So, we can write our query:

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

Results will be:

CodePudding user response：

execute this query, success executed:

SELECT * FROM 
JSON_TABLE('{"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"}
    ]
}', '$[*]' 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"' 
        )
    )