SQL/JSON documentation JSON_TABLE

Started by Erik Rijkersover 3 years ago5 messages

Erik Rijkers

er@xs4all.nl

over 3 years ago

1 attachment(s)

Hi,

Attached are a few small changes to the JSON_TABLE section in func.sgml.

The first two changes are simple typos.

Then there was this line:

----
context_item, path_expression [ AS json_path_name ] [ PASSING { value AS
varname } [, ...]]
----

those are the parameters to JSON_TABLE() so I changed that line to:

----
JSON_TABLE(context_item, path_expression [ AS json_path_name ] [ PASSING
{ value AS varname } [, ...]])
----

Some parts of the JSON_TABLE text strike me as opaque. For instance,
there are paragraphs that more than once use the term:
json_api_common_syntax

'json_api_common_syntax' is not explained. It turns out it's a relic
from Nikita's original docs. I dug up a 2018 patch where the term is
used as:

---- 2018:
JSON_TABLE (
json_api_common_syntax [ AS path_name ]
COLUMNS ( json_table_column [, ...] )
(etc...)
----

with explanation:

---- 2018:
json_api_common_syntax:
The input data to query, the JSON path expression defining the
query, and an optional PASSING clause.
----

So that made sense then (input+jsonpath+params=api), but it doesn't now
fit as such in the current docs.

I think it would be best to remove all uses of that compound term, and
rewrite the explanations using only the current parameter names
(context_item, path_expression, etc).

But I wasn't sure and I haven't done any such changes in the attached.

Perhaps I'll give it a try during the weekend.

Erik Rijkers

Attachments:

func.sgml.20220708.difftext/x-patch; charset=UTF-8; name=func.sgml.20220708.diffDownload

--- ./doc/src/sgml/func.sgml.orig	2022-07-08 19:46:46.018505707 +0200
+++ ./doc/src/sgml/func.sgml	2022-07-08 20:47:35.488303254 +0200
@@ -18026,7 +18026,7 @@
         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 a scalar string, by default the value returned will have
+        If the result is a scalar string, by default the value returned will have
         surrounding quotes making it a valid JSON value. However, this behavior
         is reversed if <literal>OMIT QUOTES</literal> is specified.
         The <literal>ON ERROR</literal> and <literal>ON EMPTY</literal>
@@ -18097,7 +18097,7 @@
    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.
@@ -18106,7 +18106,7 @@
   <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>JSON_TABLE(<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>
     </term>
     <listitem>
     <para>

Andrew Dunstan

andrew@dunslane.net

over 3 years ago

In reply to: Erik Rijkers (#1)

Re: SQL/JSON documentation JSON_TABLE

On 2022-07-08 Fr 16:03, Erik Rijkers wrote:

Hi,

Attached are a few small changes to the JSON_TABLE section in func.sgml.

The first two changes are simple typos.

Then there was this line:

----
context_item, path_expression [ AS json_path_name ] [ PASSING { value
AS varname } [, ...]]
----

those are the parameters to JSON_TABLE() so I changed that line to:

----
JSON_TABLE(context_item, path_expression [ AS json_path_name ] [
PASSING { value AS varname } [, ...]])
----

Some parts of the JSON_TABLE text strike me as opaque. For instance,
there are paragraphs that more than once use the term:
   json_api_common_syntax

'json_api_common_syntax' is not explained. It turns out it's a relic
from Nikita's original docs. I dug up a 2018 patch where the term is
used as:

---- 2018:
JSON_TABLE (
json_api_common_syntax [ AS path_name ]
COLUMNS ( json_table_column [, ...] )
     (etc...)
----

with explanation:

---- 2018:
json_api_common_syntax:
   The input data to query, the JSON path expression defining the
query, and an optional PASSING clause.
----

So that made sense then (input+jsonpath+params=api), but it doesn't
now fit as such in the current docs.

I think it would be best to remove all uses of that compound term, and
rewrite the explanations using only the current parameter names
(context_item, path_expression, etc).

But I wasn't sure and I haven't done any such changes in the attached.

Perhaps I'll give it a try during the weekend.

Thanks for this. If you want to follow up that last sentence I will try
to commit a single fix early next week.

cheers

andrew

--
Andrew Dunstan
EDB: https://www.enterprisedb.com

Andrew Dunstan

andrew@dunslane.net

over 3 years ago

In reply to: Andrew Dunstan (#2)

1 attachment(s)

Re: SQL/JSON documentation JSON_TABLE

On 2022-07-08 Fr 16:20, Andrew Dunstan wrote:

On 2022-07-08 Fr 16:03, Erik Rijkers wrote:

Hi,

Attached are a few small changes to the JSON_TABLE section in func.sgml.

The first two changes are simple typos.

Then there was this line:

----
context_item, path_expression [ AS json_path_name ] [ PASSING { value
AS varname } [, ...]]
----

those are the parameters to JSON_TABLE() so I changed that line to:

----
JSON_TABLE(context_item, path_expression [ AS json_path_name ] [
PASSING { value AS varname } [, ...]])
----

Some parts of the JSON_TABLE text strike me as opaque. For instance,
there are paragraphs that more than once use the term:
   json_api_common_syntax

'json_api_common_syntax' is not explained. It turns out it's a relic
from Nikita's original docs. I dug up a 2018 patch where the term is
used as:

---- 2018:
JSON_TABLE (
json_api_common_syntax [ AS path_name ]
COLUMNS ( json_table_column [, ...] )
     (etc...)
----

with explanation:

---- 2018:
json_api_common_syntax:
   The input data to query, the JSON path expression defining the
query, and an optional PASSING clause.
----

So that made sense then (input+jsonpath+params=api), but it doesn't
now fit as such in the current docs.

I think it would be best to remove all uses of that compound term, and
rewrite the explanations using only the current parameter names
(context_item, path_expression, etc).

But I wasn't sure and I haven't done any such changes in the attached.

Perhaps I'll give it a try during the weekend.

Thanks for this. If you want to follow up that last sentence I will try
to commit a single fix early next week.

Here's a patch that deals with most of this. There's one change you
wanted that I don't think is correct, which I omitted.

cheers

andrew

--
Andrew Dunstan
EDB: https://www.enterprisedb.com

Attachments:

json-docs-fix.patchtext/x-patch; charset=UTF-8; name=json-docs-fix.patchDownload

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index b6783b7ad0..478d6eccd8 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -18030,9 +18030,9 @@ FROM
         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 a scalar string, by default the value returned will have
-        surrounding quotes making it a valid JSON value. However, this behavior
-        is reversed if <literal>OMIT QUOTES</literal> is specified.
+        If the result is a scalar string, by default the value returned will
+        have surrounding quotes making it a valid JSON value. However, this
+        behavior is reversed if <literal>OMIT QUOTES</literal> is specified.
         The <literal>ON ERROR</literal> and <literal>ON EMPTY</literal>
         clauses have similar semantics to those clauses for
         <function>json_value</function>.
@@ -18101,7 +18101,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.
@@ -18151,9 +18151,9 @@ FROM
      the specified column.
     </para>
     <para>
-     The provided <literal>PATH</literal> expression parses the
-     row pattern defined by <parameter>json_api_common_syntax</parameter>
-     and fills the column with produced SQL/JSON items, one for each row.
+     The provided <literal>PATH</literal> expression is evaluated and
+     and the column is filled with the produced SQL/JSON items, one for each
+     row.
      If the <literal>PATH</literal> expression is omitted,
      <function>JSON_TABLE</function> uses the
      <literal>$.<replaceable>name</replaceable></literal> path expression,
@@ -18185,9 +18185,8 @@ FROM
      item into each row of this column.
     </para>
     <para>
-     The provided <literal>PATH</literal> expression parses the
-     row pattern defined by <parameter>json_api_common_syntax</parameter>
-     and fills the column with produced SQL/JSON items, one for each row.
+     The provided <literal>PATH</literal> expression is evaluated and
+     the column is filled with the 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,
@@ -18216,11 +18215,10 @@ FROM
      Generates a column and inserts a boolean item into each row of this column.
     </para>
     <para>
-     The provided <literal>PATH</literal> expression parses the
-     row pattern defined by <parameter>json_api_common_syntax</parameter>,
-     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 provided <literal>PATH</literal> expression is evaluated,
+     a check whether any SQL/JSON items were returned is done, and
+     the column is filled with the resulting boolean value, one for each row.
+     The specified <parameter>type</parameter> should have a cast from the
      <type>boolean</type>.
      If the <literal>PATH</literal> expression is omitted,
      <function>JSON_TABLE</function> uses the

Erik Rijkers

er@xs4all.nl

over 3 years ago

In reply to: Andrew Dunstan (#3)

Re: SQL/JSON documentation JSON_TABLE

On 7/14/22 17:45, Andrew Dunstan wrote:

On 2022-07-08 Fr 16:20, Andrew Dunstan wrote:

On 2022-07-08 Fr 16:03, Erik Rijkers wrote:

Hi,

Attached are a few small changes to the JSON_TABLE section in func.sgml.

The first two changes are simple typos.

Then there was this line:

----
context_item, path_expression [ AS json_path_name ] [ PASSING { value
AS varname } [, ...]]
----

those are the parameters to JSON_TABLE() so I changed that line to:

----
JSON_TABLE(context_item, path_expression [ AS json_path_name ] [
PASSING { value AS varname } [, ...]])
----

Some parts of the JSON_TABLE text strike me as opaque. For instance,
there are paragraphs that more than once use the term:
   json_api_common_syntax

'json_api_common_syntax' is not explained. It turns out it's a relic
from Nikita's original docs. I dug up a 2018 patch where the term is
used as:

---- 2018:
JSON_TABLE (
json_api_common_syntax [ AS path_name ]
COLUMNS ( json_table_column [, ...] )
     (etc...)
----

with explanation:

---- 2018:
json_api_common_syntax:
   The input data to query, the JSON path expression defining the
query, and an optional PASSING clause.
----

So that made sense then (input+jsonpath+params=api), but it doesn't
now fit as such in the current docs.

I think it would be best to remove all uses of that compound term, and
rewrite the explanations using only the current parameter names
(context_item, path_expression, etc).

Thanks for this. If you want to follow up that last sentence I will try
to commit a single fix early next week.

Here's a patch that deals with most of this. There's one change you
wanted that I don't think is correct, which I omitted.

[json-docs-fix.patch]

Thanks, much better. I also agree that the change I proposed (and you
omitted) wasn't great (although it leaves the paragraph somewhat
orphaned - but maybe it isn't too bad.).

I've now compared our present document not only with the original doc as
produced by Nikita Glukhov et al in 2018, but also with the ISO draft
from 2017 (ISO/IEC TR 19075-6 (JSON) for JavaScript Object).

I think we can learn a few things from that ISO draft's JSON_TABLE text.
Let me copy-paste its first explicatory paragraph on JSON_TABLE:

-------------- [ ISO SQL/JSON draft 2017 ] ---------
Like the other JSON querying operators, JSON_TABLE begins with <JSON API
common syntax> to specify the context item, path expression and PASSING
clause. The path expression in this case is more accurately called the
row pattern path expression. This path expression is intended to produce
an SQL/JSON sequence, with one SQL/JSON item for each row of the output
table.

The COLUMNS clause can define two kinds of columns: ordinality columns
and regular columns.

An ordinality column provides a sequential numbering of rows. Row
numbering is 1-based.

A regular column supports columns of scalar type. The column is produced
using the semantics of JSON_VALUE. The column has an optional path
expression, called the column pattern, which can be defaulted from the
column name. The column pattern is used to search for the column within
the current SQL/JSON item produced by the row pattern. The column also
has optional ON EMPTY and ON ERROR clauses, with the same choices and
semantics as JSON_VALUE.
--------------

So, where the ISO draft introduces the term 'row pattern' it /also/
introduces the term 'column pattern' close by, in the next paragraph.

I think our docs too should have both terms. The presence of both 'row
pattern' and 'column pattern' immediately makes their meanings obvious.
At the moment our docs only use the term 'row pattern', for all the
JSON_TABLE json path expressions (also those in the COLUMN clause, it
seems).

At the moment, we say, in the JSON_TABLE doc:
----
To split the row pattern into columns, json_table provides the COLUMNS
clause that defines the schema of the created view.
----

I think that to use 'row pattern' here is just wrong, or at least
confusing. The 'row pattern' is /not/ the data as produced from the
json expression; the 'row pattern' /is/ the json path expression. (ISO
draft: 'The path expression in this case is more accurately called the
row pattern path expression.' )

If you agree with my reasoning I can try to rewrite these bits in our
docs accordingly.

Erik Rijkers

Andrew Dunstan

andrew@dunslane.net

over 3 years ago

In reply to: Erik Rijkers (#4)

Re: SQL/JSON documentation JSON_TABLE

On 2022-07-15 Fr 02:20, Erik Rijkers wrote:

On 7/14/22 17:45, Andrew Dunstan wrote:

Here's a patch that deals with most of this. There's one change you
wanted that I don't think is correct, which I omitted.

[json-docs-fix.patch]

Thanks, much better. I also agree that the change I proposed (and you
omitted) wasn't great (although it leaves the paragraph somewhat
orphaned - but maybe it isn't too bad.).

I've now compared our present document not only with the original doc
as produced by Nikita Glukhov et al in 2018, but also with the ISO
draft from 2017 (ISO/IEC TR 19075-6 (JSON) for JavaScript Object).

I think we can learn a few things from that ISO draft's JSON_TABLE
text. Let me copy-paste its first explicatory paragraph on JSON_TABLE:

-------------- [ ISO SQL/JSON draft 2017 ] ---------
Like the other JSON querying operators, JSON_TABLE begins with <JSON
API common syntax> to specify the context item, path expression and
PASSING clause. The path expression in this case is more accurately
called the row pattern path expression. This path expression is
intended to produce an SQL/JSON sequence, with one SQL/JSON item for
each row of the output table.

The COLUMNS clause can define two kinds of columns: ordinality columns
and regular columns.

An ordinality column provides a sequential numbering of rows. Row
numbering is 1-based.

A regular column supports columns of scalar type. The column is
produced using the semantics of JSON_VALUE. The column has an optional
path expression, called the column pattern, which can be defaulted
from the column name. The column pattern is used to search for the
column within the current SQL/JSON item produced by the row pattern.
The column also has optional ON EMPTY and ON ERROR clauses, with the
same choices and semantics as JSON_VALUE.
--------------

So, where the ISO draft introduces the term 'row pattern' it /also/
introduces the term 'column pattern' close by, in the next paragraph.

I think our docs too should have both terms. The presence of both
'row pattern' and 'column pattern' immediately makes their meanings
obvious. At the moment our docs only use the term 'row pattern', for
all the JSON_TABLE json path expressions (also those in the COLUMN
clause, it seems).

At the moment, we say, in the JSON_TABLE doc:
----
To split the row pattern into columns, json_table provides the COLUMNS
clause that defines the schema of the created view.
----

I think that to use 'row pattern' here is just wrong, or at least
confusing. The 'row pattern' is /not/ the data as produced from the
json expression; the 'row pattern' /is/ the json path expression.
(ISO draft: 'The path expression in this case is more accurately
called the row pattern path expression.' )

If you agree with my reasoning I can try to rewrite these bits in our
docs accordingly.

Yes, please do.

cheers

andrew

--
Andrew Dunstan
EDB: https://www.enterprisedb.com