Oracle SQL: JSON query

Question

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?

Farshid Shekari · Accepted Answer · 2022-07-14 21:08:35Z

3

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;

answered Jul 14, 2022 at 21:08

Farshid Shekari

2,4394 gold badges31 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Oracle SQL: JSON query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related