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

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?

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. (Some of
that squinting was already done by the SQL committee in the SQL/XML
spec, which has plural pages on how those conversions have to happen,
especially for the date/time types.)

If I look in [1], am I looking in the right place for the most
current jsonpath draft?

(I'm a little squeamish reading as a goal "cover only essential
parts of XPath 1.0", given that XPath 1.0 is the one w3 threw away
so XPath 2.0 wouldn't have the same problems.)

On details of the patch itself, I only have quick first impressions,
like:

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

- I notice that .bigint() and .integer() finish up by casting the
   value to numeric so the existing jbv->val.numeric can hold it.
   That may leave some opportunity on the table: there is another
   patch under way [2] that concerns quickly getting such result
   values from json operations to the surrounding SQL query. That
   could avoid the trip through numeric completely if the query
   wants a bigint, if there were a val.bigint in JsonbValue.

   But of course that would complicate everything else that
   touches JsonbValue. Is there a way for a jsonpath operator to
   determine that it's the terminal operation in the path, and
   leave a value in val.bigint if it is, or build a numeric if
   it's not? Then most other jsonpath code could go on expecting
   a numeric value is always in val.numeric, and the only code
   checking for a val.bigint would be code involved with
   getting the result value out to the SQL caller.

Regards,
-Chap


[1] 
https://www.ietf.org/archive/id/draft-goessner-dispatch-jsonpath-00.html
[2] https://commitfest.postgresql.org/44/4476/