Thread: JSONB marshalling
Hello, I'm testing out the jsonb functionality on postgres 9.4 and up. I added an extension to sqlalchemy which enables nicer jsonb access through the orm and extends support for all the query types: http://docs.sqlalchemy.org/en/rel_0_9/dialects/postgresql.html?highlight=json#sqlalchemy.dialects.postgresql.JSON The psycopg2 driver doesn't recognise jsonb yet however and results don't get run through json.reads. According to http://schinckel.net/2014/05/24/python,-postgres-and-jsonb/ the oid and typarray are 3802 and 3807 respectively. Are there any plans to add jsonb support to psycopg2, and/or is someone working on this already? Thanks! Damian
On 7/22/14, Damian Dimmich <damian@tauri-tec.com> wrote: > Hello, > > I'm testing out the jsonb functionality on postgres 9.4 and up. I added > an extension to sqlalchemy which enables nicer jsonb access through the > orm and extends support for all the query types: > > http://docs.sqlalchemy.org/en/rel_0_9/dialects/postgresql.html?highlight=json#sqlalchemy.dialects.postgresql.JSON > > The psycopg2 driver doesn't recognise jsonb yet however and results > don't get run through json.reads. > > According to http://schinckel.net/2014/05/24/python,-postgres-and-jsonb/ > the oid and typarray are 3802 and 3807 respectively. > > Are there any plans to add jsonb support to psycopg2, and/or is someone > working on this already? Hello Damian, for what I understand the jsonb type has exactly the same textual representation of the regular json, so it should be enough to call psycopg2.extras.register_json(oid=3802, array_oid=3807, globally=True) to get jsonb support for the entire process. Of course this should be handled by psycopg by default: somebody has found default automatic json unmarshalling questionable for the json type, but now it's in, and I'd find an asymmetric support for jsonb surprising. So yes, I'd add a register_default_jsonb() function (which just calls register_json() with the known oids) and I'd call it initially with the default unmarshall function. When would this happen? I don't think we can say it's a bugfix and it's a change in the adapter behaviour so I think it should be released in a future 2.6, which is still unplanned and I don't see many new features to release. OTOH PG 9.4 has not been released yet and the jsonb oids have never been used in the wild (user-defined types get bigger oids, right?) so if there was interest in it we could consider its release in 2.5.4, which would be released in a much shorter time. Does any opinionated person have an opinion? Thank you -- Daniele
Il giorno 24/lug/2014, alle ore 23:05, Daniele Varrazzo <daniele.varrazzo@gmail.com> ha scritto: > When would this happen? I don't think we can say it's a bugfix and > it's a change in the adapter behaviour so I think it should be > released in a future 2.6, which is still unplanned and I don't see > many new features to release. OTOH PG 9.4 has not been released yet > and the jsonb oids have never been used in the wild (user-defined > types get bigger oids, right?) so if there was interest in it we could > consider its release in 2.5.4, which would be released in a much > shorter time. I second the proposal about a 2.5.4 mini-release, with this feature as it’s possibly sole content. This will permit easy experimentation of the "new" types pretty soon, and avoid burdening the maintainer(s) with long featurelists in the future. Anyway, not sooner than full 9.4 release, as you said — no reason to rush, really. Thanks as always Guido
Hi All - I am not sure that the psycopg2 project is understanding the strategic and tactical significance of JSONb. I will briefly use Lucene / SOLR are a (distant) example story, per my limited perspective: Lucene was invented in the 1990s by Doug Cutting, and has proven to be deeply strong in its text search abilities. Lucene is designed to run in a single Java Virtual Machine. Compute power was added as machines and budgets grew, and it became clear that Lucene could handle millions of documents, sometimes large and dense documents. Among certain users, Lucene use grew, but on the whole, the world continued on, with many many users looking for other solutions. Sometime in the 2000's, a user in San Francisco decided that an internet-ready wrapper would enhance Lucene, and so the SOLR project was begun. SOLR basically provides GET/PUT and admin verbs using http. This long story made short, SOLR is now arguably the "center of gravity" for Lucene. Lucene is a remarkable engine, and now stable, but new features, new users, new advances are almost entirely in terms of SOLR, not Lucene. What does this mean to psycopg2 and PostgreSQL ? PostgreSQL is a core engine like Lucene, and shares many properties described above. However, the internet world of information exchange looked past Postgres to invent REST/http based stores because the way the data moves is as important as the engine, in human terms. When Heroku intervened and guided the JSONB implemenetation in Postgres, they knew that JSONB in core Postgres enables interoperability .. in this story, like SOLR. I submit that JSONB, and following, Psycopg2 with JSONB support, will grow and grow. I say to the psycopg2 project, this is not a point-point release update with a sidenote, but rather a very strategic feature that ought to be fully support, documented and presented to all psycopg2 developers. I will leave the current status of psycopg2 in the python stack to be described by others that are more capable of that than I. agree, disagree or indifferent, psycopg2 would be missing a major opportunity if the project does not fully embrace and present JSONB in PostgreSQL. best regards from Berkeley, California Brian M Hamlin OSGeo California Chapter On Fri, 25 Jul 2014 18:01:15 +0200, Guido Notari <gnotari@linkgroup.it> wrote: Il giorno 24/lug/2014, alle ore 23:05, Daniele Varrazzo <daniele.varrazzo@gmail.com> ha scritto: > > > When would this happen? I don't think we can say it's a bugfix and > > it's a change in the adapter behaviour so I think it should be > > released in a future 2.6, which is still unplanned and I don't see > > many new features to release. OTOH PG 9.4 has not been released yet > > and the jsonb oids have never been used in the wild (user-defined > > types get bigger oids, right?) so if there was interest in it we could > > consider its release in 2.5.4, which would be released in a much > > shorter time. > > I second the proposal about a 2.5.4 mini-release, with this feature > as it’s possibly sole content. > This will permit easy experimentation of the "new" types pretty soon, > and avoid burdening the maintainer(s) with long feature lists in the > future. > Anyway, not sooner than full 9.4 release, as you said — no reason > to rush, really. > > Thanks as always > Guido > > -- Sent via psycopg mailing list (psycopg@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/psycopg > >
On 25/07/14 18:29, maplabs@light42.com wrote: [Out of topic and subjective stuff removed] > agree, disagree or indifferent, psycopg2 would be missing a major > opportunity if the project does not > fully embrace and present JSONB in PostgreSQL. Have you read Daniele email? psycopg2 supports jsonb RIGHT NOW. You only have to register the ALREADY EXISTING type caster/adapter and you're done. What Daniele was asking is if someone has something against enabling it as default in the next point release. Given that json type caster/adapter is already enabled I don't see any reason to not register jsonb one too. federico -- Federico Di Gregorio federico.digregorio@dndg.it Di Nunzio & Di Gregorio srl http://dndg.it A desobediência é uma virtude necessária à criatividade. -- Raul Seixas
On Fri, Jul 25, 2014 at 5:40 PM, Federico Di Gregorio <fog@dndg.it> wrote: > What Daniele was asking is if someone has something against enabling it > as default in the next point release. Given that json type > caster/adapter is already enabled I don't see any reason to not register > jsonb one too. After reading this SO question <http://stackoverflow.com/questions/25263736/jsonb-with-psycopg2-realdictcursor> I'm thinking that users expect jsonb to work exactly like json does. Our docs say that json is automatically converted into python objects since psycopg 2.5 <http://initd.org/psycopg/docs/extras.html#json-adaptation> so I think we won't get many complaints if starting from 2.5.4 we supported jsonb the same way. Only behavioural change would be noticed by users running an older psycopg2 version with a still-to-be-released Postgres version, which is an odd combination at best, unlikely to be found in the wild. it would be a better scenario if people upgrading their schemas from json to jsonb found their default psycopg installation not changing behaviour with the different data type. So, if nobody shouts in disagreement, there will be a 2.5.4 released in a few days/couple of weeks with a handful of secondary bugfixes and jsonb support baked in. -- Daniele
On Aug 12, 2014, at 2:23 PM, Daniele Varrazzo <daniele.varrazzo@gmail.com> wrote: > After reading this SO question > <http://stackoverflow.com/questions/25263736/jsonb-with-psycopg2-realdictcursor> > I'm thinking that users expect jsonb to work exactly like json does. > Our docs say that json is automatically converted into python objects > since psycopg 2.5 +1. > So, if nobody shouts in disagreement, there will be a 2.5.4 released > in a few days/couple of weeks with a handful of secondary bugfixes and > jsonb support baked in. +2! :) -- -- Christophe Pettus xof@thebuild.com