3 ms·
> “But postgres isn’t a document store!” I hear you cry. Well, no, it isn’t, but it does have a JSONB column type, with support for indexes on fields within the
by RDundon 8y ago
> “But postgres isn’t a document store!” I hear you cry. Well, no, it isn’t, but it does have a JSONB column type, with support for indexes on fields within the JSON blob.
Interesting. I didn't know you could make indexes for things /within/ the JSON.
- dtech 8y agoYep, you can index any expression on a single row in postgres. The downside is that the optimizer will only use the index if the query contains that exact expression, while it's usually smarter with column indexes. The only limit that I've run into in comparison with NoSQL databases is that you cannot index fields inside arrays within the column (json -> arrayIndex -> field), you need to normalize the array to a different table.
- cdelsolar 8y agoYes! And a lot of the time querying properly indexed JSONB is faster than Mongo.
- sitharus 8y agoWith a GIN index and a JSONB column you can index the entire json document. It's the same index type as Postgres uses for the full text search, but not super efficient. You can also project out specific subsets of the document to an index and Postgres will use them.
- h1d 8y agoI take it this is Postgres specific and MySQL can't index JSON items.
- sitharus 8y agoI'm not familiar with MySQL but the features I outlined are Postgres specific.
- evanelias 8y agoThe syntax and terminology are different, but MySQL has supported indexes on values inside of JSON (or any arbitrary expression/function) since MySQL 5.7, released about three years ago. In MySQL, you can define a "generated column" and index it in InnoDB. https://dev.mysql.com/doc/refman/5.7/en/create-table-secondary-indexes.html https://dev.mysql.com/doc/refman/5.7/en/create-table-seconda... contains some JSON-specific examples.