Обсуждение: Postgresql 9.4 / JSONB / JDBC
Christopher
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
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 ?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
This message was drafted on a tiny touch screen; please forgive brevity & tpyos
Dave,I've only just started looking at how JSONB works in Postgresql, and am mainly basing ideas on what I can see here: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,ChristopherOn 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 ?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
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.
Alexis Meneses
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 ?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
Dave,I've only just started looking at how JSONB works in Postgresql, and am mainly basing ideas on what I can see here: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,ChristopherOn 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 ?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
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
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 ?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
Christopher
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,- HansFrom: 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 / JDBCDave,I've only just started looking at how JSONB works in Postgresql, and am mainly basing ideas on what I can see here: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,ChristopherOn 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 ?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
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.
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.
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.
Christopher
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.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.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.Alexis2014-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: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,ChristopherOn 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 ?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
Christopher
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
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).--
ChristopherOn 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
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
Sent: 29 December 2014 13:42
To: Christopher BROWN
Cc: List
Subject: Re: [JDBC] Postgresql 9.4 / JSONB / JDBC
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).--
ChristopherOn 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
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 / JDBCYes, we can pull the data from the server in binary. After that though we have to convert it to textOn 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).--
ChristopherOn 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
I just checked with Andrew Dunstan, and apparently this is true for JSON, but not JSONBDaveOn 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 / JDBCYes, we can pull the data from the server in binary. After that though we have to convert it to textOn 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).--
ChristopherOn 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
Christopher
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 / JDBCYes, we can pull the data from the server in binary. After that though we have to convert it to textOn 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).--
ChristopherOn 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
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?
--
ChristopherOn 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 / JDBCYes, we can pull the data from the server in binary. After that though we have to convert it to textOn 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).--
ChristopherOn 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
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.
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
Christopher
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 / JDBCYes, we can pull the data from the server in binary. After that though we have to convert it to textOn 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).--
ChristopherOn 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