cannot get correct syntax for pljson

Question

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 if type JSON_LIST and then accessing it from there but also wasnt able to get a working example.

Any help appreciated. Many Thanks.

James Sumners · Accepted Answer · 2014-12-31T14:57:22.863

1

See ex8.sql. In particular, it says:

JSON Path for PL/JSON:

never raises an exception (null is returned instead)

arrays are 1-indexed

use dots to navigate through the json scopes.

the empty string as path returns the entire json object.

JSON Path only work with JSON as input.

7 get types are supported: string, number, bool, null, json, json_list and date!

spaces inside [ ] are not important, but is important otherwise

Thus, your path should be:

l_id := json_ext.get_string(instance, 'Instances[1].InstanceId');

Or, without directly using json_ext:

l_id := instance.path('Instances[1].InstanceId');

edited Dec 31 '14 at 14:57

answered Dec 31 '14 at 00:49

James Sumners

14,485
10
59
77

1

Yes, we need to get this stuff sorted in the documentation. If you're up for helping -- github.com/pljson/pljson – James Sumners Dec 31 '14 at 00:50

cannot get correct syntax for pljson

1 Answers1