From: Anastasia Lubennikova <a.lubennikova@postgrespro.ru>
To: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Cast jsonb to numeric, int, float, bool
Date: Wed, 28 Feb 2018 21:42:45 +0300
Message-ID: <9c013081-5dc0-1b87-a559-a0c2d405727a@postgrespro.ru> (raw)
In-Reply-To: <0154d35a-24ae-f063-5273-9ffcdf1c7f2e@postgrespro.ru>
References: <0154d35a-24ae-f063-5273-9ffcdf1c7f2e@postgrespro.ru>
01.02.2017 17:41, Anastasia Lubennikova:
> Now the simplest way to extract booleans and numbers from json/jsonb
> is to cast it
> to text and then cast to the appropriate type:
>
> postgres=# select 'true'::jsonb::text::bool;
> bool
> ------
> t
> postgres=# select '1.0'::jsonb::text::numeric;
> numeric
> ---------
> 1.0
>
>
> This patch implements direct casts from jsonb numeric (jbvNumeric) to
> numeric, int4 and float8,
> and from jsonb bool (jbvBool) to bool.
>
> postgres=# select 'true'::jsonb::bool;
> bool
> ------
> t
>
> postgres=# select '1.0'::jsonb::numeric;
> numeric
> ---------
> 1.0
>
>
> Waiting for your feedback.
> If you find it useful, I can also add support of json and other types,
> such as smallint and bigint.
>
>
I totally forgot about this thread, but it is a small but useful feature.
Maybe it's time to dust it off.
This patch was made by request of one of our clients,
and though they already use custom build, I think it would be better to
have these casts in core.
The patch is attached.
There are no tests yet, since I don't really sure what set of types do
we want to support.
Now the patch provides jsonb to numeric, int4, float8 and bool.
Also I have some doubts about castcontext. But 'explisit' seems to be
the correct one here.
--
Anastasia Lubennikova
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
Attachments:jsonb_numeric,int,float,bool_cast_11.0_v0.patch (text/x-patch, 6.0K) download
commit 03b57ad50b9fe5bc785958bcf16badb58971d489
Author: Anastasia <a.lubennikova@postgrespro.ru>
Date: Wed Feb 28 21:14:31 2018 +0300
Cast jsonbNumeric to numeric, int4 and float8.
Cast jsonbBool to bool
diff --git a/src/backend/utils/adt/jsonb.c b/src/backend/utils/adt/jsonb.cindex 0f70180..3678dbe 100644--- a/src/backend/utils/adt/jsonb.c+++ b/src/backend/utils/adt/jsonb.c@@ -1845,3 +1845,129 @@ jsonb_object_agg_finalfn(PG_FUNCTION_ARGS)
PG_RETURN_POINTER(out);
}
++Datum+jsonb_numeric(PG_FUNCTION_ARGS)+{+ Jsonb *in = PG_GETARG_JSONB_P(0);+ JsonbIterator *it;+ JsonbValue v;++ if (!JB_ROOT_IS_OBJECT(in)+ && !(JB_ROOT_IS_ARRAY(in) && !JB_ROOT_IS_SCALAR(in)))+ {+ Assert(JB_ROOT_IS_SCALAR(in));++ it = JsonbIteratorInit(&in->root);++ /*+ * A root scalar is stored as an array of one element, so we get the+ * array and then its first (and only) member.+ */+ (void) JsonbIteratorNext(&it, &v, true);+ Assert(v.type == jbvArray);+ (void) JsonbIteratorNext(&it, &v, true);++ if (v.type == jbvNumeric)+ PG_RETURN_NUMERIC(v.val.numeric);+ }++ ereport(ERROR,+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),+ errmsg("key value must be json numeric")));+}++Datum+jsonb_int4(PG_FUNCTION_ARGS)+{+ Jsonb *in = PG_GETARG_JSONB_P(0);+ JsonbIterator *it;+ JsonbValue v;++ if (!JB_ROOT_IS_OBJECT(in)+ && !(JB_ROOT_IS_ARRAY(in) && !JB_ROOT_IS_SCALAR(in)))+ {+ Assert(JB_ROOT_IS_SCALAR(in));++ it = JsonbIteratorInit(&in->root);++ /*+ * A root scalar is stored as an array of one element, so we get the+ * array and then its first (and only) member.+ */+ (void) JsonbIteratorNext(&it, &v, true);+ Assert(v.type == jbvArray);+ (void) JsonbIteratorNext(&it, &v, true);++ if (v.type == jbvNumeric)+ PG_RETURN_INT32(DatumGetInt32(DirectFunctionCall1(numeric_int4,+ NumericGetDatum(v.val.numeric))));+ }++ ereport(ERROR,+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),+ errmsg("key value must be json numeric")));+}++Datum+jsonb_float8(PG_FUNCTION_ARGS)+{+ Jsonb *in = PG_GETARG_JSONB_P(0);+ JsonbIterator *it;+ JsonbValue v;++ if (!JB_ROOT_IS_OBJECT(in)+ && !(JB_ROOT_IS_ARRAY(in) && !JB_ROOT_IS_SCALAR(in)))+ {+ Assert(JB_ROOT_IS_SCALAR(in));++ it = JsonbIteratorInit(&in->root);++ /*+ * A root scalar is stored as an array of one element, so we get the+ * array and then its first (and only) member.+ */+ (void) JsonbIteratorNext(&it, &v, true);+ Assert(v.type == jbvArray);+ (void) JsonbIteratorNext(&it, &v, true);++ if (v.type == jbvNumeric)+ PG_RETURN_FLOAT8(DatumGetFloat8(DirectFunctionCall1(numeric_float8,+ NumericGetDatum(v.val.numeric))));+ }++ ereport(ERROR,+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),+ errmsg("key value must be json numeric")));+}++Datum+jsonb_bool(PG_FUNCTION_ARGS)+{+ Jsonb *in = PG_GETARG_JSONB_P(0);+ JsonbIterator *it;+ JsonbValue v;++ if (!JB_ROOT_IS_OBJECT(in)+ && !(JB_ROOT_IS_ARRAY(in) && !JB_ROOT_IS_SCALAR(in)))+ {+ Assert(JB_ROOT_IS_SCALAR(in));++ it = JsonbIteratorInit(&in->root);++ /*+ * A root scalar is stored as an array of one element, so we get the+ * array and then its first (and only) member.+ */+ (void) JsonbIteratorNext(&it, &v, true);+ Assert(v.type == jbvArray);+ (void) JsonbIteratorNext(&it, &v, true);++ if (v.type == jbvBool)+ PG_RETURN_BOOL(v.val.boolean);+ }++ ereport(ERROR,+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),+ errmsg("key value must be json 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");
diff --git a/src/include/utils/builtins.h b/src/include/utils/builtins.hindex 3e462f1..2b01f17 100644--- a/src/include/utils/builtins.h+++ b/src/include/utils/builtins.h@@ -128,4 +128,10 @@ extern int32 type_maximum_size(Oid type_oid, int32 typemod);
/* quote.c */
extern char *quote_literal_cstr(const char *rawstr);
+/* jsonb.c */+extern Datum jsonb_numeric(PG_FUNCTION_ARGS);+extern Datum jsonb_int4(PG_FUNCTION_ARGS);+extern Datum jsonb_float8(PG_FUNCTION_ARGS);+extern Datum jsonb_bool(PG_FUNCTION_ARGS);+
#endif /* BUILTINS_H */
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: a.lubennikova@postgrespro.ru
Subject: Re: [HACKERS] Cast jsonb to numeric, int, float, bool
In-Reply-To: <9c013081-5dc0-1b87-a559-a0c2d405727a@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