Re: More new SQL/JSON item methods - Mailing list pgsql-hackers

On 2023-Aug-30, Chapman Flack wrote:

> Hi,
> 
> On 2023-08-29 03:05, Jeevan Chalke wrote:
> > This commit implements jsonpath .bigint(), .integer(), and .number()
> > ---
> > This commit implements jsonpath .date(), .time(), .time_tz(),
> > .timestamp(), .timestamp_tz() methods.
> > ---
> > This commit implements jsonpath .boolean() and .string() methods.
> 
> Writing as an interested outsider to the jsonpath spec, my first
> question would be, is there a published jsonpath spec independent
> of PostgreSQL, and are these methods in it, and are the semantics
> identical?

Looking at the SQL standard itself, in the 2023 edition section 9.46
"SQL/JSON path language: syntax and semantics", it shows this:

<JSON method> ::=
type <left paren> <right paren>
| size <left paren> <right paren>
| double <left paren> <right paren>
| ceiling <left paren> <right paren>
| floor <left paren> <right paren>
| abs <left paren> <right paren>
| datetime <left paren> [ <JSON datetime template> ] <right paren>
| keyvalue <left paren> <right paren>
| bigint <left paren> <right paren>
| boolean <left paren> <right paren>
| date <left paren> <right paren>
| decimal <left paren> [ <precision> [ <comma> <scale> ] ] <right paren>
| integer <left paren> <right paren>
| number <left paren> <right paren>
| string <left paren> <right paren>
| time <left paren> [ <time precision> ] <right paren>
| time_tz <left paren> [ <time precision> ] <right paren>
| timestamp <left paren> [ <timestamp precision> ] <right paren>
| timestamp_tz <left paren> [ <timestamp precision> ] <right paren>

and then details, for each of those, rules like

III) If JM specifies <double>, then:
     1) For all j, 1 (one) ≤ j ≤ n,
        Case:
    a) If I_j is not a number or character string, then let ST be data
           exception — non-numeric SQL/JSON item (22036).
        b) Otherwise, let X be an SQL variable whose value is I_j.
           Let V_j be the result of
           CAST (X AS DOUBLE PRECISION)
           If this conversion results in an exception condition, then
           let ST be that exception condition.
     2) Case:
        a) If ST is not successful completion, then the result of JAE
           is ST.
        b) Otherwise, the result of JAE is the SQL/JSON sequence V_1,
           ..., V_n.

so at least superficially our implementation is constrained by what the
SQL standard says to do, and we should verify that this implementation
matches those rules.  We don't necessarily need to watch what do other
specs such as jsonpath itself.

> The question comes out of my experience on a PostgreSQL integration
> of XQuery/XPath, which was nontrivial because the w3 specs for those
> languages give rigorous definitions of their data types, independently
> of SQL, and a good bit of the work was squinting at those types and at
> the corresponding PostgreSQL types to see in what ways they were
> different, and what the constraints on converting them were.

Yeah, I think the experience of the SQL committee with XML was pretty
bad, as you carefully documented.  I hope they don't make such a mess
with JSON.

-- 
Álvaro Herrera               48°01'N 7°57'E  —  https://www.EnterpriseDB.com/