> Attached new version of the patch in which I removed duplicated code using new subroutine JsonbExtractScalar(). I am not sure what is better to do when a JSON item has an unexpected type: to throw an error or to return SQL NULL. Also JSON nulls could be converted to SQL NULLs.
I would expect it to follow whatever is happening in JavaScript.
I'm unsure about mapping of NULL and undefined/null though.
> I should note here that expression (jb -> 'key')::datatype can be rewritten with SQL/JSON function JSON_VALUE: JSON_VALUE(jb, '$.key' RETURNING datatype ERROR ON ERROR)
I would expect some casts to be implicit, so that chaining with other functions is possible:
select ST_MakePoint(r->'lon', r->'lat');
select sum(r->'income');
> But by standard JSON_VALUE tries to cast string JSON items to the specified datatype too, so JSON_VALUE('{"key": "123"}'::jsonb, '$.key' RETURNING int ERROR ON ERROR) does not throw an error but returns 123.
In actual JSON implementations number datatype is usually the one available in browsers, double precision.
For some numbers (I've met this with nanoseconds) it leads to value being changed on subsequent serializations and deserializations, so it's common to wrap them in a string to be unchanged.
So, I would expect that to work, but give me an exception if the datatype loses precision on conversion of specific value.