Thread: SQLJSON

On Jun 26, 2015, at 7:57 AM, Dave Cramer <davecramer@gmail.com> wrote:

> I'm looking for comments on how to implement a SQLJSON type in JDBC.
>
> As there is no getSQLJSON in the resultset interface this could only be used in getObject.
>
> Notionally it would model itself after SQLXML.
https://docs.oracle.com/javase/7/docs/api/index.html?java/sql/SQLXML.html

I used JSON extensively in one of my projects, but have never used SQLXML.  I'm having trouble understanding why the
SQLXMLinterface adds any value to passing rs.getBinaryStream to your favorite JSON parser.  Especially since you would
haveto use getObject, I am not seeing how: 

rs.getObject("field", SQLJSON.class).mapToType(MyType.class)

is simpler than:
jacksonObjectMapper.readValue(rs.getBinaryStream("field"), MyType.class)

which already works today as far as I understand.  Doubly so since nobody will agree on which JSON parsing library to
use.

I'm sure I'm missing something?

On Jun 26, 2015, at 10:23 AM, Dave Cramer <davecramer@gmail.com> wrote:

>
> On 26 June 2015 at 13:01, Steven Schlansker <stevenschlansker@gmail.com> wrote:
>
> On Jun 26, 2015, at 7:57 AM, Dave Cramer <davecramer@gmail.com> wrote:
>
> > I'm looking for comments on how to implement a SQLJSON type in JDBC.
> >
> > As there is no getSQLJSON in the resultset interface this could only be used in getObject.
> >
> > Notionally it would model itself after SQLXML.
https://docs.oracle.com/javase/7/docs/api/index.html?java/sql/SQLXML.html
>
> I used JSON extensively in one of my projects, but have never used SQLXML.  I'm having trouble understanding why the
SQLXMLinterface adds any value to passing rs.getBinaryStream to your favorite JSON parser.  Especially since you would
haveto use getObject, I am not seeing how: 
>
> rs.getObject("field", SQLJSON.class).mapToType(MyType.class)
>
> is simpler than:
> jacksonObjectMapper.readValue(rs.getBinaryStream("field"), MyType.class)
>
> which already works today as far as I understand.  Doubly so since nobody will agree on which JSON parsing library to
use.
>
> I'm sure I'm missing something?
>
>
> I don't think you are; as you rightly pointed out now we would have to add a json parser to the driver, which I'm
reluctantto do  
>

If this feature is developed, I think the JSON parser should be pluggable and optional if possible.  Then users that do
notwant it do not need to drag in a large dependency. 

That said, without a more convincing use case or a compelling API that we could easily add, I don't see this interface
being"worth its weight" as an addition. 

Please read this regarding pluggability:
https://json-processing-spec.java.net/nonav/releases/1.0/fcs/javadocs/javax/
json/spi/JsonProvider.html.

Please read this regarding inability to agree on a standard:
https://jcp.org/en/jsr/results?id=5486.

The merits of adding a SQLJSON type to JDBC would be that in case an RDBMS
might have some kind of special support for high-performance object-tree
transmission in the network protocol (like lazy lookup and delayed
transmission of child objects for example, or using binary transmission
instead of JSONP plain-text), the driver could make use of that knowledge
and work in the fastest and memory-saving way. That benefits are impossible
to gain when using a character stream, as that requires to explicitly
inflate to a full JSONP representation in-memory just to parse that string
in turn in a second step.

whether or not to support JSON is not up to the implementors of drivers, but
up to the JDBC specification vendor. I doubt that with JSONP being a
mandatory part of Java EE they will leave room for not providing JSONP in
JDBC. We might be reluctant for now, but I believe that JDBC5 will enforce
it.

Regards
-Markus

-----Original Message-----
From: pgsql-jdbc-owner@postgresql.org
[mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Steven Schlansker
Sent: Freitag, 26. Juni 2015 19:29
To: Dave Cramer
Cc: List
Subject: Re: [JDBC] SQLJSON


On Jun 26, 2015, at 10:23 AM, Dave Cramer <davecramer@gmail.com> wrote:

>
> On 26 June 2015 at 13:01, Steven Schlansker <stevenschlansker@gmail.com>
wrote:
>
> On Jun 26, 2015, at 7:57 AM, Dave Cramer <davecramer@gmail.com> wrote:
>
> > I'm looking for comments on how to implement a SQLJSON type in JDBC.
> >
> > As there is no getSQLJSON in the resultset interface this could only be
used in getObject.
> >
> > Notionally it would model itself after SQLXML.
> > https://docs.oracle.com/javase/7/docs/api/index.html?java/sql/SQLXML
> > .html
>
> I used JSON extensively in one of my projects, but have never used SQLXML.
I'm having trouble understanding why the SQLXML interface adds any value to
passing rs.getBinaryStream to your favorite JSON parser.  Especially since
you would have to use getObject, I am not seeing how:
>
> rs.getObject("field", SQLJSON.class).mapToType(MyType.class)
>
> is simpler than:
> jacksonObjectMapper.readValue(rs.getBinaryStream("field"),
> MyType.class)
>
> which already works today as far as I understand.  Doubly so since nobody
will agree on which JSON parsing library to use.
>
> I'm sure I'm missing something?
>
>
> I don't think you are; as you rightly pointed out now we would have to
> add a json parser to the driver, which I'm reluctant to do
>

If this feature is developed, I think the JSON parser should be pluggable
and optional if possible.  Then users that do not want it do not need to
drag in a large dependency.

That said, without a more convincing use case or a compelling API that we
could easily add, I don't see this interface being "worth its weight" as an
addition.



--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org) To make changes
to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc

On 26/06/15 19:29, Steven Schlansker wrote:
> On Jun 26, 2015, at 10:23 AM, Dave Cramer <davecramer@gmail.com> wrote:
>
>> On 26 June 2015 at 13:01, Steven Schlansker <stevenschlansker@gmail.com> wrote:
>>
>> On Jun 26, 2015, at 7:57 AM, Dave Cramer <davecramer@gmail.com> wrote:
>>
>>> I'm looking for comments on how to implement a SQLJSON type in JDBC.
>>>
>>> As there is no getSQLJSON in the resultset interface this could only be used in getObject.
>>>
>>> Notionally it would model itself after SQLXML.
https://docs.oracle.com/javase/7/docs/api/index.html?java/sql/SQLXML.html
>> I used JSON extensively in one of my projects, but have never used SQLXML.  I'm having trouble understanding why the
SQLXMLinterface adds any value to passing rs.getBinaryStream to your favorite JSON parser.  Especially since you would
haveto use getObject, I am not seeing how: 
>>
>> rs.getObject("field", SQLJSON.class).mapToType(MyType.class)
>>
>> is simpler than:
>> jacksonObjectMapper.readValue(rs.getBinaryStream("field"), MyType.class)
>>
>> which already works today as far as I understand.  Doubly so since nobody will agree on which JSON parsing library
touse. 
>>
>> I'm sure I'm missing something?
>>
>>
>> I don't think you are; as you rightly pointed out now we would have to add a json parser to the driver, which I'm
reluctantto do 
>>
> If this feature is developed, I think the JSON parser should be pluggable and optional if possible.  Then users that
donot want it do not need to drag in a large dependency. 
>
> That said, without a more convincing use case or a compelling API that we could easily add, I don't see this
interfacebeing "worth its weight" as an addition. 
>
>
>

     Hi List.

     I always try to think more from the user perspective than from the
developer one. This is also going to be the case.

     Having JSON support in the PostgreSQL JDBC driver is a *must*.
jsonb was 9.4's next-big-thing-since-sliced-bread jet there's no support
in one of the most used PostgreSQL drivers. No blame here (at all), just
trying to support my point here.

     I don't see the advantage of using SQLJSON, although I wouldn't
argue against. What I clearly believe is that at the end of the day you
should be able to easily return a javax.json.JsonObject from a
ResultSet. Being a JavaEE standard and a JSR, I believe it's the best
(and obvious) choice.

     Regarding pluggability, JSR353's SPI mechanism is good, but asking
the user to provide a further dependency "just for reading JSON" seems
again to me not good from the user perspective. I'd ideally expect json
to be supported as-is, as with any other datatype. Having the SPI we
could choose whatever implementation we want. Is that enlarging the
driver's size? So what? Users want easy-of-use, not driver size. And
there are many mechanisms to reduce size for unused classes.

     If any, my 2 cents are: let's add JSON, let's take JSR353 as an API
for it and let's make it as easy as possible for final users to use it.

     Regards,

     Álvaro



--
Álvaro Hernández Tortosa


-----------
8Kdata

Álvaro,

I also think we should look at the topic with the eyes of the user, but I
have to disagree in two point exactly due to that attitude:

(1) JSON is definitively not just another datatype. Just like XML, it
provides the ability to return a complete object graph as the content of one
column of one row. Hence, it contains the same amount of data that in
pre-XML and pre-JSON times, the ResultSet did handle alone. Or in other
words, from the complexity of the information and from the aspect of end
user performance, it "feels" like a ResulSet that contains ResultSets that
contains ResultSets... Handling it just like any other datatype is what
pgjdbc does currently, and it provides very poor performance due to that, as
it simply ships the complete graph - independent of the fact whether the end
user will process is completely or just pick a single node of it. Asking to
handle JSON as just any other data type is like asking to give up ResultSet
in favor of returning an ArrayList<ArrayList<?>>! So looking with the eyes
of an end user, the answer must be, to NOT handle JSON like any other
datatype, and NOT return a JsonObject, but instead provide a streaming API,
just like SQLXML does. This allows to only transfer and process that few
nodes the the end user actually likes to have, just like an end user
typically will never process a complete ResultSet but process it iteratively
and most typically filtered.

(2) The user MUST be always asked to provide a JSON parser, as it is HIM who
wants to use JSON, and so it is HIS choice to select one among those
fulfilling the JSONP API standard. Hence it makes no sense that pgjdbc picks
and / or provides one. Most typically this will end up in the same design
choice than most XML applications ended up with: Relying on Java SE / EE
providing a default product, like it is the case with JAXB (Xerces). The
only thing pgjdbc can really do NOW is to support the JSON SPI *if* there is
a processor on the classpath, and once the postgresql server produces an
improved streaming procotol for JSON / XML we can couple the client's JSON
event handler with the server's protocol events.

Regards
Markus

-----Original Message-----
From: pgsql-jdbc-owner@postgresql.org
[mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Álvaro Hernández
Tortosa
Sent: Samstag, 27. Juni 2015 18:56
To: pgsql-jdbc@postgresql.org
Subject: Re: [JDBC] SQLJSON


On 26/06/15 19:29, Steven Schlansker wrote:
> On Jun 26, 2015, at 10:23 AM, Dave Cramer <davecramer@gmail.com> wrote:
>
>> On 26 June 2015 at 13:01, Steven Schlansker <stevenschlansker@gmail.com>
wrote:
>>
>> On Jun 26, 2015, at 7:57 AM, Dave Cramer <davecramer@gmail.com> wrote:
>>
>>> I'm looking for comments on how to implement a SQLJSON type in JDBC.
>>>
>>> As there is no getSQLJSON in the resultset interface this could only be
used in getObject.
>>>
>>> Notionally it would model itself after SQLXML.
>>> https://docs.oracle.com/javase/7/docs/api/index.html?java/sql/SQLXML
>>> .html
>> I used JSON extensively in one of my projects, but have never used
SQLXML.  I'm having trouble understanding why the SQLXML interface adds any
value to passing rs.getBinaryStream to your favorite JSON parser.
Especially since you would have to use getObject, I am not seeing how:
>>
>> rs.getObject("field", SQLJSON.class).mapToType(MyType.class)
>>
>> is simpler than:
>> jacksonObjectMapper.readValue(rs.getBinaryStream("field"),
>> MyType.class)
>>
>> which already works today as far as I understand.  Doubly so since nobody
will agree on which JSON parsing library to use.
>>
>> I'm sure I'm missing something?
>>
>>
>> I don't think you are; as you rightly pointed out now we would have
>> to add a json parser to the driver, which I'm reluctant to do
>>
> If this feature is developed, I think the JSON parser should be pluggable
and optional if possible.  Then users that do not want it do not need to
drag in a large dependency.
>
> That said, without a more convincing use case or a compelling API that we
could easily add, I don't see this interface being "worth its weight" as an
addition.
>
>
>

     Hi List.

     I always try to think more from the user perspective than from the
developer one. This is also going to be the case.

     Having JSON support in the PostgreSQL JDBC driver is a *must*.
jsonb was 9.4's next-big-thing-since-sliced-bread jet there's no support in
one of the most used PostgreSQL drivers. No blame here (at all), just trying
to support my point here.

     I don't see the advantage of using SQLJSON, although I wouldn't argue
against. What I clearly believe is that at the end of the day you should be
able to easily return a javax.json.JsonObject from a ResultSet. Being a
JavaEE standard and a JSR, I believe it's the best (and obvious) choice.

     Regarding pluggability, JSR353's SPI mechanism is good, but asking the
user to provide a further dependency "just for reading JSON" seems again to
me not good from the user perspective. I'd ideally expect json to be
supported as-is, as with any other datatype. Having the SPI we could choose
whatever implementation we want. Is that enlarging the driver's size? So
what? Users want easy-of-use, not driver size. And there are many mechanisms
to reduce size for unused classes.

     If any, my 2 cents are: let's add JSON, let's take JSR353 as an API for
it and let's make it as easy as possible for final users to use it.

     Regards,

     Álvaro



--
Álvaro Hernández Tortosa


-----------
8Kdata



--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc

On 27/06/15 20:07, Markus KARG wrote:
> Álvaro,
>
> I also think we should look at the topic with the eyes of the user, but I
> have to disagree in two point exactly due to that attitude:
     Hi Markus. Good that we agree on that :)
