From: | Nikita Glukhov <n(dot)gluhov(at)postgrespro(dot)ru> |
---|---|
To: | Darafei Praliaskouski <me(at)komzpa(dot)net>, Anastasia Lubennikova <a(dot)lubennikova(at)postgrespro(dot)ru>, pgsql-hackers(at)lists(dot)postgresql(dot)org |
Subject: | Re: Cast jsonb to numeric, int, float, bool |
Date: | 2018-03-01 00:12:38 |
Message-ID: | 76b000ae-94d1-cd8f-a2e0-e536096469b0@postgrespro.ru |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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 ERRORbehavior
(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
Attachment | Content-Type | Size |
---|---|---|
jsonb_numeric,int,float,bool_cast_11.0_v1.patch | text/x-patch | 5.0 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2018-03-01 00:26:48 | Re: IndexTupleDSize macro seems redundant |
Previous Message | Tom Lane | 2018-03-01 00:03:01 | Re: Missing comment edit |