Re: SQLJSON

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.

Regards,
-- Sehrope Sarkuni
Founder & CEO | JackDB, Inc. | https://www.jackdb.com/