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

On 01.03.2018 00:43, Darafei Praliaskouski wrote:

The following review has been posted through the commitfest application:
make installcheck-world:  tested, passed
Implements feature:       tested, passed
Spec compliant:           tested, passed
Documentation:            not tested

We're using this patch and like it a lot.

We store a lot of log-like data in s3-hosted .json.gz files.
Sometimes we need to suddenly ingest them and calculate statistics and check the new what-ifs.
We ingest data to simple single-column table with jsonb field, and then perform our calculation on top of it.
Without this patch the only option to get data already parsed as numbers into jsonb into calculation is via binary-text-binary transformation.

We're relying on the patch for our custom spatial type extension and casting in it.
https://github.com/gojuno/lostgis/blob/master/sql/types/type_tpv.sql#L21 

For postgres installations without the patch we do WITH INOUT cast stubbing,
https://github.com/gojuno/lostgis/blob/master/sql/types/__jsonb_casts.sql
- but without performance benefits of raw C processing :)

A thing this patch does not implement is cast from jsonb to bigint.
That would be useful for transforming stored osm_id OpenStreetMap object identifiers.
Right now we're stubbing it with jsonb::numeric::bigint cast, but the middle one would be nice to get rid of.

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 ERROR behavior (which is default in SQL/JSON)
in casts, then casts should not throw any errors.

-- 
Nikita Glukhov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company