Re: JSON for PG 9.2

On Mon, Dec 5, 2011 at 3:12 PM, Bruce Momjian <bruce@momjian.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
thatcondenses 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