>
> (1) JSON is definitively not just another datatype. Just like XML, it
> provides the ability to return a complete object graph as the content of one
> column of one row. Hence, it contains the same amount of data that in
> pre-XML and pre-JSON times, the ResultSet did handle alone. Or in other
> words, from the complexity of the information and from the aspect of end
> user performance, it "feels" like a ResulSet that contains ResultSets that
> contains ResultSets... Handling it just like any other datatype is what
> pgjdbc does currently, and it provides very poor performance due to that, as
> it simply ships the complete graph - independent of the fact whether the end
> user will process is completely or just pick a single node of it. Asking to
> handle JSON as just any other data type is like asking to give up ResultSet
> in favor of returning an ArrayList<ArrayList<?>>! So looking with the eyes
> of an end user, the answer must be, to NOT handle JSON like any other
> datatype, and NOT return a JsonObject, but instead provide a streaming API,
> just like SQLXML does. This allows to only transfer and process that few
> nodes the the end user actually likes to have, just like an end user
> typically will never process a complete ResultSet but process it iteratively
> and most typically filtered.
     When I said "as just another datatype", I mean "as simple as". So
from a user perspective I would expect to have a get...JSON() method
with return value an object similar to (or IMHO exactly) JsonObject. In
other words, something directly usable.

     As Dave Cramer correctly points out in response earlier in this
thread, there is no way in current PostgreSQL support to send a partial
chunk of the JSON, so --unfortunately-- streaming API is not possible.
Not being it possible, why not provide methods to return JsonObject, the
Java EE standard and defined by a JSR?

>
> (2) The user MUST be always asked to provide a JSON parser, as it is HIM who
> wants to use JSON, and so it is HIS choice to select one among those
> fulfilling the JSONP API standard. Hence it makes no sense that pgjdbc picks
> and / or provides one. Most typically this will end up in the same design
> choice than most XML applications ended up with: Relying on Java SE / EE
> providing a default product, like it is the case with JAXB (Xerces). The
> only thing pgjdbc can really do NOW is to support the JSON SPI *if* there is
> a processor on the classpath, and once the postgresql server produces an
> improved streaming procotol for JSON / XML we can couple the client's JSON
> event handler with the server's protocol events.

     Why not instead provide a default parser and let the user choose
another one if so she wants? It makes life easier for 90% of users who
don't care or even know about which parser to choose. Providing a
sensible default (or just a working default) is better than providing none.

     Regards,

     Álvaro


--
Álvaro Hernández Tortosa


-----------
8Kdata



