JSON will be a core type in PostgreSQL 9.2

This is nice, but it's a shame that after going to the trouble of writing a JSON parser in C and incorporating it into the main Postgres codebase, they didn't go one step further and add a json_project_key function that pulls values out of a JSON blob. That, combined with functional indices, allows you to store JSON blobs in your database <i>and</i> index on values inside them, or put another way, get the data model benefits of a NoSQL database [1] without sacrificing the mature Postgres internals.<p>The Heroku Postgres guys have been playing with this idea [2] using the PL/V8 plugin, which embeds Javascript as a supported language inside Postgres (and thus makes it trivial to implement the json_project_key function), but if Postgres is going to natively support JSON parsing then it shouldn't take an addon module to achieve this.<p>[1] Attempting to forestall the thread-jacking: I know NoSQL databases have other benefits besides their data model, but for some applications that's certainly <i>one of</i> the benefits. [2] <a href="https://gist.github.com/1150804" rel="nofollow">https://gist.github.com/1150804</a>

Interesting. Being able to output the results of a query as JSON will be useful when running something like Node.js. I can't shake my feeling that something like this doesn't belong in the SQL statement, though- choice of output format seems like it's something connecting libraries ought to do.<p>That said, having seen 'core type' I instantly imagined being able to query based on JSON properties, which doesn't appear to be the case. Not surprising, because it would be a huge amount of work.. but it's nice to imagine.<p>(before anyone says anything- yes, I know NoSQL exists. But a hybrid solution using Postgres would be very interesting)

For the record, the title is misleading and Andrew Dunstan (the guy whose blog this entry links to, a major PostgreSQL contributor and one of the people involved in having a JSON type in Postgres) clarifies:<p><a href="http://people.planetpostgresql.org/andrew/index.php?/archives/245-I-didnt-say-that.html" rel="nofollow">http://people.planetpostgresql.org/andrew/index.php?/archive...</a>

My first reaction to this is - "Ugh. Haven't we been down this road with XML?" I don't want the format du jour baked into my datastore. Now, if it had a field that stored some sort of abstract lists-and-hashes structure (sort of redis-in-a-field), that sounds more interesting to me. I realize JSON is isomorphic to just that, but it still seems like flavor of the week.

Pretty neat. I think this is the important bit though: "Basically, his patch just parses the text to make sure it was valid JSON, and stores it as text."<p>So, if you already have an environment where you are certain you are inserting valid JSON, it's not much different than just using the text type today.

I think it's worth mentioning that MySQL has a nice collection of UDF's to handle JSON object output, arrays, and so on: <a href="http://www.mysqludf.org/lib_mysqludf_json/index.php" rel="nofollow">http://www.mysqludf.org/lib_mysqludf_json/index.php</a>

it would be super awesome if JSONH <a href="https://github.com/WebReflection/JSONH" rel="nofollow">https://github.com/WebReflection/JSONH</a> (json for homogenous collections) was standardized and adopted. it would be a great csv replacement and a perfect fit for recordsets, providing minimal overhead.

Erm... JSON is pure text - so not much of an innovation in truth - I can store it in SQLight right now.