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?
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