Re: SQL/JSON documentation JSON_TABLE

From: Andrew Dunstan <andrew(at)dunslane(dot)net>
To: Erik Rijkers <er(at)xs4all(dot)nl>
Cc: PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Re: SQL/JSON documentation JSON_TABLE
Date: 2022-07-15 13:51:29
Message-ID: 99bbae89-8927-c77c-094f-90185f59ab63@dunslane.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers


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

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2022-07-15 14:03:24 Re: Allowing REINDEX to have an optional name
Previous Message osumi.takamichi@fujitsu.com 2022-07-15 13:43:14 RE: [BUG] Logical replication failure "ERROR: could not map filenode "base/13237/442428" to relation OID" with catalog modifying txns