31 ms·

As of ver 9.4, PostgreSQL support storing data in JSON format. Which it is nothing but 'text' datatype with additional syntax check built-in. You can't do a que

by reactor 13y ago

As of ver 9.4, PostgreSQL support storing data in JSON format. Which it is nothing but 'text' datatype with additional syntax check built-in. You can't do a query or do update partial/selected node inside the JSON doc. But with this new jsonb (JSON Binary) format, you can do simple query as well as update certain node (if it can reach through the query). Think it like a very basic "MongoDB" implementation :) Take a look at their devel doc http://www.postgresql.org/docs/devel/static/datatype-json.html http://www.postgresql.org/docs/devel/static/datatype-json.ht... for more details.

pilif 13y ago
just nitpicking, but the json data type was added with 9.2 where it was not much more than syntax checked text plus one or two functions to convert from records and arrays into json. In 9.3 we got many more useful functions that allow to query into json documents, though if you wanted to select rows based on the content of a json document, you'd have to resort to functional indexes. 9.4 (which is what the above post is talking about) will provide index support for arbitrary queries and a much more efficient format to store JSON data which will not involve reparsing it whenever you use any of the above functions.
- gdulli 13y ago
  You don't need an index to select rows based on the contents of a json document in 9.3. You may want or need them depending on your queries. (I use them sparingly, the most common pattern for me is that an indexed non-json column filters a query down to a smaller set of rows, and then a further index on the attributes referenced in the query isn't necessary.) And if you do want to create an index now on a JSON attribute in 9.3, a function isn't necessary.
- hackerboos 13y ago
  Am I right in thinking this is only good for reads though? Do writes still require loading the field into memory, parsing, updating and the writing back?
  - pilif 13y ago
    Yes. You are right. At the moment there's no exposed functions for altering that binary data structure. Because of the way how Postgres works though, the row will always have to be rewritten in the future (all updates to a row will cause a new copy to be written - rows are immutable in Postgres). What we might gain in the future is a way to skip the parsing process, but the document will always have to be rewritten.

by reactor 13y ago

pilif 13y ago

just nitpicking, but the json data type was added with 9.2 where it was not much more than syntax checked text plus one or two functions to convert from records and arrays into json. In 9.3 we got many more useful functions that allow to query into json documents, though if you wanted to select rows based on the content of a json document, you'd have to resort to functional indexes. 9.4 (which is what the above post is talking about) will provide index support for arbitrary queries and a much more efficient format to store JSON data which will not involve reparsing it whenever you use any of the above functions.

gdulli 13y ago
You don't need an index to select rows based on the contents of a json document in 9.3. You may want or need them depending on your queries. (I use them sparingly, the most common pattern for me is that an indexed non-json column filters a query down to a smaller set of rows, and then a further index on the attributes referenced in the query isn't necessary.) And if you do want to create an index now on a JSON attribute in 9.3, a function isn't necessary.
hackerboos 13y ago
Am I right in thinking this is only good for reads though? Do writes still require loading the field into memory, parsing, updating and the writing back?
- pilif 13y ago
  Yes. You are right. At the moment there's no exposed functions for altering that binary data structure. Because of the way how Postgres works though, the row will always have to be rewritten in the future (all updates to a row will cause a new copy to be written - rows are immutable in Postgres). What we might gain in the future is a way to skip the parsing process, but the document will always have to be rewritten.