diff options
author | Tom Lane <tgl@sss.pgh.pa.us> | 2022-06-08 12:01:51 -0400 |
---|---|---|
committer | Tom Lane <tgl@sss.pgh.pa.us> | 2022-06-08 12:01:51 -0400 |
commit | abed46aea4739c78802ab2ce5e93dc9a7e23c113 (patch) | |
tree | 181711dd700180781c6bd624b2ca0380a4350701 /doc | |
parent | b46e8a83e978c7f7edfe70b3a47bca3e9806e101 (diff) | |
download | postgresql-abed46aea4739c78802ab2ce5e93dc9a7e23c113.tar.gz |
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.
Diffstat (limited to 'doc')
-rw-r--r-- | doc/src/sgml/json.sgml | 54 |
1 files changed, 32 insertions, 22 deletions
diff --git a/doc/src/sgml/json.sgml b/doc/src/sgml/json.sgml index 0ffb5a708d..78125bc859 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> @@ -515,9 +502,32 @@ SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @> '{"tags": ["qu </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> |