public inbox for [email protected]  

help / color / mirror / Atom feed

From: David E. Wheeler <[email protected]>
To: [email protected]
Subject: GIN-Indexable JSON Patterns
Date: Sun, 17 Dec 2023 13:10:03 -0500
Message-ID: <[email protected]> (raw)

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/[email protected]
  [2]: https://commitfest.postgresql.org/45/4624/
  [3]: https://www.postgresql.org/message-id/[email protected]
  [4]: https://www.postgresql.org/docs/current/datatype-json.html#JSON-INDEXING

view thread (2+ messages)

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: GIN-Indexable JSON Patterns
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox