Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1erBtS-0007uL-6Z for pgsql-hackers@arkaria.postgresql.org; Thu, 01 Mar 2018 00:16:18 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1erBtQ-00076r-O9 for pgsql-hackers@arkaria.postgresql.org; Thu, 01 Mar 2018 00:16:16 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1erBtQ-00076i-CQ for pgsql-hackers@lists.postgresql.org; Thu, 01 Mar 2018 00:16:16 +0000 Received: from mail.postgrespro.ru ([93.174.131.138]) by magus.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1erBtL-0006UM-7b for pgsql-hackers@lists.postgresql.org; Thu, 01 Mar 2018 00:16:15 +0000 Received: from localhost (localhost [127.0.0.1]) by mail.postgrespro.ru (Postfix) with ESMTP id 64E3521CA038; Thu, 1 Mar 2018 03:16:09 +0300 (MSK) X-Virus-Scanned: Debian amavisd-new at postgrespro.ru X-Spam-Flag: NO X-Spam-Score: 0 X-Spam-Level: X-Spam-Status: No, score=x tagged_above=-99 required=4 WHITELISTED tests=[] autolearn=unavailable Received: from [10.100.236.30] (unknown [195.82.154.27]) (using TLSv1.2 with cipher ECDHE-RSA-AES128-GCM-SHA256 (128/128 bits)) (Client did not present a certificate) by mail.postgrespro.ru (Postfix) with ESMTPSA id 094AE21CA020; Thu, 1 Mar 2018 03:16:08 +0300 (MSK) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=postgrespro.ru; s=mail; t=1519863369; bh=5phYxmFoH37EPUi7u3c+JCYp8Ei38WdrT1KJTAQmQJw=; h=Subject:To:References:From:Date:In-Reply-To; b=Gnof2Jxh9UdT+L8ewvRphrk9ZjSbHj5+X4REbxaFZZVz6YkomB0Uwn7uTbdJaGWTc bwraeblF6pHu5v0mtoTWpIVhZL73zmAJtRFVP/i9isdJr0CNdRGF7AIE/mVV8zK6yF dnDaL/mKBZfuPBRe9EX+y/lubCk2C7o5iBN4m/oo= Subject: Re: Cast jsonb to numeric, int, float, bool To: Darafei Praliaskouski , Anastasia Lubennikova , pgsql-hackers@lists.postgresql.org References: <0154d35a-24ae-f063-5273-9ffcdf1c7f2e@postgrespro.ru> <9c013081-5dc0-1b87-a559-a0c2d405727a@postgrespro.ru> <151985418868.6915.17223208131889314092.pgcf@coridan.postgresql.org> From: Nikita Glukhov Organization: Postgres Professional Message-ID: <76b000ae-94d1-cd8f-a2e0-e536096469b0@postgrespro.ru> Date: Thu, 1 Mar 2018 03:12:38 +0300 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:52.0) Gecko/20100101 Thunderbird/52.6.0 MIME-Version: 1.0 In-Reply-To: <151985418868.6915.17223208131889314092.pgcf@coridan.postgresql.org> Content-Type: multipart/mixed; boundary="------------E207B489B25F0643CD1C8D65" Content-Language: en-US List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------E207B489B25F0643CD1C8D65 Content-Type: multipart/alternative; boundary="------------9132824F86EF0A156293DF01" --------------9132824F86EF0A156293DF01 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 7bit 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 --------------9132824F86EF0A156293DF01 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 7bit

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
--------------9132824F86EF0A156293DF01-- --------------E207B489B25F0643CD1C8D65 Content-Type: text/x-patch; name="jsonb_numeric,int,float,bool_cast_11.0_v1.patch" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename="jsonb_numeric,int,float,bool_cast_11.0_v1.patch" From fd71e22f087af2cd14c4a9101c96efb56888ab72 Mon Sep 17 00:00:00 2001 From: Anastasia Date: Thu, 1 Mar 2018 01:55:31 +0300 Subject: [PATCH] Cast jsonbNumeric to numeric, int4 and float8. Cast jsonbBool to bool. --- src/backend/utils/adt/jsonb.c | 95 +++++++++++++++++++++++++++++++++++++++++++ src/include/catalog/pg_cast.h | 5 +++ src/include/catalog/pg_proc.h | 9 ++++ 3 files changed, 109 insertions(+) diff --git a/src/backend/utils/adt/jsonb.c b/src/backend/utils/adt/jsonb.c index 0f70180..fb907f2 100644 --- a/src/backend/utils/adt/jsonb.c +++ b/src/backend/utils/adt/jsonb.c @@ -1845,3 +1845,98 @@ jsonb_object_agg_finalfn(PG_FUNCTION_ARGS) PG_RETURN_POINTER(out); } + + +/* + * Extract scalar value from raw-scalar pseudo-array jsonb. + */ +static JsonbValue * +JsonbExtractScalar(JsonbContainer *jbc, JsonbValue *res) +{ + JsonbIterator *it; + JsonbIteratorToken tok PG_USED_FOR_ASSERTS_ONLY; + JsonbValue tmp; + + if (!JsonContainerIsArray(jbc) || !JsonContainerIsScalar(jbc)) + return NULL; + + /* + * A root scalar is stored as an array of one element, so we get the + * array and then its first (and only) member. + */ + it = JsonbIteratorInit(jbc); + + tok = JsonbIteratorNext(&it, &tmp, true); + Assert(tok == WJB_BEGIN_ARRAY); + Assert(tmp.val.array.nElems == 1 && tmp.val.array.rawScalar); + + tok = JsonbIteratorNext(&it, res, true); + Assert (tok == WJB_ELEM); + Assert(IsAJsonbScalar(res)); + + tok = JsonbIteratorNext(&it, &tmp, true); + Assert (tok == WJB_END_ARRAY); + + tok = JsonbIteratorNext(&it, &tmp, true); + Assert(tok == WJB_DONE); + + return res; +} + +Datum +jsonb_numeric(PG_FUNCTION_ARGS) +{ + Jsonb *in = PG_GETARG_JSONB_P(0); + JsonbValue v; + + if (!JsonbExtractScalar(&in->root, &v) || v.type != jbvNumeric) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("jsonb value must be numeric"))); + + PG_RETURN_NUMERIC(v.val.numeric); +} + +Datum +jsonb_int4(PG_FUNCTION_ARGS) +{ + Jsonb *in = PG_GETARG_JSONB_P(0); + JsonbValue v; + + if (!JsonbExtractScalar(&in->root, &v) || v.type != jbvNumeric) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("jsonb value must be numeric"))); + + PG_RETURN_INT32(DatumGetInt32(DirectFunctionCall1(numeric_int4, + NumericGetDatum(v.val.numeric)))); +} + +Datum +jsonb_float8(PG_FUNCTION_ARGS) +{ + Jsonb *in = PG_GETARG_JSONB_P(0); + JsonbValue v; + + if (!JsonbExtractScalar(&in->root, &v) || v.type != jbvNumeric) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("jsonb value must be numeric"))); + + PG_RETURN_FLOAT8(DatumGetFloat8(DirectFunctionCall1(numeric_float8, + NumericGetDatum(v.val.numeric)))); +} + +Datum +jsonb_bool(PG_FUNCTION_ARGS) +{ + Jsonb *in = PG_GETARG_JSONB_P(0); + JsonbValue v; + + if (!JsonbExtractScalar(&in->root, &v) || v.type != jbvBool) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("jsonb value must be boolean"))); + + PG_RETURN_BOOL(v.val.boolean); +} diff --git a/src/include/catalog/pg_cast.h b/src/include/catalog/pg_cast.h index b447818..ea765df 100644 --- a/src/include/catalog/pg_cast.h +++ b/src/include/catalog/pg_cast.h @@ -391,5 +391,10 @@ DATA(insert ( 1700 1700 1703 i f )); /* json to/from jsonb */ DATA(insert ( 114 3802 0 a i )); DATA(insert ( 3802 114 0 a i )); +/* jsonb to numeric types */ +DATA(insert ( 3802 1700 4001 e f )); +DATA(insert ( 3802 23 4002 e f )); +DATA(insert ( 3802 701 4003 e f )); +DATA(insert ( 3802 16 4004 e f )); #endif /* PG_CAST_H */ diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h index c00d055..2f84978 100644 --- a/src/include/catalog/pg_proc.h +++ b/src/include/catalog/pg_proc.h @@ -2477,6 +2477,15 @@ DESCR("convert int2 to numeric"); DATA(insert OID = 1783 ( int2 PGNSP PGUID 12 1 0 0 0 f f f f t f i s 1 0 21 "1700" _null_ _null_ _null_ _null_ _null_ numeric_int2 _null_ _null_ _null_ )); DESCR("convert numeric to int2"); +DATA(insert OID = 4001 ( numeric PGNSP PGUID 12 1 0 0 0 f f f f t f i s 1 0 1700 "3802" _null_ _null_ _null_ _null_ _null_ jsonb_numeric _null_ _null_ _null_ )); +DESCR("convert jsonb to numeric"); +DATA(insert OID = 4002 ( int4 PGNSP PGUID 12 1 0 0 0 f f f f t f i s 1 0 23 "3802" _null_ _null_ _null_ _null_ _null_ jsonb_int4 _null_ _null_ _null_ )); +DESCR("convert jsonb to int4"); +DATA(insert OID = 4003 ( float8 PGNSP PGUID 12 1 0 0 0 f f f f t f i s 1 0 701 "3802" _null_ _null_ _null_ _null_ _null_ jsonb_float8 _null_ _null_ _null_ )); +DESCR("convert jsonb to float8"); +DATA(insert OID = 4004 ( bool PGNSP PGUID 12 1 0 0 0 f f f f t f i s 1 0 16 "3802" _null_ _null_ _null_ _null_ _null_ jsonb_bool _null_ _null_ _null_ )); +DESCR("convert jsonb to boolean"); + /* formatting */ DATA(insert OID = 1770 ( to_char PGNSP PGUID 12 1 0 0 0 f f f f t f s s 2 0 25 "1184 25" _null_ _null_ _null_ _null_ _null_ timestamptz_to_char _null_ _null_ _null_ )); DESCR("format timestamp with time zone to text"); -- 2.7.4 --------------E207B489B25F0643CD1C8D65--