Re: SQLJSON - Mailing list pgsql-jdbc

Thinking this through a bit, I see a couple things we'd need to figure out:

1. What parser would we use?

2. What object type(s) would the parser return?

3. What would the end-user API look like?

4. What's the end user benefit of this?

#1 has lots of possible answers. Everything from pluggable APIs, classpath scanning, the JSON APIs mentioned so far in this thread.

#2 is driven a lot by #1 as depending on the parser implementation there may be different object types returned. JSON is a bit tricky as "valid JSON" can mean null, a scalar, an object, or an array. Most people thing of it as just an object but "foobar" is valid JSON as well. This leads us to #3...

#3 doesn't have a straight answer as there is no getJSON(...) methods in the JDBC spec. It'd probably have to be returned via getObject(...).

An alternative is to provide PGResultSet and PGPreparedStatement classes similar to PGConnection that provides PG extensions. They could have the get/set methods (ex: getJsonScalar(...) or setJsonObject(Map<String,Object> ...)) to retrieve JSON values as specific object types (i.e. scalar, object, array). It'd be a bit more type safe as presumably most people using json/jsonb types know the top level type of what they're storing. 

For #4 I see two possible wins. First off on the usability side, there's some convenience to natively interfacing with json/jsonb types. It'll only have value though if those types are the same ones that users are using in the rest of their code. If they're just using them as Map<String,Object> everywhere then it'd still be a pain for a user to convert to our "native" PG JSON types to use via JDBC. Having a dedicated API that allows for interaction using native Java types would make this more convenient.

The other win I can see for #4 is on performance. Right now JSON is converted to a String. That means everybody using it has to convert it twice. First raw bytes to String, then String to object. A dedicated API could cut one of those out of the way. Given how the wire protocol is implemented in the driver, it wouldn't be a direct reading from the input stream (it'll be buffered in a byte array), but at least it won't be copied twice.