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?
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 only takes a minute to sign up.Sign up to join this community
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.
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.
.. 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
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
json), which makes a number of additional operations possible (like
DISTINCT or a
Yet more functions have been added for both
json_to_recordset() etc. More in the release notes.
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:
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 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.