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

From Jeevan Chalke
Subject Re: More new SQL/JSON item methods
Date
Msg-id CAM2+6=XgqwH40TVpDw-hcGfFAq4P3S8MoHrNguCzCGe5Cd05wQ@mail.gmail.com
Whole thread Raw
In response to Re: More new SQL/JSON item methods  (Alvaro Herrera <alvherre@alvh.no-ip.org>)
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.

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 Manager
Product Development




edbpostgres.com

pgsql-hackers by date:

Previous
From: "Lepikhov Andrei"
Date:
Subject: Re: Optimize planner memory consumption for huge arrays
Next
From: Dilip Kumar
Date:
Subject: Re: Impact of checkpointer during pg_upgrade