From: Elena Indrupskaya <e.indrupskaya@postgrespro.ru>
To: Amit Langote <amitlangote09@gmail.com>
To: PostgreSQL-development <pgsql-hackers@postgresql.org>
To: n.gluhov@postgrespro.ru
Cc: Andrew Dunstan <andrew@dunslane.net>
Subject: Re: SQL/JSON revisited
Date: Tue, 10 Jan 2023 15:51:04 +0300
Message-ID: <98ab8c72-49ad-d1e1-c9b6-8aca3a58e0f4@postgrespro.ru> (raw)
In-Reply-To: <CA+HiwqHROpf9e644D8BRqYvaAPmgBZVup-xKMDPk-nd4EpgzHw@mail.gmail.com>
References: <CA+HiwqHROpf9e644D8BRqYvaAPmgBZVup-xKMDPk-nd4EpgzHw@mail.gmail.com>
Hi,
The Postgres Pro documentation team prepared another SQL/JSON
documentation patch (attached), to apply on top of
v1-0009-Documentation-for-SQL-JSON-features.patch.
The new patch:
- Fixes minor typos
- Does some rewording agreed with Nikita Glukhov
- Updates Docbook markup to make tags consistent across SQL/JSON
documentation and across func.sgml, and in particular, consistent with
the XMLTABLE function, which resembles SQL/JSON functions pretty much.
--
Elena Indrupskaya
Lead Technical Writer
Postgres Professional http://www.postgrespro.com
On 28.12.2022 10:28, Amit Langote wrote:
> Hi,
>
> Rebased the SQL/JSON patches over the latest HEAD. I've decided to
> keep the same division of code into individual commits as that
> mentioned in the revert commit 2f2b18bd3f, squashing fixup commits in
> that list into the appropriate feature commits.
>
> The main difference from the patches as they were committed into v15
> is that JsonExpr evaluation no longer needs to use sub-transactions,
> thanks to the work done recently to handle type errors softly. I've
> made the new code pass an ErrorSaveContext into the type-conversion
> related functions as needed and also added an ExecEvalExprSafe() to
> evaluate sub-expressions of JsonExpr that might contain expressions
> that call type-conversion functions, such as CoerceViaIO contained in
> JsonCoercion nodes. ExecExprEvalSafe() is based on one of the patches
> that Nikita Glukhov had submitted in a previous discussion about
> redesigning SQL/JSON expression evaluation [1]. Though, I think that
> new interface will become unnecessary after I have finished rebasing
> my patches to remove subsidiary ExprStates of JsonExprState that we
> had also discussed back in [2].
>
> Adding this to January CF.
>
Attachments:func-SQL-JSON.patch (text/x-patch, 30.5K) downloaddiff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgmlindex cc72b9c2f6d..8614a26fe95 100644--- a/doc/src/sgml/func.sgml+++ b/doc/src/sgml/func.sgml@@ -17675,18 +17675,18 @@ $.* ? (@ like_regex "^\\d+$")
<entry role="func_table_entry"><para role="func_signature">
<indexterm><primary>json constructor</primary></indexterm>
<function>json</function> (
- <parameter>expression</parameter>+ <replaceable>expression</replaceable>
<optional> <literal>FORMAT JSON</literal> <optional> <literal>ENCODING UTF8</literal> </optional></optional>
<optional> { <literal>WITH</literal> | <literal>WITHOUT</literal> } <literal>UNIQUE</literal> <optional> <literal>KEYS</literal> </optional></optional>
<optional> <literal>RETURNING</literal> <replaceable>json_data_type</replaceable> </optional>)
</para>
<para>
- The <parameter>expression</parameter> can be any text type or a+ The <replaceable>expression</replaceable> can be any text type or a
<type>bytea</type> in UTF8 encoding. If the
- <parameter>expression</parameter> is NULL, an+ <replaceable>expression</replaceable> is NULL, an
<acronym>SQL</acronym> null value is returned.
If <literal>WITH UNIQUE</literal> is specified, the
- <parameter>expression</parameter> must not contain any duplicate+ <replaceable>expression</replaceable> must not contain any duplicate
object keys.
</para>
<para>
@@ -17701,12 +17701,12 @@ $.* ? (@ like_regex "^\\d+$")
<row>
<entry role="func_table_entry"><para role="func_signature">
<indexterm><primary>json_scalar</primary></indexterm>
- <function>json_scalar</function> (<parameter>expression</parameter>+ <function>json_scalar</function> (<replaceable>expression</replaceable>
<optional> <literal>RETURNING</literal> <replaceable>json_data_type</replaceable> </optional>)
</para>
<para>
Returns a JSON scalar value representing
- <parameter>expression</parameter>.+ <replaceable>expression</replaceable>.
If the input is NULL, an SQL NULL is returned. If the input is a number
or a boolean value, a corresponding JSON number or boolean value is
returned. For any other value a JSON string is returned.
@@ -17724,8 +17724,8 @@ $.* ? (@ like_regex "^\\d+$")
<entry role="func_table_entry"><para role="func_signature">
<indexterm><primary>json_object</primary></indexterm>
<function>json_object</function> (
- <optional> { <parameter>key_expression</parameter> { <literal>VALUE</literal> | ':' }- <parameter>value_expression</parameter> <optional> <literal>FORMAT JSON</literal> <optional> <literal>ENCODING UTF8</literal> </optional> </optional> }<optional>, ...</optional> </optional>+ <optional> { <replaceable>key_expression</replaceable> { <literal>VALUE</literal> | ':' }+ <replaceable>value_expression</replaceable> <optional> <literal>FORMAT JSON</literal> <optional> <literal>ENCODING UTF8</literal> </optional> </optional> }<optional>, ...</optional> </optional>
<optional> { <literal>NULL</literal> | <literal>ABSENT</literal> } <literal>ON NULL</literal> </optional>
<optional> { <literal>WITH</literal> | <literal>WITHOUT</literal> } <literal>UNIQUE</literal> <optional> <literal>KEYS</literal> </optional> </optional>
<optional> <literal>RETURNING</literal> <replaceable>data_type</replaceable> <optional> <literal>FORMAT JSON</literal> <optional> <literal>ENCODING UTF8</literal> </optional> </optional> </optional>)
@@ -17733,15 +17733,15 @@ $.* ? (@ like_regex "^\\d+$")
<para>
Constructs a JSON object of all the key value pairs given,
or an empty object if none are given.
- <parameter>key_expression</parameter> is a scalar expression+ <replaceable>key_expression</replaceable> is a scalar expression
defining the <acronym>JSON</acronym> key, which is
converted to the <type>text</type> type.
It cannot be <literal>NULL</literal> nor can it
belong to a type that has a cast to the <type>json</type>.
If <literal>WITH UNIQUE</literal> is specified, there must not
- be any duplicate <parameter>key_expression</parameter>.+ be any duplicate <replaceable>key_expression</replaceable>.
If <literal>ABSENT ON NULL</literal> is specified, the entire
- pair is omitted if the <parameter>value_expression</parameter>+ pair is omitted if the <replaceable>value_expression</replaceable>
is <literal>NULL</literal>.
</para>
<para>
@@ -17753,7 +17753,7 @@ $.* ? (@ like_regex "^\\d+$")
<entry role="func_table_entry"><para role="func_signature">
<indexterm><primary>json_objectagg</primary></indexterm>
<function>json_objectagg</function> (
- <optional> { <parameter>key_expression</parameter> { <literal>VALUE</literal> | ':' } <parameter>value_expression</parameter> } </optional>+ <optional> { <replaceable>key_expression</replaceable> { <literal>VALUE</literal> | ':' } <replaceable>value_expression</replaceable> } </optional>
<optional> { <literal>NULL</literal> | <literal>ABSENT</literal> } <literal>ON NULL</literal> </optional>
<optional> { <literal>WITH</literal> | <literal>WITHOUT</literal> } <literal>UNIQUE</literal> <optional> <literal>KEYS</literal> </optional> </optional>
<optional> <literal>RETURNING</literal> <replaceable>data_type</replaceable> <optional> <literal>FORMAT JSON</literal> <optional> <literal>ENCODING UTF8</literal> </optional> </optional> </optional>)
@@ -17761,8 +17761,8 @@ $.* ? (@ like_regex "^\\d+$")
<para>
Behaves like <function>json_object</function> above, but as an
aggregate function, so it only takes one
- <parameter>key_expression</parameter> and one- <parameter>value_expression</parameter> parameter.+ <replaceable>key_expression</replaceable> and one+ <replaceable>value_expression</replaceable> parameter.
</para>
<para>
<literal>SELECT json_objectagg(k:v) FROM (VALUES ('a'::text,current_date),('b',current_date + 1)) AS t(k,v)</literal>
@@ -17773,7 +17773,7 @@ $.* ? (@ like_regex "^\\d+$")
<entry role="func_table_entry"><para role="func_signature">
<indexterm><primary>json_array</primary></indexterm>
<function>json_array</function> (
- <optional> { <parameter>value_expression</parameter> <optional> <literal>FORMAT JSON</literal> </optional> } <optional>, ...</optional> </optional>+ <optional> { <replaceable>value_expression</replaceable> <optional> <literal>FORMAT JSON</literal> </optional> } <optional>, ...</optional> </optional>
<optional> { <literal>NULL</literal> | <literal>ABSENT</literal> } <literal>ON NULL</literal> </optional>
<optional> <literal>RETURNING</literal> <replaceable>data_type</replaceable> <optional> <literal>FORMAT JSON</literal> <optional> <literal>ENCODING UTF8</literal> </optional> </optional> </optional>)
</para>
@@ -17784,7 +17784,7 @@ $.* ? (@ like_regex "^\\d+$")
</para>
<para>
Constructs a JSON array from either a series of
- <parameter>value_expression</parameter> parameters or from the results+ <replaceable>value_expression</replaceable> parameters or from the results
of <replaceable>query_expression</replaceable>,
which must be a SELECT query returning a single column. If
<literal>ABSENT ON NULL</literal> is specified, NULL values are ignored.
@@ -17804,7 +17804,7 @@ $.* ? (@ like_regex "^\\d+$")
<entry role="func_table_entry"><para role="func_signature">
<indexterm><primary>json_arrayagg</primary></indexterm>
<function>json_arrayagg</function> (
- <optional> <parameter>value_expression</parameter> </optional>+ <optional> <replaceable>value_expression</replaceable> </optional>
<optional> <literal>ORDER BY</literal> <replaceable>sort_expression</replaceable> </optional>
<optional> { <literal>NULL</literal> | <literal>ABSENT</literal> } <literal>ON NULL</literal> </optional>
<optional> <literal>RETURNING</literal> <replaceable>data_type</replaceable> <optional> <literal>FORMAT JSON</literal> <optional> <literal>ENCODING UTF8</literal> </optional> </optional> </optional>)
@@ -17812,7 +17812,7 @@ $.* ? (@ like_regex "^\\d+$")
<para>
Behaves in the same way as <function>json_array</function>
but as an aggregate function so it only takes one
- <parameter>value_expression</parameter> parameter.+ <replaceable>value_expression</replaceable> parameter.
If <literal>ABSENT ON NULL</literal> is specified, any NULL
values are omitted.
If <literal>ORDER BY</literal> is specified, the elements will
@@ -17852,18 +17852,18 @@ $.* ? (@ like_regex "^\\d+$")
<row>
<entry role="func_table_entry"><para role="func_signature">
<indexterm><primary>IS JSON</primary></indexterm>
- <parameter>expression</parameter> <literal>IS</literal> <optional> <literal>NOT</literal> </optional> <literal>JSON</literal>+ <replaceable>expression</replaceable> <literal>IS</literal> <optional> <literal>NOT</literal> </optional> <literal>JSON</literal>
<optional> { <literal>VALUE</literal> | <literal>SCALAR</literal> | <literal>ARRAY</literal> | <literal>OBJECT</literal> } </optional>
<optional> { <literal>WITH</literal> | <literal>WITHOUT</literal> } <literal>UNIQUE</literal> <optional> <literal>KEYS</literal> </optional> </optional>
</para>
<para>
- This predicate tests whether <parameter>expression</parameter> can be+ This predicate tests whether <replaceable>expression</replaceable> can be
parsed as JSON, possibly of a specified type.
If <literal>SCALAR</literal> or <literal>ARRAY</literal> or
<literal>OBJECT</literal> is specified, the
test is whether or not the JSON is of that particular type. If
- <literal>WITH UNIQUE</literal> is specified, then an any object in the- <parameter>expression</parameter> is also tested to see if it+ <literal>WITH UNIQUE</literal> is specified, then any object in the+ <replaceable>expression</replaceable> is also tested to see if it
has duplicate keys.
</para>
<para>
@@ -17889,12 +17889,12 @@ FROM
<row>
<entry role="func_table_entry"><para role="func_signature">
<function>json_serialize</function> (
- <parameter>expression</parameter> <optional> <literal>FORMAT JSON</literal> <optional> <literal>ENCODING UTF8</literal> </optional> </optional>- <optional> <literal>RETURNING</literal> <parameter>data_type</parameter> <optional> <literal>FORMAT JSON</literal> <optional> <literal>ENCODING UTF8</literal> </optional> </optional> </optional>)+ <replaceable>expression</replaceable> <optional> <literal>FORMAT JSON</literal> <optional> <literal>ENCODING UTF8</literal> </optional> </optional>+ <optional> <literal>RETURNING</literal> <replaceable>data_type</replaceable> <optional> <literal>FORMAT JSON</literal> <optional> <literal>ENCODING UTF8</literal> </optional> </optional> </optional>)
</para>
<para>
Transforms an SQL/JSON value into a character or binary string. The
- <parameter>expression</parameter> can be of any JSON type, any+ <replaceable>expression</replaceable> can be of any JSON type, any
character string type, or <type>bytea</type> in UTF8 encoding.
The returned type can be any character string type or
<type>bytea</type>. The default is <type>text</type>.
@@ -17917,7 +17917,7 @@ FROM
<note>
<para>
SQL/JSON paths can only be applied to the <type>jsonb</type> type, so it
- might be necessary to cast the <parameter>context_item</parameter>+ might be necessary to cast the <replaceable>context_item</replaceable>
argument of these functions to <type>jsonb</type>.
</para>
</note>
@@ -17943,16 +17943,16 @@ FROM
<entry role="func_table_entry"><para role="func_signature">
<indexterm><primary>json_exists</primary></indexterm>
<function>json_exists</function> (
- <parameter>context_item</parameter>, <parameter>path_expression</parameter> <optional> <literal>PASSING</literal> { <parameter>value</parameter> <literal>AS</literal> <replaceable>varname</replaceable> } <optional>, ...</optional></optional>+ <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>TRUE</literal> | <literal>FALSE</literal> |<literal> UNKNOWN</literal> | <literal>ERROR</literal> } <literal>ON ERROR</literal> </optional>)
</para>
<para>
- Returns true if the SQL/JSON <parameter>path_expression</parameter>- applied to the <parameter>context_item</parameter> using the- <parameter>value</parameter>s yields any items.+ 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 what is returned if
- an error occurs. Note that if the <parameter>path_expression</parameter>+ an error occurs. Note that if the <replaceable>path_expression</replaceable>
is <literal>strict</literal>, an error is generated if it yields no items.
The default value is <literal>UNKNOWN</literal> which causes a NULL
result.
@@ -17974,28 +17974,30 @@ FROM
<entry role="func_table_entry"><para role="func_signature">
<indexterm><primary>json_value</primary></indexterm>
<function>json_value</function> (
- <parameter>context_item</parameter>, <parameter>path_expression</parameter>- <optional> <literal>PASSING</literal> { <parameter>value</parameter> <literal>AS</literal> <parameter>varname</parameter> } <optional>, ...</optional></optional>- <optional> <literal>RETURNING</literal> <parameter>data_type</parameter> </optional>- <optional> { <literal>ERROR</literal> | <literal>NULL</literal> | <literal>DEFAULT</literal> <parameter>expression</parameter> } <literal>ON EMPTY</literal> </optional>- <optional> { <literal>ERROR</literal> | <literal>NULL</literal> | <literal>DEFAULT</literal> <parameter>expression</parameter> } <literal>ON ERROR</literal> </optional>)+ <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
- <parameter>path_expression</parameter> to the- <parameter>context_item</parameter> using the- <parameter>value</parameter>s. The extracted value must be+ <replaceable>path_expression</replaceable> to the+ <replaceable>context_item</replaceable> using the+ <replaceable>value</replaceable>s. The extracted value must be
a single <acronym>SQL/JSON</acronym> scalar item. For results that
are objects or arrays, use the <function>json_query</function>
- instead.- The returned <parameter>data_type</parameter> has the same semantics+ function instead.+ The returned <replaceable>data_type</replaceable> has the same semantics
as for constructor functions like <function>json_objectagg</function>.
The default returned type is <type>text</type>.
The <literal>ON EMPTY</literal> clause specifies the behavior if the
- <parameter>path_expression</parameter> yields no value at all.+ <replaceable>path_expression</replaceable> yields no value at all.
The <literal>ON ERROR</literal> clause specifies the behavior if an
- error occurs, as a result of either the evaluation or the application- of the <literal>ON EMPTY</literal> clause.+ error occurs as a result of <type>jsonpath</type> evaluation+ (including cast to the output type) or execution of+ <literal>ON EMPTY</literal> behavior (that was caused by empty result+ of <type>jsonpath</type> evaluation).
</para>
<para>
<literal>json_value(jsonb '"123.45"', '$' RETURNING float)</literal>
@@ -18014,24 +18016,24 @@ FROM
<entry role="func_table_entry"><para role="func_signature">
<indexterm><primary>json_query</primary></indexterm>
<function>json_query</function> (
- <parameter>context_item</parameter>, <parameter>path_expression</parameter> <optional> <literal>PASSING</literal> { <parameter>value</parameter> <literal>AS</literal> <parameter>varname</parameter> } <optional>, ...</optional></optional>- <optional> <literal>RETURNING</literal> <parameter>data_type</parameter> <optional> <literal>FORMAT JSON</literal> <optional> <literal>ENCODING UTF8</literal> </optional> </optional> </optional>+ <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> <parameter>expression</parameter> } <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> <parameter>expression</parameter> } <literal>ON ERROR</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 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
- <parameter>path_expression</parameter> to the- <parameter>context_item</parameter> using the- <parameter>value</parameter>s.+ <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
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
be applied, even if the returned value is already a single JSON object
- or array, but if it is <literal>CONDITIONAL</literal> it will not be+ or array, but if it is <literal>CONDITIONAL</literal>, it will not be
applied to a single array or object. <literal>UNCONDITIONAL</literal>
is the default.
If the result is a scalar string, by default the value returned will have
@@ -18040,7 +18042,7 @@ FROM
The <literal>ON ERROR</literal> and <literal>ON EMPTY</literal>
clauses have similar semantics to those clauses for
<function>json_value</function>.
- The returned <parameter>data_type</parameter> has the same semantics+ The returned <replaceable>data_type</replaceable> has the same semantics
as for constructor functions like <function>json_objectagg</function>.
The default returned type is <type>text</type>.
</para>
@@ -18105,7 +18107,7 @@ FROM
columns. Columns produced by <literal>NESTED PATH</literal>s at the
same level are considered to be <firstterm>siblings</firstterm>,
while a column produced by a <literal>NESTED PATH</literal> is
- considered to be a child of the column produced by and+ considered to be a child of the column produced by a
<literal>NESTED PATH</literal> or row expression at a higher level.
Sibling columns are always joined first. Once they are processed,
the resulting rows are joined to the parent row.
@@ -18114,13 +18116,13 @@ FROM
<variablelist>
<varlistentry>
<term>
- <literal><parameter>context_item</parameter>, <parameter>path_expression</parameter> <optional> <literal>AS</literal> <parameter>json_path_name</parameter> </optional> <optional> <literal>PASSING</literal> { <parameter>value</parameter> <literal>AS</literal> <parameter>varname</parameter> } <optional>, ...</optional></optional></literal>+ <literal><replaceable>context_item</replaceable>, <replaceable>path_expression</replaceable> <optional> <literal>AS</literal> <replaceable>json_path_name</replaceable> </optional> <optional> <literal>PASSING</literal> { <replaceable>value</replaceable> <literal>AS</literal> <replaceable>varname</replaceable> } <optional>, ...</optional></optional></literal>
</term>
<listitem>
<para>
The input data to query, the JSON path expression defining the query,
and an optional <literal>PASSING</literal> clause, which can provide data
- values to the <parameter>path_expression</parameter>.+ values to the <replaceable>path_expression</replaceable>.
The result of the input data
evaluation is called the <firstterm>row pattern</firstterm>. The row
pattern is used as the source for row values in the constructed view.
@@ -18130,7 +18132,7 @@ FROM
<varlistentry>
<term>
- <literal>COLUMNS</literal>( <parameter>json_table_column</parameter> <optional>, ...</optional> )+ <literal>COLUMNS</literal>( <replaceable>json_table_column</replaceable> <optional>, ...</optional> )
</term>
<listitem>
@@ -18138,15 +18140,15 @@ FROM
The <literal>COLUMNS</literal> clause defining the schema of the
constructed view. In this clause, you must specify all the columns
to be filled with SQL/JSON items.
- The <parameter>json_table_column</parameter>+ The <replaceable>json_table_column</replaceable>
expression has the following syntax variants:
</para>
<variablelist>
<varlistentry>
<term>
- <literal><parameter>name</parameter> <parameter>type</parameter>- <optional> <literal>PATH</literal> <parameter>json_path_specification</parameter> </optional></literal>+ <literal><replaceable>name</replaceable> <replaceable>type</replaceable>+ <optional> <literal>PATH</literal> <replaceable>json_path_specification</replaceable> </optional></literal>
</term>
<listitem>
@@ -18156,7 +18158,7 @@ FROM
</para>
<para>
The provided <literal>PATH</literal> expression parses the
- row pattern defined by <parameter>json_api_common_syntax</parameter>+ row pattern defined by <replaceable>json_api_common_syntax</replaceable>
and fills the column with produced SQL/JSON items, one for each row.
If the <literal>PATH</literal> expression is omitted,
<function>JSON_TABLE</function> uses the
@@ -18179,8 +18181,8 @@ FROM
<varlistentry>
<term>
- <parameter>name</parameter> <parameter>type</parameter> <literal>FORMAT</literal> <parameter>json_representation</parameter>- <optional> <literal>PATH</literal> <parameter>json_path_specification</parameter> </optional>+ <replaceable>name</replaceable> <replaceable>type</replaceable> <literal>FORMAT</literal> <replaceable>json_representation</replaceable>+ <optional> <literal>PATH</literal> <replaceable>json_path_specification</replaceable> </optional>
</term>
<listitem>
@@ -18190,12 +18192,12 @@ FROM
</para>
<para>
The provided <literal>PATH</literal> expression parses the
- row pattern defined by <parameter>json_api_common_syntax</parameter>+ row pattern defined by <replaceable>json_api_common_syntax</replaceable>
and fills the column with produced SQL/JSON items, one for each row.
If the <literal>PATH</literal> expression is omitted,
<function>JSON_TABLE</function> uses the
- <literal>$.<parameter>name</parameter></literal> path expression,- where <parameter>name</parameter> is the provided column name.+ <literal>$.<replaceable>name</replaceable></literal> path expression,+ where <replaceable>name</replaceable> is the provided column name.
In this case, the column name must correspond to one of the
keys within the SQL/JSON item produced by the row pattern.
</para>
@@ -18211,8 +18213,8 @@ FROM
<varlistentry>
<term>
- <parameter>name</parameter> <parameter>type</parameter>- <literal>EXISTS</literal> <optional> <literal>PATH</literal> <parameter>json_path_specification</parameter> </optional>+ <replaceable>name</replaceable> <replaceable>type</replaceable>+ <literal>EXISTS</literal> <optional> <literal>PATH</literal> <replaceable>json_path_specification</replaceable> </optional>
</term>
<listitem>
@@ -18221,10 +18223,10 @@ FROM
</para>
<para>
The provided <literal>PATH</literal> expression parses the
- row pattern defined by <parameter>json_api_common_syntax</parameter>,+ row pattern defined by <replaceable>json_api_common_syntax</replaceable>,
checks whether any SQL/JSON items were returned, and fills the column with
resulting boolean value, one for each row.
- The specified <parameter>type</parameter> should have cast from+ The specified <replaceable>type</replaceable> should have cast from
<type>boolean</type>.
If the <literal>PATH</literal> expression is omitted,
<function>JSON_TABLE</function> uses the
@@ -18241,8 +18243,8 @@ FROM
<varlistentry>
<term>
- <literal>NESTED PATH</literal> <parameter>json_path_specification</parameter> <optional> <literal>AS</literal> <parameter>json_path_name</parameter> </optional>- <literal>COLUMNS</literal> ( <parameter>json_table_column</parameter> <optional>, ...</optional> )+ <literal>NESTED PATH</literal> <replaceable>json_path_specification</replaceable> <optional> <literal>AS</literal> <replaceable>json_path_name</replaceable> </optional>+ <literal>COLUMNS</literal> ( <replaceable>json_table_column</replaceable> <optional>, ...</optional> )
</term>
<listitem>
@@ -18250,7 +18252,7 @@ FROM
Extracts SQL/JSON items from nested levels of the row pattern,
generates one or more columns as defined by the <literal>COLUMNS</literal>
subclause, and inserts the extracted SQL/JSON items into each row of these columns.
- The <parameter>json_table_column</parameter> expression in the+ The <replaceable>json_table_column</replaceable> expression in the
<literal>COLUMNS</literal> subclause uses the same syntax as in the
parent <literal>COLUMNS</literal> clause.
</para>
@@ -18266,14 +18268,14 @@ FROM
<para>
You can use the <literal>PLAN</literal> clause to define how
- to join the columns returned by <parameter>NESTED PATH</parameter> clauses.+ to join the columns returned by <literal>NESTED PATH</literal> clauses.
</para>
</listitem>
</varlistentry>
<varlistentry>
<term>
- <parameter>name</parameter> <literal>FOR ORDINALITY</literal>+ <replaceable>name</replaceable> <literal>FOR ORDINALITY</literal>
</term>
<listitem>
@@ -18292,13 +18294,13 @@ FROM
<varlistentry>
<term>
- <literal>AS</literal> <parameter>json_path_name</parameter>+ <literal>AS</literal> <replaceable>json_path_name</replaceable>
</term>
<listitem>
<para>
- The optional <parameter>json_path_name</parameter> serves as an- identifier of the provided <parameter>json_path_specification</parameter>.+ The optional <replaceable>json_path_name</replaceable> serves as an+ identifier of the provided <replaceable>json_path_specification</replaceable>.
The path name must be unique and distinct from the column names.
When using the <literal>PLAN</literal> clause, you must specify the names
for all the paths, including the row pattern. Each path name can appear in
@@ -18309,7 +18311,7 @@ FROM
<varlistentry>
<term>
- <literal>PLAN</literal> ( <parameter>json_table_plan</parameter> )+ <literal>PLAN</literal> ( <replaceable>json_table_plan</replaceable> )
</term>
<listitem>
@@ -18395,7 +18397,7 @@ FROM
<varlistentry>
<term>
- <literal>PLAN DEFAULT</literal> ( <replaceable>OUTER | INNER</replaceable> <optional>, <replaceable>UNION | CROSS</replaceable> </optional> )+ <literal>PLAN DEFAULT</literal> ( <literal><replaceable>OUTER | INNER</replaceable> <optional>, <replaceable>UNION | CROSS</replaceable> </optional></literal> )
</term>
<listitem>
<para>
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: pgsql-hackers@postgresql.org
Cc: e.indrupskaya@postgrespro.ru, amitlangote09@gmail.com, n.gluhov@postgrespro.ru, andrew@dunslane.net
Subject: Re: SQL/JSON revisited
In-Reply-To: <98ab8c72-49ad-d1e1-c9b6-8aca3a58e0f4@postgrespro.ru>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox