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
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(>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(, , true);
+ Assert(v.type == jbvArray);
+ (void) JsonbIteratorNext(, , 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(>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(, , true);
+ Assert(v.type == jbvArray);
+ (void) JsonbIteratorNext(, , 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(>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(, , true);
+ Assert(v.type == jbvArray);
+ (void) JsonbIteratorNext(, , 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(>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(, , true);
+ Assert(v.type == jbvArray);
+ (void) JsonbIteratorNext(, , 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 38020 a i ));
DATA(insert ( 3802 1140 a i ));
+DATA(insert ( 3802 1700 774 e f ));
+DATA(insert ( 3802 23 775 e f ));
+DATA(insert ( 3802 701776 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_
