Skip to Main Content
  • Questions

  • Problem in flattening the Json data using oracle

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Rishi.

Asked: August 24, 2021 - 5:29 pm UTC

Last updated: August 25, 2021 - 10:15 am UTC

Version: 19

Viewed 1000+ times

You Asked

Hi, I have a json sitting in clob data type in one of the table as below. I am not able to extract data from "DataAsJson"(Set 1 and Set2)

{
"RequestId":"test-RequestId"
"RequestDate":"test-RequestId"
"RequestTime":"test-RequestTime"
"DataAsJson": "[{\"Key1\":\"Value1\",\"Key2\":\"Value2\",\"Key3\":\"Value3\",
               " \": [{\"Set1\":{\"Key_a\":\"Value_a\",\"Key_b\":\"Value_b\",
                     \"Set2\":[{\"Key_aa\":\"Value_aa\",\"Key_bb\":\"Value_bb\"}]}
}

select jt*
from table_name jte,
json_table(jte.data , '$'
columns (DataAsJason path '$.DataAsJason'))jt


I have tried using nested path, $[*], but still no luck. Every time I get null.

and Chris said...

Is that really an example of the source JSON?

If so, the problem is it's just junk - not even close to valid JSON!

There are missing commas, closing paraentheses, and closing brackets

There's also no need to quote the "DataAsJson" attribute and escape the quotes within it. You can nest JSON structures within each other just fine.

You need to fix the JSON datat before JSON queries will work.

Is this answer out of date? If it is, please let us know via a Comment
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.