Table FRUIT has a column CONTENT is clob, save the Json data, similar to the following
"Fruit" : [{
"Type" : "apple",
"Id" : 12345,
"Code" : "AP_12345,"
"Type" : "apple", "
},
{
"Type" : "pear",
"Id" : 23456,
"Code" : "PE_23456,"
"Type" : "pear", "
}]
Code:
SELECT
FR. *
FROM the FRUIT,
JSON_TABLE (CONTENT, '$. Fruit [*]'
The COLUMNS (Code VARCHAR2 (10) PATH '$. Code',
Id VARCHAR2 (10) PATH '$. Id',
Type VARCHAR2 (10) PATH '$. Type'
)) AS FR
;
Ran out of the results of the Type field is empty, because of there are two Type? On the premise of not change data, have what method to solve?
CodePudding user response:
SELECT FR. *
FROM the FRUIT,
JSON_TABLE (CONTENT,
'$' COLUMNS (NESTED PATH' $[*] 'COLUMNS (Code VARCHAR2 (10) PATH' $. Code ',
Id VARCHAR2 (10) PATH '$. Id',
Type VARCHAR2 (10) PATH '$. Type'))) AS FR;
With my hair of the SQL,