Hi All
Im using APEX_JSON because Im running on 12.1.0.1
given the below JSON, I'm trying to get the nested array values for CategoryPath. i.e.
"Food",
"Meat",
"Beef",
“KObe”
Here is the JSON and code.. Ive tried every variation of path, i.e Data[%d]CategoryPath but to no avail. If someone could help Id be grateful..…
declare
l_count number;
l_cat_count number;
l_json_text clob;
j apex_json.t_values;
begin
l_json_text := '
{
"IsSuccess": true,
"Data": [
{
"ID": 3,
"Code": "Sprite",
"Name": "Sprite 0.3l",
"VatCharge": 20.0,
"IDState": 4,
"IDSupplier": -1,
"IDMainStock": 1000079,
"IDUnit": 1,
"UnitCode": "pcs",
"PricePri": 1.3072,
"PricePurchase": 1.3072,
"PriceRef": 0.0000,
"IDCategory": 1,
"CategoryPath": null
},
{
"ID": 5,
"Code": "Meat",
"Name": "Meat",
"VatCharge": 20.0,
"IDState": 4,
"IDSupplier": 1000087,
"IDMainStock": -1,
"IDUnit": 2,
"UnitCode": "Kg",
"PricePri": 0.0000,
"PricePurchase": 1.5384,
"PriceRef": 0.0000,
"IDCategory": 26,
"CategoryPath": [
"Food",
"Meat",
"Beef",
"KObe"
]
}
],
"Errors": null
}';
APEX_JSON.parse(l_json_text);
l_count := APEX_JSON.get_count(p_path => 'Data');
DBMS_OUTPUT.put_line('Count : ' || l_count);
FOR i IN 1 .. l_count LOOP
DBMS_OUTPUT.put_line('Name : ' ||
APEX_JSON.get_varchar2(p_path => 'Data[%d].Name', p0 => i));
IF apex_json.does_exist(p_path => 'CategoryPath[%d]', p0 => i, p_values=>j) THEN
l_cat_count := apex_json.get_count(p_path => 'CategoryPath[%d]',p0 => i, p_values=>j);
dbms_output.put_line('Category count '|| l_cat_count);
else
dbms_output.put_line('Invalid Path');
end if;
end loop;
end;