PL/JSON - JSON Path

A JSON Path is a way to navigate in a JSON object. The implementation in PL/JSON is quite simple and does not support all the features that are available in Stefan Goessner's JavaScript, Python and PHP implementation of JSON Path. Actually, all that it does is to add the support for navigation in JSON, which are already built in into those languages. When navigating with JSON Path in PL/JSON, members of JSON objects are found with the dot operator while elements in lists are found with square brackets. Accepted input in path navigation is formalized in the grammar (ws is insignificant whitespace):

jsonpath : ((alphanum | 'space')*) (( '.' ((alphanum | 'space')+) | '[' ('ws') (string | integer) ('ws') ']' ('ws') )*);
string   : (dquote ((unia|esc)*) dquote) | (quote ((unib|esc)*) quote);
integer  : pdigits (() + digits);

From version 0.9.6 the implementation accepts an extended grammar where you use a zero-indexed JSON Path. The following examples show how you can use JSON Path to extract from a JSON object.

The JSON object:

{
  "xyz" : {
    "abc" : [1,2,3,[4,5,{"123":45}]]
  }
}

Extract the abc list:

pljson_ext.get_json_list(obj, 'xyz.abc').print;

-- [1, 2, 3, [4 ,5 , {
--   "123" : 45
-- }]]

Extract the 123 number:

pljson_ext.get_number(obj, 'xyz.abc[4][3].123').print;

-- 45

As of version 0.8.4, square brackets can be used to extract JSON members like you would do in JavaScript:

pljson_ext.get_number(obj, '["xyz"]["abc"][4][3]["123"]').print;

-- 45

You can also use JSON Path to modify an existing JSON object:

pljson_ext.put(obj, 'xyz.abc', pljson('{"val":123}'));

-- {
--   "xyz" : {
--     "abc" : {
--       "val" : 123
--     }
--   }
-- }

Removing unwanted elements is also an option:

pljson_ext.remove(obj, 'xyz.abc');

-- {
--   "xyz" : {
--   }
-- }

In the 0.9.1 release, both pljson and pljson_list are hooked to the JSON Path implementation:

declare
  v_obj pljson := pljson('{a:true}');
  v_list pljson_list := pljson_list('[1,2,[3,4]]');
begin
  v_obj.path('a').print;
  v_list.path('[3][1]').print;
end;

-- true
-- 3

The example files 8 to 10 provides a more detailed explanation.

In the 0.9.2 release, it is also possible to use path to modify objects and arrays:

declare
  v_obj pljson := pljson('{a:true}');
  v_list pljson_list := pljson_list('[1,2,[3,4]]');
begin
  v_obj.path_put('a[2]', pljson_list('[true, true]'));
  v_obj.print;
  v_list.path_put('[3][1]', 'test');
  v_list.print;
end;

-- {
--   "a" : [null, [true, true]]
-- }
-- [1, 2, ["test", 4]]