From: Nikita Glukhov <n.gluhov@postgrespro.ru>
To: Darafei Praliaskouski <me@komzpa.net>
To: Anastasia Lubennikova <a.lubennikova@postgrespro.ru>
To: pgsql-hackers@lists.postgresql.org
Subject: Re: Cast jsonb to numeric, int, float, bool
Date: Thu, 1 Mar 2018 03:12:38 +0300
Message-ID: <76b000ae-94d1-cd8f-a2e0-e536096469b0@postgrespro.ru> (raw)
In-Reply-To: <151985418868.6915.17223208131889314092.pgcf@coridan.postgresql.org>
References: <0154d35a-24ae-f063-5273-9ffcdf1c7f2e@postgrespro.ru>
<9c013081-5dc0-1b87-a559-a0c2d405727a@postgrespro.ru>
<151985418868.6915.17223208131889314092.pgcf@coridan.postgresql.org>
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
Attachments:jsonb_numeric,int,float,bool_cast_11.0_v1.patch (text/x-patch, 5.0K) download
From fd71e22f087af2cd14c4a9101c96efb56888ab72 Mon Sep 17 00:00:00 2001
From: Anastasia <a.lubennikova@postgrespro.ru>
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.cindex 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.hindex 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.hindex 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: n.gluhov@postgrespro.ru, me@komzpa.net, a.lubennikova@postgrespro.ru, pgsql-hackers@lists.postgresql.org
Subject: Re: Cast jsonb to numeric, int, float, bool
In-Reply-To: <76b000ae-94d1-cd8f-a2e0-e536096469b0@postgrespro.ru>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox