4 ms·
> JSON in postgres is just a container type isn't it? At the moment JSON is stored in a text field so accessing a JSON field involves parsing the entire text v
by sehrope 13y ago
> JSON in postgres is just a container type isn't it?
At the moment JSON is stored in a text field so accessing a JSON field involves parsing the entire text value. If instead the JSON is stored in a parsed binary format then you can more efficiently access individual fields.
If you're only accessing a small set of known fields you can work around the issue by using plv8 to create function indexes on the fields that you'll be using. This doesn't work for arbitrary expressions though. If you want to filter a WHERE clause based on a non-indexed field the entire JSON text will need to be parsed.
Binary JSON storage makes all of this much faster with no change on the user's side. It's all transparent and just faster!
In theory it could also reduce storage space for tuples. Duplicate field names need not be repeated. I don't think it's in scope in the Postgres JSON improvements but it's a possibility.
> Why is everyone expecting it to become document storage?
Document storage in a relational database is really useful and there are plenty of use cases for it.
The standard example I use for JSON (or hstore) usage for Postgres is an audit table. You'd have all the usual audit fields (who/what/when) but you'd also have a "detail" field with event specific details. Using hstore or JSON for this is perfect. Improved support for JSON makes it much easier to provide event specific search.
- gdulli 13y ago> If you're only accessing a small set of known fields you can work around the issue by using plv8 to create function indexes on the fields that you'll be using. You don't need plv8 to create an index on a JSON field, it's supported natively.
- sehrope 13y agoYes my mistake on that. I'm mixing up 9.2 (where you had to do use plv8) and 9.3 (which has the native operator). The rest of the comment still stands though, a parsed representation makes the operator faster and more efficient.
- deleted 13y ago[deleted]