What can I do with the new json field?

Question

PostgreSQL 9.2 introduced the json field type. Why and when should I use it? What benefits does it have over a text field?

I thought there were new query options available, however I haven't seen any. Am I missing something?

Answer 1

The benefit of the new feature is two-fold. Columns of type json verify the validity of its content so that what's in the column is automatically valid JSON and you get an error if you try to write anything else to it.
And you have basic functions to create valid JSON on the fly from rows or arrays - which is a very common use-case.

I quote Andrew Dunstan on the pgsql-hackers list:

At some stage there will possibly be some json-processing (as opposed to json-producing) functions, but not in 9.2.

I used that quote before under this related question on SO.

Answer 2

Basically I see three use cases here:

1. pass complex results easily back to the application
2. pass complex data into the db from the application, and
3. Store relatively free-form data for later processing.

The first one can be done directly from PostgreSQL with no addons needed. You should be able to do something like:

SELECT row_to_json(mt.id, mt.testval, array_agg(mt2))
  FROM my_table mt
  JOIN my_table2 mt2 ON mt.id = mt2.mt_id
 WHERE mt.id = 123;

This can then be used to create nested arrays etc. in your output and avoid a lot of messy parsing problems on the app side.

the second is passing complex data into the db for processing. Currently there are no built-in functions to facilitate this, but with addons like pl/v8js, you can program your database in Javascript and use json as an exchange format. This can allow the creation of richer interfaces inside your database. Note that since you can index function outputs, you can use this to create indexes of aspects of JSON to be stored in your db.

The third is one area we plan to use it in LedgerSMB. The idea is that we might want to allow system integrators to store very simple information along with customer accounts. This could then be packed in a JSON field that would be stored. This would not be able to be directly queried by the main apps, but if folks wanted to add this using pl/v8js this could be done for individual businesses using the software.