Oracle SQL: JSON query-CodePudding

I have below JSON in 12c Oracle table

[
  {
    "od": "2022-01-01",
    "md": "2022-01-01",
    "di": 2.1
  },
  {
    "od": "2022-02-02",
    "md": "2022-02-02",
    "di": 3.1
  },
  {
    "od": "2022-02-03",
    "md": "2022-02-03",
    "di": 4.1
  }
]

od= order_date, md=modified_date, di= discount

Expected output

od	md	di
2022-01-01	2022-01-01	2.10
2022-02-02	2022-02-02	3.10
2022-02-03	2022-02-03	4.10

I tried JSON_VALUE(ORDER_DATA, '$.di' ) it gives me null

I tried JSON_TABLE() as below but then too it gives me null

SELECT j.id, jt.di 
  FROM order_date j, 
       JSON_TABLE(
                  j.json_order_data, 
                  '$' COLUMNS (
                                di number(10) PATH '$.di[*]'
                              )
                 ) jt;

JSON_EXTRACT() does not work in oracle

I have tried that as well, can anyone please help me for this SQL query?

CodePudding user response：

Use it in this way:

SELECT j.id, jt.di 
  FROM ORDER_DATE j, 
       json_table(j.JSON_ORDER_DATA, '$[*]' 
        COLUMNS (
          di number(10) PATH '$.di'
        )) jt;