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

Postgres 9.2

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.

Postgres 9.3

.. finally brings a number of functions and operators. Check out the manual page for JSON functions.

Related answer on SO:

• How to query for a specific element of json field.
• How to support that with an index.

@Will put up blog post. See comment below.

Postgres 9.4

Be sure to check out the new jsonb type with a host of new functionality.

Above all, the decomposed, binary storage allows for smaller storage on disk and an equality operator for jsonb (unlike json), which makes a number of additional operations possible (like DISTINCT or a UNIQUE index).

Yet more functions have been added for both json and jsonb. json_to_record(), json_to_recordset() etc. More in the release notes.

Answer 2

In a nutshell, the JSON datatype (and the older HSTORE extension and datatype as well) allow you to use PostgreSQL as a "schema-less" data store (or combine relational and non-relational "schema-less" data), instead of having to resort to some of the other NoSQL options (such as MongoDB). You even gain some things that you can't do with MongoDB, like filtered indexing, expression indexing, etc. The only drawback is that PostgreSQL doesn't support sharding out-of-the-box like MongoDB does...however, I really question how frequently sharding is truly needed. With a well-configured PostgreSQL 9.3 database, sufficient O/S resources, and some reasonably well thought-out filtered expression indexes, you should easily be able to achieve schema-less row retrievals in the 0.25 millisecond range.

HTH, Dave Sisk

Answer 3

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.