To: pgsql-hackers <pgsql-hackers@postgresql.org>
From: Anastasia Lubennikova <a.lubennikova@postgrespro.ru>
Subject: Cast jsonb to numeric, int, float, bool
Message-ID: <0154d35a-24ae-f063-5273-9ffcdf1c7f2e@postgrespro.ru>
Date: Wed, 1 Feb 2017 17:41:34 +0300
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:45.0) Gecko/20100101
 Thunderbird/45.5.1
MIME-Version: 1.0
Content-Type: multipart/mixed;
 boundary="------------F65D30624E0DFCB6FDB53B08"
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org

This is a multi-part message in MIME format.
--------------F65D30624E0DFCB6FDB53B08
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit

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.

-- 
Anastasia Lubennikova
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company


--------------F65D30624E0DFCB6FDB53B08
Content-Type: text/x-patch;
 name="jsonb_numeric,int,float,bool_cast_v1.patch"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
 filename="jsonb_numeric,int,float,bool_cast_v1.patch"

diff --git a/src/backend/utils/adt/jsonb.c b/src/backend/utils/adt/jsonb.c
index b9bf18f..4bbe81c 100644
--- a/src/backend/utils/adt/jsonb.c
+++ b/src/backend/utils/adt/jsonb.c
@@ -1939,3 +1939,129 @@ jsonb_object_agg_finalfn(PG_FUNCTION_ARGS)
 
 	PG_RETURN_POINTER(out);
 }
+
+Datum
+jsonb_numeric(PG_FUNCTION_ARGS)
+{
+	Jsonb	   *in = PG_GETARG_JSONB(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(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(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(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")));
+}
\ No newline at end of file
diff --git a/src/include/catalog/pg_cast.h b/src/include/catalog/pg_cast.h
index 80a40ab..0646f99 100644
--- a/src/include/catalog/pg_cast.h
+++ b/src/include/catalog/pg_cast.h
@@ -377,5 +377,9 @@ 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 ));
+DATA(insert ( 3802	1700   774 e f ));
+DATA(insert ( 3802	23     775 e f ));
+DATA(insert ( 3802	701    776 e f ));
+DATA(insert ( 3802	16     777 e f ));
 
 #endif   /* PG_CAST_H */
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index 31c828a..ff7da5b 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -2364,6 +2364,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 = 774 ( 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 = 775 ( 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 = 776 ( float4			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 = 777 ( 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.h
index 5bdca82..090767c 100644
--- a/src/include/utils/builtins.h
+++ b/src/include/utils/builtins.h
@@ -120,4 +120,9 @@ 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_float4(PG_FUNCTION_ARGS);
+
 #endif   /* BUILTINS_H */

--------------F65D30624E0DFCB6FDB53B08
Content-Type: text/plain
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
MIME-Version: 1.0


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

--------------F65D30624E0DFCB6FDB53B08--