Hi,
I have been working on JSON features in Oracle to extract field values from the Clob & came across one question - Is it possible to extract the number of elements in the JSON data ?
Ex: If i have below the JSON , is it possible to get the count of elements as 3 ( Fruit, Vegetable & Nuts ) in SQL ?
[{"Fruit":{"currentValue":"Apple", "newValue":"Mango"}, "Vegetable":{"currentValue":"Broccoli", "newValue":"Cabbage"}, "Nuts":{"currentValue":"Walnuts", "newValue":"Almonds"}}]
I am in need of this since i do not have a specific structure of incoming clob . My Structure will be same, element followed by currentValue & newValue.
Thanks,
Prashanth
You can convert JSON arrays to rows using the nested path clause of json_table. So you can get the number of elements with a count(*):
create table t (
x varchar2(1000) check (x is json)
);
insert into t values ('[
{
"Fruit": {
"currentValue": "Apple",
"newValue": "Mango"
}
},
{
"Vegetable": {
"currentValue": "Broccoli",
"newValue": "Cabbage"
}
},
{
"Nuts": {
"currentValue": "Walnuts",
"newValue": "Almonds"
}
}
]');
commit;
select count(*)
from t,
json_table(x, '$' columns
nested path '$[*]'
columns (
elem varchar2(100) format json path '$'
)
)
group by x ;
But... your JSON has an array with only one element which is the document:
{
"Fruit": {
"currentValue": "Apple",
"newValue": "Mango"
},
"Vegetable": {
"currentValue": "Broccoli",
"newValue": "Cabbage"
},
"Nuts": {
"currentValue": "Walnuts",
"newValue": "Almonds"
}
}
If this is your document, you can extract the elements (Fruit, etc.) using JSON table. If you know the names of them!
If these are dynamic, you can use APEX_JSON (in APEX 5) to count the number of elements in the document:
https://docs.oracle.com/cd/E59726_01/doc.50/e39149/apex_json.htm#AEAPI29635 https://oracle-base.com/articles/misc/apex_json-package-generate-and-parse-json-documents-in-oracle