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

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.

> - surely there's a more direct way to make boolean from numeric
>   than to serialize the numeric and parse an int?