>
> Regards
> Markus
>
> -----Original Message-----
> From: pgsql-jdbc-owner@postgresql.org
> [mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Álvaro Hernández
> Tortosa
> Sent: Samstag, 27. Juni 2015 18:56
> To: pgsql-jdbc@postgresql.org
> Subject: Re: [JDBC] SQLJSON
>
>
> On 26/06/15 19:29, Steven Schlansker wrote:
>> On Jun 26, 2015, at 10:23 AM, Dave Cramer <davecramer@gmail.com> wrote:
>>
>>> On 26 June 2015 at 13:01, Steven Schlansker <stevenschlansker@gmail.com>
> wrote:
>>> On Jun 26, 2015, at 7:57 AM, Dave Cramer <davecramer@gmail.com> wrote:
>>>
>>>> I'm looking for comments on how to implement a SQLJSON type in JDBC.
>>>>
>>>> As there is no getSQLJSON in the resultset interface this could only be
> used in getObject.
>>>> Notionally it would model itself after SQLXML.
>>>> https://docs.oracle.com/javase/7/docs/api/index.html?java/sql/SQLXML
>>>> .html
>>> I used JSON extensively in one of my projects, but have never used
> SQLXML.  I'm having trouble understanding why the SQLXML interface adds any
> value to passing rs.getBinaryStream to your favorite JSON parser.
> Especially since you would have to use getObject, I am not seeing how:
>>> rs.getObject("field", SQLJSON.class).mapToType(MyType.class)
>>>
>>> is simpler than:
>>> jacksonObjectMapper.readValue(rs.getBinaryStream("field"),
>>> MyType.class)
>>>
>>> which already works today as far as I understand.  Doubly so since nobody
> will agree on which JSON parsing library to use.
>>> I'm sure I'm missing something?
>>>
>>>
>>> I don't think you are; as you rightly pointed out now we would have
>>> to add a json parser to the driver, which I'm reluctant to do
>>>
>> If this feature is developed, I think the JSON parser should be pluggable
> and optional if possible.  Then users that do not want it do not need to
> drag in a large dependency.
>> That said, without a more convincing use case or a compelling API that we
> could easily add, I don't see this interface being "worth its weight" as an
> addition.
>>
>>
>       Hi List.
>
>       I always try to think more from the user perspective than from the
> developer one. This is also going to be the case.
>
>       Having JSON support in the PostgreSQL JDBC driver is a *must*.
> jsonb was 9.4's next-big-thing-since-sliced-bread jet there's no support in
> one of the most used PostgreSQL drivers. No blame here (at all), just trying
> to support my point here.
>
>       I don't see the advantage of using SQLJSON, although I wouldn't argue
> against. What I clearly believe is that at the end of the day you should be
> able to easily return a javax.json.JsonObject from a ResultSet. Being a
> JavaEE standard and a JSR, I believe it's the best (and obvious) choice.
>
>       Regarding pluggability, JSR353's SPI mechanism is good, but asking the
> user to provide a further dependency "just for reading JSON" seems again to
> me not good from the user perspective. I'd ideally expect json to be
> supported as-is, as with any other datatype. Having the SPI we could choose
> whatever implementation we want. Is that enlarging the driver's size? So
> what? Users want easy-of-use, not driver size. And there are many mechanisms
> to reduce size for unused classes.
>
>       If any, my 2 cents are: let's add JSON, let's take JSR353 as an API for
> it and let's make it as easy as possible for final users to use it.
>
>       Regards,
>
>       Álvaro
>
>
>
> --
> Álvaro Hernández Tortosa
>
>
> -----------
> 8Kdata
>
>
>

 

(2) The user MUST be always asked to provide a JSON parser, as it is HIM who
wants to use JSON, and so it is HIS choice to select one among those
fulfilling the JSONP API standard. Hence it makes no sense that pgjdbc picks
and / or provides one. Most typically this will end up in the same design
choice than most XML applications ended up with: Relying on Java SE / EE
providing a default product, like it is the case with JAXB (Xerces). The
only thing pgjdbc can really do NOW is to support the JSON SPI *if* there is
a processor on the classpath, and once the postgresql server produces an
improved streaming procotol for JSON / XML we can couple the client's JSON
event handler with the server's protocol events.

Given that Java itself can'd decide on a parser (yes I'm aware of the various JSR's) we might want to think of defining our own parser api which the user can provide an implementation of. This could just be a wrapper around jackson, but would give the user more flexibility ??

-- 
Álvaro Hernández Tortosa


-----------
8Kdata

Dave

-----Original Message-----
From: pgsql-jdbc-owner@postgresql.org
[mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Álvaro Hernández
Tortosa
Sent: Samstag, 27. Juni 2015 18:56
To: pgsql-jdbc@postgresql.org
Subject: Re: [JDBC] SQLJSON


On 26/06/15 19:29, Steven Schlansker wrote:
> On Jun 26, 2015, at 10:23 AM, Dave Cramer <davecramer@gmail.com> wrote:
>
>> On 26 June 2015 at 13:01, Steven Schlansker <stevenschlansker@gmail.com>
wrote:
>>
>> On Jun 26, 2015, at 7:57 AM, Dave Cramer <davecramer@gmail.com> wrote:
>>
>>> I'm looking for comments on how to implement a SQLJSON type in JDBC.
>>>
>>> As there is no getSQLJSON in the resultset interface this could only be
used in getObject.
>>>
>>> Notionally it would model itself after SQLXML.
>>> https://docs.oracle.com/javase/7/docs/api/index.html?java/sql/SQLXML
>>> .html
>> I used JSON extensively in one of my projects, but have never used
SQLXML.  I'm having trouble understanding why the SQLXML interface adds any
value to passing rs.getBinaryStream to your favorite JSON parser.
Especially since you would have to use getObject, I am not seeing how:
>>
>> rs.getObject("field", SQLJSON.class).mapToType(MyType.class)
>>
>> is simpler than:
>> jacksonObjectMapper.readValue(rs.getBinaryStream("field"),
>> MyType.class)
>>
>> which already works today as far as I understand.  Doubly so since nobody
will agree on which JSON parsing library to use.
>>
>> I'm sure I'm missing something?
>>
>>
>> I don't think you are; as you rightly pointed out now we would have
>> to add a json parser to the driver, which I'm reluctant to do
>>
> If this feature is developed, I think the JSON parser should be pluggable
and optional if possible.  Then users that do not want it do not need to
drag in a large dependency.
>
> That said, without a more convincing use case or a compelling API that we
could easily add, I don't see this interface being "worth its weight" as an
addition.
>
>
>

     Hi List.

     I always try to think more from the user perspective than from the
developer one. This is also going to be the case.

     Having JSON support in the PostgreSQL JDBC driver is a *must*.
jsonb was 9.4's next-big-thing-since-sliced-bread jet there's no support in
one of the most used PostgreSQL drivers. No blame here (at all), just trying
to support my point here.

     I don't see the advantage of using SQLJSON, although I wouldn't argue
against. What I clearly believe is that at the end of the day you should be
able to easily return a javax.json.JsonObject from a ResultSet. Being a
JavaEE standard and a JSR, I believe it's the best (and obvious) choice.

     Regarding pluggability, JSR353's SPI mechanism is good, but asking the
user to provide a further dependency "just for reading JSON" seems again to
me not good from the user perspective. I'd ideally expect json to be
supported as-is, as with any other datatype. Having the SPI we could choose
whatever implementation we want. Is that enlarging the driver's size? So
what? Users want easy-of-use, not driver size. And there are many mechanisms
to reduce size for unused classes.

     If any, my 2 cents are: let's add JSON, let's take JSR353 as an API for
it and let's make it as easy as possible for final users to use it.

     Regards,

     Álvaro



--
Álvaro Hernández Tortosa


-----------
8Kdata



--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc



--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc

(1) I have no problem with providing a JSON Object. I just want to point out
that it cannot be the final solution from the point of a USER as it implies
horrible performance drawbacks.

(2) A default parser bundled with pgjdbc simply makes no sense as (a) in a
Java EE environment a JSONP API provider IS found on the classpath already,
and (b) it is not unlikely that the same will be the case for Java SE 9 due
to the implied importance of JSON these days, and (c) an application
programmer dealing with JSON has to have a JSON parser anyways, even if he
does not use JDBC at all.

-----Original Message-----
From: pgsql-jdbc-owner@postgresql.org
[mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Álvaro Hernández
Tortosa
Sent: Samstag, 27. Juni 2015 23:30
To: pgsql-jdbc@postgresql.org
Subject: Re: [JDBC] SQLJSON


On 27/06/15 20:07, Markus KARG wrote:
> Álvaro,
>
> I also think we should look at the topic with the eyes of the user, but I
> have to disagree in two point exactly due to that attitude:
     Hi Markus. Good that we agree on that :)
>
> (1) JSON is definitively not just another datatype. Just like XML, it
> provides the ability to return a complete object graph as the content of
one
> column of one row. Hence, it contains the same amount of data that in
> pre-XML and pre-JSON times, the ResultSet did handle alone. Or in other
> words, from the complexity of the information and from the aspect of end
> user performance, it "feels" like a ResulSet that contains ResultSets that
> contains ResultSets... Handling it just like any other datatype is what
> pgjdbc does currently, and it provides very poor performance due to that,
as
> it simply ships the complete graph - independent of the fact whether the
end
> user will process is completely or just pick a single node of it. Asking
to
> handle JSON as just any other data type is like asking to give up
ResultSet
> in favor of returning an ArrayList<ArrayList<?>>! So looking with the eyes
> of an end user, the answer must be, to NOT handle JSON like any other
> datatype, and NOT return a JsonObject, but instead provide a streaming
API,
> just like SQLXML does. This allows to only transfer and process that few
> nodes the the end user actually likes to have, just like an end user
> typically will never process a complete ResultSet but process it
iteratively
> and most typically filtered.
     When I said "as just another datatype", I mean "as simple as". So
from a user perspective I would expect to have a get...JSON() method
with return value an object similar to (or IMHO exactly) JsonObject. In
other words, something directly usable.

     As Dave Cramer correctly points out in response earlier in this
thread, there is no way in current PostgreSQL support to send a partial
chunk of the JSON, so --unfortunately-- streaming API is not possible.
Not being it possible, why not provide methods to return JsonObject, the
Java EE standard and defined by a JSR?

>
> (2) The user MUST be always asked to provide a JSON parser, as it is HIM
who
> wants to use JSON, and so it is HIS choice to select one among those
> fulfilling the JSONP API standard. Hence it makes no sense that pgjdbc
picks
> and / or provides one. Most typically this will end up in the same design
> choice than most XML applications ended up with: Relying on Java SE / EE
> providing a default product, like it is the case with JAXB (Xerces). The
> only thing pgjdbc can really do NOW is to support the JSON SPI *if* there
is
> a processor on the classpath, and once the postgresql server produces an
> improved streaming procotol for JSON / XML we can couple the client's JSON
> event handler with the server's protocol events.

     Why not instead provide a default parser and let the user choose
another one if so she wants? It makes life easier for 90% of users who
don't care or even know about which parser to choose. Providing a
sensible default (or just a working default) is better than providing none.

     Regards,

     Álvaro


--
Álvaro Hernández Tortosa


-----------
8Kdata



>
> Regards
> Markus
>
> -----Original Message-----
> From: pgsql-jdbc-owner@postgresql.org
> [mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Álvaro Hernández
> Tortosa
> Sent: Samstag, 27. Juni 2015 18:56
> To: pgsql-jdbc@postgresql.org
> Subject: Re: [JDBC] SQLJSON
>
>
> On 26/06/15 19:29, Steven Schlansker wrote:
>> On Jun 26, 2015, at 10:23 AM, Dave Cramer <davecramer@gmail.com> wrote:
>>
>>> On 26 June 2015 at 13:01, Steven Schlansker <stevenschlansker@gmail.com>
> wrote:
>>> On Jun 26, 2015, at 7:57 AM, Dave Cramer <davecramer@gmail.com> wrote:
>>>
>>>> I'm looking for comments on how to implement a SQLJSON type in JDBC.
>>>>
>>>> As there is no getSQLJSON in the resultset interface this could only be
> used in getObject.
>>>> Notionally it would model itself after SQLXML.
>>>> https://docs.oracle.com/javase/7/docs/api/index.html?java/sql/SQLXML
>>>> .html
>>> I used JSON extensively in one of my projects, but have never used
> SQLXML.  I'm having trouble understanding why the SQLXML interface adds
any
> value to passing rs.getBinaryStream to your favorite JSON parser.
> Especially since you would have to use getObject, I am not seeing how:
>>> rs.getObject("field", SQLJSON.class).mapToType(MyType.class)
>>>
>>> is simpler than:
>>> jacksonObjectMapper.readValue(rs.getBinaryStream("field"),
>>> MyType.class)
>>>
>>> which already works today as far as I understand.  Doubly so since
nobody
> will agree on which JSON parsing library to use.
>>> I'm sure I'm missing something?
>>>
>>>
>>> I don't think you are; as you rightly pointed out now we would have
>>> to add a json parser to the driver, which I'm reluctant to do
>>>
>> If this feature is developed, I think the JSON parser should be pluggable
> and optional if possible.  Then users that do not want it do not need to
> drag in a large dependency.
>> That said, without a more convincing use case or a compelling API that we
> could easily add, I don't see this interface being "worth its weight" as
an
> addition.
>>
>>
>       Hi List.
>
>       I always try to think more from the user perspective than from the
> developer one. This is also going to be the case.
>
>       Having JSON support in the PostgreSQL JDBC driver is a *must*.
> jsonb was 9.4's next-big-thing-since-sliced-bread jet there's no support
in
> one of the most used PostgreSQL drivers. No blame here (at all), just
trying
> to support my point here.
>
>       I don't see the advantage of using SQLJSON, although I wouldn't
argue
> against. What I clearly believe is that at the end of the day you should
be
> able to easily return a javax.json.JsonObject from a ResultSet. Being a
> JavaEE standard and a JSR, I believe it's the best (and obvious) choice.
>
>       Regarding pluggability, JSR353's SPI mechanism is good, but asking
the
> user to provide a further dependency "just for reading JSON" seems again
to
> me not good from the user perspective. I'd ideally expect json to be
> supported as-is, as with any other datatype. Having the SPI we could
choose
> whatever implementation we want. Is that enlarging the driver's size? So
> what? Users want easy-of-use, not driver size. And there are many
mechanisms
> to reduce size for unused classes.
>
>       If any, my 2 cents are: let's add JSON, let's take JSR353 as an API
for
> it and let's make it as easy as possible for final users to use it.
>
>       Regards,
>
>       Álvaro
>
>
>
> --
> Álvaro Hernández Tortosa
>
>
> -----------
> 8Kdata
>
>
>



--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc

On 28/06/15 00:10, Markus KARG wrote:
> (1) I have no problem with providing a JSON Object. I just want to point out
> that it cannot be the final solution from the point of a USER as it implies
> horrible performance drawbacks.
     Those performance problems are inevitable as of today. Unless you
change PostgreSQL's protocol (and you will have me by your side on that
one) there's nothing we can do. So with the current status quo, there's
nothing else to do. And so the best we can do (today) is provide that
JsonObject.

>
> (2) A default parser bundled with pgjdbc simply makes no sense as (a) in a
> Java EE environment a JSONP API provider IS found on the classpath already,
     We may provide classloading mechanism to opt-out or different
packaging alternatives (a JDBC driver for EE and one for standalone).
There may be more alternatives. But not providing one is making our
user's life more complicated.

> and (b) it is not unlikely that the same will be the case for Java SE 9 due
> to the implied importance of JSON these days,
     Java9 is far and away. And it will change the way dynamic code
loading happens. I'm all in to refactor the driver for Java9 and take
advantage of modules, and that surely helps here, but we need to look
now at the present.

>   and (c) an application
> programmer dealing with JSON has to have a JSON parser anyways, even if he
> does not use JDBC at all.
     That I don't agree with. You may store data in json format in
PostgreSQL and then convert to your domain objects which may or may not
use JSON. So we cannot assume the user already has a JSON parser.

     The best we can do is that the JDBC driver works by default, rather
than throwing an exception because there is no JSON parser loaded. We
have to make it easy for the users.

     Best,

     Álvaro


--
Álvaro Hernández Tortosa


-----------
8Kdata



>
> -----Original Message-----
> From: pgsql-jdbc-owner@postgresql.org
> [mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Álvaro Hernández
> Tortosa
> Sent: Samstag, 27. Juni 2015 23:30
> To: pgsql-jdbc@postgresql.org
> Subject: Re: [JDBC] SQLJSON
>
>
> On 27/06/15 20:07, Markus KARG wrote:
>> Álvaro,
>>
>> I also think we should look at the topic with the eyes of the user, but I
>> have to disagree in two point exactly due to that attitude:
>       Hi Markus. Good that we agree on that :)
>> (1) JSON is definitively not just another datatype. Just like XML, it
>> provides the ability to return a complete object graph as the content of
> one
>> column of one row. Hence, it contains the same amount of data that in
>> pre-XML and pre-JSON times, the ResultSet did handle alone. Or in other
>> words, from the complexity of the information and from the aspect of end
>> user performance, it "feels" like a ResulSet that contains ResultSets that
>> contains ResultSets... Handling it just like any other datatype is what
>> pgjdbc does currently, and it provides very poor performance due to that,
> as
>> it simply ships the complete graph - independent of the fact whether the
> end
>> user will process is completely or just pick a single node of it. Asking
> to
>> handle JSON as just any other data type is like asking to give up
> ResultSet
>> in favor of returning an ArrayList<ArrayList<?>>! So looking with the eyes
>> of an end user, the answer must be, to NOT handle JSON like any other
>> datatype, and NOT return a JsonObject, but instead provide a streaming
> API,
>> just like SQLXML does. This allows to only transfer and process that few
>> nodes the the end user actually likes to have, just like an end user
>> typically will never process a complete ResultSet but process it
> iteratively
>> and most typically filtered.
>       When I said "as just another datatype", I mean "as simple as". So
> from a user perspective I would expect to have a get...JSON() method
> with return value an object similar to (or IMHO exactly) JsonObject. In
> other words, something directly usable.
>
>       As Dave Cramer correctly points out in response earlier in this
> thread, there is no way in current PostgreSQL support to send a partial
> chunk of the JSON, so --unfortunately-- streaming API is not possible.
> Not being it possible, why not provide methods to return JsonObject, the
> Java EE standard and defined by a JSR?
>
>> (2) The user MUST be always asked to provide a JSON parser, as it is HIM
> who
>> wants to use JSON, and so it is HIS choice to select one among those
>> fulfilling the JSONP API standard. Hence it makes no sense that pgjdbc
> picks
>> and / or provides one. Most typically this will end up in the same design
>> choice than most XML applications ended up with: Relying on Java SE / EE
>> providing a default product, like it is the case with JAXB (Xerces). The
>> only thing pgjdbc can really do NOW is to support the JSON SPI *if* there
> is
>> a processor on the classpath, and once the postgresql server produces an
>> improved streaming procotol for JSON / XML we can couple the client's JSON
>> event handler with the server's protocol events.
>       Why not instead provide a default parser and let the user choose
> another one if so she wants? It makes life easier for 90% of users who
> don't care or even know about which parser to choose. Providing a
> sensible default (or just a working default) is better than providing none.
>
>       Regards,
>
>       Álvaro
>
>

On 28/06/15 00:11, Sehrope Sarkuni wrote:
> 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.

     Hi Sehrope!

     To me, this is the least important question. If based on JSR353's
SPI, it's trivial to swap the default, included one, for another one.
Just picking a sensible default (Jackson, for instance) is probably good
enough.

>
> #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...

     The object type to return has to be IMHO JsonObject:
http://docs.oracle.com/javaee/7/api/javax/json/JsonObject.html

>
> #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.

     Probably adding methods to PG classes would be better than
getObject and force explicit casts. Regarding the methods, if they
simply return JsonObject, you already have a full API there to parse and
extract and process. So anything that returns a JsonObject from a column
(identifier or #) would be enough for me.

>
> 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.

     As far as I know, most users are using JsonObject, so returning
that is a perfect match for pgjdbc. I don't expect however big
performance wins as JSON is sent as a String over the wire...

     Regards,

     Álvaro

--
Álvaro Hernández Tortosa


-----------
8Kdata

On Sat, Jun 27, 2015 at 6:25 PM, Álvaro Hernández Tortosa <aht@8kdata.com> wrote:

    Hi Sehrope!

Hi Álvaro! :D

 

    To me, this is the least important question. If based on JSR353's SPI, it's trivial to swap the default, included one, for another one. Just picking a sensible default (Jackson, for instance) is probably good enough.

I think I've used Jackson almost every time I've had to deal with JSON in Java. The mapping API is pretty cool in that it lets you directly create an target object type. If we got the route of adding methods to PGResultSet then we could have something like: <T> T getJsonAsType(String, Class<T> clazz)

I'm not wedded to Jackson though. Honestly if JS353 is the standard then that's what we should be using. We'd still need to figure out how to handle older JVMs or maybe just selectively disable the feature (JDK8+?).

#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...

    The object type to return has to be IMHO JsonObject: http://docs.oracle.com/javaee/7/api/javax/json/JsonObject.html

 

Not always though. All these are valid JSON too: 

=> SELECT '1'::json AS num, '"test"'::json AS string, '[1,2,3]'::json AS arr, '{"foo":"bar"}'::json AS obj;

 num | string |   arr   |      obj      

-----+--------+---------+---------------

 1   | "test" | [1,2,3] | {"foo":"bar"}

(1 row)

We'll need separate getters/setters for the scalar and array types as well. I agree that most people will just be using the object type though (and maybe the array).

 

#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.

    Probably adding methods to PG classes would be better than getObject and force explicit casts. Regarding the methods, if they simply return JsonObject, you already have a full API there to parse and extract and process. So anything that returns a JsonObject from a column (identifier or #) would be enough for me.

For most cases I think it'd be fine. I think the custom mapping I mentioned above would cover the rest. Anything beyond that would be a full on transformation and would be very application specific.

 

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.

    As far as I know, most users are using JsonObject, so returning that is a perfect match for pgjdbc. I don't expect however big performance wins as JSON is sent as a String over the wire...

The performance gain isn't on the wire, it's from not having to convert bytes => String => JsonObject. It'd be bytes => JsonObject or bytes => CustomObject. Less work and less GC. The bigger the JSON string, the bigger the savings too.

-- 
Álvaro Hernández Tortosa


-----------
8Kdata

I do not see the benefit of that effort, as getting JSON as a LONG VARCHAR
and then parsing it on behalf of the application is pretty simple and
straightforward. My vote would be to not do anything until JDBC 4.3 of JDBC
5.0 provides a standard API for dealing with JSON inside of the driver or at
least PostgreSQL 9.5 or PostgreSQL 10 provides a streaming protocol for JSON
and / or XML.


-----Original Message-----
From: pgsql-jdbc-owner@postgresql.org
[mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Álvaro Hernández
Tortosa
Sent: Sonntag, 28. Juni 2015 00:19
To: pgsql-jdbc@postgresql.org
Subject: Re: [JDBC] SQLJSON



On 28/06/15 00:10, Markus KARG wrote:
> (1) I have no problem with providing a JSON Object. I just want to point
out
> that it cannot be the final solution from the point of a USER as it
implies
> horrible performance drawbacks.
     Those performance problems are inevitable as of today. Unless you
change PostgreSQL's protocol (and you will have me by your side on that
one) there's nothing we can do. So with the current status quo, there's
nothing else to do. And so the best we can do (today) is provide that
JsonObject.

>
> (2) A default parser bundled with pgjdbc simply makes no sense as (a) in a
> Java EE environment a JSONP API provider IS found on the classpath
already,
     We may provide classloading mechanism to opt-out or different
packaging alternatives (a JDBC driver for EE and one for standalone).
There may be more alternatives. But not providing one is making our
user's life more complicated.

> and (b) it is not unlikely that the same will be the case for Java SE 9
due
> to the implied importance of JSON these days,
     Java9 is far and away. And it will change the way dynamic code
loading happens. I'm all in to refactor the driver for Java9 and take
advantage of modules, and that surely helps here, but we need to look
now at the present.

>   and (c) an application
> programmer dealing with JSON has to have a JSON parser anyways, even if he
> does not use JDBC at all.
     That I don't agree with. You may store data in json format in
PostgreSQL and then convert to your domain objects which may or may not
use JSON. So we cannot assume the user already has a JSON parser.

     The best we can do is that the JDBC driver works by default, rather
than throwing an exception because there is no JSON parser loaded. We
have to make it easy for the users.

     Best,

     Álvaro


--
Álvaro Hernández Tortosa


-----------
8Kdata



>
> -----Original Message-----
> From: pgsql-jdbc-owner@postgresql.org
> [mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Álvaro Hernández
> Tortosa
> Sent: Samstag, 27. Juni 2015 23:30
> To: pgsql-jdbc@postgresql.org
> Subject: Re: [JDBC] SQLJSON
>
>
> On 27/06/15 20:07, Markus KARG wrote:
>> Álvaro,
>>
>> I also think we should look at the topic with the eyes of the user, but I
>> have to disagree in two point exactly due to that attitude:
>       Hi Markus. Good that we agree on that :)
>> (1) JSON is definitively not just another datatype. Just like XML, it
>> provides the ability to return a complete object graph as the content of
> one
>> column of one row. Hence, it contains the same amount of data that in
>> pre-XML and pre-JSON times, the ResultSet did handle alone. Or in other
>> words, from the complexity of the information and from the aspect of end
>> user performance, it "feels" like a ResulSet that contains ResultSets
that
>> contains ResultSets... Handling it just like any other datatype is what
>> pgjdbc does currently, and it provides very poor performance due to that,
> as
>> it simply ships the complete graph - independent of the fact whether the
> end
>> user will process is completely or just pick a single node of it. Asking
> to
>> handle JSON as just any other data type is like asking to give up
> ResultSet
>> in favor of returning an ArrayList<ArrayList<?>>! So looking with the
eyes
>> of an end user, the answer must be, to NOT handle JSON like any other
>> datatype, and NOT return a JsonObject, but instead provide a streaming
> API,
>> just like SQLXML does. This allows to only transfer and process that few
>> nodes the the end user actually likes to have, just like an end user
>> typically will never process a complete ResultSet but process it
> iteratively
>> and most typically filtered.
>       When I said "as just another datatype", I mean "as simple as". So
> from a user perspective I would expect to have a get...JSON() method
> with return value an object similar to (or IMHO exactly) JsonObject. In
> other words, something directly usable.
>
>       As Dave Cramer correctly points out in response earlier in this
> thread, there is no way in current PostgreSQL support to send a partial
> chunk of the JSON, so --unfortunately-- streaming API is not possible.
> Not being it possible, why not provide methods to return JsonObject, the
> Java EE standard and defined by a JSR?
>
>> (2) The user MUST be always asked to provide a JSON parser, as it is HIM
> who
>> wants to use JSON, and so it is HIS choice to select one among those
>> fulfilling the JSONP API standard. Hence it makes no sense that pgjdbc
> picks
>> and / or provides one. Most typically this will end up in the same design
>> choice than most XML applications ended up with: Relying on Java SE / EE
>> providing a default product, like it is the case with JAXB (Xerces). The
>> only thing pgjdbc can really do NOW is to support the JSON SPI *if* there
> is
>> a processor on the classpath, and once the postgresql server produces an
>> improved streaming procotol for JSON / XML we can couple the client's
JSON
>> event handler with the server's protocol events.
>       Why not instead provide a default parser and let the user choose
> another one if so she wants? It makes life easier for 90% of users who
> don't care or even know about which parser to choose. Providing a
> sensible default (or just a working default) is better than providing
none.
>
>       Regards,
>
>       Álvaro
>
>



--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc

Hello,

Quick contribution, I'm not answering in-line because there are already too many in-line answers and it's getting unreadable.

• In my own applications, I use Jackson, but it's many up of different ".jar" files and has therefore no definitive form (you can concoct lots of combinations).  It's also quite heavy in terms of footprint, and embedding it makes no sense, because you'd have to keep updating the driver to keep up to date with Jackson. Finally, it doesn't actually implement JSR353 (although it would be possible to create a thin wrapper), out-of-the-box (via a compatibility API) it can read JSR-353 but it basically rebuilds a Jackson representation out of a "standard" representation.  I might choose Jackson, but I wouldn't want to impose it or require that it be bundled with the driver (indeed, that would cause me classloader issues as I often update to the latest version of Jackson).

• You can compile the driver against the JSONP API without embedding either the interfaces or an implementation.  It's therefore an optional feature for those that require it, and it's not rocket science to add the necessary APIs to the classpath.
• I disagree that bundling interfaces + implementation is "making it easy".  For some users, perhaps, but for others, you're going to cause headaches due to creating classloader conflicts (when it's already bundled in their application).

• If as Dave Cramer says, the PG protocol doesn't currently support streaming, it still makes sense to add streaming support that reads from already fully-read resources... because in that way, if the protocol improves in the future, client code using the streaming API will benefit (with no changes to client code) in the future.
  

-- 
Álvaro Hernández Tortosa


-----------
8Kdata

--

Christopher

On 28 June 2015 at 01:53, Álvaro Hernández Tortosa <aht@8kdata.com> wrote:

On 28/06/15 00:55, Sehrope Sarkuni wrote:

On Sat, Jun 27, 2015 at 6:25 PM, Álvaro Hernández Tortosa <aht@8kdata.com> wrote:

    Hi Sehrope!

Hi Álvaro! :D

 

    To me, this is the least important question. If based on JSR353's SPI, it's trivial to swap the default, included one, for another one. Just picking a sensible default (Jackson, for instance) is probably good enough.

I think I've used Jackson almost every time I've had to deal with JSON in Java. The mapping API is pretty cool in that it lets you directly create an target object type. If we got the route of adding methods to PGResultSet then we could have something like: <T> T getJsonAsType(String, Class<T> clazz)

    That might be a nice addition. But I believe that goes beyond driver's responsibility: I think it ends when it returns you the JSON type you queried (JsonObject in my previous email, but I'm correcting now myself: JsonValue)

I'm not wedded to Jackson though. Honestly if JS353 is the standard then that's what we should be using. We'd still need to figure out how to handle older JVMs or maybe just selectively disable the feature (JDK8+?).

    JSR353 is targeted for JavaSE 6 :)

#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...

    The object type to return has to be IMHO JsonObject: http://docs.oracle.com/javaee/7/api/javax/json/JsonObject.html

 

Not always though. All these are valid JSON too: 

=> SELECT '1'::json AS num, '"test"'::json AS string, '[1,2,3]'::json AS arr, '{"foo":"bar"}'::json AS obj;

 num | string |   arr   |      obj      

-----+--------+---------+---------------

 1   | "test" | [1,2,3] | {"foo":"bar"}

(1 row)

We'll need separate getters/setters for the scalar and array types as well. I agree that most people will just be using the object type though (and maybe the array).

    You are right here. Please s/JsonObject/JsonValue/g JsonValue is a container for any of the above including objects and arrays. So it would be enough just with JsonValue getJsonValue(....)

 

#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.

    Probably adding methods to PG classes would be better than getObject and force explicit casts. Regarding the methods, if they simply return JsonObject, you already have a full API there to parse and extract and process. So anything that returns a JsonObject from a column (identifier or #) would be enough for me.

For most cases I think it'd be fine. I think the custom mapping I mentioned above would cover the rest. Anything beyond that would be a full on transformation and would be very application specific.

    Yepp

 

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.

    As far as I know, most users are using JsonObject, so returning that is a perfect match for pgjdbc. I don't expect however big performance wins as JSON is sent as a String over the wire...

The performance gain isn't on the wire, it's from not having to convert bytes => String => JsonObject. It'd be bytes => JsonObject or bytes => CustomObject. Less work and less GC. The bigger the JSON string, the bigger the savings too.

    You are right in that JSR353 allows you to create a parser directly out of an InputStream, so you would avoid converting to String. That's a win. The rest of the conversions are inevitable (having the latter one you pointed out laying in user's realm, beyond driver's responsibility).

    Regards,

    Álvaro

-- 
Álvaro Hernández Tortosa


-----------
8Kdata

On 28/06/15 11:17, Markus KARG wrote:
> I do not see the benefit of that effort, as getting JSON as a LONG VARCHAR
> and then parsing it on behalf of the application is pretty simple and
> straightforward. My vote would be to not do anything until JDBC 4.3 of JDBC
> 5.0 provides a standard API for dealing with JSON inside of the driver or at
> least PostgreSQL 9.5 or PostgreSQL 10 provides a streaming protocol for JSON
> and / or XML.
     Don't do anything?

     And let Java PostgreSQL users down, without a (driver, supported)
means of getting JSON out of their database? So we make the "marketing"
that 9.4 is all about jsonb and the NoSQL replacement yet you cannot do
JSON with Java?

     Really?

     User's don't care about extreme performance. Users care about easy
of use and decent set of features. Adding JSON support, even thought
it's not the most performant one is something we should be doing as
quickly as possible.

     Regards,

     Álvaro


--
Álvaro Hernández Tortosa


-----------
8Kdata



>
>
> -----Original Message-----
> From: pgsql-jdbc-owner@postgresql.org
> [mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Álvaro Hernández
> Tortosa
> Sent: Sonntag, 28. Juni 2015 00:19
> To: pgsql-jdbc@postgresql.org
> Subject: Re: [JDBC] SQLJSON
>
>
>
> On 28/06/15 00:10, Markus KARG wrote:
>> (1) I have no problem with providing a JSON Object. I just want to point
> out
>> that it cannot be the final solution from the point of a USER as it
> implies
>> horrible performance drawbacks.
>       Those performance problems are inevitable as of today. Unless you
> change PostgreSQL's protocol (and you will have me by your side on that
> one) there's nothing we can do. So with the current status quo, there's
> nothing else to do. And so the best we can do (today) is provide that
> JsonObject.
>
>> (2) A default parser bundled with pgjdbc simply makes no sense as (a) in a
>> Java EE environment a JSONP API provider IS found on the classpath
> already,
>       We may provide classloading mechanism to opt-out or different
> packaging alternatives (a JDBC driver for EE and one for standalone).
> There may be more alternatives. But not providing one is making our
> user's life more complicated.
>
>> and (b) it is not unlikely that the same will be the case for Java SE 9
> due
>> to the implied importance of JSON these days,
>       Java9 is far and away. And it will change the way dynamic code
> loading happens. I'm all in to refactor the driver for Java9 and take
> advantage of modules, and that surely helps here, but we need to look
> now at the present.
>
>>    and (c) an application
>> programmer dealing with JSON has to have a JSON parser anyways, even if he
>> does not use JDBC at all.
>       That I don't agree with. You may store data in json format in
> PostgreSQL and then convert to your domain objects which may or may not
> use JSON. So we cannot assume the user already has a JSON parser.
>
>       The best we can do is that the JDBC driver works by default, rather
> than throwing an exception because there is no JSON parser loaded. We
> have to make it easy for the users.
>
>       Best,
>
>       Álvaro
>
>

It is not *us* who let the JSON users down, it is the PostgreSQL protocol
guys who did not add any useful support for JSON. A driver is not a
compensation for missing product features, it is just a thin wrapper around
the base product's features.

I mean, what happens if the application shall work with a different product?
If you rely on non-JDBC-features, you're screwed. So a profession
application using JSON should ALWAYS come with JSR 253 anyways.

-----Original Message-----
From: pgsql-jdbc-owner@postgresql.org
[mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Álvaro Hernández
Tortosa
Sent: Sonntag, 28. Juni 2015 11:44
To: pgsql-jdbc@postgresql.org
Subject: Re: [JDBC] SQLJSON


On 28/06/15 11:17, Markus KARG wrote:
> I do not see the benefit of that effort, as getting JSON as a LONG VARCHAR
> and then parsing it on behalf of the application is pretty simple and
> straightforward. My vote would be to not do anything until JDBC 4.3 of
JDBC
> 5.0 provides a standard API for dealing with JSON inside of the driver or
at
> least PostgreSQL 9.5 or PostgreSQL 10 provides a streaming protocol for
JSON
> and / or XML.
     Don't do anything?

     And let Java PostgreSQL users down, without a (driver, supported)
means of getting JSON out of their database? So we make the "marketing"
that 9.4 is all about jsonb and the NoSQL replacement yet you cannot do
JSON with Java?

     Really?

     User's don't care about extreme performance. Users care about easy
of use and decent set of features. Adding JSON support, even thought
it's not the most performant one is something we should be doing as
quickly as possible.

     Regards,

     Álvaro


--
Álvaro Hernández Tortosa


-----------
8Kdata



>
>
> -----Original Message-----
> From: pgsql-jdbc-owner@postgresql.org
> [mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Álvaro Hernández
> Tortosa
> Sent: Sonntag, 28. Juni 2015 00:19
> To: pgsql-jdbc@postgresql.org
> Subject: Re: [JDBC] SQLJSON
>
>
>
> On 28/06/15 00:10, Markus KARG wrote:
>> (1) I have no problem with providing a JSON Object. I just want to point
> out
>> that it cannot be the final solution from the point of a USER as it
> implies
>> horrible performance drawbacks.
>       Those performance problems are inevitable as of today. Unless you
> change PostgreSQL's protocol (and you will have me by your side on that
> one) there's nothing we can do. So with the current status quo, there's
> nothing else to do. And so the best we can do (today) is provide that
> JsonObject.
>
>> (2) A default parser bundled with pgjdbc simply makes no sense as (a) in
a
>> Java EE environment a JSONP API provider IS found on the classpath
> already,
>       We may provide classloading mechanism to opt-out or different
> packaging alternatives (a JDBC driver for EE and one for standalone).
> There may be more alternatives. But not providing one is making our
> user's life more complicated.
>
>> and (b) it is not unlikely that the same will be the case for Java SE 9
> due
>> to the implied importance of JSON these days,
>       Java9 is far and away. And it will change the way dynamic code
> loading happens. I'm all in to refactor the driver for Java9 and take
> advantage of modules, and that surely helps here, but we need to look
> now at the present.
>
>>    and (c) an application
>> programmer dealing with JSON has to have a JSON parser anyways, even if
he
>> does not use JDBC at all.
>       That I don't agree with. You may store data in json format in
> PostgreSQL and then convert to your domain objects which may or may not
> use JSON. So we cannot assume the user already has a JSON parser.
>
>       The best we can do is that the JDBC driver works by default, rather
> than throwing an exception because there is no JSON parser loaded. We
> have to make it easy for the users.
>
>       Best,
>
>       Álvaro
>
>



--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc

You miss one essential point against bundling: An application author who wants to process JsonObject must have that class on his classpath, even if he does not use JDBC at all. This essential is HIS problem as HE wants to process it. So where does he get that class from other than putting any JSR 253 implementation in his classpath? Again, it is simply not pgjdbc's problem as in that scenario no JDBC is used at all.

-- 
Álvaro Hernández Tortosa


-----------
8Kdata

 

From: Álvaro Hernández Tortosa [mailto:aht@8Kdata.com]
Sent: Sonntag, 28. Juni 2015 11:41
To: Christopher BROWN
Cc: Sehrope Sarkuni; Markus KARG; Dave Cramer; List
Subject: Re: [JDBC] SQLJSON

 

 

On 28/06/15 09:34, Christopher BROWN wrote:

Hello,

 

Quick contribution, I'm not answering in-line because there are already too many in-line answers and it's getting unreadable.

• In my own applications, I use Jackson, but it's many up of different ".jar" files and has therefore no definitive form (you can concoct lots of combinations).  It's also quite heavy in terms of footprint, and embedding it makes no sense, because you'd have to keep updating the driver to keep up to date with Jackson. Finally, it doesn't actually implement JSR353 (although it would be possible to create a thin wrapper), out-of-the-box (via a compatibility API) it can read JSR-353 but it basically rebuilds a Jackson representation out of a "standard" representation.  I might choose Jackson, but I wouldn't want to impose it or require that it be bundled with the driver (indeed, that would cause me classloader issues as I often update to the latest version of Jackson).

    Although I mentioned Jackson as a good candidate for a default implementation, you are right it does not implement JSR353 directly (although wrappers already exist, like https://github.com/pgelinas/jackson-javax-json). But it does not need to be the default choice. I did a quick test and wrapping Jackson with jaackson-javax-json and the set of dependencies to make it work would add 1102Kb to the Jar file. Not much IMHO, although bigger than current driver size. I would not be scared however to see a 2Mb jdbc jar file.

    However, the Reference Implementation (https://jsonp.java.net/) is probably good enough and only adds 64Kb to the Jar file. The JSR353 is just another 32Kb, so in total 96Kb would be added if using the RI rather than Jackson. I don't consider this offensive.

    In summary: why not bundle then the RI? Works out of the box and does not conflict with Jackson. Want to use Jackson? Go for it. The rest of the world would have something running out-of-the-box.

• You can compile the driver against the JSONP API without embedding either the interfaces or an implementation.  It's therefore an optional feature for those that require it, and it's not rocket science to add the necessary APIs to the classpath.
• I disagree that bundling interfaces + implementation is "making it easy".  For some users, perhaps, but for others, you're going to cause headaches due to creating classloader conflicts (when it's already bundled in their application).

    Technically, it's possible. But from a user perspective, not bundling an implementation means:

- Understanding why the code I wrote fails with a "Provider org.glassfish.json.JsonProviderImpl not found".
- Google that. Find that you need to add the dependency and very likely create a SPI file (META-INF/services/javax.json.spi.JsonProvider).
- Blame PostgreSQL for not doing that by default and shipping a half-baked driver that is not finished (won't be my opinion, but might be user's opinion).
- Google again to see what JSR353-compliant implementations are out there.
- Blame PostgreSQL again for not making this choice for you.
- Struggling to find a compatible implementation. Find Jackson but realize requires third-party driver. Question the "quality" of that wrapper and consider whether that would be "supported" with PostgreSQL driver.
- Luckily enough a search points you to a StackOverflow link that suggests to use either this Jackson wrapper or the Reference Implementation (there are not many JSR353 implementations, after all).
- Choose without knowing which JSON parser is best.
- Bundle the dependency, check now it works. Clean your sweat.
- Wonder why not to choose MongoDB next time, it works out of the box.

    Not funny.

    The alternative is: bundle a default (the RI) and let advanced users not happy with the default implementation to make another choice (basically create the META-INF/services/javax.json.spi.JsonProvider and that will override the bundled RI). You have the best of both worlds.

    Pareto (https://en.wikipedia.org/wiki/Pareto_principle): let's make it work for 80% and let that 20% to edit a file and "suffer" a 64Kb size increase in the driver, rather than ask everybody to go through the above process.

• If as Dave Cramer says, the PG protocol doesn't currently support streaming, it still makes sense to add streaming support that reads from already fully-read resources... because in that way, if the protocol improves in the future, client code using the streaming API will benefit (with no changes to client code) in the future.

    JSR353 already has a Streaming API. I don't understand what do we need to do here, it's already done.

    Best regards,

    Álvaro

-- 

Álvaro Hernández Tortosa

-----------

8Kdata

--

Christopher

 

 

On 28 June 2015 at 01:53, Álvaro Hernández Tortosa <aht@8kdata.com> wrote:

 

On 28/06/15 00:55, Sehrope Sarkuni wrote:

On Sat, Jun 27, 2015 at 6:25 PM, Álvaro Hernández Tortosa <aht@8kdata.com> wrote:

    Hi Sehrope!

 

Hi Álvaro! :D

 

    To me, this is the least important question. If based on JSR353's SPI, it's trivial to swap the default, included one, for another one. Just picking a sensible default (Jackson, for instance) is probably good enough.

 

I think I've used Jackson almost every time I've had to deal with JSON in Java. The mapping API is pretty cool in that it lets you directly create an target object type. If we got the route of adding methods to PGResultSet then we could have something like: <T> T getJsonAsType(String, Class<T> clazz)

    That might be a nice addition. But I believe that goes beyond driver's responsibility: I think it ends when it returns you the JSON type you queried (JsonObject in my previous email, but I'm correcting now myself: JsonValue)

 

I'm not wedded to Jackson though. Honestly if JS353 is the standard then that's what we should be using. We'd still need to figure out how to handle older JVMs or maybe just selectively disable the feature (JDK8+?).

    JSR353 is targeted for JavaSE 6 :)

 

#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...

    The object type to return has to be IMHO JsonObject: http://docs.oracle.com/javaee/7/api/javax/json/JsonObject.html

 

Not always though. All these are valid JSON too: 

 

=> SELECT '1'::json AS num, '"test"'::json AS string, '[1,2,3]'::json AS arr, '{"foo":"bar"}'::json AS obj;

 num | string |   arr   |      obj      

-----+--------+---------+---------------

 1   | "test" | [1,2,3] | {"foo":"bar"}

(1 row)

 

We'll need separate getters/setters for the scalar and array types as well. I agree that most people will just be using the object type though (and maybe the array).

    You are right here. Please s/JsonObject/JsonValue/g JsonValue is a container for any of the above including objects and arrays. So it would be enough just with JsonValue getJsonValue(....)

 

#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.

    Probably adding methods to PG classes would be better than getObject and force explicit casts. Regarding the methods, if they simply return JsonObject, you already have a full API there to parse and extract and process. So anything that returns a JsonObject from a column (identifier or #) would be enough for me.

 

For most cases I think it'd be fine. I think the custom mapping I mentioned above would cover the rest. Anything beyond that would be a full on transformation and would be very application specific.

    Yepp

 

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.

    As far as I know, most users are using JsonObject, so returning that is a perfect match for pgjdbc. I don't expect however big performance wins as JSON is sent as a String over the wire...

 

The performance gain isn't on the wire, it's from not having to convert bytes => String => JsonObject. It'd be bytes => JsonObject or bytes => CustomObject. Less work and less GC. The bigger the JSON string, the bigger the savings too.

    You are right in that JSR353 allows you to create a parser directly out of an InputStream, so you would avoid converting to String. That's a win. The rest of the conversions are inevitable (having the latter one you pointed out laying in user's realm, beyond driver's responsibility).

    Regards,

    Álvaro

-- 

Álvaro Hernández Tortosa

-----------

8Kdata

 

 

On 28/06/15 11:51, Markus KARG wrote:
> It is not *us* who let the JSON users down, it is the PostgreSQL protocol
> guys who did not add any useful support for JSON. A driver is not a
> compensation for missing product features, it is just a thin wrapper around
> the base product's features.
     To have proper JSON support at the protocol level (something which
I'd love to have) only translates to more performance, no more
functionality. So is a nice-to-have, not a must-to-have (as is
supporting PostgreSQL's json data types).
>
> I mean, what happens if the application shall work with a different product?
> If you rely on non-JDBC-features, you're screwed. So a profession
> application using JSON should ALWAYS come with JSR 253 anyways.
     We have had to extend JDBC in several ways in the past. We should
do it again, now, in the best possible manner (getObject, PGResultSet,
whatever). And then, if JDBC adds support in the future, retrofit into
it. But not wait until then, because we don't even know if that would
even happen.

     Cheers,

     Álvaro


--
Álvaro Hernández Tortosa


-----------
8Kdata



>
> -----Original Message-----
> From: pgsql-jdbc-owner@postgresql.org
> [mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Álvaro Hernández
> Tortosa
> Sent: Sonntag, 28. Juni 2015 11:44
> To: pgsql-jdbc@postgresql.org
> Subject: Re: [JDBC] SQLJSON
>
>
> On 28/06/15 11:17, Markus KARG wrote:
>> I do not see the benefit of that effort, as getting JSON as a LONG VARCHAR
>> and then parsing it on behalf of the application is pretty simple and
>> straightforward. My vote would be to not do anything until JDBC 4.3 of
> JDBC
>> 5.0 provides a standard API for dealing with JSON inside of the driver or
> at
>> least PostgreSQL 9.5 or PostgreSQL 10 provides a streaming protocol for
> JSON
>> and / or XML.
>       Don't do anything?
>
>       And let Java PostgreSQL users down, without a (driver, supported)
> means of getting JSON out of their database? So we make the "marketing"
> that 9.4 is all about jsonb and the NoSQL replacement yet you cannot do
> JSON with Java?
>
>       Really?
>
>       User's don't care about extreme performance. Users care about easy
> of use and decent set of features. Adding JSON support, even thought
> it's not the most performant one is something we should be doing as
> quickly as possible.
>
>       Regards,
>
>       Álvaro
>
>

If your application does not want to deal with JSON data then don't request it from the database, but use http://www.postgresql.org/docs/9.4/static/functions-json.html in your SELECT statement. Performing a JSON extraction function natively on the server and simply use the existing data types in your application not only reliefs us from dealing with JSON in the driver, but also will work magnitudes faster particularly on congested networks. That does work by default already in the existing driver. What you ask for does not provide any benefit to the user from my view, or I still do not understand you exact scenario.

-- 
Álvaro Hernández Tortosa


-----------
8Kdata

 

From: pgsql-jdbc-owner@postgresql.org [mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Álvaro Hernández Tortosa
Sent: Sonntag, 28. Juni 2015 11:57
To: pgsql-jdbc@postgresql.org
Subject: Re: [JDBC] SQLJSON

 

 

On 28/06/15 11:49, Markus KARG wrote:

You miss one essential point against bundling: An application author who wants to process JsonObject must have that class on his classpath, even if he does not use JDBC at all. This essential is HIS problem as HE wants to process it. So where does he get that class from other than putting any JSR 253 implementation in his classpath? Again, it is simply not pgjdbc's problem as in that scenario no JDBC is used at all.

    I don't agree, it's not a must. I may want to extract JSON data from the database and then manipulate it as non-JSON data. It all depends on your domain Objects.

    I already stated how bad for the user is not to have a driver that works by default. It may be as hard as adding a 64Kb to the driver. I don't understand how is this a problem, vs. the problem it creates for the user.

    Regards,

    Álvaro

-- 

Álvaro Hernández Tortosa

-----------

8Kdata

 

From: Álvaro Hernández Tortosa [mailto:aht@8Kdata.com]
Sent: Sonntag, 28. Juni 2015 11:41
To: Christopher BROWN
Cc: Sehrope Sarkuni; Markus KARG; Dave Cramer; List
Subject: Re: [JDBC] SQLJSON

 

 

On 28/06/15 09:34, Christopher BROWN wrote:

Hello,

 

Quick contribution, I'm not answering in-line because there are already too many in-line answers and it's getting unreadable.

• In my own applications, I use Jackson, but it's many up of different ".jar" files and has therefore no definitive form (you can concoct lots of combinations).  It's also quite heavy in terms of footprint, and embedding it makes no sense, because you'd have to keep updating the driver to keep up to date with Jackson. Finally, it doesn't actually implement JSR353 (although it would be possible to create a thin wrapper), out-of-the-box (via a compatibility API) it can read JSR-353 but it basically rebuilds a Jackson representation out of a "standard" representation.  I might choose Jackson, but I wouldn't want to impose it or require that it be bundled with the driver (indeed, that would cause me classloader issues as I often update to the latest version of Jackson).

    Although I mentioned Jackson as a good candidate for a default implementation, you are right it does not implement JSR353 directly (although wrappers already exist, like https://github.com/pgelinas/jackson-javax-json). But it does not need to be the default choice. I did a quick test and wrapping Jackson with jaackson-javax-json and the set of dependencies to make it work would add 1102Kb to the Jar file. Not much IMHO, although bigger than current driver size. I would not be scared however to see a 2Mb jdbc jar file.

    However, the Reference Implementation (https://jsonp.java.net/) is probably good enough and only adds 64Kb to the Jar file. The JSR353 is just another 32Kb, so in total 96Kb would be added if using the RI rather than Jackson. I don't consider this offensive.

    In summary: why not bundle then the RI? Works out of the box and does not conflict with Jackson. Want to use Jackson? Go for it. The rest of the world would have something running out-of-the-box.

• You can compile the driver against the JSONP API without embedding either the interfaces or an implementation.  It's therefore an optional feature for those that require it, and it's not rocket science to add the necessary APIs to the classpath.
• I disagree that bundling interfaces + implementation is "making it easy".  For some users, perhaps, but for others, you're going to cause headaches due to creating classloader conflicts (when it's already bundled in their application).

    Technically, it's possible. But from a user perspective, not bundling an implementation means:

- Understanding why the code I wrote fails with a "Provider org.glassfish.json.JsonProviderImpl not found".
- Google that. Find that you need to add the dependency and very likely create a SPI file (META-INF/services/javax.json.spi.JsonProvider).
- Blame PostgreSQL for not doing that by default and shipping a half-baked driver that is not finished (won't be my opinion, but might be user's opinion).
- Google again to see what JSR353-compliant implementations are out there.
- Blame PostgreSQL again for not making this choice for you.
- Struggling to find a compatible implementation. Find Jackson but realize requires third-party driver. Question the "quality" of that wrapper and consider whether that would be "supported" with PostgreSQL driver.
- Luckily enough a search points you to a StackOverflow link that suggests to use either this Jackson wrapper or the Reference Implementation (there are not many JSR353 implementations, after all).
- Choose without knowing which JSON parser is best.
- Bundle the dependency, check now it works. Clean your sweat.
- Wonder why not to choose MongoDB next time, it works out of the box.

    Not funny.

    The alternative is: bundle a default (the RI) and let advanced users not happy with the default implementation to make another choice (basically create the META-INF/services/javax.json.spi.JsonProvider and that will override the bundled RI). You have the best of both worlds.

    Pareto (https://en.wikipedia.org/wiki/Pareto_principle): let's make it work for 80% and let that 20% to edit a file and "suffer" a 64Kb size increase in the driver, rather than ask everybody to go through the above process.

• If as Dave Cramer says, the PG protocol doesn't currently support streaming, it still makes sense to add streaming support that reads from already fully-read resources... because in that way, if the protocol improves in the future, client code using the streaming API will benefit (with no changes to client code) in the future.

    JSR353 already has a Streaming API. I don't understand what do we need to do here, it's already done.

    Best regards,

    Álvaro

-- 

Álvaro Hernández Tortosa

-----------

8Kdata

--

Christopher

 

 

On 28 June 2015 at 01:53, Álvaro Hernández Tortosa <aht@8kdata.com> wrote:

 

On 28/06/15 00:55, Sehrope Sarkuni wrote:

On Sat, Jun 27, 2015 at 6:25 PM, Álvaro Hernández Tortosa <aht@8kdata.com> wrote:

    Hi Sehrope!

 

Hi Álvaro! :D

 

    To me, this is the least important question. If based on JSR353's SPI, it's trivial to swap the default, included one, for another one. Just picking a sensible default (Jackson, for instance) is probably good enough.

 

I think I've used Jackson almost every time I've had to deal with JSON in Java. The mapping API is pretty cool in that it lets you directly create an target object type. If we got the route of adding methods to PGResultSet then we could have something like: <T> T getJsonAsType(String, Class<T> clazz)

    That might be a nice addition. But I believe that goes beyond driver's responsibility: I think it ends when it returns you the JSON type you queried (JsonObject in my previous email, but I'm correcting now myself: JsonValue)

 

I'm not wedded to Jackson though. Honestly if JS353 is the standard then that's what we should be using. We'd still need to figure out how to handle older JVMs or maybe just selectively disable the feature (JDK8+?).

    JSR353 is targeted for JavaSE 6 :)

 

#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...

    The object type to return has to be IMHO JsonObject: http://docs.oracle.com/javaee/7/api/javax/json/JsonObject.html

 

Not always though. All these are valid JSON too: 

 

=> SELECT '1'::json AS num, '"test"'::json AS string, '[1,2,3]'::json AS arr, '{"foo":"bar"}'::json AS obj;

 num | string |   arr   |      obj      

-----+--------+---------+---------------

 1   | "test" | [1,2,3] | {"foo":"bar"}

(1 row)

 

We'll need separate getters/setters for the scalar and array types as well. I agree that most people will just be using the object type though (and maybe the array).

    You are right here. Please s/JsonObject/JsonValue/g JsonValue is a container for any of the above including objects and arrays. So it would be enough just with JsonValue getJsonValue(....)

 

#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.

    Probably adding methods to PG classes would be better than getObject and force explicit casts. Regarding the methods, if they simply return JsonObject, you already have a full API there to parse and extract and process. So anything that returns a JsonObject from a column (identifier or #) would be enough for me.

 

For most cases I think it'd be fine. I think the custom mapping I mentioned above would cover the rest. Anything beyond that would be a full on transformation and would be very application specific.

    Yepp

 

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.

    As far as I know, most users are using JsonObject, so returning that is a perfect match for pgjdbc. I don't expect however big performance wins as JSON is sent as a String over the wire...

 

The performance gain isn't on the wire, it's from not having to convert bytes => String => JsonObject. It'd be bytes => JsonObject or bytes => CustomObject. Less work and less GC. The bigger the JSON string, the bigger the savings too.

    You are right in that JSR353 allows you to create a parser directly out of an InputStream, so you would avoid converting to String. That's a win. The rest of the conversions are inevitable (having the latter one you pointed out laying in user's realm, beyond driver's responsibility).

    Regards,

    Álvaro

-- 

Álvaro Hernández Tortosa

-----------

8Kdata

 

 

 

So I think we should support JSR 353 at the very least Whether we extend the result set or not we can at a minimum return a JsonValue  from getObject

I agree with Alvaro, 99% of the users would just like a JsonValue returned. It would be nice if we could design this so more advanced users could plug in their parser of choice.

-- 
Álvaro Hernández Tortosa


-----------
8Kdata

Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca

On 28 June 2015 at 06:00, Álvaro Hernández Tortosa <aht@8kdata.com> wrote:

On 28/06/15 11:51, Markus KARG wrote:

It is not *us* who let the JSON users down, it is the PostgreSQL protocol
guys who did not add any useful support for JSON. A driver is not a
compensation for missing product features, it is just a thin wrapper around
the base product's features.

    To have proper JSON support at the protocol level (something which I'd love to have) only translates to more performance, no more functionality. So is a nice-to-have, not a must-to-have (as is supporting PostgreSQL's json data types).

I mean, what happens if the application shall work with a different product?
If you rely on non-JDBC-features, you're screwed. So a profession
application using JSON should ALWAYS come with JSR 253 anyways.

    We have had to extend JDBC in several ways in the past. We should do it again, now, in the best possible manner (getObject, PGResultSet, whatever). And then, if JDBC adds support in the future, retrofit into it. But not wait until then, because we don't even know if that would even happen.

    Cheers,

    Álvaro


--
Álvaro Hernández Tortosa


-----------
8Kdata

-----Original Message-----
From: pgsql-jdbc-owner@postgresql.org
[mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Álvaro Hernández
Tortosa
Sent: Sonntag, 28. Juni 2015 11:44
To: pgsql-jdbc@postgresql.org
Subject: Re: [JDBC] SQLJSON


On 28/06/15 11:17, Markus KARG wrote:

I do not see the benefit of that effort, as getting JSON as a LONG VARCHAR
and then parsing it on behalf of the application is pretty simple and
straightforward. My vote would be to not do anything until JDBC 4.3 of

JDBC

5.0 provides a standard API for dealing with JSON inside of the driver or

at

least PostgreSQL 9.5 or PostgreSQL 10 provides a streaming protocol for

JSON

and / or XML.

      Don't do anything?

      And let Java PostgreSQL users down, without a (driver, supported)
means of getting JSON out of their database? So we make the "marketing"
that 9.4 is all about jsonb and the NoSQL replacement yet you cannot do
JSON with Java?

      Really?

      User's don't care about extreme performance. Users care about easy
of use and decent set of features. Adding JSON support, even thought
it's not the most performant one is something we should be doing as
quickly as possible.

      Regards,

      Álvaro

--

Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc

Embedding the API will cause classloader conflicts for those who already have the API in their classpath.  Same goes for embedding the reference implementation.

The service loader API can be problematic for OSGi users, as it isn't very helpful for hot reloading of classes.  The PostgreSQL JDBC driver currently works well in such environments, it would be unfortunate to lose that advantage through an attempt to help out another category of users.

This shouldn't be the only way of selecting an implementation, and bundling a given version of the API + RI shouldn't be the only build option. I'm certainly not against making this Just Work, but here there's a possibility that all this extra stuff could actually cause things to break .

Embedding the API will cause classloader conflicts for those who already have the API in their classpath.  Same goes for embedding the reference implementation.

The service loader API can be problematic for OSGi users, as it isn't very helpful for hot reloading of classes.  The PostgreSQL JDBC driver currently works well in such environments, it would be unfortunate to lose that advantage through an attempt to help out another category of users.

This shouldn't be the only way of selecting an implementation, and bundling a given version of the API + RI shouldn't be the only build option. I'm certainly not against making this Just Work, but here there's a possibility that all this extra stuff could actually cause things to break .

-- 
Álvaro Hernández Tortosa


-----------
8Kdata

Le 28 juin 2015 22:11, "Álvaro Hernández Tortosa" <aht@8kdata.com> a écrit :

On 28/06/15 15:56, Dave Cramer wrote:

So I think we should support JSR 353 at the very least Whether we extend the result set or not we can at a minimum return a JsonValue  from getObject

I agree with Alvaro, 99% of the users would just like a JsonValue returned. It would be nice if we could design this so more advanced users could plug in their parser of choice.

    Yes, at least to have a JsonValue would be a really nice addition.

    To plug your parser, JSR 353 follows Java's standard SPI and is as simple as write the fqcn of the driver implementation to META-INF/services/javax.json.spi.JsonProvider. So rather than asking everybody to do that, it would be even nicer to embed the JSR353 Reference Implementation (a mere 64Kb) and let advanced users override the parser by writing the services file. I know that adding external dependencies is not everybody's favorite idea here, but I really believe it definitely help (most) users and would allow us to ship a driver that would work out-of-the-box with JSON.

    Regards,

    Álvaro

-- 
Álvaro Hernández Tortosa


-----------
8Kdata

Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca

On 28 June 2015 at 06:00, Álvaro Hernández Tortosa <aht@8kdata.com> wrote:

On 28/06/15 11:51, Markus KARG wrote:

It is not *us* who let the JSON users down, it is the PostgreSQL protocol
guys who did not add any useful support for JSON. A driver is not a
compensation for missing product features, it is just a thin wrapper around
the base product's features.

    To have proper JSON support at the protocol level (something which I'd love to have) only translates to more performance, no more functionality. So is a nice-to-have, not a must-to-have (as is supporting PostgreSQL's json data types).

I mean, what happens if the application shall work with a different product?
If you rely on non-JDBC-features, you're screwed. So a profession
application using JSON should ALWAYS come with JSR 253 anyways.

    We have had to extend JDBC in several ways in the past. We should do it again, now, in the best possible manner (getObject, PGResultSet, whatever). And then, if JDBC adds support in the future, retrofit into it. But not wait until then, because we don't even know if that would even happen.

    Cheers,

    Álvaro


--
Álvaro Hernández Tortosa


-----------
8Kdata

-----Original Message-----
From: pgsql-jdbc-owner@postgresql.org
[mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Álvaro Hernández
Tortosa
Sent: Sonntag, 28. Juni 2015 11:44
To: pgsql-jdbc@postgresql.org
Subject: Re: [JDBC] SQLJSON


On 28/06/15 11:17, Markus KARG wrote:

I do not see the benefit of that effort, as getting JSON as a LONG VARCHAR
and then parsing it on behalf of the application is pretty simple and
straightforward. My vote would be to not do anything until JDBC 4.3 of

JDBC

5.0 provides a standard API for dealing with JSON inside of the driver or

at

least PostgreSQL 9.5 or PostgreSQL 10 provides a streaming protocol for

JSON

and / or XML.

      Don't do anything?

      And let Java PostgreSQL users down, without a (driver, supported)
means of getting JSON out of their database? So we make the "marketing"
that 9.4 is all about jsonb and the NoSQL replacement yet you cannot do
JSON with Java?

      Really?

      User's don't care about extreme performance. Users care about easy
of use and decent set of features. Adding JSON support, even thought
it's not the most performant one is something we should be doing as
quickly as possible.

      Regards,

      Álvaro

--

Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc

As we've seen from a recent discussion on this list, there's apparently a lot of users still downloading old versions of the driver.  If you embed a version of the API, then (unless a user derives their own version of the driver), you prevent the user from independently changing the JSONP version.  The RI is even more subject to this, as it may require updates to fix bugs.

Anyone compiling client code requiring use of the JSONP API will need to have that API in their compiler classpath.  Many of the "make it just work" users are likely to compile their JDBC code against the JDBC classes and interfaces in the JDK classpath without requiring to add the PostgreSQL JDBC driver to the compiler classpath.  They may or may not already have a version of the JSONP API in their classpath (probably will if compiling for a Java EE application server, not so likely otherwise).  They will of course need the PostgreSQL JDBC driver in their classpath for compiling if they use any PGxxx classes.

The general classloading strategy mandated is that parent classloaders should be used to load classes before child classloaders.  Many application servers however will lookup classes from embedded libraries (WEB-INF/lib) first, but this varies from one server to another, and often based on configuration options.  OSGi (and possibly Java9 modules) allow classloading from classloader graphs.  In any case, suppose that your application container defines a more recent version of the API than the driver: you'll need to hack the driver to be able to use the newer APIs.  Suppose that the driver embeds a more recent version of the API: you'll get classloading errors because you refer to methods or fields that don't exist in the application server's version.  And of course, if some other helpful library also embeds the JSONP API (or you have an alternative to the RI in your classpath, expecting a different version of the API), say a web services library or a persistence in WEB-INF/lib, you'll have other types of errors.

One of my colleagues is already using JSONB with PostgreSQL, by reading and writing stringified JSON using Jackson.  I don't know how a meaningful (accurate) study could be conducted for more insight into what prospective users would select, especially as that would in turn depend upon what is decided here.  Self-fulfilling prophecies, etc.

It would make sense to make few assumptions about what getObject() should return, because in some cases you'd want an Object Model view, and in other cases access to a parser or generator (because of improved performance).  It would make sense for me to be able to cast to a PGxxx object, in turn providing access to either a fully-constructed JSON object tree, or as a push/pull parser.  In any case, the PostgreSQL JSONB support isn't SQL-neutral, as it defines custom operators and lots of specific stuff anyway (the PGxxx object via getObject() to make it accessible via JDBC pools that wrap statements and resultsets).  You currently can't write database-neutral JSON/SQL, so until JSON is standardised at SQL level, and at JDBC level, I can't see anything wrong with a specific driver dependency.

Performance is better through streaming APIs because you create less objects, using less memory, with less GC pressure.  You can also skip through lots of data thereby avoiding lots of heap allocations.

The solution that would make most sense, would be to provide a version that embeds the API + RI, and a version that doesn't (the latter version would be compiled in the same way, it would just require that the API be made available by the host application).

-- 
Álvaro Hernández Tortosa


-----------
8Kdata

--

Christopher

On 28 June 2015 at 22:40, Álvaro Hernández Tortosa <aht@8kdata.com> wrote:

On 28/06/15 22:32, Christopher BROWN wrote:

Embedding the API will cause classloader conflicts for those who already have the API in their classpath.  Same goes for embedding the reference implementation.

    Hi Christopher. May you elaborate on which conflicts may it create? The API is quite stable, so I don't see a chance for conflicts. The RI may be a more likely source of conflicts, yet I see them quite unlikely. And if so, if it would be so important to avoid them, different packages may be provided.

The service loader API can be problematic for OSGi users, as it isn't very helpful for hot reloading of classes.  The PostgreSQL JDBC driver currently works well in such environments, it would be unfortunate to lose that advantage through an attempt to help out another category of users.

    I'm no OSGI expert, so I may fail to understand this properly, but how problematic this is? Is hot reloading the JDBC driver a big issue?

This shouldn't be the only way of selecting an implementation, and bundling a given version of the API + RI shouldn't be the only build option. I'm certainly not against making this Just Work, but here there's a possibility that all this extra stuff could actually cause things to break .

    So if there are problems even bundling the API, what solution do you suggest to provide end users with a facility to read their new, fancy and much hyped jsonb columns?

    If there is none, I'd definitely study what the percentage of people would have problems with the proposed approach (API + RI?).

    Regards,

    Álvaro

-- 
Álvaro Hernández Tortosa


-----------
8Kdata

Le 28 juin 2015 22:11, "Álvaro Hernández Tortosa" <aht@8kdata.com> a écrit :

On 28/06/15 15:56, Dave Cramer wrote:

So I think we should support JSR 353 at the very least Whether we extend the result set or not we can at a minimum return a JsonValue  from getObject

I agree with Alvaro, 99% of the users would just like a JsonValue returned. It would be nice if we could design this so more advanced users could plug in their parser of choice.

    Yes, at least to have a JsonValue would be a really nice addition.

    To plug your parser, JSR 353 follows Java's standard SPI and is as simple as write the fqcn of the driver implementation to META-INF/services/javax.json.spi.JsonProvider. So rather than asking everybody to do that, it would be even nicer to embed the JSR353 Reference Implementation (a mere 64Kb) and let advanced users override the parser by writing the services file. I know that adding external dependencies is not everybody's favorite idea here, but I really believe it definitely help (most) users and would allow us to ship a driver that would work out-of-the-box with JSON.

    Regards,

    Álvaro

-- 
Álvaro Hernández Tortosa


-----------
8Kdata

Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca

On 28 June 2015 at 06:00, Álvaro Hernández Tortosa <aht@8kdata.com> wrote:

On 28/06/15 11:51, Markus KARG wrote:

It is not *us* who let the JSON users down, it is the PostgreSQL protocol
guys who did not add any useful support for JSON. A driver is not a
compensation for missing product features, it is just a thin wrapper around
the base product's features.

    To have proper JSON support at the protocol level (something which I'd love to have) only translates to more performance, no more functionality. So is a nice-to-have, not a must-to-have (as is supporting PostgreSQL's json data types).

I mean, what happens if the application shall work with a different product?
If you rely on non-JDBC-features, you're screwed. So a profession
application using JSON should ALWAYS come with JSR 253 anyways.

    We have had to extend JDBC in several ways in the past. We should do it again, now, in the best possible manner (getObject, PGResultSet, whatever). And then, if JDBC adds support in the future, retrofit into it. But not wait until then, because we don't even know if that would even happen.

    Cheers,

    Álvaro


--
Álvaro Hernández Tortosa


-----------
8Kdata

-----Original Message-----
From: pgsql-jdbc-owner@postgresql.org
[mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Álvaro Hernández
Tortosa
Sent: Sonntag, 28. Juni 2015 11:44
To: pgsql-jdbc@postgresql.org
Subject: Re: [JDBC] SQLJSON


On 28/06/15 11:17, Markus KARG wrote:

I do not see the benefit of that effort, as getting JSON as a LONG VARCHAR
and then parsing it on behalf of the application is pretty simple and
straightforward. My vote would be to not do anything until JDBC 4.3 of

JDBC

5.0 provides a standard API for dealing with JSON inside of the driver or

at

least PostgreSQL 9.5 or PostgreSQL 10 provides a streaming protocol for

JSON

and / or XML.

      Don't do anything?

      And let Java PostgreSQL users down, without a (driver, supported)
means of getting JSON out of their database? So we make the "marketing"
that 9.4 is all about jsonb and the NoSQL replacement yet you cannot do
JSON with Java?

      Really?

      User's don't care about extreme performance. Users care about easy
of use and decent set of features. Adding JSON support, even thought
it's not the most performant one is something we should be doing as
quickly as possible.

      Regards,

      Álvaro

--

Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc

On Sun, Jun 28, 2015 at 4:35 PM, Dave Cramer <pg@fastcrypt.com> wrote:

On 28 June 2015 at 16:32, Christopher BROWN <brown@reflexe.fr> wrote:

Embedding the API will cause classloader conflicts for those who already have the API in their classpath.  Same goes for embedding the reference implementation.

The API, or the implementation ???  

Both. Embedding dependencies is mildly convenient for someone getting started with something but royally inconvenient for someone with a complicated environment. Dependency management systems like Maven handle this pretty well but even they can't deal with situations where classes are embedded in a dependency (ex: javax.json.* embedded within the PG JDBC driver).

The service loader API can be problematic for OSGi users, as it isn't very helpful for hot reloading of classes.  The PostgreSQL JDBC driver currently works well in such environments, it would be unfortunate to lose that advantage through an attempt to help out another category of users.

This shouldn't be the only way of selecting an implementation, and bundling a given version of the API + RI shouldn't be the only build option. I'm certainly not against making this Just Work, but here there's a possibility that all this extra stuff could actually cause things to break .

so how do we make it "Just Work" ?

 

I don't think there's a way to do this without breaking backward compatibility.

You definitely can't rely on specific dependencies on the classpath that legacy users will not have. Bundling the dependencies doesn't work either as it'd clobber existing ones on the classpath. Making things dynamic/pluggable may be option for internal implementations but it breaks anything with the required dependencies in the method signature. That means we could have code that dynamically picks a JSON parser and uses it internally, but we can't have a getJsonValue() method on a public PGResultSet interface as the class wouldn't even load properly on an older JVM[1].

In a lot of ways this is similar to the other thread we had about dropping support for older JVMs. To natively support new features (like a native getJsonValue()) we'd need to specify a min JDK version.

At this point I think we have to bite bullet and either drop support for older versions (not likely) or offer multiple versions of the driver. The latter could make modern assumptions about the classpath/environment to support new features natively. That could include JsonValue and the new Java 8 date/time types.

-- 
Álvaro Hernández Tortosa


-----------
8Kdata

Unfortunately, yes, it is weird to ask, as what you do actually is working already once you accept the technical reality that an application either has to use JSON SQL functions OR provide an implementation of the JSONP API to be able to process JsonObject.

I actually cannot see what is so specific to PostgreSQL that pgjdbc has to support a use case that no other driver supports.

 

The argument against it is that it provides classpath clashes once pgjdbc is used in a Java EE environment, and that our solution might be incompatible with JDBC 5, and that our solution imposes work and complexity on the pgjdbc contributors just for the sake to make you happy, without providing you any measurable benefit.

-- 
Álvaro Hernández Tortosa


-----------
8Kdata

 

-Markus

 

From: pgsql-jdbc-owner@postgresql.org [mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Álvaro Hernández Tortosa
Sent: Sonntag, 28. Juni 2015 21:06
To: pgsql-jdbc@postgresql.org
Subject: Re: [JDBC] SQLJSON

 

 

On 28/06/15 17:09, Markus KARG wrote:

If your application does not want to deal with JSON data then don't request it from the database, but use http://www.postgresql.org/docs/9.4/static/functions-json.html in your SELECT statement. Performing a JSON extraction function natively on the server and simply use the existing data types in your application not only reliefs us from dealing with JSON in the driver, but also will work magnitudes faster particularly on congested networks. That does work by default already in the existing driver. What you ask for does not provide any benefit to the user from my view, or I still do not understand you exact scenario.

    Markus, here you are trying to tell users what to do. I prefer to give them freedom, works best.

    All I'm saying is:

- Give users an API to get a javax.json.JsonValue out of a PostgreSQL column which is of type json, jsonb or the result of an expression which evaluates to any of those.

- Embed a JSON parser in the driver (like the RI implementation, which adds only 64Kb) so that users don't need to load any other code, unless they want to override the default JSON parser.

    From there, I don't care whether the user uses JSON functions at the server or the JSON API. What I want is that it works by default and that you can query JSON. Is this that weird to ask?

    I'm sorry, I fail to understand your solution to the problem. Would you please mind elaborating on the drawbacks of my proposal? I stated in previous emails the drawbacks I see on not doing it, but I still fail to see an argument against this.

    Thanks,

    Álvaro

-- 

Álvaro Hernández Tortosa

-----------

8Kdata

 

From: pgsql-jdbc-owner@postgresql.org [mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Álvaro Hernández Tortosa
Sent: Sonntag, 28. Juni 2015 11:57
To: pgsql-jdbc@postgresql.org
Subject: Re: [JDBC] SQLJSON

 

 

On 28/06/15 11:49, Markus KARG wrote:

You miss one essential point against bundling: An application author who wants to process JsonObject must have that class on his classpath, even if he does not use JDBC at all. This essential is HIS problem as HE wants to process it. So where does he get that class from other than putting any JSR 253 implementation in his classpath? Again, it is simply not pgjdbc's problem as in that scenario no JDBC is used at all.

    I don't agree, it's not a must. I may want to extract JSON data from the database and then manipulate it as non-JSON data. It all depends on your domain Objects.

    I already stated how bad for the user is not to have a driver that works by default. It may be as hard as adding a 64Kb to the driver. I don't understand how is this a problem, vs. the problem it creates for the user.

    Regards,

    Álvaro

-- 

Álvaro Hernández Tortosa

-----------

8Kdata

 

From: Álvaro Hernández Tortosa [mailto:aht@8Kdata.com]
Sent: Sonntag, 28. Juni 2015 11:41
To: Christopher BROWN
Cc: Sehrope Sarkuni; Markus KARG; Dave Cramer; List
Subject: Re: [JDBC] SQLJSON

 

 

On 28/06/15 09:34, Christopher BROWN wrote:

Hello,

 

Quick contribution, I'm not answering in-line because there are already too many in-line answers and it's getting unreadable.

• In my own applications, I use Jackson, but it's many up of different ".jar" files and has therefore no definitive form (you can concoct lots of combinations).  It's also quite heavy in terms of footprint, and embedding it makes no sense, because you'd have to keep updating the driver to keep up to date with Jackson. Finally, it doesn't actually implement JSR353 (although it would be possible to create a thin wrapper), out-of-the-box (via a compatibility API) it can read JSR-353 but it basically rebuilds a Jackson representation out of a "standard" representation.  I might choose Jackson, but I wouldn't want to impose it or require that it be bundled with the driver (indeed, that would cause me classloader issues as I often update to the latest version of Jackson).

    Although I mentioned Jackson as a good candidate for a default implementation, you are right it does not implement JSR353 directly (although wrappers already exist, like https://github.com/pgelinas/jackson-javax-json). But it does not need to be the default choice. I did a quick test and wrapping Jackson with jaackson-javax-json and the set of dependencies to make it work would add 1102Kb to the Jar file. Not much IMHO, although bigger than current driver size. I would not be scared however to see a 2Mb jdbc jar file.

    However, the Reference Implementation (https://jsonp.java.net/) is probably good enough and only adds 64Kb to the Jar file. The JSR353 is just another 32Kb, so in total 96Kb would be added if using the RI rather than Jackson. I don't consider this offensive.

    In summary: why not bundle then the RI? Works out of the box and does not conflict with Jackson. Want to use Jackson? Go for it. The rest of the world would have something running out-of-the-box.

• You can compile the driver against the JSONP API without embedding either the interfaces or an implementation.  It's therefore an optional feature for those that require it, and it's not rocket science to add the necessary APIs to the classpath.
• I disagree that bundling interfaces + implementation is "making it easy".  For some users, perhaps, but for others, you're going to cause headaches due to creating classloader conflicts (when it's already bundled in their application).

    Technically, it's possible. But from a user perspective, not bundling an implementation means:

- Understanding why the code I wrote fails with a "Provider org.glassfish.json.JsonProviderImpl not found".
- Google that. Find that you need to add the dependency and very likely create a SPI file (META-INF/services/javax.json.spi.JsonProvider).
- Blame PostgreSQL for not doing that by default and shipping a half-baked driver that is not finished (won't be my opinion, but might be user's opinion).
- Google again to see what JSR353-compliant implementations are out there.
- Blame PostgreSQL again for not making this choice for you.
- Struggling to find a compatible implementation. Find Jackson but realize requires third-party driver. Question the "quality" of that wrapper and consider whether that would be "supported" with PostgreSQL driver.
- Luckily enough a search points you to a StackOverflow link that suggests to use either this Jackson wrapper or the Reference Implementation (there are not many JSR353 implementations, after all).
- Choose without knowing which JSON parser is best.
- Bundle the dependency, check now it works. Clean your sweat.
- Wonder why not to choose MongoDB next time, it works out of the box.

    Not funny.

    The alternative is: bundle a default (the RI) and let advanced users not happy with the default implementation to make another choice (basically create the META-INF/services/javax.json.spi.JsonProvider and that will override the bundled RI). You have the best of both worlds.

    Pareto (https://en.wikipedia.org/wiki/Pareto_principle): let's make it work for 80% and let that 20% to edit a file and "suffer" a 64Kb size increase in the driver, rather than ask everybody to go through the above process.

• If as Dave Cramer says, the PG protocol doesn't currently support streaming, it still makes sense to add streaming support that reads from already fully-read resources... because in that way, if the protocol improves in the future, client code using the streaming API will benefit (with no changes to client code) in the future.

    JSR353 already has a Streaming API. I don't understand what do we need to do here, it's already done.

    Best regards,

    Álvaro

-- 

Álvaro Hernández Tortosa

-----------

8Kdata

--

Christopher

 

 

On 28 June 2015 at 01:53, Álvaro Hernández Tortosa <aht@8kdata.com> wrote:

 

On 28/06/15 00:55, Sehrope Sarkuni wrote:

On Sat, Jun 27, 2015 at 6:25 PM, Álvaro Hernández Tortosa <aht@8kdata.com> wrote:

    Hi Sehrope!

 

Hi Álvaro! :D

 

    To me, this is the least important question. If based on JSR353's SPI, it's trivial to swap the default, included one, for another one. Just picking a sensible default (Jackson, for instance) is probably good enough.

 

I think I've used Jackson almost every time I've had to deal with JSON in Java. The mapping API is pretty cool in that it lets you directly create an target object type. If we got the route of adding methods to PGResultSet then we could have something like: <T> T getJsonAsType(String, Class<T> clazz)

    That might be a nice addition. But I believe that goes beyond driver's responsibility: I think it ends when it returns you the JSON type you queried (JsonObject in my previous email, but I'm correcting now myself: JsonValue)

 

I'm not wedded to Jackson though. Honestly if JS353 is the standard then that's what we should be using. We'd still need to figure out how to handle older JVMs or maybe just selectively disable the feature (JDK8+?).

    JSR353 is targeted for JavaSE 6 :)

 

#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...

    The object type to return has to be IMHO JsonObject: http://docs.oracle.com/javaee/7/api/javax/json/JsonObject.html

 

Not always though. All these are valid JSON too: 

 

=> SELECT '1'::json AS num, '"test"'::json AS string, '[1,2,3]'::json AS arr, '{"foo":"bar"}'::json AS obj;

 num | string |   arr   |      obj      

-----+--------+---------+---------------

 1   | "test" | [1,2,3] | {"foo":"bar"}

(1 row)

 

We'll need separate getters/setters for the scalar and array types as well. I agree that most people will just be using the object type though (and maybe the array).

    You are right here. Please s/JsonObject/JsonValue/g JsonValue is a container for any of the above including objects and arrays. So it would be enough just with JsonValue getJsonValue(....)

 

#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.

    Probably adding methods to PG classes would be better than getObject and force explicit casts. Regarding the methods, if they simply return JsonObject, you already have a full API there to parse and extract and process. So anything that returns a JsonObject from a column (identifier or #) would be enough for me.

 

For most cases I think it'd be fine. I think the custom mapping I mentioned above would cover the rest. Anything beyond that would be a full on transformation and would be very application specific.

    Yepp

 

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.

    As far as I know, most users are using JsonObject, so returning that is a perfect match for pgjdbc. I don't expect however big performance wins as JSON is sent as a String over the wire...

 

The performance gain isn't on the wire, it's from not having to convert bytes => String => JsonObject. It'd be bytes => JsonObject or bytes => CustomObject. Less work and less GC. The bigger the JSON string, the bigger the savings too.

    You are right in that JSR353 allows you to create a parser directly out of an InputStream, so you would avoid converting to String. That's a win. The rest of the conversions are inevitable (having the latter one you pointed out laying in user's realm, beyond driver's responsibility).

    Regards,

    Álvaro

-- 

Álvaro Hernández Tortosa

-----------

8Kdata

 

 

 

 

So assuming the reference implementation allows us to do this, we can change the namespace of the RI and include it in our jar. Apparently Hibernate used this approach

-- 
Álvaro Hernández Tortosa


-----------
8Kdata

Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca

On 29 June 2015 at 06:28, Sehrope Sarkuni <sehrope@jackdb.com> wrote:

On Mon, Jun 29, 2015 at 2:30 AM, Álvaro Hernández Tortosa <aht@8kdata.com> wrote:

On 28/06/15 22:54, Sehrope Sarkuni wrote:

Both. Embedding dependencies is mildly convenient for someone getting started with something but royally inconvenient for someone with a complicated environment. Dependency management systems like Maven handle this pretty well but even they can't deal with situations where classes are embedded in a dependency (ex: javax.json.* embedded within the PG JDBC driver).

    Sehrope, what do you mean by Maven can't deal with embedded classes in the dependencies? It very well can :) The only problem would be, as stated in a previous email, if different versions of the same class would collide on transitive dependencies. But at least for the JSR353 IMHO that's extremely unlikely --and not worth to engineer around it.

I was referring to dependencies being in the pgjdbc jar itself, not specified as an dependency in the pom. For example if we had a single version of the driver that was self-contained with the class files for the javax.json classes in their as well. Maven can't work around that as the classes would be within the pgjdbc depdendency.

Honestly it's not really a Maven issue at all. There's no real way to work around classpath conflicts like that without unzipping/removing/rezipping the jar. 

Much better to either have them be externally specified (ex: Maven) or just assume they're there and leave it to the user to specify them. If users are manually managing dependencies it's up to them to ensure they're all included.

 

The service loader API can be problematic for OSGi users, as it isn't very helpful for hot reloading of classes.  The PostgreSQL JDBC driver currently works well in such environments, it would be unfortunate to lose that advantage through an attempt to help out another category of users.

This shouldn't be the only way of selecting an implementation, and bundling a given version of the API + RI shouldn't be the only build option. I'm certainly not against making this Just Work, but here there's a possibility that all this extra stuff could actually cause things to break .

so how do we make it "Just Work" ?

 

I don't think there's a way to do this without breaking backward compatibility.

You definitely can't rely on specific dependencies on the classpath that legacy users will not have. Bundling the dependencies doesn't work either as it'd clobber existing ones on the classpath. Making things dynamic/pluggable may be option for internal implementations but it breaks anything with the required dependencies in the method signature. That means we could have code that dynamically picks a JSON parser and uses it internally, but we can't have a getJsonValue() method on a public PGResultSet interface as the class wouldn't even load properly on an older JVM[1].

In a lot of ways this is similar to the other thread we had about dropping support for older JVMs. To natively support new features (like a native getJsonValue()) we'd need to specify a min JDK version.

    JSR353 targets 1.6+. So if by older you mean 4 or 5 then yes, it won't be supported.

Oh that's nice then. I haven't read through the spec for it but based on the release date assumed it was 1.7+. This might make things a bit easier.

At this point I think we have to bite bullet and either drop support for older versions (not likely) or offer multiple versions of the driver. The latter could make modern assumptions about the classpath/environment to support new features natively. That could include JsonValue and the new Java 8 date/time types.

+1 to drop support for less than 1.6. Or at least, create different versions (but that is indirectly happening now, with different JDBC API levels). But not adding as of today JsonValue to a method signature because we want to retain compatibility with 1.5 or less is not something our users would understand.

I'm really liking the separate version approach. We're gonna have to do it anyway for JDBC 4.2 ... might as well support PG extensions in the same way.

 

Regards,

-- Sehrope Sarkuni

Founder & CEO | JackDB, Inc. | https://www.jackdb.com/