Re: JSON for PG 9.2

On Mon, Dec 5, 2011 at 3:12 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> Where are we with adding JSON for Postgres 9.2?  We got bogged down in
> the data representation last time we discussed this.

We should probably have a wiki page titled "JSON datatype status" to
help break the cycle we're in:

* Someone asks about the status of JSON

* Various ideas are suggested

* Patches are posted (maybe)

* More discussion about fundamental issues ensues

* Nothing is accomplished (as far as adding JSON to Postgres core)

There are several JSON implementations for Postgres floating around, including:

* http://pgxn.org/dist/pg-json/ : Mentioned in previous posts; a JSON
library based on Jansson supporting path subscript and equality
testing

* http://git.postgresql.org/gitweb/?p=json-datatype.git;a=summary :
The JSON datatype I implemented for Google Summer of Code 2010. It
has the most features of any implementation I'm aware of, but:

* Is in the form of a contrib module

* Preserves input text verbatim, a guarantee that will be broken
by more efficient implementations

* http://git.postgresql.org/gitweb/?p=json-datatype.git;a=shortlog;h=refs/heads/json2
: My rewrite of the JSON module that condenses input (but still
stores it as text) and addresses the issue of JSON when either the
server or client encoding is not UTF-8. Needs more features and
documentation, but like my other implementation, may not be quite what
we want.

Issues we've encountered include:

* Should JSON be stored as binary or as text?

* How do we deal with Unicode escapes and characters if the server or
client encoding is not UTF-8? Some (common!) character encodings have
code points that don't map to Unicode. Also, the charset conversion
modules do not provide fast entry points for converting individual
characters; each conversion involves a funcapi call.

---

In an application I'm working on, I store JSON-encoded objects in a
PostgreSQL database (using TEXT). I do so because it allows me to
store non-relational data that is easy for my JavaScript code to work
with.

However, I fail to see much benefit of a JSON type. When I need to
work with the data in PHP, C, or Haskell, I use JSON parsing libraries
available in each programming language. Although being able to
transform or convert JSON data within SQL might be convenient, I can't
think of any compelling reason to do it in my case.

Can someone clarify why a JSON type would be useful, beyond storage
and validation? What is a real-world, *concrete* example of a problem
where JSON manipulation in the database would be much better than:

* Using the application's programming language to manipulate the data
(which it does a lot already) ?

* Using CouchDB or similar instead of PostgreSQL?

- Joey