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