Re: Re: Cast jsonb to numeric, int, float, bool

On 2/28/18 7:12 PM, Nikita Glukhov wrote:
> On 01.03.2018 00:43, Darafei Praliaskouski wrote:
>>
>> The new status of this patch is: Ready for Committer
>
> 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
> 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) 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. We already have jsonpath
> operators @#, @*, so it might be very useful if our jsonb casts were
> equivalent to theirs SQL/JSON analogues. For example, (jb @#
> '$.key')::datatype could be equivalent to JSON_VALUE(jb, '$.key'
> RETURNING datatype ERROR ON ERROR) or JSON_VALUE(jb, '$.key' RETURNING
> datatype [NULL ON ERROR]). But if we want to have NULL ON ERRORbehavior
> (which is default in SQL/JSON) in casts, then casts should not throw any
> errors.

Since this patch was updated after being set a Ready for Committer and
there appear to be some open questions, I have set it to Needs Review.

Thanks,
--
-David
david(at)pgmasters(dot)net