I'm with you, this smells of feature creep. I don't see a big benefit over serializing the result set on the client. This is literally a one-liner if you're using an ORM or something like PEP 249.
The new constraint allowing you to ensure the string stored is valid JSON sounds useful though.
There are good reasons to have JSON support in the DBMS, such as querying and indexing on the fields within a JSON document.
My only criticism is that postgres has a great extensions mechanism, and it would be better if effort were spent improving the ecosystem around that. I can't think of any reason for it to be in core if installing an extension were a trivial and widely accepted practice.
But, it takes time to really develop that ecosystem. And there are users that want JSON support yesterday.
EDIT: from a technological standpoint, there is no reason why it can't exist as an extension. PostGIS is much more sophisticated on all counts, and it's an extension.
I agree with you insomuch as it's ideal for an extension, and argued for its inclusion as a 9.3 feature (looks like Christmas might come early, though). However, I concluded with this:
Right now the perception of Postgres...actually, databases in general,
including virtually all of the newcomers -- is that they are
monolithic systems, and for most people either "9.3" will "have"
javascript and indexing of JSON documents, or it won't. In most cases
I would say "meh, let them eat cake until extensions become so
apparently dominant that we can wave someone aside to extension-land",
but in this case I think that would be a strategic mistake.
So this is definitely a social problem. Why not move UUID out of the core? (not uuid-ossp, which implements generation, but uuid parsing and storage) Because, for now, one cannot assume extensions for really common useful functionality.
Luckily, I think things are on the right path to at a future juncture that even a commonly desired data type can live as an extension.
The new constraint allowing you to ensure the string stored is valid JSON sounds useful though.