4 ms·

How does pg handle this? I believe it has GIN index support on JSON columns but that can not do range queries. Is it possible to use a functional index in postg

by eknkc 11y ago

How does pg handle this? I believe it has GIN index support on JSON columns but that can not do range queries. Is it possible to use a functional index in postgresql on JSON types too?

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 ago
  To 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).
lucian1900 11y ago
You can create expression-based indexes on anything. That has been possible with regular JSON for a while now and it still works with JSONB.
saurik 11y ago
To make lucian1900's comment 100% clear: what MySQL is talking about was not just possible but not that difficult to do with PostgreSQL years ago (via plv8), has always been possible on PostgreSQL with only slight ingenuity required (give me a copy of PostgreSQL from ten years ago and with a couple lines of plpython I will show you efficient range queries of keys from columns of JSON stored as TEXT), and has now for a while been "trivial" due to the addition of native JSON access functions. PostgreSQL has had functional indexes since essentially forever: MySQL seems to have only been given a way to simulate this feature two months ago, by the same people who are working on this JSON support. The real question you should be asking is the opposite: how MySQL handles indexing arbitrary JSON content, and the answer is that given how it is only just as of two months ago capable of indexing limited JSON content, the answer is that MySQL has nothing to say on this matter: PostgreSQL has spent decades pushing the boundary on index technology, while MySQL was trying to make multi-master replication possible (the one use case people have where MySQL seems to ever make sense; though it still isn't clear that this form of replication is theoretically sound :/).

by eknkc 11y ago

How does pg handle this? I believe it has GIN index support on JSON columns but that can not do range queries. Is it possible to use a functional index in postgresql on JSON types too?

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 ago
To 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).

lucian1900 11y ago

You can create expression-based indexes on anything. That has been possible with regular JSON for a while now and it still works with JSONB.

saurik 11y ago

To make lucian1900's comment 100% clear: what MySQL is talking about was not just possible but not that difficult to do with PostgreSQL years ago (via plv8), has always been possible on PostgreSQL with only slight ingenuity required (give me a copy of PostgreSQL from ten years ago and with a couple lines of plpython I will show you efficient range queries of keys from columns of JSON stored as TEXT), and has now for a while been "trivial" due to the addition of native JSON access functions. PostgreSQL has had functional indexes since essentially forever: MySQL seems to have only been given a way to simulate this feature two months ago, by the same people who are working on this JSON support. The real question you should be asking is the opposite: how MySQL handles indexing arbitrary JSON content, and the answer is that given how it is only just as of two months ago capable of indexing limited JSON content, the answer is that MySQL has nothing to say on this matter: PostgreSQL has spent decades pushing the boundary on index technology, while MySQL was trying to make multi-master replication possible (the one use case people have where MySQL seems to ever make sense; though it still isn't clear that this form of replication is theoretically sound :/).