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