Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Abraham.

Asked: August 02, 2018 - 5:13 pm UTC

Last updated: August 03, 2018 - 3:17 pm UTC

Version: 12

Viewed 1000+ times

You Asked

The following json_table works perfectly well. The object "testing" holds an array, and data from the array is fetcehd as two rows, when the path is "$.testing[*]"

select j.*
from 
 json_table (
  '
{"testing":
  [
{
   "message": "This is message nr. one",
   "name": "Adam",
   "created_time": "2015-05-12T16:26:12+0000"   
  },
  {
   "message": "This is message nr. two",
   "name": "Eve",
   "created_time": "2015-05-11T19:23:11+0000"
  }
 ]
}
'
, '$.testing[*]'
columns (
 "name" path '$.name',
 "message" path '$.message'
)
) j;


But how do I go about accomplishing the same, if the JSON on hand doesn't have the object name? Suppose the entire JSON looks like this:

[
{
   "message": "This is message nr. one",
   "name": "Adam",
   "created_time": "2015-05-12T16:26:12+0000"   
  },
  {
   "message": "This is message nr. two",
   "name": "Eve",
   "created_time": "2015-05-11T19:23:11+0000"
  }
]



what would be the correct syntax to achieve the above results?

and Chris said...

Place the [*] immediately after $:

select j.*
from 
 json_table (
  '[
{
   "message": "This is message nr. one",
   "name": "Adam",
   "created_time": "2015-05-12T16:26:12+0000"   
  },
  {
   "message": "This is message nr. two",
   "name": "Eve",
   "created_time": "2015-05-11T19:23:11+0000"
  }
]', '$[*]'
columns (
 "name" path '$.name',
 "message" path '$.message'
)
) j;

name   message                   
Adam   This is message nr. one   
Eve    This is message nr. two  

Rating

  (1 rating)

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

Comments

Perfect - thanks

A reader, August 03, 2018 - 3:35 pm UTC


More to Explore

SQL

The Oracle documentation contains a complete SQL reference.