PostgreSQL-9.2 JSON datatype

[Priit Laes]

PostgreSQL 9.2 gained a new datatype - JSON + some extra goodies like array_to_json and row_to_json.

It is available since psycopg-2.4.6 and enabled by default on pgsql-9.2+ (with older versions one needs to register json extension).

I'm not sure whether it makes sense to have an all-around JSON column type that defaults to either Text or VARCHAR or make it currently just pgsql specific. Though there's already JSONcolumn snippet + JSONEncodedDict sample in the docs.

Also, hstore + JSON looks like an ideal match for storing partially relational data. Mainly because hstore by itself only allows string data or NULL in values.

[Mike Bayer]

we would look to integrate the hstore extension here: https://bitbucket.org/zzzeek/hstore_sqla_08 and additionally add the JSON type using similar techniques.

[Mike Bayer]

also this would be PG specific for now. i don't see such a type happening on other platforms very soon.

[aaditya sood]

I'm interested in this as well.

[Former user]

(original author: foobar)

[Former user]

(original author: foobar)

[Former user]

(original author: foobar)

[Former user]

(original author: foobar)

[aaditya sood]

Any news :)

[Former user]

What is the timeline for this? The milestone clearly needs to be updated.

PostgreSQL 9.3 released in September has added new JSON operators that make this field extremely powerful.

However, at this stage even some partial support (simple dumping and loading) would be interesting; especially if – as it looks now – support for all the enhanced query operators will take more time or not be introduced at all.

[Mike Bayer]

Replying to lyschoening:

What is the timeline for this?

JSON is relatively low priority for me to implement solely, as this is one that anyone can contribute - you can have the feature within days via pull request, as mentioner earlier, JSON support will look very similar to hstore and perhaps range - both of which were contributed by motivated individuals.

The milestone clearly needs to be updated.

done!

[Mike Bayer]

some notes, the HSTORE type goes through the effort to provide Python-level parsers as well as support for psycopg2's range plugin - for most users the pure-python HSTORE serializers are not used. The RANGE type OTOH I think took a simplistic approach and just assumes the DBAPI handling of the data in all cases (to that extent, it probably doesn't work on DBAPIs other than psycopg2).

for JSON, it would be best if we typically make use of whatever extensions psycopg2 provides (I'm sure they have JSON support now), with a fallback available for when the plugin isn't available. tests will be needed as well.

[Mike Bayer]

see pullreq: https://github.com/zzzeek/sqlalchemy/pull/50/

additional changes: 73013914e7eae2a0480492ece08

[Mike Bayer]

moved the serializer/deserializer arguments up to the dialect level, as psycopg2 isn't compatible with a per-expression deserailizer. fec03c88d659bf9a0b102dd328afac

[Mike Bayer]

Removing milestone: 0.9.0 (automated comment)