Converting JSON Data to Relational Data in Oracle 12C

This video demonstrates how to convert JSON data to relational data using json_table in Oracle.

WORK WITH ME👇🏼

✅ Need help with your project? Schedule a call with me at:

[yashreyansh]

I was struggling to fetch details from the list for a while and this video solved my problem within minutes. I couldn’t find any way while googling. Kudos to you✨

[jeeves]

Thank you so much for making this video. You are a great teacher; you've taken an area that terrifies me and actually made it fun! Parsing JSON and XML is always a huge pain for me and this video saved my day. Thank you!!!!

[pratikgaurav]

Superb Explanation team, totally loved it.

[tadibadri]

Thanks for sharing nice and short video, got a question - in real time is there a scenario where the keys of json document keep changing if so, how to handle keys dynamically in the SQL which was shown in this video?

[josecp]

Thx bro, great video. It's been very useful for me :)

[shrihara]

Thanks, was looking for this.
What if there is an array inside an array? How to handle that?

[dharamthakkar]

Thank you so much! I really appreciate this. Superb explanation.
Could you also post update/delete queries and few join examples as well when ever you get time? That would actually make this series complete :)

Once again, thank you for knowledge sharing

[chandhinikrishnakumar]

Thanks! This video shows only for one record. I want to convert my entire json data into columns. Is that possible? I cannot restrict to single row in where clause.

[shivaram]

Thankyou for the video, I really enjoyed it. But is there a way to show the json array as a single row instead of columns?

[vikaspatel]

I have one attribute more than 4000 characters in json, It is returning null, how to use substr there?

[sanchitbajaj]

Hi, I tried to do the same in my Oracle db but it is saying JSON_TABLE is not present in my Oracle. How can I install it into my Oracle db?

[salimjemai]

Did you make the json_table() or it’s built-in?

[rockiru]

Thanks for the tutorial very clean demo
insert into tmp_json
values (1,
'{"
"data_rows": [
["w01_2017", "900002", "1005", 1],
["w01_2018", "900003", "1008", 2]
]
}'
);
Do you how to place this value to relational table with each row within the data_row array mapped to a row in table?


select jt1.*
from tmp_json t
, json_table(t.doc, '$.data_rows[0 to 100][0 to 100]'
columns data_rows varchar2(1000) FORMAT JSON WITH WRAPPER path '$'
) jt1
;

This printing vertically rather than horizontally
["w01_2017"]
["900002"]
["1005"]
[1]
["w01_2018"]
["900003"]
["1008"]
[2]