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
Pathequality operators are supported by indexes, as [previously discussed][3]. I thought perhaps adding a note to this
bitof the docs would be useful:
> For these operators, a GIN index extracts clauses of the form accessors_chain = constant out of the jsonpath pattern,
anddoes 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
operatorclass does not.
But perhaps that’s what `accessors_chain = constant` is supposed to mean? I’m not super clear on it, though, since the
operatoris `==` 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"';
</programlisting>
For these operators, a GIN index extracts clauses of the form
<literal><replaceable>accessors_chain</replaceable>
- = <replaceable>constant</replaceable></literal> out of
+ == <replaceable>constant</replaceable></literal> out of
the <type>jsonpath</type> pattern, and does the index search based on
the keys and values mentioned in these clauses. The accessors chain
may include <literal>.<replaceable>key</replaceable></literal>,
@@ -522,6 +522,9 @@ SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @@ '$.tags[*] == "qui"';
The <literal>jsonb_ops</literal> operator class also
supports <literal>.*</literal> and <literal>.**</literal> accessors,
but the <literal>jsonb_path_ops</literal> operator class does not.
+ Only the <literal>==</literal> and <literal>!=</literal> <link
+ linkend="functions-sqljson-path-operators">SQL/JSON Path Operators</link>
+ can use the index.
</para>
<para>
Best,
David
[1]: https://www.postgresql.org/message-id/15DD78A5-B5C4-4332-ACFE-55723259C07F@justatheory.com
[2]: https://commitfest.postgresql.org/45/4624/
[3]: https://www.postgresql.org/message-id/973d6495-cf28-4d06-7d46-758bd2615e34@xs4all.nl
[4]: https://www.postgresql.org/docs/current/datatype-json.html#JSON-INDEXING