Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

3 Answers 3

up vote 6 down vote accepted

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 (update)

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

Related answer on SO:

@Will put up blog post. See comment below.

share|improve this answer
1  
Also you can use pl/v8js to manipulate them which gives you some pretty awesome capabilities. –  Chris Travers Sep 29 '12 at 4:08
1  
Yeah, but if you are using a hosted Postgres instance (such as Heroku), PLV8 is probably not an option (certainly isn't on Heroku). In which case, from what I can see, the JSON datatype is of pretty limited value in 9.2. It looks like 9.3 might have some nice support. –  David S Apr 19 '13 at 4:59
1  
A look at the updates coming in 9.3 michael.otacoo.com/postgresql-2/… –  Will Jul 3 '13 at 3:57
add comment

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

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.