3 ms·
I just skimmed the documentation[1]. Doesn't look look like it: 8.14.4. jsonb Indexing ... However, the [GIN] index could not be used for queries li
by hliyan 11y ago
I just skimmed the documentation[1]. Doesn't look look like it:
8.14.4. jsonb Indexing
...
However, the [GIN] index could not be used for queries like the following:
-- Find documents in which the key "tags" contains key or array element "qui"
SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc -> 'tags' ? 'qui';
[1]: http://www.postgresql.org/docs/9.4/static/datatype-json.html http://www.postgresql.org/docs/9.4/static/datatype-json.html
- saurik 11y agoTo do a range query you would need to use a BTree index (or a GiST index that is essentially configured to simulate a BTree) over the extracted value, not on the column itself, value (exactly as is possible now in MySQL as of two months ago, as they finally caught up and got a way to do functional indexes, specifically designed for this use case). PostgreSQL has been capable of this essentially forever (if you really needed to do this ten years ago, one line of plpython to parse the JSON and extract the field would have worked perfectly and built a perfectly efficient index; just no one wanted to store this kind of stuff until MongoDB tried to push it as the future, and so no one cared to bother doing writeup on how to do that).