Parsing JSON into oracle sql

Question

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

version of Oracle Database - is it actually 11 as you have it tagged? Because this is MUCH easier in 12.2 and going forward. — thatjeffsmith, Nov 17 '21 at 15:09
1) `jason_table` should be `json_table`. 2) column specifications should be comma separated: `hprecision number, hmethod ...`. Also please, [**do not post images of code**](https://meta.stackoverflow.com/questions/285551/why-not-upload-images-of-code-errors-when-asking-a-question#285557), they are unusable — astentx, Nov 17 '21 at 16:35
sorry about that. i changed the screenshot with the code sample. — goldenbutter, Nov 18 '21 at 07:29

score 1 · Accepted Answer · answered Nov 18 '21 at 00:52

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:

CLASSID	ATRB_ID	HREF	HPRECISION	HMETHOD	GEOMETHOD	GEOPRECISION	GEOVISIBILITY
32000	4548	-1	5	96	96	5	0
30074	265794	-1	5	100	90	5	0

score 1 · Answer 2 · answered Nov 18 '21 at 08:43

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

Parsing JSON into oracle sql

2 Answers2

Linked