Hey Hackers,
Quick follow-up to my slew of questions back in [September][1]. I wanted to
update [my patch][2] to note that only JSON Path equality operators are
supported by indexes, as [previously discussed][3]. I thought perhaps adding a
note to this bit of the docs would be useful:
> For these operators, a GIN index extracts clauses of the form accessors_chain
> = constant out of the jsonpath pattern, and does the index search based on
> the keys and values mentioned in these clauses. The accessors chain may
> include .key, [*], and [index] accessors. The jsonb_ops operator class also
> supports .* and .** accessors, but the jsonb_path_ops operator class does not.
But perhaps that’s what `accessors_chain = constant` is supposed to mean? I’m
not super clear on it, though, since the operator is `==` and not `=` (and I
would presume that `!=` would use the index, as well. Is that correct?
If so, how would you feel about something like this?
--- a/doc/src/sgml/json.sgml
+++ b/doc/src/sgml/json.sgml
@@ -513,7 +513,7 @@ SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @@
'$.tags[*] == "qui"';
For these operators, a GIN index extracts clauses of the form
accessors_chain
-= constant out of
+== constant out of
the jsonpath pattern, and does the index search based on
the keys and values mentioned in these clauses. The accessors chain
may include .key,
@@ -522,6 +522,9 @@ SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @@
'$.tags[*] == "qui"';
The jsonb_ops operator class also
supports .* and .** accessors,
but the jsonb_path_ops operator class does not.
+Only the == and != SQL/JSON Path Operators
+can use the index.
Best,
David
[1]:
https://www.postgresql.org/message-id/15dd78a5-b5c4-4332-acfe-55723259c...@justatheory.com
[2]: https://commitfest.postgresql.org/45/4624/
[3]:
https://www.postgresql.org/message-id/973d6495-cf28-4d06-7d46-758bd2615...@xs4all.nl
[4]: https://www.postgresql.org/docs/current/datatype-json.html#JSON-INDEXING