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?
The benefit of the new feature is two-fold. Columns of type
I used that quote before under this related question on SO.
.. 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.
Be sure to check out the new
Above all, the decomposed, binary storage allows for smaller storage on disk and an equality operator for
Yet more functions have been added for both
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
Basically I see three use cases here:
The first one can be done directly from PostgreSQL with no addons needed. You should be able to do something like:
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 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.