postgres.git / summary / log / commit / refs
commit 0ccef410e4c35fd6a742091736b6fe1997eec6d3
Author: Tom Lane <tgl@sss.pgh.pa.us>
Date: Wed Jun 08 16:01:51 2022 +0000
Doc: copy-edit "jsonb Indexing" section.
The patch introducing jsonpath dropped a para about that between
two related examples, and didn't bother updating the introductory
sentences that it falsified. The grammar was pretty shaky as well.
doc/src/sgml/json.sgml | 54 ++++++++++++++++++++++++++++++--------------------
1 file changed, 32 insertions(+), 22 deletions(-)
diff --git a/doc/src/sgml/json.sgml b/doc/src/sgml/json.sgml
index 673c70c3bb8..a173368229b 100644
--- a/doc/src/sgml/json.sgml
+++ b/doc/src/sgml/json.sgml
@@ -415,9 +415,10 @@ SELECT doc->'site_name' FROM websites
</para>
<para>
The default GIN operator class for <type>jsonb</type> supports queries with
- top-level key-exists operators <literal>?</literal>, <literal>?&</literal>
- and <literal>?|</literal> operators and path/value-exists operator
- <literal>@></literal>.
+ the key-exists operators <literal>?</literal>, <literal>?|</literal>
+ and <literal>?&</literal>, the containment operator
+ <literal>@></literal>, and the <type>jsonpath</type> match
+ operators <literal>@?</literal> and <literal>@@</literal>.
(For details of the semantics that these operators
implement, see <xref linkend="functions-jsonb-op-table"/>.)
An example of creating an index with this operator class is:
@@ -425,7 +426,8 @@ SELECT doc->'site_name' FROM websites
CREATE INDEX idxgin ON api USING GIN (jdoc);
</programlisting>
The non-default GIN operator class <literal>jsonb_path_ops</literal>
- supports indexing the <literal>@></literal> operator only.
+ does not support the key-exists operators, but it does support
+ <literal>@></literal>, <literal>@?</literal> and <literal>@@</literal>.
An example of creating an index with this operator class is:
<programlisting>
CREATE INDEX idxginp ON api USING GIN (jdoc jsonb_path_ops);
@@ -482,22 +484,7 @@ CREATE INDEX idxgintags ON api USING GIN ((jdoc -> 'tags'));
(More information on expression indexes can be found in <xref
linkend="indexes-expressional"/>.)
</para>
- <para>
- Also, GIN index supports <literal>@@</literal> and <literal>@?</literal>
- operators, which perform <literal>jsonpath</literal> matching.
-<programlisting>
-SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @@ '$.tags[*] == "qui"';
-</programlisting>
-<programlisting>
-SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @? '$.tags[*] ? (@ == "qui")';
-</programlisting>
- GIN index extracts statements of following form out of
- <literal>jsonpath</literal>: <replaceable>accessors_chain</replaceable> = <replaceable>const</replaceable>.
- Accessors chain may consist of <literal>.key</literal>,
- <literal>[*]</literal>, and <literal>[<replaceable>index</replaceable>]</literal> accessors.
- <literal>jsonb_ops</literal> additionally supports <literal>.*</literal>
- and <literal>.**</literal> accessors.
- </para>
+
<para>
Another approach to querying is to exploit containment, for example:
<programlisting>
@@ -514,10 +501,33 @@ SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @> '{"tags": ["qu
index.
</para>
+ <para>
+ GIN indexes also support the <literal>@?</literal>
+ and <literal>@@</literal> operators, which
+ perform <type>jsonpath</type> matching. Examples are
+<programlisting>
+SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @? '$.tags[*] ? (@ == "qui")';
+</programlisting>
+<programlisting>
+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
+ 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>,
+ <literal>[*]</literal>,
+ and <literal>[<replaceable>index</replaceable>]</literal> accessors.
+ 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.
+ </para>
+
<para>
Although the <literal>jsonb_path_ops</literal> operator class supports
- only queries with the <literal>@></literal>, <literal>@@</literal>
- and <literal>@?</literal> operators, it has notable
+ only queries with the <literal>@></literal>, <literal>@?</literal>
+ and <literal>@@</literal> operators, it has notable
performance advantages over the default operator
class <literal>jsonb_ops</literal>. A <literal>jsonb_path_ops</literal>
index is usually much smaller than a <literal>jsonb_ops</literal>
[parent: 804a5079220d]