Home > mailing lists

Thread: Postgresql 9.4 / JSONB / JDBC

Postgresql 9.4 / JSONB / JDBC

From

Christopher BROWN

Date:

20 December 2014, 12:45:34

Hello,

I'm new to this list, and have tried searching both the mailing list archives, and internet in general, for information on how to use the new JSONB data type with JDBC. Found almost nothing in the mailing list archives, and internet searches "helpfully" assume I've misspelled "json" and give me results that are unrelated or refer to the basic "JSON" type from older Postgresql versions.

How can this type be used, with simple "flat" key-value collections (maps), with tree-like maps-of-maps, and (ideally) with JSON APIs such as Jackson JSON API?

Is it possible? Is it already described somewhere that I missed?

Thanks,
Christopher

Re: Postgresql 9.4 / JSONB / JDBC

From

Dave Cramer

Date:

20 December 2014, 15:20:17

Christopher,

No you have not missed anything, there has been nothing done with jsonb and the driver. Since you are the first, perhaps you can give me your wish list ?

What would you like to see happen ?

Dave Cramer

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

On 20 December 2014 at 04:44, Christopher BROWN <brown@reflexe.fr> wrote:

Hello,

I'm new to this list, and have tried searching both the mailing list archives, and internet in general, for information on how to use the new JSONB data type with JDBC. Found almost nothing in the mailing list archives, and internet searches "helpfully" assume I've misspelled "json" and give me results that are unrelated or refer to the basic "JSON" type from older Postgresql versions.

How can this type be used, with simple "flat" key-value collections (maps), with tree-like maps-of-maps, and (ideally) with JSON APIs such as Jackson JSON API?

Is it possible? Is it already described somewhere that I missed?

Thanks,
Christopher

Re: Postgresql 9.4 / JSONB / JDBC

From

Christopher BROWN

Date:

20 December 2014, 17:15:17

Dave,

I've only just started looking at how JSONB works in Postgresql, and am mainly basing ideas on what I can see here:

http://www.postgresql.org/docs/9.4/static/datatype-json.html

I suppose the basic requirement would be to be able to do stuff like this in JDBC:

PreparedStatement stmt = connection.prepareStatement("UPDATE MYTABLE SET MYJSONCOL = ? WHERE MYID = ?");

stmt.setObject(1, map);

stmt.setInt(2, 123);

...as opposed to something like this (with a potentially large and variable number of "?" markers):

prepareStatement("UPDATE MYTABLE SET MYJSONCOL = '{\"?\":?, \"?\": {\"?\":?}}' WHERE MYID = ?");

...or just creating a normal statement and rolling my own escaping to prevent SQL injection.

It would make sense to only allow keys of strings, and values of either Map, Collection, Iterable, Stream, array, string, number, boolean, or null (although I'm a bit confused about the latter after consulting table 8-23 from the linked documentation above).

That would be the first requirement.

The second would be an optimisation to avoid having to convert existing data structures but might need to be "a capability that you can add" rather than "built in". For example, I use the Jackson JSON API (there are others of course) and I wouldn't expect it to be bundled into the driver, however it'd be useful to pass a JSONNode object into "stmt.setObject" and have a way of telling the driver how to serialize it (I don't know if the getObject method of a resultset can be forced to use Map or something else). Jackson has useful serialization methods of course, so I could just write a string suffixed with "::json", but I'm guessing maybe the driver provides some sort of optimized binary representation which could be used for better performance.

Thanks,

Christopher

On 20 December 2014 at 13:19, Dave Cramer <pg@fastcrypt.com> wrote:

Christopher,

No you have not missed anything, there has been nothing done with jsonb and the driver. Since you are the first, perhaps you can give me your wish list ?

What would you like to see happen ?

Dave Cramer

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

On 20 December 2014 at 04:44, Christopher BROWN <brown@reflexe.fr> wrote:
Hello,

I'm new to this list, and have tried searching both the mailing list archives, and internet in general, for information on how to use the new JSONB data type with JDBC. Found almost nothing in the mailing list archives, and internet searches "helpfully" assume I've misspelled "json" and give me results that are unrelated or refer to the basic "JSON" type from older Postgresql versions.

How can this type be used, with simple "flat" key-value collections (maps), with tree-like maps-of-maps, and (ideally) with JSON APIs such as Jackson JSON API?

Is it possible? Is it already described somewhere that I missed?

Thanks,
Christopher

Re: Postgresql 9.4 / JSONB / JDBC

From

Mo Omer

Date:

20 December 2014, 19:10:57

Morning all,

I've been using JSONb pretty extensively for about 2 weeks now in a pre-pro setup.

It's advisable to not only think very hard before both of the below requests, but to probably make them easy to disable. I recall there was a request to do the same serialization for JSON (mayhap it was a question about JSONb, about serializing on the other end, though) which was answered with a swift rejection.

I'm pretty wary about conversions here, because if the strings I send through aren't matching up with the java bytes that actually go in, that can be a massive PITA.

However, what would be welcome is an (optional) escape method for JSON in general.

After inserting ~100MM rows with COPY/JSONB, it sure would be great to optionally invoke a jdbc string escape method rather than substituting/escaping them all myself. The existing built-in methods don't fit the requirements of COPY. And, COPY with JSONb has further unique requirements (I.e not escaping the quotes around keys and values more than language default of one slash, yet escaping internal chars further - or removing them)

E.g.

{"hello": "\tthere, \"John\""} needs to have a few extra slashes present in order to be valid. Would be nice to have a solid escape method for this.

Thank you for all the hard work JDBC maintainers,

Mo

This message was drafted on a tiny touch screen; please forgive brevity & tpyos

On Dec 20, 2014, at 8:14 AM, Christopher BROWN <brown@reflexe.fr> wrote:

Dave,

I've only just started looking at how JSONB works in Postgresql, and am mainly basing ideas on what I can see here:
http://www.postgresql.org/docs/9.4/static/datatype-json.html

I suppose the basic requirement would be to be able to do stuff like this in JDBC:

PreparedStatement stmt = connection.prepareStatement("UPDATE MYTABLE SET MYJSONCOL = ? WHERE MYID = ?");
stmt.setObject(1, map);
stmt.setInt(2, 123);

...as opposed to something like this (with a potentially large and variable number of "?" markers):

prepareStatement("UPDATE MYTABLE SET MYJSONCOL = '{\"?\":?, \"?\": {\"?\":?}}' WHERE MYID = ?");

...or just creating a normal statement and rolling my own escaping to prevent SQL injection.

It would make sense to only allow keys of strings, and values of either Map, Collection, Iterable, Stream, array, string, number, boolean, or null (although I'm a bit confused about the latter after consulting table 8-23 from the linked documentation above).

That would be the first requirement.

The second would be an optimisation to avoid having to convert existing data structures but might need to be "a capability that you can add" rather than "built in". For example, I use the Jackson JSON API (there are others of course) and I wouldn't expect it to be bundled into the driver, however it'd be useful to pass a JSONNode object into "stmt.setObject" and have a way of telling the driver how to serialize it (I don't know if the getObject method of a resultset can be forced to use Map or something else). Jackson has useful serialization methods of course, so I could just write a string suffixed with "::json", but I'm guessing maybe the driver provides some sort of optimized binary representation which could be used for better performance.

Thanks,
Christopher

On 20 December 2014 at 13:19, Dave Cramer <pg@fastcrypt.com> wrote:
Christopher,

No you have not missed anything, there has been nothing done with jsonb and the driver. Since you are the first, perhaps you can give me your wish list ?

What would you like to see happen ?

Dave Cramer

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

On 20 December 2014 at 04:44, Christopher BROWN <brown@reflexe.fr> wrote:
Hello,

I'm new to this list, and have tried searching both the mailing list archives, and internet in general, for information on how to use the new JSONB data type with JDBC. Found almost nothing in the mailing list archives, and internet searches "helpfully" assume I've misspelled "json" and give me results that are unrelated or refer to the basic "JSON" type from older Postgresql versions.

How can this type be used, with simple "flat" key-value collections (maps), with tree-like maps-of-maps, and (ideally) with JSON APIs such as Jackson JSON API?

Is it possible? Is it already described somewhere that I missed?

Thanks,
Christopher

Re: Postgresql 9.4 / JSONB / JDBC

From

Alexis Meneses

Date:

20 December 2014, 19:58:39

Hi Christopher,

I already started to think about something to add the ability to use JSON objects with pgjdbc to meet Postgresql 9.4 major features on jdbc side (setObject/getObject).

IMO, the JSON API that should be used is javax.json (http://docs.oracle.com/javaee/7/api/javax/json/package-summary.html) because I think we should stick to standard APIs in the jdbc driver.
Many JSON implementations other that the default one can be used behind this API thanks to Java ServiceLoader API even if I believe that the widely used Jackson does not provide a bridge between javax.json and their implementation.

Anyway, as javax.json is not in Java SE, I think we may have to create an extensible way of managing the mapping from Java Object to Postgresql OID and structures. Loading the javax.json mapper could be done accordingly to what is available in the JVM pgjdbc is running in.

That way, one could also provide its own custom mapping implementations to the driver to handle any kind of custom object (Jackson objects could be handled by 3rd parties that way).

Alexis Meneses

2014-12-20 13:19 GMT+01:00 Dave Cramer <pg@fastcrypt.com>:

Christopher,

No you have not missed anything, there has been nothing done with jsonb and the driver. Since you are the first, perhaps you can give me your wish list ?

What would you like to see happen ?

Dave Cramer

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

On 20 December 2014 at 04:44, Christopher BROWN <brown@reflexe.fr> wrote:
Hello,

I'm new to this list, and have tried searching both the mailing list archives, and internet in general, for information on how to use the new JSONB data type with JDBC. Found almost nothing in the mailing list archives, and internet searches "helpfully" assume I've misspelled "json" and give me results that are unrelated or refer to the basic "JSON" type from older Postgresql versions.

How can this type be used, with simple "flat" key-value collections (maps), with tree-like maps-of-maps, and (ideally) with JSON APIs such as Jackson JSON API?

Is it possible? Is it already described somewhere that I missed?

Thanks,
Christopher

Re: Postgresql 9.4 / JSONB / JDBC

From

Alexis Meneses

Date:

21 December 2014, 20:08:24

Concerning the first requirement, it's not so trivial within the scope of the Jdbc interface.

On Jdbc side, it's impossible to guess that MYJSONCOL is a Jsonb datatype and unfortunately PreparedStatement.setObject(int, Map) is currently bound to the Hstore datatype.

Moreover, Map isn't enough to represent a Jsonb data as it does not fit a Json array for example. This would lead to bind many classes in setObject to the jsonb data-type.

A way to handle it without breaking things is maybe to add some driver specific methods to provide Jsonb data [eg. PGStatement.setJsonb(int, Map) and so on] that would require one to cast the Jdbc PreparedStatement into PGStatement.

Concerning the second, it's what I was thinking of and discussing in my previous post (http://postgresql.nabble.com/Postgresql-9-4-JSONB-JDBC-tp5831557p5831576.html). A "builtin" feature could be provided if based on Java standards.

Alexis

2014-12-20 15:14 GMT+01:00 Christopher BROWN <brown@reflexe.fr>:

Dave,

I've only just started looking at how JSONB works in Postgresql, and am mainly basing ideas on what I can see here:
http://www.postgresql.org/docs/9.4/static/datatype-json.html

I suppose the basic requirement would be to be able to do stuff like this in JDBC:

PreparedStatement stmt = connection.prepareStatement("UPDATE MYTABLE SET MYJSONCOL = ? WHERE MYID = ?");
stmt.setObject(1, map);
stmt.setInt(2, 123);

...as opposed to something like this (with a potentially large and variable number of "?" markers):

prepareStatement("UPDATE MYTABLE SET MYJSONCOL = '{\"?\":?, \"?\": {\"?\":?}}' WHERE MYID = ?");

...or just creating a normal statement and rolling my own escaping to prevent SQL injection.

It would make sense to only allow keys of strings, and values of either Map, Collection, Iterable, Stream, array, string, number, boolean, or null (although I'm a bit confused about the latter after consulting table 8-23 from the linked documentation above).

That would be the first requirement.

The second would be an optimisation to avoid having to convert existing data structures but might need to be "a capability that you can add" rather than "built in". For example, I use the Jackson JSON API (there are others of course) and I wouldn't expect it to be bundled into the driver, however it'd be useful to pass a JSONNode object into "stmt.setObject" and have a way of telling the driver how to serialize it (I don't know if the getObject method of a resultset can be forced to use Map or something else). Jackson has useful serialization methods of course, so I could just write a string suffixed with "::json", but I'm guessing maybe the driver provides some sort of optimized binary representation which could be used for better performance.

Thanks,
Christopher

On 20 December 2014 at 13:19, Dave Cramer <pg@fastcrypt.com> wrote:
Christopher,

No you have not missed anything, there has been nothing done with jsonb and the driver. Since you are the first, perhaps you can give me your wish list ?

What would you like to see happen ?

Dave Cramer

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

On 20 December 2014 at 04:44, Christopher BROWN <brown@reflexe.fr> wrote:
Hello,

I'm new to this list, and have tried searching both the mailing list archives, and internet in general, for information on how to use the new JSONB data type with JDBC. Found almost nothing in the mailing list archives, and internet searches "helpfully" assume I've misspelled "json" and give me results that are unrelated or refer to the basic "JSON" type from older Postgresql versions.

How can this type be used, with simple "flat" key-value collections (maps), with tree-like maps-of-maps, and (ideally) with JSON APIs such as Jackson JSON API?

Is it possible? Is it already described somewhere that I missed?

Thanks,
Christopher

Re: Postgresql 9.4 / JSONB / JDBC

From

Hans Klett

Date:

21 December 2014, 22:10:27

Hello Christopher,

If you’re already using Jackson, can’t you just serialize the json data structure and bind it as a single SQL parameter? Jackson can handle writing out correct JSON, and prepared statements can handle escaping.

Am I missing something?

Thanks,

- Hans

From: Christopher BROWN <brown@reflexe.fr>
Date: Saturday, December 20, 2014 at 7:14 AM
To: Dave Cramer <pg@fastcrypt.com>
Cc: List <pgsql-jdbc@postgresql.org>
Subject: Re: [JDBC] Postgresql 9.4 / JSONB / JDBC

Dave,

I've only just started looking at how JSONB works in Postgresql, and am mainly basing ideas on what I can see here:

http://www.postgresql.org/docs/9.4/static/datatype-json.html

I suppose the basic requirement would be to be able to do stuff like this in JDBC:

PreparedStatement stmt = connection.prepareStatement("UPDATE MYTABLE SET MYJSONCOL = ? WHERE MYID = ?");

stmt.setObject(1, map);

stmt.setInt(2, 123);

...as opposed to something like this (with a potentially large and variable number of "?" markers):

prepareStatement("UPDATE MYTABLE SET MYJSONCOL = '{\"?\":?, \"?\": {\"?\":?}}' WHERE MYID = ?");

...or just creating a normal statement and rolling my own escaping to prevent SQL injection.

It would make sense to only allow keys of strings, and values of either Map, Collection, Iterable, Stream, array, string, number, boolean, or null (although I'm a bit confused about the latter after consulting table 8-23 from the linked documentation above).

That would be the first requirement.

The second would be an optimisation to avoid having to convert existing data structures but might need to be "a capability that you can add" rather than "built in". For example, I use the Jackson JSON API (there are others of course) and I wouldn't expect it to be bundled into the driver, however it'd be useful to pass a JSONNode object into "stmt.setObject" and have a way of telling the driver how to serialize it (I don't know if the getObject method of a resultset can be forced to use Map or something else). Jackson has useful serialization methods of course, so I could just write a string suffixed with "::json", but I'm guessing maybe the driver provides some sort of optimized binary representation which could be used for better performance.

Thanks,

Christopher

On 20 December 2014 at 13:19, Dave Cramer <pg@fastcrypt.com> wrote:

Christopher,

No you have not missed anything, there has been nothing done with jsonb and the driver. Since you are the first, perhaps you can give me your wish list ?

What would you like to see happen ?

Dave Cramer

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

On 20 December 2014 at 04:44, Christopher BROWN <brown@reflexe.fr> wrote:
Hello,

I'm new to this list, and have tried searching both the mailing list archives, and internet in general, for information on how to use the new JSONB data type with JDBC. Found almost nothing in the mailing list archives, and internet searches "helpfully" assume I've misspelled "json" and give me results that are unrelated or refer to the basic "JSON" type from older Postgresql versions.

How can this type be used, with simple "flat" key-value collections (maps), with tree-like maps-of-maps, and (ideally) with JSON APIs such as Jackson JSON API?

Is it possible? Is it already described somewhere that I missed?

Thanks,
Christopher

Re: Postgresql 9.4 / JSONB / JDBC

From

Christopher BROWN

Date:

21 December 2014, 23:56:02

Hello Hans,

If it's possible to bind using a single "?" parameter, that would seem logic. However, that means (potentially) serializing a large chunk of data; I was wondering if an API could be provided to pull data in a streaming way, to keep memory usage under control in "under pressure" performance scenarii.

--
Christopher

On 21 December 2014 at 19:41, Hans Klett <hansk@spectralogic.com> wrote:

Hello Christopher,

If you’re already using Jackson, can’t you just serialize the json data structure and bind it as a single SQL parameter? Jackson can handle writing out correct JSON, and prepared statements can handle escaping.

Am I missing something?

Thanks,
- Hans

From: Christopher BROWN <brown@reflexe.fr>
Date: Saturday, December 20, 2014 at 7:14 AM
To: Dave Cramer <pg@fastcrypt.com>
Cc: List <pgsql-jdbc@postgresql.org>
Subject: Re: [JDBC] Postgresql 9.4 / JSONB / JDBC

Dave,

I've only just started looking at how JSONB works in Postgresql, and am mainly basing ideas on what I can see here:
http://www.postgresql.org/docs/9.4/static/datatype-json.html

I suppose the basic requirement would be to be able to do stuff like this in JDBC:

PreparedStatement stmt = connection.prepareStatement("UPDATE MYTABLE SET MYJSONCOL = ? WHERE MYID = ?");
stmt.setObject(1, map);
stmt.setInt(2, 123);

...as opposed to something like this (with a potentially large and variable number of "?" markers):

prepareStatement("UPDATE MYTABLE SET MYJSONCOL = '{\"?\":?, \"?\": {\"?\":?}}' WHERE MYID = ?");

...or just creating a normal statement and rolling my own escaping to prevent SQL injection.

It would make sense to only allow keys of strings, and values of either Map, Collection, Iterable, Stream, array, string, number, boolean, or null (although I'm a bit confused about the latter after consulting table 8-23 from the linked documentation above).

That would be the first requirement.

The second would be an optimisation to avoid having to convert existing data structures but might need to be "a capability that you can add" rather than "built in". For example, I use the Jackson JSON API (there are others of course) and I wouldn't expect it to be bundled into the driver, however it'd be useful to pass a JSONNode object into "stmt.setObject" and have a way of telling the driver how to serialize it (I don't know if the getObject method of a resultset can be forced to use Map or something else). Jackson has useful serialization methods of course, so I could just write a string suffixed with "::json", but I'm guessing maybe the driver provides some sort of optimized binary representation which could be used for better performance.

Thanks,
Christopher

On 20 December 2014 at 13:19, Dave Cramer <pg@fastcrypt.com> wrote:
Christopher,

No you have not missed anything, there has been nothing done with jsonb and the driver. Since you are the first, perhaps you can give me your wish list ?

What would you like to see happen ?

Dave Cramer

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

On 20 December 2014 at 04:44, Christopher BROWN <brown@reflexe.fr> wrote:
Hello,

I'm new to this list, and have tried searching both the mailing list archives, and internet in general, for information on how to use the new JSONB data type with JDBC. Found almost nothing in the mailing list archives, and internet searches "helpfully" assume I've misspelled "json" and give me results that are unrelated or refer to the basic "JSON" type from older Postgresql versions.

How can this type be used, with simple "flat" key-value collections (maps), with tree-like maps-of-maps, and (ideally) with JSON APIs such as Jackson JSON API?

Is it possible? Is it already described somewhere that I missed?

Thanks,
Christopher

Re: Postgresql 9.4 / JSONB / JDBC

From

Bosco Rama

Date:

22 December 2014, 21:14:19

On 12/20/14 06:18, Alexis Meneses wrote:
>
> Many JSON implementations other that the default one can be used behind
> this API thanks to Java ServiceLoader API even if I believe that the widely
> used Jackson does not provide a bridge between javax.json and their
> implementation.

IIRC, there is both a conversion module for, and an actual
implementation of, javax.json (aka JSR-353) available for Jackson.

HTH,
Bosco.

Re: Postgresql 9.4 / JSONB / JDBC

From

Dave Cramer

Date:

22 December 2014, 21:16:20

Dave Cramer

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

On 22 December 2014 at 13:14, Bosco Rama <postgres@boscorama.com> wrote:

On 12/20/14 06:18, Alexis Meneses wrote:
>
> Many JSON implementations other that the default one can be used behind
> this API thanks to Java ServiceLoader API even if I believe that the widely
> used Jackson does not provide a bridge between javax.json and their
> implementation.

IIRC, there is both a conversion module for, and an actual
implementation of, javax.json (aka JSR-353) available for Jackson.

Is this going to make it in? I seem to recall this was being pulled. That being said it is still a better starting point than anything else ?

HTH,
Bosco.

Re: Postgresql 9.4 / JSONB / JDBC

From

Bosco Rama

Date:

22 December 2014, 21:33:22

On 12/22/14 10:15, Dave Cramer wrote:
> On 22 December 2014 at 13:14, Bosco Rama <postgres@boscorama.com> wrote:
>>
>> IIRC, there is both a conversion module for, and an actual
>> implementation of, javax.json (aka JSR-353) available for Jackson.
>>
>
> Is this going to make it in? I seem to recall this was being pulled. That
> being said it is still a better starting point than anything else ?

Their big issue with the JSR was that it doesn't do data-binding, which
is one of the major things about Jackson.  Hence it will probably never
make it into the 'core'.  The JSR support is provided via add-on data
type support.  The question may become: Does the driver want to support
data-binding too?

Another issue that may need considering is that once a javax.json item
is 'built' it is immutable.  I'm not sure how that would benefit/hinder
the driver.

HTH,
Bosco.

Re: Postgresql 9.4 / JSONB / JDBC

From

Christopher BROWN

Date:

22 December 2014, 22:15:39

With reference to your the message below, and to the JavaDoc for "javax.json" (JSR-353):

https://json-processing-spec.java.net/nonav/releases/1.0/fcs/javadocs/index.html

I think it's possible to have unambiguous support for setObject(int, ...) using the standard API in such a way that you can also theoretically wrap any other JSON API (Jackson or anything else). This would require a compile-time dependency on the interfaces of the "javax.json" API but would not require bundling any particular implementation. It would be slightly more involved with regards to the getObject() call, but I'll get to that.

First off, the "javax.json" API is defined almost entirely in terms of interfaces, apart from the "javax.json.Json" factory class. I'm not suggesting the the JDBC driver implements these interfaces at all, it need only consume these APIs, so nothing to worry about there. The API defines both a streaming API and an object model API. Therefore, as far as "setObject(...)" is concerned, it would be good for it to accept either a JsonArray or a JsonObject as a parameter, in the first case using the pull-parser paradigm to read in data, and the second case iterating over keys of the JsonObject (noting of course that values can also be JsonArray/JsonObject instances). JsonObject also implements the Map interface, which would conflict with the "hstore" usage of Map already mentioned by Alexis, but it should be straightforward to differentiate by ordering "instanceof" checks within the "setObject(...)" implementation.

How would that fit with Jackson or any other API? Jackson does all sorts of stuff, like databinding, but I think that's way out of scope for the JDBC driver's core features. However, it doesn't look particularly complicated to create an implementation of a subset of "javax.json" (the JsonParser or the JsonStructure objects) that are simple wrappers around a specific implementation, such as Jackson. That way, the driver could stick the standards, and anyone could feed data in just by wrapping there own JSON implementation (or anything else with an equivalent structure) in that API.

As for "getObject(...)", it's desirable to get stuff out in a similar format to what went in, but that would require that the driver has access to any implementation of "javax.json.Json". If "getString(...)" is invoked on the ResultSet, then a serialized string should be returned, if "getObject(...)" is invoked, you should probably return a JsonGenerator (to encourage efficiency by default). Maybe a "PGjsonb" driver-specific class could provide a more direct approach to get out the data as either a JsonGenerator or JsonStructure.

I would be favorable to have a PGjsonb object with an API that I could call into to explicitly provide the Json factory object; I'd like an alternative to the service loader API as I'm a heavy user of OSGi and sometimes these two approaches don't play well together when reloading bundles (but I wouldn't mind it if the driver called the suggested API (explicit setting of the factory) if it finds something via the service loader API. Lots of options via system properties or whatever would be nice :-)

Hope that helps,
Christopher

On 21 December 2014 at 11:36, Alexis Meneses <alexis.meneses@gmail.com> wrote:

Concerning the first requirement, it's not so trivial within the scope of the Jdbc interface.
On Jdbc side, it's impossible to guess that MYJSONCOL is a Jsonb datatype and unfortunately PreparedStatement.setObject(int, Map) is currently bound to the Hstore datatype.

Moreover, Map isn't enough to represent a Jsonb data as it does not fit a Json array for example. This would lead to bind many classes in setObject to the jsonb data-type.

A way to handle it without breaking things is maybe to add some driver specific methods to provide Jsonb data [eg. PGStatement.setJsonb(int, Map) and so on] that would require one to cast the Jdbc PreparedStatement into PGStatement.

Concerning the second, it's what I was thinking of and discussing in my previous post (http://postgresql.nabble.com/Postgresql-9-4-JSONB-JDBC-tp5831557p5831576.html). A "builtin" feature could be provided if based on Java standards.

Alexis

2014-12-20 15:14 GMT+01:00 Christopher BROWN <brown@reflexe.fr>:
Dave,

I've only just started looking at how JSONB works in Postgresql, and am mainly basing ideas on what I can see here:
http://www.postgresql.org/docs/9.4/static/datatype-json.html

I suppose the basic requirement would be to be able to do stuff like this in JDBC:

PreparedStatement stmt = connection.prepareStatement("UPDATE MYTABLE SET MYJSONCOL = ? WHERE MYID = ?");
stmt.setObject(1, map);
stmt.setInt(2, 123);

...as opposed to something like this (with a potentially large and variable number of "?" markers):

prepareStatement("UPDATE MYTABLE SET MYJSONCOL = '{\"?\":?, \"?\": {\"?\":?}}' WHERE MYID = ?");

...or just creating a normal statement and rolling my own escaping to prevent SQL injection.

It would make sense to only allow keys of strings, and values of either Map, Collection, Iterable, Stream, array, string, number, boolean, or null (although I'm a bit confused about the latter after consulting table 8-23 from the linked documentation above).

That would be the first requirement.

The second would be an optimisation to avoid having to convert existing data structures but might need to be "a capability that you can add" rather than "built in". For example, I use the Jackson JSON API (there are others of course) and I wouldn't expect it to be bundled into the driver, however it'd be useful to pass a JSONNode object into "stmt.setObject" and have a way of telling the driver how to serialize it (I don't know if the getObject method of a resultset can be forced to use Map or something else). Jackson has useful serialization methods of course, so I could just write a string suffixed with "::json", but I'm guessing maybe the driver provides some sort of optimized binary representation which could be used for better performance.

Thanks,
Christopher

On 20 December 2014 at 13:19, Dave Cramer <pg@fastcrypt.com> wrote:
Christopher,

No you have not missed anything, there has been nothing done with jsonb and the driver. Since you are the first, perhaps you can give me your wish list ?

What would you like to see happen ?

Dave Cramer

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

On 20 December 2014 at 04:44, Christopher BROWN <brown@reflexe.fr> wrote:
Hello,

I'm new to this list, and have tried searching both the mailing list archives, and internet in general, for information on how to use the new JSONB data type with JDBC. Found almost nothing in the mailing list archives, and internet searches "helpfully" assume I've misspelled "json" and give me results that are unrelated or refer to the basic "JSON" type from older Postgresql versions.

How can this type be used, with simple "flat" key-value collections (maps), with tree-like maps-of-maps, and (ideally) with JSON APIs such as Jackson JSON API?

Is it possible? Is it already described somewhere that I missed?

Thanks,
Christopher

Re: Postgresql 9.4 / JSONB / JDBC

From

Christopher BROWN

Date:

29 December 2014, 13:09:16

Hello,

Regarding my previous messages on this subject (JSONB support in the Postgresql JDBC driver), one assumption I'm making is that the driver might be able to stream the JSON data more efficiently that just throwing a chunk of text onto the wire... as text isn't the most efficient way of encoding numbers, booleans, and nulls.

How does the native network protocol of Postgresql actually transfer such data? Is there an opportunity for sending the data efficiently? Personally for some of my applications, I'm able to use Jackson's "Smile" binary JSON format, although I doubt that the Postgresql server would understand it or want to implement it (I don't know if it could be considered a standard, even if it's specified -- http://wiki.fasterxml.com/SmileFormatSpec).

--
Christopher

On 20 December 2014 at 10:44, Christopher BROWN <brown@reflexe.fr> wrote:

Hello,

I'm new to this list, and have tried searching both the mailing list archives, and internet in general, for information on how to use the new JSONB data type with JDBC. Found almost nothing in the mailing list archives, and internet searches "helpfully" assume I've misspelled "json" and give me results that are unrelated or refer to the basic "JSON" type from older Postgresql versions.

How can this type be used, with simple "flat" key-value collections (maps), with tree-like maps-of-maps, and (ideally) with JSON APIs such as Jackson JSON API?

Is it possible? Is it already described somewhere that I missed?

Thanks,
Christopher

Re: Postgresql 9.4 / JSONB / JDBC

From

Dave Cramer

Date:

29 December 2014, 14:43:04

Yes, we can pull the data from the server in binary. After that though we have to convert it to text

Dave Cramer

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

On 29 December 2014 at 05:08, Christopher BROWN <brown@reflexe.fr> wrote:

Hello,

Regarding my previous messages on this subject (JSONB support in the Postgresql JDBC driver), one assumption I'm making is that the driver might be able to stream the JSON data more efficiently that just throwing a chunk of text onto the wire... as text isn't the most efficient way of encoding numbers, booleans, and nulls.

How does the native network protocol of Postgresql actually transfer such data? Is there an opportunity for sending the data efficiently? Personally for some of my applications, I'm able to use Jackson's "Smile" binary JSON format, although I doubt that the Postgresql server would understand it or want to implement it (I don't know if it could be considered a standard, even if it's specified -- http://wiki.fasterxml.com/SmileFormatSpec).

--
Christopher

On 20 December 2014 at 10:44, Christopher BROWN <brown@reflexe.fr> wrote:
Hello,

I'm new to this list, and have tried searching both the mailing list archives, and internet in general, for information on how to use the new JSONB data type with JDBC. Found almost nothing in the mailing list archives, and internet searches "helpfully" assume I've misspelled "json" and give me results that are unrelated or refer to the basic "JSON" type from older Postgresql versions.

How can this type be used, with simple "flat" key-value collections (maps), with tree-like maps-of-maps, and (ideally) with JSON APIs such as Jackson JSON API?

Is it possible? Is it already described somewhere that I missed?

Thanks,
Christopher

Re: Postgresql 9.4 / JSONB / JDBC

From

Mikko Tiihonen

Date:

31 December 2014, 21:30:35

Last time I checked (few months ago), the postgresql jsonb binary format was just a extra 4 byte version header followed by text json data. So basically the binary jsonb format was slower than text format. And since there is no official plan on how to modify the binary formats without breaking backwards compatibility it most likely will stay that way.

Having a proper binary encoding for jsonb would speed up parsing on both server and client side.

-Mikko

From: pgsql-jdbc-owner@postgresql.org <pgsql-jdbc-owner@postgresql.org> on behalf of Dave Cramer <pg@fastcrypt.com>
Sent: 29 December 2014 13:42
To: Christopher BROWN
Cc: List
Subject: Re: [JDBC] Postgresql 9.4 / JSONB / JDBC

Yes, we can pull the data from the server in binary. After that though we have to convert it to text

Dave Cramer

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

On 29 December 2014 at 05:08, Christopher BROWN <brown@reflexe.fr> wrote:

Hello,

Regarding my previous messages on this subject (JSONB support in the Postgresql JDBC driver), one assumption I'm making is that the driver might be able to stream the JSON data more efficiently that just throwing a chunk of text onto the wire... as text isn't the most efficient way of encoding numbers, booleans, and nulls.

How does the native network protocol of Postgresql actually transfer such data? Is there an opportunity for sending the data efficiently? Personally for some of my applications, I'm able to use Jackson's "Smile" binary JSON format, although I doubt that the Postgresql server would understand it or want to implement it (I don't know if it could be considered a standard, even if it's specified -- http://wiki.fasterxml.com/SmileFormatSpec).

--
Christopher

On 20 December 2014 at 10:44, Christopher BROWN <brown@reflexe.fr> wrote:
Hello,

I'm new to this list, and have tried searching both the mailing list archives, and internet in general, for information on how to use the new JSONB data type with JDBC. Found almost nothing in the mailing list archives, and internet searches "helpfully" assume I've misspelled "json" and give me results that are unrelated or refer to the basic "JSON" type from older Postgresql versions.

How can this type be used, with simple "flat" key-value collections (maps), with tree-like maps-of-maps, and (ideally) with JSON APIs such as Jackson JSON API?

Is it possible? Is it already described somewhere that I missed?

Thanks,
Christopher

Re: Postgresql 9.4 / JSONB / JDBC

From

Dave Cramer

Date:

31 December 2014, 21:51:03

I just checked with Andrew Dunstan, and apparently this is true for JSON, but not JSONB

Dave

Dave Cramer

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

On 31 December 2014 at 13:29, Mikko Tiihonen <Mikko.Tiihonen@nitorcreations.com> wrote:

Last time I checked (few months ago), the postgresql jsonb binary format was just a extra 4 byte version header followed by text json data. So basically the binary jsonb format was slower than text format. And since there is no official plan on how to modify the binary formats without breaking backwards compatibility it most likely will stay that way.

Having a proper binary encoding for jsonb would speed up parsing on both server and client side.

-Mikko
From: pgsql-jdbc-owner@postgresql.org <pgsql-jdbc-owner@postgresql.org> on behalf of Dave Cramer <pg@fastcrypt.com>
Sent: 29 December 2014 13:42
To: Christopher BROWN
Cc: List
Subject: Re: [JDBC] Postgresql 9.4 / JSONB / JDBC

Yes, we can pull the data from the server in binary. After that though we have to convert it to text

Dave Cramer

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

On 29 December 2014 at 05:08, Christopher BROWN <brown@reflexe.fr> wrote:
Hello,

Regarding my previous messages on this subject (JSONB support in the Postgresql JDBC driver), one assumption I'm making is that the driver might be able to stream the JSON data more efficiently that just throwing a chunk of text onto the wire... as text isn't the most efficient way of encoding numbers, booleans, and nulls.

How does the native network protocol of Postgresql actually transfer such data? Is there an opportunity for sending the data efficiently? Personally for some of my applications, I'm able to use Jackson's "Smile" binary JSON format, although I doubt that the Postgresql server would understand it or want to implement it (I don't know if it could be considered a standard, even if it's specified -- http://wiki.fasterxml.com/SmileFormatSpec).

--
Christopher

On 20 December 2014 at 10:44, Christopher BROWN <brown@reflexe.fr> wrote:
Hello,

I'm new to this list, and have tried searching both the mailing list archives, and internet in general, for information on how to use the new JSONB data type with JDBC. Found almost nothing in the mailing list archives, and internet searches "helpfully" assume I've misspelled "json" and give me results that are unrelated or refer to the basic "JSON" type from older Postgresql versions.

How can this type be used, with simple "flat" key-value collections (maps), with tree-like maps-of-maps, and (ideally) with JSON APIs such as Jackson JSON API?

Is it possible? Is it already described somewhere that I missed?

Thanks,
Christopher

Re: Postgresql 9.4 / JSONB / JDBC

From

Dave Cramer

Date:

31 December 2014, 21:52:33

Check that, apparently you are correct Mikko.

Sorry for the noise.

Dave

Dave Cramer

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

On 31 December 2014 at 13:50, Dave Cramer <pg@fastcrypt.com> wrote:

I just checked with Andrew Dunstan, and apparently this is true for JSON, but not JSONB

Dave

Dave Cramer

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

On 31 December 2014 at 13:29, Mikko Tiihonen <Mikko.Tiihonen@nitorcreations.com> wrote:
Last time I checked (few months ago), the postgresql jsonb binary format was just a extra 4 byte version header followed by text json data. So basically the binary jsonb format was slower than text format. And since there is no official plan on how to modify the binary formats without breaking backwards compatibility it most likely will stay that way.

Having a proper binary encoding for jsonb would speed up parsing on both server and client side.

-Mikko
From: pgsql-jdbc-owner@postgresql.org <pgsql-jdbc-owner@postgresql.org> on behalf of Dave Cramer <pg@fastcrypt.com>
Sent: 29 December 2014 13:42
To: Christopher BROWN
Cc: List
Subject: Re: [JDBC] Postgresql 9.4 / JSONB / JDBC

Yes, we can pull the data from the server in binary. After that though we have to convert it to text

Dave Cramer

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

On 29 December 2014 at 05:08, Christopher BROWN <brown@reflexe.fr> wrote:
Hello,

Regarding my previous messages on this subject (JSONB support in the Postgresql JDBC driver), one assumption I'm making is that the driver might be able to stream the JSON data more efficiently that just throwing a chunk of text onto the wire... as text isn't the most efficient way of encoding numbers, booleans, and nulls.

How does the native network protocol of Postgresql actually transfer such data? Is there an opportunity for sending the data efficiently? Personally for some of my applications, I'm able to use Jackson's "Smile" binary JSON format, although I doubt that the Postgresql server would understand it or want to implement it (I don't know if it could be considered a standard, even if it's specified -- http://wiki.fasterxml.com/SmileFormatSpec).

--
Christopher

On 20 December 2014 at 10:44, Christopher BROWN <brown@reflexe.fr> wrote:
Hello,

I'm new to this list, and have tried searching both the mailing list archives, and internet in general, for information on how to use the new JSONB data type with JDBC. Found almost nothing in the mailing list archives, and internet searches "helpfully" assume I've misspelled "json" and give me results that are unrelated or refer to the basic "JSON" type from older Postgresql versions.

How can this type be used, with simple "flat" key-value collections (maps), with tree-like maps-of-maps, and (ideally) with JSON APIs such as Jackson JSON API?

Is it possible? Is it already described somewhere that I missed?

Thanks,
Christopher

Re: Postgresql 9.4 / JSONB / JDBC

From

Christopher BROWN

Date:

01 January 2015, 00:18:58

Unless there is a requirement to devise a new solution, in the absence of any formal standard, I am aware of two similar specifications:

- http://bsonspec.org/ (for MongoDB)

- http://wiki.fasterxml.com/SmileFormatSpec (for Jackson JSON)

I've used and continue to use the latter and it works well for me.

Does the PostgreSQL client/server protocol allow for protocol upgrades, in a similar way to what HTTP defines for SPDY or websockets? That might address Mikko's remarks about backward compatibility.

How is "hstore" data transmitted? Is it optimized and could JSONB "piggyback" it?

--
Christopher

On 31 December 2014 at 19:29, Mikko Tiihonen <Mikko.Tiihonen@nitorcreations.com> wrote:

Last time I checked (few months ago), the postgresql jsonb binary format was just a extra 4 byte version header followed by text json data. So basically the binary jsonb format was slower than text format. And since there is no official plan on how to modify the binary formats without breaking backwards compatibility it most likely will stay that way.

Having a proper binary encoding for jsonb would speed up parsing on both server and client side.

-Mikko
From: pgsql-jdbc-owner@postgresql.org <pgsql-jdbc-owner@postgresql.org> on behalf of Dave Cramer <pg@fastcrypt.com>
Sent: 29 December 2014 13:42
To: Christopher BROWN
Cc: List
Subject: Re: [JDBC] Postgresql 9.4 / JSONB / JDBC

Yes, we can pull the data from the server in binary. After that though we have to convert it to text

Dave Cramer

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

On 29 December 2014 at 05:08, Christopher BROWN <brown@reflexe.fr> wrote:
Hello,

Regarding my previous messages on this subject (JSONB support in the Postgresql JDBC driver), one assumption I'm making is that the driver might be able to stream the JSON data more efficiently that just throwing a chunk of text onto the wire... as text isn't the most efficient way of encoding numbers, booleans, and nulls.

How does the native network protocol of Postgresql actually transfer such data? Is there an opportunity for sending the data efficiently? Personally for some of my applications, I'm able to use Jackson's "Smile" binary JSON format, although I doubt that the Postgresql server would understand it or want to implement it (I don't know if it could be considered a standard, even if it's specified -- http://wiki.fasterxml.com/SmileFormatSpec).

--
Christopher

On 20 December 2014 at 10:44, Christopher BROWN <brown@reflexe.fr> wrote:
Hello,

I'm new to this list, and have tried searching both the mailing list archives, and internet in general, for information on how to use the new JSONB data type with JDBC. Found almost nothing in the mailing list archives, and internet searches "helpfully" assume I've misspelled "json" and give me results that are unrelated or refer to the basic "JSON" type from older Postgresql versions.

How can this type be used, with simple "flat" key-value collections (maps), with tree-like maps-of-maps, and (ideally) with JSON APIs such as Jackson JSON API?

Is it possible? Is it already described somewhere that I missed?

Thanks,
Christopher

Re: Postgresql 9.4 / JSONB / JDBC

From

Dave Cramer

Date:

01 January 2015, 00:56:05

Dave Cramer

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

On 31 December 2014 at 16:18, Christopher BROWN <brown@reflexe.fr> wrote:

Unless there is a requirement to devise a new solution, in the absence of any formal standard, I am aware of two similar specifications:

- http://bsonspec.org/ (for MongoDB)
- http://wiki.fasterxml.com/SmileFormatSpec (for Jackson JSON)

I've used and continue to use the latter and it works well for me.

Does the PostgreSQL client/server protocol allow for protocol upgrades, in a similar way to what HTTP defines for SPDY or websockets? That might address Mikko's remarks about backward compatibility.

No, unfortunately not. And we have no control over it anyways.

How is "hstore" data transmitted? Is it optimized and could JSONB "piggyback" it?

no clue.

--
Christopher

On 31 December 2014 at 19:29, Mikko Tiihonen <Mikko.Tiihonen@nitorcreations.com> wrote:
Last time I checked (few months ago), the postgresql jsonb binary format was just a extra 4 byte version header followed by text json data. So basically the binary jsonb format was slower than text format. And since there is no official plan on how to modify the binary formats without breaking backwards compatibility it most likely will stay that way.

Having a proper binary encoding for jsonb would speed up parsing on both server and client side.

-Mikko
From: pgsql-jdbc-owner@postgresql.org <pgsql-jdbc-owner@postgresql.org> on behalf of Dave Cramer <pg@fastcrypt.com>
Sent: 29 December 2014 13:42
To: Christopher BROWN
Cc: List
Subject: Re: [JDBC] Postgresql 9.4 / JSONB / JDBC

Yes, we can pull the data from the server in binary. After that though we have to convert it to text

Dave Cramer

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

On 29 December 2014 at 05:08, Christopher BROWN <brown@reflexe.fr> wrote:
Hello,

Regarding my previous messages on this subject (JSONB support in the Postgresql JDBC driver), one assumption I'm making is that the driver might be able to stream the JSON data more efficiently that just throwing a chunk of text onto the wire... as text isn't the most efficient way of encoding numbers, booleans, and nulls.

How does the native network protocol of Postgresql actually transfer such data? Is there an opportunity for sending the data efficiently? Personally for some of my applications, I'm able to use Jackson's "Smile" binary JSON format, although I doubt that the Postgresql server would understand it or want to implement it (I don't know if it could be considered a standard, even if it's specified -- http://wiki.fasterxml.com/SmileFormatSpec).

--
Christopher

On 20 December 2014 at 10:44, Christopher BROWN <brown@reflexe.fr> wrote:
Hello,

I'm new to this list, and have tried searching both the mailing list archives, and internet in general, for information on how to use the new JSONB data type with JDBC. Found almost nothing in the mailing list archives, and internet searches "helpfully" assume I've misspelled "json" and give me results that are unrelated or refer to the basic "JSON" type from older Postgresql versions.

How can this type be used, with simple "flat" key-value collections (maps), with tree-like maps-of-maps, and (ideally) with JSON APIs such as Jackson JSON API?

Is it possible? Is it already described somewhere that I missed?

Thanks,
Christopher

Re: Postgresql 9.4 / JSONB / JDBC

From

Hans Klett

Date:

01 January 2015, 02:26:07

Correct me if I’m wrong, but streaming a parameter value is impossible unless you know its size beforehand:

http://www.postgresql.org/docs/9.4/static/protocol-message-formats.html

Under “Bind”, which does prepared statement parameter binding:

Next, the following pair of fields appear for each parameter:

Int32: The length of the parameter value, in bytes (this count does not include itself). Can be zero. As a special case, -1 indicates a NULL parameter value. No value bytes follow in the NULL case.
Byten: The value of the parameter, in the format indicated by the associated format code. n is the above length.

I don’t know how you figure out the payload size without encoding the whole thing. Note that array parameters are serialized into a string before being sent over the wire.

Thanks,

- Hans

From: Christopher BROWN <brown@reflexe.fr>
Date: Wednesday, December 31, 2014 at 2:18 PM
To: Mikko Tiihonen <Mikko.Tiihonen@nitorcreations.com>
Cc: Dave Cramer <pg@fastcrypt.com>, List <pgsql-jdbc@postgresql.org>
Subject: Re: [JDBC] Postgresql 9.4 / JSONB / JDBC

Unless there is a requirement to devise a new solution, in the absence of any formal standard, I am aware of two similar specifications:

- http://bsonspec.org/ (for MongoDB)

- http://wiki.fasterxml.com/SmileFormatSpec (for Jackson JSON)

I've used and continue to use the latter and it works well for me.

Does the PostgreSQL client/server protocol allow for protocol upgrades, in a similar way to what HTTP defines for SPDY or websockets? That might address Mikko's remarks about backward compatibility.

How is "hstore" data transmitted? Is it optimized and could JSONB "piggyback" it?

--
Christopher

On 31 December 2014 at 19:29, Mikko Tiihonen <Mikko.Tiihonen@nitorcreations.com> wrote:

Last time I checked (few months ago), the postgresql jsonb binary format was just a extra 4 byte version header followed by text json data. So basically the binary jsonb format was slower than text format. And since there is no official plan on how to modify the binary formats without breaking backwards compatibility it most likely will stay that way.

Having a proper binary encoding for jsonb would speed up parsing on both server and client side.

-Mikko
From:pgsql-jdbc-owner@postgresql.org <pgsql-jdbc-owner@postgresql.org> on behalf of Dave Cramer <pg@fastcrypt.com>
Sent: 29 December 2014 13:42
To: Christopher BROWN
Cc: List
Subject: Re: [JDBC] Postgresql 9.4 / JSONB / JDBC

Yes, we can pull the data from the server in binary. After that though we have to convert it to text

Dave Cramer

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

On 29 December 2014 at 05:08, Christopher BROWN <brown@reflexe.fr> wrote:
Hello,

Regarding my previous messages on this subject (JSONB support in the Postgresql JDBC driver), one assumption I'm making is that the driver might be able to stream the JSON data more efficiently that just throwing a chunk of text onto the wire... as text isn't the most efficient way of encoding numbers, booleans, and nulls.

How does the native network protocol of Postgresql actually transfer such data? Is there an opportunity for sending the data efficiently? Personally for some of my applications, I'm able to use Jackson's "Smile" binary JSON format, although I doubt that the Postgresql server would understand it or want to implement it (I don't know if it could be considered a standard, even if it's specified -- http://wiki.fasterxml.com/SmileFormatSpec).

--
Christopher

On 20 December 2014 at 10:44, Christopher BROWN <brown@reflexe.fr> wrote:
Hello,

I'm new to this list, and have tried searching both the mailing list archives, and internet in general, for information on how to use the new JSONB data type with JDBC. Found almost nothing in the mailing list archives, and internet searches "helpfully" assume I've misspelled "json" and give me results that are unrelated or refer to the basic "JSON" type from older Postgresql versions.

How can this type be used, with simple "flat" key-value collections (maps), with tree-like maps-of-maps, and (ideally) with JSON APIs such as Jackson JSON API?

Is it possible? Is it already described somewhere that I missed?

Thanks,
Christopher