How to parse json in oracle sql? (Version:11.2.0)

Question

I saved json data to a field in the database.I want to parse the json file with Oracle . How can I do it?

{
  "widget": {
    "debug": "on",
    "window": {
      "title": "Sample Konfabulator Widget",
      "name": "main_window",
      "width": 500,
      "height": 500
    },
    "image": {
      "src": "Images/Sun.png",
      "name": "sun1",
      "hOffset": 250,
      "vOffset": 250,
      "alignment": "center"
    },
    "text": {
      "data": "Click Here",
      "size": 36,
      "style": "bold",
      "name": "text1",
      "hOffset": 250,
      "vOffset": 100,
      "alignment": "center",
      "onMouseUp": "sun1.opacity = (sun1.opacity / 100) * 90;"
    }
  }
}

Prior to Oracle 12c there is no built-in support for JSON. You will need to look for third-party solutions in PL/SQL or as Java which you can then embed into the database. Or else, upgrade your database. — MT0, Oct 21 '19 at 11:23

score 2 · Answer 1 · answered Oct 21 '19 at 11:19

If you are using Oracle 12c or later then you can use JSON_TABLE:

SELECT *
FROM   JSON_TABLE(
         '{
           "widget": {
             "debug": "on",
             "window": {
               "title": "Sample Konfabulator Widget",
               "name": "main_window",
               "width": 500,
               "height": 500
             },
             "image": { 
               "src": "Images/Sun.png",
               "name": "sun1",
               "hOffset": 250,
               "vOffset": 250,
               "alignment": "center"
             },
             "text": {
               "data": "Click Here",
               "size": 36,
               "style": "bold",
               "name": "text1",
               "hOffset": 250,
               "vOffset": 100,
               "alignment": "center",
               "onMouseUp": "sun1.opacity = (sun1.opacity / 100) * 90;"
             }
           }
         }',
         '$.widget'
         COLUMNS
           debug       VARCHAR2(3)  PATH '$.debug',
           title       VARCHAR2(50) PATH '$.window.title',
           window_name VARCHAR2(20) PATH '$.window.name',
           width       NUMBER(5,0)  PATH '$.window.width',
           height      NUMBER(5,0)  PATH '$.window.height',
           src         VARCHAR2(50) PATH '$.image.src',
           image_name  VARCHAR2(20) PATH '$.image.name',
           hOffset     NUMBER(5,0)  PATH '$.image.hOffset',
           vOffset     NUMBER(5,0)  PATH '$.image.vOffset',
           align       VARCHAR2(10) PATH '$.image.alignment'
       );

which outputs:

DEBUG | TITLE                      | WINDOW_NAME | WIDTH | HEIGHT | SRC            | IMAGE_NAME | HOFFSET | VOFFSET | ALIGN 
:---- | :------------------------- | :---------- | ----: | -----: | :------------- | :--------- | ------: | ------: | :-----
on    | Sample Konfabulator Widget | main_window |   500 |    500 | Images/Sun.png | sun1       |     250 |     250 | center

db<>fiddle here

How to parse json in oracle sql? (Version:11.2.0)

1 Answers1

Linked