v35-0001-refactor-json_value-json_query-js.sql_json_new_section
application/octet-stream
Filename: v35-0001-refactor-json_value-json_query-js.sql_json_new_section
Type: application/octet-stream
Part: 1
Message:
Re: remaining sql/json patches
From 99522126628aba105b16f7ac1c19fd41af955ac3 Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
Date: Sat, 20 Jan 2024 20:21:01 +0800
Subject: [PATCH v35 1/1] refactor json_value, json_query, json_exists doc
---
doc/src/sgml/func.sgml | 180 ++++++++++++++++++++---------------------
1 file changed, 88 insertions(+), 92 deletions(-)
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 0481490f..67aa4229 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -15461,6 +15461,11 @@ table2-mapping
the SQL/JSON path language
</para>
</listitem>
+ <listitem>
+ <para>
+ SQL/JSON query functions
+ </para>
+ </listitem>
</itemizedlist>
</para>
@@ -18162,89 +18167,81 @@ $.* ? (@ like_regex "^\\d+$")
</programlisting>
</para>
</sect3>
+ </sect2>
+
+ <sect2 id="functions-sqljson-query">
+ <title>SQL/JSON query functions</title>
+
+ <indexterm zone="functions-sqljson-query">
+ <primary>SQL/JSON query functions </primary>
+ </indexterm>
- <sect3 id="sqljson-query-functions">
- <title>SQL/JSON Query Functions</title>
<para>
- <xref linkend="functions-sqljson-querying"/> details the SQL/JSON
- functions that can be used to query JSON data.
+ These SQL/JSON functions: (<literal>json_exists</literal>, <literal>json_query</literal>, <literal>json_value</literal>) can be used to query JSON data.
+ All these functions applying the <replaceable>path_expression</replaceable> to the <replaceable>context_item</replaceable>.
+ See detail explanation of the <replaceable>path_expression</replaceable> in <xref linkend="functions-sqljson-path"/>.
</para>
<note>
<para>
- SQL/JSON path expression can currently only accept values of the
+ SQL/JSON query functions can currently only accept values of the
<type>jsonb</type> type, so it might be necessary to cast the
<replaceable>context_item</replaceable> argument of these functions to
<type>jsonb</type>.
</para>
</note>
+<para>
+<literal> json_exists</literal> has the syntax:
+</para>
- <table id="functions-sqljson-querying">
- <title>SQL/JSON Query Functions</title>
- <tgroup cols="1">
- <thead>
- <row>
- <entry role="func_table_entry"><para role="func_signature">
- Function signature
- </para>
+<synopsis>
+json_exists(
+<replaceable>context_item</replaceable>, <replaceable>path_expression</replaceable> <optional> <literal>PASSING</literal> { <replaceable>value</replaceable> <literal>AS</literal> <replaceable>varname</replaceable> } <optional>, ...</optional></optional>
+<optional> { <literal>TRUE</literal> | <literal>FALSE</literal> |<literal> UNKNOWN</literal> | <literal>ERROR</literal> } <literal>ON ERROR</literal> </optional>)
+</synopsis>
<para>
- Description
- </para>
- <para>
- Example(s)
- </para></entry>
- </row>
- </thead>
- <tbody>
- <row>
- <entry role="func_table_entry"><para role="func_signature">
- <indexterm><primary>json_exists</primary></indexterm>
- <function>json_exists</function> (
- <replaceable>context_item</replaceable>, <replaceable>path_expression</replaceable> <optional> <literal>PASSING</literal> { <replaceable>value</replaceable> <literal>AS</literal> <replaceable>varname</replaceable> } <optional>, ...</optional></optional>
- <optional> { <literal>TRUE</literal> | <literal>FALSE</literal> |<literal> UNKNOWN</literal> | <literal>ERROR</literal> } <literal>ON ERROR</literal> </optional>)
- </para>
- <para>
- Returns true if the SQL/JSON <replaceable>path_expression</replaceable>
+ <literal>json_exists </literal> returns true if the SQL/JSON <replaceable>path_expression</replaceable>
applied to the <replaceable>context_item</replaceable> using the
<replaceable>value</replaceable>s yields any items.
The <literal>ON ERROR</literal> clause specifies the behavior if
an error occurs; the default is to return the <type>boolean</type>
<literal>FALSE</literal> value.
Note that if the <replaceable>path_expression</replaceable>
- is <literal>strict</literal>, an error is generated if it yields no
- items, provided the specified <literal>ON ERROR</literal> behavior is
- <literal>ERROR</literal>.
+ is <literal>strict</literal>, <literal>ON ERROR</literal> behavior is
+ specified <literal>ERROR</literal>, an error is generated if it yields no items.
</para>
+
+ <para>
+ Some examples:
+<programlisting>
+<literal>json_exists(jsonb '{"key1": [1,2,3]}', 'strict $.key1[*] ? (@ > 2)')</literal> <returnvalue>t</returnvalue>
+
+<literal>json_exists(jsonb '{"a": [1,2,3]}', 'lax $.a[5]' ERROR ON ERROR)</literal> <returnvalue>f</returnvalue>
+
+<literal>json_exists(jsonb '{"a": [1,2,3]}', 'strict $.a[5]' ERROR ON ERROR)</literal> <returnvalue>ERROR: jsonpath array subscript is out of bounds</returnvalue>
+</programlisting>
+ </para>
+
+<para>
+<literal>json_query</literal> has the syntax:
+</para>
+
+<synopsis>
+json_query(
+<replaceable>context_item</replaceable>, <replaceable>path_expression</replaceable> <optional> <literal>PASSING</literal> { <replaceable>value</replaceable> <literal>AS</literal> <replaceable>varname</replaceable> } <optional>, ...</optional></optional>
+<optional> <literal>RETURNING</literal> <replaceable>data_type</replaceable> <optional> <literal>FORMAT JSON</literal> <optional> <literal>ENCODING UTF8</literal> </optional> </optional> </optional>
+<optional> { <literal>WITHOUT</literal> | <literal>WITH</literal> { <literal>CONDITIONAL</literal> | <optional><literal>UNCONDITIONAL</literal></optional> } } <optional> <literal>ARRAY</literal> </optional> <literal>WRAPPER</literal> </optional>
+<optional> { <literal>KEEP</literal> | <literal>OMIT</literal> } <literal>QUOTES</literal> <optional> <literal>ON SCALAR STRING</literal> </optional> </optional>
+<optional> { <literal>ERROR</literal> | <literal>NULL</literal> | <literal>EMPTY</literal> { <optional> <literal>ARRAY</literal> </optional> | <literal>OBJECT</literal> } | <literal>DEFAULT</literal> <replaceable>expression</replaceable> } <literal>ON EMPTY</literal> </optional>
+<optional> { <literal>ERROR</literal> | <literal>NULL</literal> | <literal>EMPTY</literal> { <optional> <literal>ARRAY</literal> </optional> | <literal>OBJECT</literal> } | <literal>DEFAULT</literal> <replaceable>expression</replaceable> } <literal>ON ERROR</literal> </optional>)
+</synopsis>
+
<para>
- <literal>json_exists(jsonb '{"key1": [1,2,3]}', 'strict $.key1[*] ? (@ > 2)')</literal>
- <returnvalue>t</returnvalue>
- </para>
- <para>
- <literal>json_exists(jsonb '{"a": [1,2,3]}', 'lax $.a[5]' ERROR ON ERROR)</literal>
- <returnvalue>f</returnvalue>
- </para>
- <para>
- <literal>json_exists(jsonb '{"a": [1,2,3]}', 'strict $.a[5]' ERROR ON ERROR)</literal>
- <returnvalue>ERROR: jsonpath array subscript is out of bounds</returnvalue>
- </para></entry>
- </row>
- <row>
- <entry role="func_table_entry"><para role="func_signature">
- <indexterm><primary>json_query</primary></indexterm>
- <function>json_query</function> (
- <replaceable>context_item</replaceable>, <replaceable>path_expression</replaceable> <optional> <literal>PASSING</literal> { <replaceable>value</replaceable> <literal>AS</literal> <replaceable>varname</replaceable> } <optional>, ...</optional></optional>
- <optional> <literal>RETURNING</literal> <replaceable>data_type</replaceable> <optional> <literal>FORMAT JSON</literal> <optional> <literal>ENCODING UTF8</literal> </optional> </optional> </optional>
- <optional> { <literal>WITHOUT</literal> | <literal>WITH</literal> { <literal>CONDITIONAL</literal> | <optional><literal>UNCONDITIONAL</literal></optional> } } <optional> <literal>ARRAY</literal> </optional> <literal>WRAPPER</literal> </optional>
- <optional> { <literal>KEEP</literal> | <literal>OMIT</literal> } <literal>QUOTES</literal> <optional> <literal>ON SCALAR STRING</literal> </optional> </optional>
- <optional> { <literal>ERROR</literal> | <literal>NULL</literal> | <literal>EMPTY</literal> { <optional> <literal>ARRAY</literal> </optional> | <literal>OBJECT</literal> } | <literal>DEFAULT</literal> <replaceable>expression</replaceable> } <literal>ON EMPTY</literal> </optional>
- <optional> { <literal>ERROR</literal> | <literal>NULL</literal> | <literal>EMPTY</literal> { <optional> <literal>ARRAY</literal> </optional> | <literal>OBJECT</literal> } | <literal>DEFAULT</literal> <replaceable>expression</replaceable> } <literal>ON ERROR</literal> </optional>)
- </para>
- <para>
- Returns the result of applying the
+ <literal> json_query </literal> returns the result of applying the
<replaceable>path_expression</replaceable> to the
<replaceable>context_item</replaceable> using the
<replaceable>value</replaceable>s.
- This function must return a JSON string, so if the path expression
+ This function must return a JSON string, if the path expression
returns multiple SQL/JSON items, you must wrap the result using the
<literal>WITH WRAPPER</literal> clause. If the wrapper is
<literal>UNCONDITIONAL</literal>, an array wrapper will always
@@ -18270,23 +18267,29 @@ $.* ? (@ like_regex "^\\d+$")
<type>jsonpath</type> evaluation); the default when
<literal>ON ERROR</literal> is not specified is to return a null value.
</para>
+
+ <para>
+ example:
+<programlisting>
+<literal>json_query(jsonb '[1,[2,3],null]', 'lax $[*][1]' WITH CONDITIONAL WRAPPER)</literal> <returnvalue>[3]</returnvalue>
+</programlisting>
+ </para>
+
+<para>
+<literal>json_value</literal> has the syntax:
+</para>
+
+<synopsis>
+json_value (
+<replaceable>context_item</replaceable>, <replaceable>path_expression</replaceable>
+<optional> <literal>PASSING</literal> { <replaceable>value</replaceable> <literal>AS</literal> <replaceable>varname</replaceable> } <optional>, ...</optional></optional>
+<optional> <literal>RETURNING</literal> <replaceable>data_type</replaceable> </optional>
+<optional> { <literal>ERROR</literal> | <literal>NULL</literal> | <literal>DEFAULT</literal> <replaceable>expression</replaceable> } <literal>ON EMPTY</literal> </optional>
+<optional> { <literal>ERROR</literal> | <literal>NULL</literal> | <literal>DEFAULT</literal> <replaceable>expression</replaceable> } <literal>ON ERROR</literal> </optional>)
+</synopsis>
+
<para>
- <literal>json_query(jsonb '[1,[2,3],null]', 'lax $[*][1]' WITH CONDITIONAL WRAPPER)</literal>
- <returnvalue>[3]</returnvalue>
- </para></entry>
- </row>
- <row>
- <entry role="func_table_entry"><para role="func_signature">
- <indexterm><primary>json_value</primary></indexterm>
- <function>json_value</function> (
- <replaceable>context_item</replaceable>, <replaceable>path_expression</replaceable>
- <optional> <literal>PASSING</literal> { <replaceable>value</replaceable> <literal>AS</literal> <replaceable>varname</replaceable> } <optional>, ...</optional></optional>
- <optional> <literal>RETURNING</literal> <replaceable>data_type</replaceable> </optional>
- <optional> { <literal>ERROR</literal> | <literal>NULL</literal> | <literal>DEFAULT</literal> <replaceable>expression</replaceable> } <literal>ON EMPTY</literal> </optional>
- <optional> { <literal>ERROR</literal> | <literal>NULL</literal> | <literal>DEFAULT</literal> <replaceable>expression</replaceable> } <literal>ON ERROR</literal> </optional>)
- </para>
- <para>
- Returns the result of applying the
+ <literal>json_value</literal> returns the result of applying the
<replaceable>path_expression</replaceable> to the
<replaceable>context_item</replaceable> using the
<literal>PASSING</literal> <replaceable>value</replaceable>s. The
@@ -18306,24 +18309,17 @@ $.* ? (@ like_regex "^\\d+$")
equivalent to what one would get with <literal>OMIT QUOTES</literal>
when using <function>json_query</function>.
</para>
- <para>
- <literal>json_value(jsonb '"123.45"', '$' RETURNING float)</literal>
- <returnvalue>123.45</returnvalue>
- </para>
- <para>
- <literal>json_value(jsonb '"03:04 2015-02-01"', '$.datetime("HH24:MI YYYY-MM-DD")' RETURNING date)</literal>
- <returnvalue>2015-02-01</returnvalue>
- </para>
- <para>
- <literal>json_value(jsonb '[1,2]', 'strict $[*]' DEFAULT 9 ON ERROR)</literal>
- <returnvalue>9</returnvalue>
- </para></entry>
- </row>
- </tbody>
- </tgroup>
- </table>
- </sect3>
+ <para>
+ Some examples:
+<programlisting>
+<literal>json_value(jsonb '"123.45"', '$' RETURNING float)</literal> <returnvalue>123.45</returnvalue>
+
+<literal>json_value(jsonb '"03:04 2015-02-01"', '$.datetime("HH24:MI YYYY-MM-DD")' RETURNING date)</literal> <returnvalue>2015-02-01</returnvalue>
+
+<literal>json_value(jsonb '[1,2]', 'strict $[*]' DEFAULT 9 ON ERROR)</literal> <returnvalue>9</returnvalue>
+</programlisting>
+</para>
</sect2>
</sect1>
--
2.34.1