In this example, store is an array that contains arrays called book. The path expression, $.store[1].book[2], retrieves property values from these nested arrays.

SELECT
    -- {option "DISABLE_PLAN_CACHE" }
    myTitle,
    author,
    price
FROM JSON_TABLE('{
"store": [{
"book":
[{
"category_2": "reference",
"author" : "Nigel Rees",
"title": "Sayings of the Century_S1-BA1-B1",
"price": 13.95
} ,
{
"category_2": "reference",
"author" : "Nigel Rees",
"title": "Sayings of the Century_S1-BA1-B1",
"price": 12.95
}
] ,
"book": [ {
"category_2": "reference",
"author" : "Nigel Rees",
"title": "Sayings of the Century_S1-BA2-B1",
"price": 11.95
} ,
{
"category_21": "reference",
"author" : "Nigel Rees",
"title": "Sayings of the Century_S1-BA2-B2",
"price": 10.95
}
]
} ,
{
"book": [ {
"category_2": "reference",
"author" : "Nigel Rees",
"title": "Sayings of the Century_S1-BA3-B1",
"price": 9.95
} ,
{
"category_21": "reference",
"author" : "Nigel Rees",
"title": "Sayings of the Century_S1-BA3-B2",
"price": 8.95
}
]
}
]}','$.store[1].book[2]'COLUMNS(myTitle VARCHAR(100) PATH '$.title',price DOUBLE PATH '$.price',author VARCHAR(100) PATH '$.author')) JT
ORDER BY price asc

How to fetch the data from json nested arrays using JSON_TABLE

any

The results are fetched based on the PATH expression and then sorted by price:
 

myTitle	author	price
Sayings of the Century_S1-BA2-B2	Nigel Rees	10.95
Sayings of the Century_S1-BA1-B1	Nigel Rees	12.95