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
>From fd71e22f087af2cd14c4a9101c96efb56888ab72 Mon Sep 17 00:00:00 2001
From: Anastasia <a.lubennik...@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.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
