Re: More new SQL/JSON item methods

>
> 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.
>

I believe our current implementation of the .double() method is in line with
this. And these new methods are following the same suit.

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

Yeah, we can directly check the value = 0 for false, true otherwise.
But looking at the PostgreSQL conversion to bool, it doesn't allow floating
point values to be converted to boolean and only accepts int4. That's why I
did the int4 conversion.

Thanks

--
Jeevan Chalke

*Senior Staff SDE, Database Architect, and ManagerProduct Development*

edbpostgres.com