mardi 30 décembre 2014

cannot get correct syntax for pljson

I've installed pljson 1.05 in Oracle Xe 11g and written a PLSQL function to extract values from the return from Amazon AWS describe-instances.


Trying to obtain the values for top level items such as reservation ID work but i am unable to get values nested within lower levels of the json.


e.g. this example works (using the cutdown AWS JSON inline



DECLARE
reservations JSON_LIST;
l_tempobj JSON;
instance JSON;
L_id VARCHAR2(20);
BEGIN
obj:= json('{
"Reservations": [
{
"ReservationId": "r-5a33ea1a",
"Instances": [
{
"State": {
"Name": "stopped"
},
"InstanceId": "i-7e02503e"
}
]
},
{
"ReservationId": "r-e5930ea5",
"Instances": [
{
"State": {
"Name": "running"
},
"InstanceId": "i-77859692"
}
]
}
]
}');
reservations := json_list(obj.get('Reservations'));
l_tempobj := json(reservations);
DBMS_OUTPUT.PUT_LINE('============');
FOR i IN 1 .. l_tempobj.count
LOOP
DBMS_OUTPUT.PUT_LINE('------------');
instance := json(l_tempobj.get(i));
instance.print;
l_id := json_ext.get_string(instance, 'ReservationId');
DBMS_OUTPUT.PUT_LINE(i||'] Instance:'||l_id);
END LOOP;
END;


returning



============
------------
{
"ReservationId" : "r-5a33ea1a",
"Instances" : [{
"State" : {
"Name" : "stopped"
},
"InstanceId" : "i-7e02503e"
}]
}
1] Instance:r-5a33ea1a
------------
{
"ReservationId" : "r-e5930ea5",
"Instances" : [{
"State" : {
"Name" : "running"
},
"InstanceId" : "i-77859692"
}]
}
2] Instance:r-e5930ea5


but this example to return the instance ID doesnt



DECLARE
l_clob CLOB;
obj JSON;
reservations JSON_LIST;
l_tempobj JSON;
instance JSON;
L_id VARCHAR2(20);
BEGIN
obj:= json('{
"Reservations": [
{
"ReservationId": "r-5a33ea1a",
"Instances": [
{
"State": {
"Name": "stopped"
},
"InstanceId": "i-7e02503e"
}
]
},
{
"ReservationId": "r-e5930ea5",
"Instances": [
{
"State": {
"Name": "running"
},
"InstanceId": "i-77859692"
}
]
}
]
}');
reservations := json_list(obj.get('Reservations'));
l_tempobj := json(reservations);
DBMS_OUTPUT.PUT_LINE('============');
FOR i IN 1 .. l_tempobj.count
LOOP
DBMS_OUTPUT.PUT_LINE('------------');
instance := json(l_tempobj.get(i));
instance.print;
l_id := json_ext.get_string(instance, 'Instances.InstanceId');
DBMS_OUTPUT.PUT_LINE(i||'] Instance:'||l_id);
END LOOP;
END;


returning



============
------------
{
"ReservationId" : "r-5a33ea1a",
"Instances" : [{
"State" : {
"Name" : "stopped"
},
"InstanceId" : "i-7e02503e"
}]
}
1] Instance:
------------
{
"ReservationId" : "r-e5930ea5",
"Instances" : [{
"State" : {
"Name" : "running"
},
"InstanceId" : "i-77859692"
}]
}
2] Instance:


The only change from the first example to the second is replacing 'ReservationId' with 'Instances.InstanceId' but in the second example, although the function succeeds and the instance.print statement outputs the full json, this code doesnt populate the Instance ID into l_id so is not output on the DBMS_OUTPUT.


I also get the same result (i.e. no value in L_id) if I just use 'InstanceId'.


My assumption and from reading the examples suggested JSON PATH should allow me to select the values using either the dot notation for nested values but it doesnt seem to work. I also tried extracting instances into a temp variable using JSON_ALL and then accessing it singularly but also wasnt able to get a working example.


Any help appreciated. Many Thanks.





Aucun commentaire:

Enregistrer un commentaire