Version 1 - Plans/Requests

[Jonas Krogsboell]

Version 1 of PL/JSON has been released. The big changes are CLOB support and a changed JSON_LIST interface.

Lets take the JSON_LIST interface first. I didn't like the method-names with _elem added to them (add_elem, set_elem, remove_elem and get_elem). The _elem names made sence in a previous release, but now, with the current object model I would rather like them to be add, set, remove and get. Unfortunately "add" and "set" are reserved words so I had to come up with alternatives and ended up with append and replace.

The getter methods, "get_elem", "get_first" and "get_last" were renamed to "get", "head" and "last". A new method "tail" returns the entire list except the first element.  The two remove methods, "remove_first" and "remove_last" remains untouched.

At the bottom of json_list.typ and json_list_body.typ the old methods are listed and can be enabled to get backward compatibility.

CLOB support: Full CLOB support has been added to the JSON_VALUE type. Every string below 32K is stored in a normal varchar2 variable, string above that limit is stored in a clob variable (the first 32K is still stored in a varchar2 field). To extract the string content to a varchar2 variable, the function get_string allows you to set a limit in bytes or chars. To extract the string to a clob variable the procedure get_string allows you to do that. In the upcoming version I might add offset as a parameter. I don't know if I should keep both max_byte_size and max_char_size as arguments - if someone has some good ideas on how to specify the get_string part, speak up please :-)

The parser and printer can of course handle the new clob field, but key-names remains to be restricted to 4000 bytes (You are insane if that is not enough!).

Two new functions is added in the JSON_EXT package. Encode and decode. They do base64 encoding and decoding on a json_value containing a string. The old way of breaking up a base64 encoding into a list of string remains in the package.

The optional package, JSON_DYN now supports clobs, char and blobs (base64 encoded).

The ability to parse and emit javascript functions is now possible using two /**/ comments to temporary disable the grammar. See the description in the doc.pdf file.

Besides small bugfixes that sum up what has been done in v1. In the future I might try to create some online documentation on how to use PL/JSON, but for now the examples and the code is all there is.

/Jonas

[James Sumners]

Looks good. Thank you for keeping up the hard work.

[Jonas Krogsboell]

You're welcome

[Jonas Krogsboell]

Latest changes:
Version: 1.0.2
    Fix for number parsing for various nls settings
    Enabled sys_refcursor in json_dyn (11g only)
Version: 1.0.1
    forgot to commit example 18 and 19 to svn
    fixed escape error in json_dyn package

[Jonas Krogsboell]

Version: 1.0.3
  UTF8 issue fixed

[Matt Nolan]

Hi Jonas

It's been a while since I've heavily used PLJSON, back in the <1.0 days so I'd like to say a big thanks for adding in support for functions it's made life really easy for APEX plugin development.

I was wondering whether you would consider adding in some helper functions in a future version for merging JSON & JSON_LISTS similar to jQuery.extend and the likes e.g.

  FUNCTION merge_json
  ( p_from_json JSON
  , p_to_json   JSON
  ) RETURN JSON
  AS
  
    l_json    JSON;
    l_keys    JSON_LIST;
    l_key     VARCHAR2(4000);
    
  BEGIN
    --
    -- Initialize our return object
    --
    l_json := p_to_json;
    
    --
    -- Copy our From JSON object to our Destination JSON object
    --
    l_keys := p_from_json.get_keys();
    
    FOR ii IN 1.. l_keys.count LOOP
      l_key := l_keys.get(ii).get_string;
      l_json.put(l_key,p_from_json.path(l_key));
    END LOOP;  
    
    RETURN l_json;
    
  END merge_json;

[Jonas Krogsboell]

Hi Matt,

Good idea - a merging function will be added in the next version.
Perhaps in a helper package.

Current ideas to functions / procedures:
merge(p_obj_a json, p_obj_b json) => json  //recursive merge
remove(p_obj in out nocopy json, keys json_list); //remove keys from json
keep(p_obj in out nocopy json, keys json_list) //keep only pair which are in keys
union(p_obj_a json_list, p_obj_b json_list) => json_list //append b to a

Package name suggestions: JSON_FUNC, JSON_HELPER, JSON_UTIL, JSON_OPERATION,

/Jonas

[Jonas Krogsboell]

New version out: 1.0.4

Two new packages: JSON_HELPER and JSON_AC.
JSON_HELPER contains set operations like merge, contains, remove, etc.

JSON_AC is a wrapper around the three core types: JSON, JSON_LIST and JSON_VALUE.
It offers nothing new, but helps me when I forget method names on those types.
It's a workaround because SQLDeveloper apparently has support for autocompletion on packages but not on object types.

Bug fix in JSON_DYN: empty_blob() gave an error.

/Jonas

[Matt Nolan]

Hi Jonas

Thanks for adding the new helper package, really helps a lot.

Is there any chance you can support using the /**/function/javascript/**/ in JSON_LIST? Currently it's getting escaped on output. It would really help me out.

Cheers
Matt

[Jonas Krogsboell]

Sure - I will look into it when i've got the time.