Hi,
I have a JSON like following structure, which stored in one clob column (json_data) of the table (sample_json)
{"Name": "Easystore",
"PhoneNumber": "(010)000-7373",
"notes": [
{
"bote": "Sample note",
"botesUuid": "1723676a985e"
}
],
"act": {
"actDate": "2023-06-27T00:00:00"
},
"Book": [
{
"BookName": "Oracle"
"Bookprice": "60",
},
{
"BookName": "Oracle 2",
"Bookprice": "90",
}
]}
I have written query to pull the information using json_table function.. like below
SELECT TJ.Name,TJ.PhoneNumber,TJ.actDate ,TJ.botesUuid,TJ.BookName, TJ.Bookprice
FROM sample_json T,
JSON_TABLE(T.json_data, '$'
COLUMNS
(Name VARCHAR2(32) PATH '$.Name',
PhoneNumber VARCHAR2(100) PATH '$.PhoneNumber',
actDate date PATH '$.act.actDate' ,
NESTED PATH '$.notes[*]'
columns (
bote VARCHAR2(100) PATH '$.bote',
botesUuid VARCHAR2(100) PATH '$.botesUuid'
)
,NESTED PATH '$.Book[*]'
columns (
BookName VARCHAR2(100) PATH '$.BookName',
Bookprice VARCHAR2(100) PATH '$.Bookprice')
)) TJ
Query is returning 3 rows. that two array information is coming in different row. like below
Name PhoneNumber actDate botesUuid BookName Bookprice
Easystore (010)000-7373 2023-06-27 1723676a985e
Easystore (010)000-7373 2023-06-27 Oracle 60
Easystore (010)000-7373 2023-06-27 Oracle 2 90
Im expecting data should some in two lines, ( Only that two arrays data should be cartesian product)
Name PhoneNumber actDate botesUuid BookName Bookprice
Easystore (010)000-7373 2023-06-27 1723676a985e Oracle 60
Easystore (010)000-7373 2023-06-27 1723676a985e Oracle 2 90
Could you help me by modifying the query which I have written to get above output ?