[ANN] JSON datatype support in PostgresV2 package
Spurred by the last discussion about GLORP, Postgres and friends, I spent the last few hours looking into the workings of PostgresV2 to add support to the native JSON datatype provided by PostgreSQL 9.2. According to PostgreSQL docs [1]: "The json data type can be used to store JSON (JavaScript Object Notation) data, as specified in RFC 4627. Such data can also be stored as text, but the json data type has the advantage of checking that each stored value is a valid JSON value. There are also related support functions available; see Section 9.15." It is available at ConfigurationOfPostgresV2-EstebanMaringolo.12 in SmalltalkHub, blessed as #development (v2.4). It includes a testcase for the field converter (if the database server supports it). JSON datatype allows the indexing and querying of data inside the structure of the json object. Eg: SELECT jsonField->'attribute1'->'attribute2' FROM sampleTable; Adding support of this to GLORP should be easy, but I have no time now :) DISCLAIMER: I'm not using this in production, I tested it and seems to work as expected, and fail nosiliy when it should. This is why I blesed it as #development. If anybody is bold enough, bless it as #stable :) Best regards, Esteban A. Maringolo [1] http://www.postgresql.org/docs/9.2/static/datatype-json.html
Oh, I forgot. The datatype fields are converted to instances of JsonObject, from the PharoExtras/JSON package. So the version 2.4 loads such package. Even though I don't use PharoExtras/JSON for my daily JSON manipulation (I use Seaside-JSON and NeoJSON), I found it to be the most "modular"/"independent" JSON package out there. The fieldConverter can be replaced "live" in the PGConnection object by anything you like. Regards! Esteban A. Maringolo 2014-09-04 16:34 GMT-03:00 Esteban A. Maringolo <emaringolo@gmail.com>:
Spurred by the last discussion about GLORP, Postgres and friends, I spent the last few hours looking into the workings of PostgresV2 to add support to the native JSON datatype provided by PostgreSQL 9.2.
According to PostgreSQL docs [1]: "The json data type can be used to store JSON (JavaScript Object Notation) data, as specified in RFC 4627. Such data can also be stored as text, but the json data type has the advantage of checking that each stored value is a valid JSON value. There are also related support functions available; see Section 9.15."
It is available at ConfigurationOfPostgresV2-EstebanMaringolo.12 in SmalltalkHub, blessed as #development (v2.4). It includes a testcase for the field converter (if the database server supports it).
JSON datatype allows the indexing and querying of data inside the structure of the json object.
Eg: SELECT jsonField->'attribute1'->'attribute2' FROM sampleTable;
Adding support of this to GLORP should be easy, but I have no time now :)
DISCLAIMER: I'm not using this in production, I tested it and seems to work as expected, and fail nosiliy when it should. This is why I blesed it as #development. If anybody is bold enough, bless it as #stable :)
Best regards,
Esteban A. Maringolo
[1] http://www.postgresql.org/docs/9.2/static/datatype-json.html
Am 04.09.2014 um 21:43 schrieb "Esteban A. Maringolo" <emaringolo@gmail.com>:
Oh, I forgot.
The datatype fields are converted to instances of JsonObject, from the PharoExtras/JSON package. So the version 2.4 loads such package.
Even though I don't use PharoExtras/JSON for my daily JSON manipulation (I use Seaside-JSON and NeoJSON), I found it to be the most "modular"/"independent" JSON package out there.
How about making the JSON generation pluggable? Norbert
The fieldConverter can be replaced "live" in the PGConnection object by anything you like.
Regards!
Esteban A. Maringolo
2014-09-04 16:34 GMT-03:00 Esteban A. Maringolo <emaringolo@gmail.com>:
Spurred by the last discussion about GLORP, Postgres and friends, I spent the last few hours looking into the workings of PostgresV2 to add support to the native JSON datatype provided by PostgreSQL 9.2.
According to PostgreSQL docs [1]: "The json data type can be used to store JSON (JavaScript Object Notation) data, as specified in RFC 4627. Such data can also be stored as text, but the json data type has the advantage of checking that each stored value is a valid JSON value. There are also related support functions available; see Section 9.15."
It is available at ConfigurationOfPostgresV2-EstebanMaringolo.12 in SmalltalkHub, blessed as #development (v2.4). It includes a testcase for the field converter (if the database server supports it).
JSON datatype allows the indexing and querying of data inside the structure of the json object.
Eg: SELECT jsonField->'attribute1'->'attribute2' FROM sampleTable;
Adding support of this to GLORP should be easy, but I have no time now :)
DISCLAIMER: I'm not using this in production, I tested it and seems to work as expected, and fail nosiliy when it should. This is why I blesed it as #development. If anybody is bold enough, bless it as #stable :)
Best regards,
Esteban A. Maringolo
[1] http://www.postgresql.org/docs/9.2/static/datatype-json.html
That's pretty cool ! +1 Norbert On Fri, Sep 5, 2014 at 6:56 AM, Norbert Hartl <norbert@hartl.name> wrote:
Am 04.09.2014 um 21:43 schrieb "Esteban A. Maringolo" < emaringolo@gmail.com>:
Oh, I forgot.
The datatype fields are converted to instances of JsonObject, from the PharoExtras/JSON package. So the version 2.4 loads such package.
Even though I don't use PharoExtras/JSON for my daily JSON manipulation (I use Seaside-JSON and NeoJSON), I found it to be the most "modular"/"independent" JSON package out there.
How about making the JSON generation pluggable?
Norbert
The fieldConverter can be replaced "live" in the PGConnection object by anything you like.
Regards!
Esteban A. Maringolo
2014-09-04 16:34 GMT-03:00 Esteban A. Maringolo <emaringolo@gmail.com>:
Spurred by the last discussion about GLORP, Postgres and friends, I spent the last few hours looking into the workings of PostgresV2 to add support to the native JSON datatype provided by PostgreSQL 9.2.
According to PostgreSQL docs [1]: "The json data type can be used to store JSON (JavaScript Object Notation) data, as specified in RFC 4627. Such data can also be stored as text, but the json data type has the advantage of checking that each stored value is a valid JSON value. There are also related support functions available; see Section 9.15."
It is available at ConfigurationOfPostgresV2-EstebanMaringolo.12 in SmalltalkHub, blessed as #development (v2.4). It includes a testcase for the field converter (if the database server supports it).
JSON datatype allows the indexing and querying of data inside the structure of the json object.
Eg: SELECT jsonField->'attribute1'->'attribute2' FROM sampleTable;
Adding support of this to GLORP should be easy, but I have no time now :)
DISCLAIMER: I'm not using this in production, I tested it and seems to work as expected, and fail nosiliy when it should. This is why I blesed it as #development. If anybody is bold enough, bless it as #stable :)
Best regards,
Esteban A. Maringolo
[1] http://www.postgresql.org/docs/9.2/static/datatype-json.html
Thank you! Any contribution in this direction is important. Doru On Thu, Sep 4, 2014 at 9:43 PM, Esteban A. Maringolo <emaringolo@gmail.com> wrote:
Oh, I forgot.
The datatype fields are converted to instances of JsonObject, from the PharoExtras/JSON package. So the version 2.4 loads such package.
Even though I don't use PharoExtras/JSON for my daily JSON manipulation (I use Seaside-JSON and NeoJSON), I found it to be the most "modular"/"independent" JSON package out there.
The fieldConverter can be replaced "live" in the PGConnection object by anything you like.
Regards!
Esteban A. Maringolo
2014-09-04 16:34 GMT-03:00 Esteban A. Maringolo <emaringolo@gmail.com>:
Spurred by the last discussion about GLORP, Postgres and friends, I spent the last few hours looking into the workings of PostgresV2 to add support to the native JSON datatype provided by PostgreSQL 9.2.
According to PostgreSQL docs [1]: "The json data type can be used to store JSON (JavaScript Object Notation) data, as specified in RFC 4627. Such data can also be stored as text, but the json data type has the advantage of checking that each stored value is a valid JSON value. There are also related support functions available; see Section 9.15."
It is available at ConfigurationOfPostgresV2-EstebanMaringolo.12 in SmalltalkHub, blessed as #development (v2.4). It includes a testcase for the field converter (if the database server supports it).
JSON datatype allows the indexing and querying of data inside the structure of the json object.
Eg: SELECT jsonField->'attribute1'->'attribute2' FROM sampleTable;
Adding support of this to GLORP should be easy, but I have no time now :)
DISCLAIMER: I'm not using this in production, I tested it and seems to work as expected, and fail nosiliy when it should. This is why I blesed it as #development. If anybody is bold enough, bless it as #stable :)
Best regards,
Esteban A. Maringolo
[1] http://www.postgresql.org/docs/9.2/static/datatype-json.html
-- www.tudorgirba.com "Every thing has its own flow"
On 04 Sep 2014, at 21:43, Esteban A. Maringolo <emaringolo@gmail.com> wrote:
Even though I don't use PharoExtras/JSON for my daily JSON manipulation (I use Seaside-JSON and NeoJSON), I found it to be the most "modular"/"independent" JSON package out there.
I don't see how the JSON and NeoJSON package differ in that respect. NeoJSON has no dependencies. Could you explain this point ?
2014-09-05 4:18 GMT-03:00 Sven Van Caekenberghe <sven@stfx.eu>:
On 04 Sep 2014, at 21:43, Esteban A. Maringolo <emaringolo@gmail.com> wrote:
Even though I don't use PharoExtras/JSON for my daily JSON manipulation (I use Seaside-JSON and NeoJSON), I found it to be the most "modular"/"independent" JSON package out there.
I don't see how the JSON and NeoJSON package differ in that respect. NeoJSON has no dependencies. Could you explain this point ?
My Bad, I didn't mean exactly that. When I wrote the above paragraph I thought NeoJSON required the specification of a schema and couldn't simply convert JSON strings to Dictionaries. The only thing I like about PharoExtras/JSON is the fact JsonObject allows its direct manipulation without having to use dictionary accessors, but still allowing for it (because JsonObject IS a Dictionary). We can perfectly replace it by NeoJSON. Mine was a proof of concept, which once I debugged through a lot types of PGPacket ended up being simpler than thought. Supporting parametrized statements with JSON is something I would like to check (I guess it is only text). Adding support to jsonb type is linear too on the client side, just replicate what I did for json. I did a quick benchmark comparing JSON parsers and NeoJSON gets slightly faster as the stream gets larger. I didn't profile them memorywise. 31bytes JSON: NeoJSON: 73,600 per second. JSON: 42,700 per second. WAJsonParser: 79,400 per second. 309bytes JSON: NeoJSON: 14,200 per second. JSON: 12,400 per second. WAJsonParser: 13,900 per second. 17215bytes JSON: NeoJSON: 277 per second. JSON: 194 per second. WAJsonParser: 255 per second. https://gist.github.com/eMaringolo/5e7c865188036faa7202#file-json-bench
As a side note to this discussion: Postgres Outperforms MongoDB and Ushers in New Developer Reality http://blogs.enterprisedb.com/2014/09/24/postgres-outperforms-mongodb-and-us... Maybe there could be a VoyagePostgresql alternative for anyone insterested ;-) Regards! Esteban A. Maringolo 2014-09-05 10:23 GMT-03:00 Esteban A. Maringolo <emaringolo@gmail.com>:
2014-09-05 4:18 GMT-03:00 Sven Van Caekenberghe <sven@stfx.eu>:
On 04 Sep 2014, at 21:43, Esteban A. Maringolo <emaringolo@gmail.com> wrote:
Even though I don't use PharoExtras/JSON for my daily JSON manipulation (I use Seaside-JSON and NeoJSON), I found it to be the most "modular"/"independent" JSON package out there.
I don't see how the JSON and NeoJSON package differ in that respect. NeoJSON has no dependencies. Could you explain this point ?
My Bad, I didn't mean exactly that. When I wrote the above paragraph I thought NeoJSON required the specification of a schema and couldn't simply convert JSON strings to Dictionaries.
The only thing I like about PharoExtras/JSON is the fact JsonObject allows its direct manipulation without having to use dictionary accessors, but still allowing for it (because JsonObject IS a Dictionary).
We can perfectly replace it by NeoJSON. Mine was a proof of concept, which once I debugged through a lot types of PGPacket ended up being simpler than thought. Supporting parametrized statements with JSON is something I would like to check (I guess it is only text). Adding support to jsonb type is linear too on the client side, just replicate what I did for json.
I did a quick benchmark comparing JSON parsers and NeoJSON gets slightly faster as the stream gets larger. I didn't profile them memorywise.
31bytes JSON: NeoJSON: 73,600 per second. JSON: 42,700 per second. WAJsonParser: 79,400 per second.
309bytes JSON: NeoJSON: 14,200 per second. JSON: 12,400 per second. WAJsonParser: 13,900 per second.
17215bytes JSON: NeoJSON: 277 per second. JSON: 194 per second. WAJsonParser: 255 per second.
https://gist.github.com/eMaringolo/5e7c865188036faa7202#file-json-bench
On 30/9/14 21:02, Esteban A. Maringolo wrote:
As a side note to this discussion:
Postgres Outperforms MongoDB and Ushers in New Developer Reality http://blogs.enterprisedb.com/2014/09/24/postgres-outperforms-mongodb-and-us...
Maybe there could be a VoyagePostgresql alternative for anyone insterested ;-)
I would love now Voyage looks more for noSQL. Did you check ROE for RDB? Avi used it as a way to compose queries. Stef
Regards!
Esteban A. Maringolo
2014-09-05 10:23 GMT-03:00 Esteban A. Maringolo <emaringolo@gmail.com>:
2014-09-05 4:18 GMT-03:00 Sven Van Caekenberghe <sven@stfx.eu>:
On 04 Sep 2014, at 21:43, Esteban A. Maringolo <emaringolo@gmail.com> wrote:
Even though I don't use PharoExtras/JSON for my daily JSON manipulation (I use Seaside-JSON and NeoJSON), I found it to be the most "modular"/"independent" JSON package out there. I don't see how the JSON and NeoJSON package differ in that respect. NeoJSON has no dependencies. Could you explain this point ? My Bad, I didn't mean exactly that. When I wrote the above paragraph I thought NeoJSON required the specification of a schema and couldn't simply convert JSON strings to Dictionaries.
The only thing I like about PharoExtras/JSON is the fact JsonObject allows its direct manipulation without having to use dictionary accessors, but still allowing for it (because JsonObject IS a Dictionary).
We can perfectly replace it by NeoJSON. Mine was a proof of concept, which once I debugged through a lot types of PGPacket ended up being simpler than thought. Supporting parametrized statements with JSON is something I would like to check (I guess it is only text). Adding support to jsonb type is linear too on the client side, just replicate what I did for json.
I did a quick benchmark comparing JSON parsers and NeoJSON gets slightly faster as the stream gets larger. I didn't profile them memorywise.
31bytes JSON: NeoJSON: 73,600 per second. JSON: 42,700 per second. WAJsonParser: 79,400 per second.
309bytes JSON: NeoJSON: 14,200 per second. JSON: 12,400 per second. WAJsonParser: 13,900 per second.
17215bytes JSON: NeoJSON: 277 per second. JSON: 194 per second. WAJsonParser: 255 per second.
https://gist.github.com/eMaringolo/5e7c865188036faa7202#file-json-bench
2014-10-03 17:22 GMT-03:00 stepharo <stepharo@free.fr>:
On 30/9/14 21:02, Esteban A. Maringolo wrote:
As a side note to this discussion:
Postgres Outperforms MongoDB and Ushers in New Developer Reality
http://blogs.enterprisedb.com/2014/09/24/postgres-outperforms-mongodb-and-us...
Maybe there could be a VoyagePostgresql alternative for anyone insterested ;-)
I would love now Voyage looks more for noSQL.
IMHO NoSQL hype has already peaked, now it is settling at a more reasonable usage: a) document oriented, b) schemaless data, c) easy horizontal scaling. With PgSQL JSON you can achieve a) and b) pretty easily, having a single server if you already have data in it :)
Did you check ROE for RDB? Avi used it as a way to compose queries.
It is the first time I hear about it. It isn't listed in SmalltalkHub. Regards! Esteban A. Maringolo
Great ! On 04 Sep 2014, at 21:34, Esteban A. Maringolo <emaringolo@gmail.com> wrote:
Spurred by the last discussion about GLORP, Postgres and friends, I spent the last few hours looking into the workings of PostgresV2 to add support to the native JSON datatype provided by PostgreSQL 9.2.
According to PostgreSQL docs [1]: "The json data type can be used to store JSON (JavaScript Object Notation) data, as specified in RFC 4627. Such data can also be stored as text, but the json data type has the advantage of checking that each stored value is a valid JSON value. There are also related support functions available; see Section 9.15."
It is available at ConfigurationOfPostgresV2-EstebanMaringolo.12 in SmalltalkHub, blessed as #development (v2.4). It includes a testcase for the field converter (if the database server supports it).
JSON datatype allows the indexing and querying of data inside the structure of the json object.
Eg: SELECT jsonField->'attribute1'->'attribute2' FROM sampleTable;
Adding support of this to GLORP should be easy, but I have no time now :)
DISCLAIMER: I'm not using this in production, I tested it and seems to work as expected, and fail nosiliy when it should. This is why I blesed it as #development. If anybody is bold enough, bless it as #stable :)
Best regards,
Esteban A. Maringolo
[1] http://www.postgresql.org/docs/9.2/static/datatype-json.html
participants (6)
-
Esteban A. Maringolo -
François Stephany -
Norbert Hartl -
stepharo -
Sven Van Caekenberghe -
Tudor Girba