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"';
</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-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
