Hi,
Thread [1] concerns (generalizing slightly) the efficient casting
to an SQL type of the result of a jsonb extracting operation
(array indexing, object keying, path evaluation) that has ended
with a scalar JsonbValue.
So far, it can efficiently rewrite casts to boolean or numeric
types.
I notice that, since 6dda292, JsonbValue includes a datetime
scalar member.
As far as I can tell, the only jsonb extracting operations
that might be capable of producing such a JsonbValue would be
jsonb_path_query(_first)?(_tz)? with a path ending in .datetime().
If casts existed from jsonb to date/time types, then the same
techniques used in [1] would be able to rewrite such casts,
eliding the JsonbValueToJsonb and subsequent reconversion via text.
But no such casts seem to exist, providing nothing to hang the
optimization on. (And, after all, 6dda292 says "These datetime
values are allowed for temporary representation only. During
serialization datetime values are converted into strings.")
Perhaps it isn't worth supplying such casts. The value is held
as text within jsonb, so .datetime() in a jsonpath had to parse
it. One might lament the extra serialization and reparsing if
that path query result goes through ::text::timestamp, but then
simply leaving .datetime() off of the jsonpath in the first place
would have left the parsing to be done just once by ::timestamp.
Optimizable casts might be of more interest if the jsonpath
language had more operations on datetimes, so that you might
efficiently retrieve the result of some arbitrary expression
in the path, not just a literal datetime value that has to get
parsed in one place or another anyway.
I haven't looked into SQL/JSON to see what it provides in terms
of casts to SQL types. I'm more familiar with SQL/XML, which does
provide XMLCAST, which can take an XML source and SQL date/time
target, and does the equivalent of an XML Query ending in
"cast as xs:dateTime" and assigns that result to the SQL type
(with some time zone subtleties rather carefully specified).
So I might assume SQL/JSON has something analogous?
On the other hand, XML Query does offer more operations on
date/time values, which may, as discussed above, make such a cast
more interesting to have around.
Thoughts?
Regards,
-Chap
[1]
https://www.postgresql.org/message-id/flat/CAKU4AWoqAVya6PBhn+BCbFaBMt3z-2=i5fKO3bW=6HPhbid2Dw@mail.gmail.com