By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
WITH t(js) AS
(
SELECT '{
"timestamp": 1654752742887,
"status": "OK",
"statusCode": 200,
"message": "GET response successful.",
"content": [
{
"Total No. of Transaction": 1,
"Sum of Transaction": 473
}
]
}'
FROM dual
)
SELECT "Total No. of Transaction", "Sum of Transaction"
FROM t,
JSON_TABLE(js, '$'
COLUMNS (NESTED PATH '$.content[*]'
COLUMNS ("Total No. of Transaction" VARCHAR2 PATH '$."Total No. of Transaction"',
"Sum of Transaction" VARCHAR2 PATH '$."Sum of Transaction"')
))
| Total No. of Transaction | Sum of Transaction |
|---|---|
| 1 | 473 |