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