User: Password:
Subscribe / Log in / New account

Why json

Why json

Posted Sep 11, 2012 9:53 UTC (Tue) by intgr (subscriber, #39733)
In reply to: Why json by aristedes
Parent article: PostgreSQL 9.2 released

It's actually multiple things.

> 1. Getting normal SQL columns in and out in json format instead of normal SELECT output?

For one, every complex type can now be represented in JSON and transparently combined with other JSON fragments. Previously, the textual representation of complex types -- especially nested types -- was quite complicated and error-prone to parse by third party code.

# select row_to_json(row('some text', 123.4, null, row(1,2,3), array[1,2,3,4,5], json '{"a":1, "b":{}}' ));
 {"f1":"some text","f2":123.4,"f3":null,"f4":{"f1":1,"f2":2,"f3":3},"f5":[1,2,3,4,5],"f6":{"a":1, "b":{}}}
> 3. Just storing a json blob of data without any new indexing of that data at all.

Even this alone is pretty useful because you have a guarantee that data stored in a "json" field is actually valid JSON

> 2. Storing json blobs of data in a dynamic way without having to predefine which columns (keys) are available and still being able to search with an index on the data attached to those keys

Not quite there yet. It's already possible and useful to index certain json keys when combined with functional indexes (as shown by aristedes).

It's also possible to use an hstore-like GIN/GiST indexing strategy to index arbitrary keys; it's not implemented yet, but only a matter of time before someone writes such an extension.

(Log in to post comments)

Copyright © 2017, Eklektix, Inc.
Comments and public postings are copyrighted by their creators.
Linux is a registered trademark of Linus Torvalds