Added and documented jsonParse, jsonObject, jsonArray, jsonArray_Agg. Also logged TEIID-2390 to improve document generation performance.

+1 for Clob type, as using in the JDBC API it will reduce any confusion for access at the same time it will have all the streaming capabilities.

This issue for the production side from Teiid. There would be another issue to tie this into the rest war generation (either automatic or from Designer). Teiid already has jsonToXml for processing incoming json and he google spreadsheet translator that consumes google json, and the odata in/out logic.

We'll probably revisit the jsonToXml logic at some point do something more idomatic to json.

Will this enable us to produce Rest API that return json objects or just consume json API?
Thanks
Luca

After starting the implementation I'm leaning toward just using a subtype of Clob (similar to the XMLType.Type subtypes) to represent JSON rather than introducing a full new type. This allows us to sidestep whether json needs to be a keyword and a host of logic that needs added related to types.

An example usage:

select jsonArray(1, null, true, {d '2007-01-01'}, jsonParse('{"name":123}', true), unescape('\t\n?'))

returns

[1,null,true,"2007-01-01",{"name":123},"\t\n?"]

note the conversion of date to string

JsonParse/JsonArray will be added as normal functions. While JsonObject and JsonArrayAgg (which can be deprecated when we generally allow array type usage) will need parsing/language object support.

The initial work should be in for beta2.

pg distills this a little more - http://www.postgresql.org/docs/devel/static/functions-json.html

It seems best to add a json type so that we are validating.

JsonParse/JsonSerialize (if we want to support other encodings / pretty print options)
JsonObject (basically the same as pg row_to_json)
JsonArray
JsonAgg (necessary since we lack formal array support)

I was thinking Clob/Blob as the object too, if we introduce a new one we will get into same issues that faced with SQLXML object prior to Java 6. I do not think this needs to be first class data type.

There is validating schema for JSON right? like XML Schema, at least I did not hear about it.

If this needs to be validating then we'll add a json type (which is quite a bit of work) and a JsonParse functions makes sense. Otherwise clob/blob will work and JsonParse isn't necessary.

I would expect the JsonForrest/JsonObject functionality to be merged. That is JsonObject should take a name and derived columns to produce an object result.

JsonArray replaces XMLConcat. Also JsonAgg could be JsonArrayAgg or just always produce an array result.