public inbox for [email protected]
help / color / mirror / Atom feedFrom: Andy Fan <[email protected]>
To: Tom Lane <[email protected]>
Cc: Chapman Flack <[email protected]>
Cc: Pavel Stehule <[email protected]>
Cc: jian he <[email protected]>
Cc: pgsql-hackers <[email protected]>
Subject: Re: Extract numeric filed in JSONB more effectively
Date: Mon, 14 Aug 2023 23:42:12 +0800
Message-ID: <CAKU4AWrBY9GHj9oZbvhiOG1BgiWyZC8FGPAET-CfRKDhYyv1HQ@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <CAKU4AWoqAVya6PBhn+BCbFaBMt3z-2=i5fKO3bW=6HPhbid2Dw@mail.gmail.com>
<CACJufxHASOEpngQ8V2tbXgs4VZC3ETrVbS=uk0KC_B_J1j7ejQ@mail.gmail.com>
<CAKU4AWrap1zpYqunJwWTN=CdP7E8e0U4mYmwn7hvTW3ERuENVg@mail.gmail.com>
<CAFj8pRD-R-GsGCjeYApbhZoiW8TV6zACaYStMBMM0=--+WgN_A@mail.gmail.com>
<CAKU4AWpDdFXAD+dMC1HeErXSKBUUBRGWkf=dAcX3wZgBNsWM=g@mail.gmail.com>
<CAFj8pRAO3oEiBaJJ9=HZp6CoP2ffbwSgrKkLKjPfYZwx9wOOuQ@mail.gmail.com>
<CAKU4AWoCHpKAVuQeOrk44cVPy_dVxn1aHrMUvHy5Ag-daFCSsQ@mail.gmail.com>
<CAFj8pRD4cdUmK0RG4oN5B2KRSeDhwfMYaL=XpfEu4iaLeZ_Kow@mail.gmail.com>
<CAKU4AWp8ab61e96v57OaB-Gm1bMfBNVLVy+s17U6_Ne3veB84g@mail.gmail.com>
<[email protected]>
<CAKU4AWp+KLes8g=BWLqZfDmW9+=ZY0UC4G0i3qVcYEviK_dDTA@mail.gmail.com>
<[email protected]>
<[email protected]>
On Mon, Aug 14, 2023 at 10:10 PM Tom Lane <[email protected]> wrote:
> Chapman Flack <[email protected]> writes:
> > Providing a function with return type declared internal but
> > with no parameter of that type is not good,
>
> Not so much "not good" as "absolutely, positively WILL NOT HAPPEN".
Chap is pretty nice to others:).
>
>
> because then a
> > user could, in principle, call it and obtain a value of
> > 'internal' type, and so get around the typing rules that
> > prevent calling other internal functions.
>
> Right --- it'd completely break the system's type-safety for
> other internal-using functions.
>
>
I do see something bad in opr_sanity.sql. Pavel suggested
get_fn_expr_argtype which can resolve this issue pretty well, so
I have changed
jsonb_extract_xx_type(.., Oid taget_oid) -> anyelement.
to
jsonb_extract_xx_type(.., anyelement) -> anyelement.
The only bad smell left is since I want to define jsonb_extract_xx_type
as strict so I can't use jsonb_extract_xx_type(.., NULL::a-type)
since it will be evaluated to NULL directly. So I hacked it with
/* mock the type. */
Const *target = makeNullConst(fexpr->funcresulttype,
-1,
InvalidOid);
/* hack the NULL attribute */
/*
* Since all the above functions are strict, we can't input
* a NULL value.
*/
target->constisnull = false;
jsonb_extract_xx_type just cares about the argtype, but
'explain select xx' will still access the const->constvalue.
const->constvalue is 0 which is set by makeNullConst currently,
and it is ok for the current supported type. but I'm not sure
about the future or if we still have a better solution.
v6 is attached. any feedback is welcome!
--
Best Regards
Andy Fan
Attachments:
[application/octet-stream] v6-0001-optimize-casting-jsonb-to-a-given-type.patch (27.4K, 3-v6-0001-optimize-casting-jsonb-to-a-given-type.patch)
download | inline diff:
From 21931a88a3a10a49f20d9690590f748581169bf3 Mon Sep 17 00:00:00 2001
From: Andy Fan <[email protected]>
Date: Thu, 10 Aug 2023 15:15:25 +0800
Subject: [PATCH v6] optimize casting jsonb to a given type.
Previously after we get a JsonbValue, we need to convert it to
Jsonb first then cast the Jsonb to the given type. In this patch,
we covert the JsonbValue to the desired type directly.
---
src/backend/utils/adt/jsonb.c | 183 ++++++++++++++++++++++++++++
src/backend/utils/adt/jsonfuncs.c | 114 +++++++++++------
src/include/catalog/catversion.h | 2 +-
src/include/catalog/pg_proc.dat | 32 +++--
src/include/utils/jsonb.h | 1 +
src/test/regress/expected/jsonb.out | 160 ++++++++++++++----------
src/test/regress/sql/jsonb.sql | 43 ++++---
7 files changed, 404 insertions(+), 131 deletions(-)
diff --git a/src/backend/utils/adt/jsonb.c b/src/backend/utils/adt/jsonb.c
index 9781852b0cb..aa2b830b646 100644
--- a/src/backend/utils/adt/jsonb.c
+++ b/src/backend/utils/adt/jsonb.c
@@ -17,11 +17,14 @@
#include "catalog/pg_proc.h"
#include "catalog/pg_type.h"
#include "funcapi.h"
+#include "nodes/makefuncs.h"
+#include "nodes/supportnodes.h"
#include "libpq/pqformat.h"
#include "miscadmin.h"
#include "utils/builtins.h"
#include "utils/date.h"
#include "utils/datetime.h"
+#include "utils/fmgroids.h"
#include "utils/json.h"
#include "utils/jsonb.h"
#include "utils/jsonfuncs.h"
@@ -2038,6 +2041,186 @@ cannotCastJsonbValue(enum jbvType type, const char *sqltype)
elog(ERROR, "unknown jsonb type: %d", (int) type);
}
+static bool
+jsonb_cast_is_optimized(Oid target_type)
+{
+ switch(target_type)
+ {
+ case NUMERICOID:
+ case BOOLOID:
+ case INT2OID:
+ case INT4OID:
+ case INT8OID:
+ case FLOAT4OID:
+ case FLOAT8OID:
+ return true;
+ default:
+ return false;
+ }
+}
+
+Datum
+jsonb_cast_support(PG_FUNCTION_ARGS)
+{
+ Node *rawreq = (Node *) PG_GETARG_POINTER(0);
+
+ if (IsA(rawreq, SupportRequestSimplify))
+ {
+ SupportRequestSimplify *req = (SupportRequestSimplify *) rawreq;
+ FuncExpr *fexpr = palloc(sizeof(FuncExpr));
+ OpExpr *opexpr;
+ Oid new_func_id = InvalidOid;
+
+ memcpy(fexpr, req->fcall, sizeof(FuncExpr));
+
+ opexpr = (OpExpr *) linitial(fexpr->args);
+
+ if (!IsA(opexpr, OpExpr) ||
+ !jsonb_cast_is_optimized(fexpr->funcresulttype))
+ {
+ /* not the desired pattern. */
+ PG_RETURN_POINTER(fexpr);
+ }
+
+ if (opexpr->opfuncid == F_JSONB_OBJECT_FIELD)
+ new_func_id = F_JSONB_OBJECT_FIELD_TYPE;
+ else if (opexpr->opfuncid == F_JSONB_ARRAY_ELEMENT)
+ new_func_id = F_JSONB_ARRAY_ELEMENT_TYPE;
+ else if (opexpr->opfuncid == F_JSONB_EXTRACT_PATH)
+ new_func_id = F_JSONB_EXTRACT_PATH_TYPE;
+
+ if (OidIsValid(new_func_id))
+ {
+ Const *target = makeNullConst(fexpr->funcresulttype,
+ -1,
+ InvalidOid);
+ /*
+ * Since all the above functions are strict, we can't input
+ * a NULL value.
+ */
+ target->constisnull = false;
+ fexpr->funcid = new_func_id;
+ fexpr->args = opexpr->args;
+ fexpr->args = lappend(fexpr->args, target);
+ }
+
+ PG_RETURN_POINTER(fexpr);
+ }
+
+ PG_RETURN_POINTER(NULL);
+}
+
+Datum
+cast_jsonbvalue_to_type(JsonbValue *v, Oid targetOid)
+{
+ switch(targetOid)
+ {
+ Datum retValue;
+
+ case BOOLOID:
+ if (v->type != jbvBool)
+ cannotCastJsonbValue(v->type, "bool");
+ PG_RETURN_BOOL(v->val.boolean);
+
+ case NUMERICOID:
+ if (v->type != jbvNumeric)
+ cannotCastJsonbValue(v->type, "numeric");
+ PG_RETURN_NUMERIC(v->val.numeric);
+ case INT2OID:
+ if (v->type != jbvNumeric)
+ cannotCastJsonbValue(v->type, "smallint");
+ retValue = DirectFunctionCall1(numeric_int2,
+ NumericGetDatum(v->val.numeric));
+ PG_RETURN_DATUM(retValue);
+ case INT4OID:
+ if (v->type != jbvNumeric)
+ cannotCastJsonbValue(v->type, "integer");
+ retValue = DirectFunctionCall1(numeric_int4,
+ NumericGetDatum(v->val.numeric));
+ PG_RETURN_DATUM(retValue);
+
+ case INT8OID:
+ if (v->type != jbvNumeric)
+ cannotCastJsonbValue(v->type, "bigint");
+ retValue = DirectFunctionCall1(numeric_int8,
+ NumericGetDatum(v->val.numeric));
+ PG_RETURN_DATUM(retValue);
+
+ case FLOAT4OID:
+ if (v->type != jbvNumeric)
+ cannotCastJsonbValue(v->type, "real");
+ retValue = DirectFunctionCall1(numeric_float4,
+ NumericGetDatum(v->val.numeric));
+ PG_RETURN_DATUM(retValue);
+
+ case FLOAT8OID:
+ if (v->type != jbvNumeric)
+ cannotCastJsonbValue(v->type, "double precision");
+ retValue = DirectFunctionCall1(numeric_float8,
+ NumericGetDatum(v->val.numeric));
+ PG_RETURN_DATUM(retValue);
+
+ default:
+ Assert(false);
+ break;
+ }
+
+ PG_RETURN_POINTER(NULL);
+}
+
+Datum
+jsonb_object_field_type(PG_FUNCTION_ARGS)
+{
+ Jsonb *jb = PG_GETARG_JSONB_P(0);
+ text *key = PG_GETARG_TEXT_PP(1);
+ Oid targetOid = get_fn_expr_argtype(fcinfo->flinfo, 2);
+
+ JsonbValue *v;
+ JsonbValue vbuf;
+
+ if (!JB_ROOT_IS_OBJECT(jb))
+ PG_RETURN_NULL();
+
+ v = getKeyJsonValueFromContainer(&jb->root,
+ VARDATA_ANY(key),
+ VARSIZE_ANY_EXHDR(key),
+ &vbuf);
+
+ if (v == NULL)
+ PG_RETURN_NULL();
+
+ return cast_jsonbvalue_to_type(v, targetOid);
+}
+
+Datum
+jsonb_array_element_type(PG_FUNCTION_ARGS)
+{
+ Jsonb *jb = PG_GETARG_JSONB_P(0);
+ int element = PG_GETARG_INT32(1);
+ Oid targetOid = get_fn_expr_argtype(fcinfo->flinfo, 2);
+ JsonbValue *v;
+
+ if (!JB_ROOT_IS_ARRAY(jb))
+ PG_RETURN_NULL();
+
+ /* Handle negative subscript */
+ if (element < 0)
+ {
+ uint32 nelements = JB_ROOT_COUNT(jb);
+
+ if (-element > nelements)
+ PG_RETURN_NULL();
+ else
+ element += nelements;
+ }
+
+ v = getIthJsonbValueFromContainer(&jb->root, element);
+ if (v == NULL)
+ PG_RETURN_NULL();
+
+ return cast_jsonbvalue_to_type(v, targetOid);
+}
+
Datum
jsonb_bool(PG_FUNCTION_ARGS)
{
diff --git a/src/backend/utils/adt/jsonfuncs.c b/src/backend/utils/adt/jsonfuncs.c
index a4bfa5e4040..6895f81fac3 100644
--- a/src/backend/utils/adt/jsonfuncs.c
+++ b/src/backend/utils/adt/jsonfuncs.c
@@ -492,6 +492,7 @@ static JsonParseErrorType transform_string_values_object_field_start(void *state
static JsonParseErrorType transform_string_values_array_element_start(void *state, bool isnull);
static JsonParseErrorType transform_string_values_scalar(void *state, char *token, JsonTokenType tokentype);
+static JsonbValue *jsonb_get_jsonbvalue(Jsonb *jb, Datum *path, int npath, bool *isnull);
/*
* pg_parse_json_or_errsave
@@ -1473,6 +1474,39 @@ get_scalar(void *state, char *token, JsonTokenType tokentype)
return JSON_SUCCESS;
}
+Datum
+jsonb_extract_path_type(PG_FUNCTION_ARGS)
+{
+ Jsonb *jb = PG_GETARG_JSONB_P(0);
+ ArrayType *path = PG_GETARG_ARRAYTYPE_P(1);
+ Oid targetOid = get_fn_expr_argtype(fcinfo->flinfo, 2);
+ JsonbValue *v;
+
+ Datum *pathtext;
+ bool *pathnulls;
+ bool isnull = false;
+ int npath;
+
+ /*
+ * If the array contains any null elements, return NULL, on the grounds
+ * that you'd have gotten NULL if any RHS value were NULL in a nested
+ * series of applications of the -> operator. (Note: because we also
+ * return NULL for error cases such as no-such-field, this is true
+ * regardless of the contents of the rest of the array.)
+ */
+ if (array_contains_nulls(path))
+ PG_RETURN_NULL();
+
+ deconstruct_array_builtin(path, TEXTOID, &pathtext, &pathnulls, &npath);
+
+ v = jsonb_get_jsonbvalue(jb, pathtext, npath, &isnull);
+
+ if (isnull)
+ PG_RETURN_POINTER(NULL);
+
+ return cast_jsonbvalue_to_type(v, targetOid);
+}
+
Datum
jsonb_extract_path(PG_FUNCTION_ARGS)
{
@@ -1516,52 +1550,36 @@ get_jsonb_path_all(FunctionCallInfo fcinfo, bool as_text)
PG_RETURN_DATUM(res);
}
-Datum
-jsonb_get_element(Jsonb *jb, Datum *path, int npath, bool *isnull, bool as_text)
+
+static JsonbValue *
+jsonb_get_jsonbvalue(Jsonb *jb, Datum *path, int npath, bool *isnull)
{
+ bool have_object = false, have_array = false;
JsonbContainer *container = &jb->root;
+ int i;
JsonbValue *jbvp = NULL;
- int i;
- bool have_object = false,
- have_array = false;
- *isnull = false;
+ /*
+ * If the array is empty, return the entire LHS object, on the grounds
+ * that we should do zero field or element extractions.
+ */
+ if (npath <= 0)
+ {
+ JsonbValue *res = NULL;
+ if (JB_ROOT_IS_ARRAY(jb) && JB_ROOT_IS_SCALAR(jb))
+ return getIthJsonbValueFromContainer(container, 0);
+
+ /* NB: res is a jbvBinary JsonbValue */
+ res = palloc0(sizeof(JsonbValue));
+ JsonbToJsonbValue(jb, res);
+ return res;
+ }
/* Identify whether we have object, array, or scalar at top-level */
if (JB_ROOT_IS_OBJECT(jb))
have_object = true;
else if (JB_ROOT_IS_ARRAY(jb) && !JB_ROOT_IS_SCALAR(jb))
have_array = true;
- else
- {
- Assert(JB_ROOT_IS_ARRAY(jb) && JB_ROOT_IS_SCALAR(jb));
- /* Extract the scalar value, if it is what we'll return */
- if (npath <= 0)
- jbvp = getIthJsonbValueFromContainer(container, 0);
- }
-
- /*
- * If the array is empty, return the entire LHS object, on the grounds
- * that we should do zero field or element extractions. For the
- * non-scalar case we can just hand back the object without much work. For
- * the scalar case, fall through and deal with the value below the loop.
- * (This inconsistency arises because there's no easy way to generate a
- * JsonbValue directly for root-level containers.)
- */
- if (npath <= 0 && jbvp == NULL)
- {
- if (as_text)
- {
- return PointerGetDatum(cstring_to_text(JsonbToCString(NULL,
- container,
- VARSIZE(jb))));
- }
- else
- {
- /* not text mode - just hand back the jsonb */
- PG_RETURN_JSONB_P(jb);
- }
- }
for (i = 0; i < npath; i++)
{
@@ -1586,7 +1604,7 @@ jsonb_get_element(Jsonb *jb, Datum *path, int npath, bool *isnull, bool as_text)
if (endptr == indextext || *endptr != '\0' || errno != 0)
{
*isnull = true;
- return PointerGetDatum(NULL);
+ return NULL;
}
if (lindex >= 0)
@@ -1607,7 +1625,7 @@ jsonb_get_element(Jsonb *jb, Datum *path, int npath, bool *isnull, bool as_text)
if (lindex == INT_MIN || -lindex > nelements)
{
*isnull = true;
- return PointerGetDatum(NULL);
+ return NULL;
}
else
index = nelements + lindex;
@@ -1619,13 +1637,13 @@ jsonb_get_element(Jsonb *jb, Datum *path, int npath, bool *isnull, bool as_text)
{
/* scalar, extraction yields a null */
*isnull = true;
- return PointerGetDatum(NULL);
+ return NULL;
}
if (jbvp == NULL)
{
*isnull = true;
- return PointerGetDatum(NULL);
+ return NULL;
}
else if (i == npath - 1)
break;
@@ -1644,6 +1662,22 @@ jsonb_get_element(Jsonb *jb, Datum *path, int npath, bool *isnull, bool as_text)
have_array = false;
}
}
+ return jbvp;
+}
+
+/*
+ * Return jsonb datum or jsonb-as-text datum.
+ */
+Datum
+jsonb_get_element(Jsonb *jb, Datum *path, int npath, bool *isnull, bool as_text)
+{
+ JsonbValue *jbvp = NULL;
+ *isnull = false;
+
+ jbvp = jsonb_get_jsonbvalue(jb, path, npath, isnull);
+
+ if (*isnull)
+ return PointerGetDatum(NULL);
if (as_text)
{
diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h
index f507b49bb28..8ed03a20efa 100644
--- a/src/include/catalog/catversion.h
+++ b/src/include/catalog/catversion.h
@@ -57,6 +57,6 @@
*/
/* yyyymmddN */
-#define CATALOG_VERSION_NO 202307261
+#define CATALOG_VERSION_NO 202308141
#endif
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 6996073989a..af89dec7db8 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -4575,25 +4575,26 @@
proname => 'pg_lsn', prorettype => 'pg_lsn', proargtypes => 'numeric',
prosrc => 'numeric_pg_lsn' },
-{ oid => '3556', descr => 'convert jsonb to boolean',
+{ oid => '3556', descr => 'convert jsonb to boolean', prosupport => 'jsonb_cast_support',
proname => 'bool', prorettype => 'bool', proargtypes => 'jsonb',
prosrc => 'jsonb_bool' },
{ oid => '3449', descr => 'convert jsonb to numeric',
- proname => 'numeric', prorettype => 'numeric', proargtypes => 'jsonb',
+ proname => 'numeric', prosupport => 'jsonb_cast_support',
+ prorettype => 'numeric', proargtypes => 'jsonb',
prosrc => 'jsonb_numeric' },
-{ oid => '3450', descr => 'convert jsonb to int2',
+{ oid => '3450', descr => 'convert jsonb to int2', prosupport => 'jsonb_cast_support',
proname => 'int2', prorettype => 'int2', proargtypes => 'jsonb',
prosrc => 'jsonb_int2' },
-{ oid => '3451', descr => 'convert jsonb to int4',
+{ oid => '3451', descr => 'convert jsonb to int4', prosupport => 'jsonb_cast_support',
proname => 'int4', prorettype => 'int4', proargtypes => 'jsonb',
prosrc => 'jsonb_int4' },
-{ oid => '3452', descr => 'convert jsonb to int8',
+{ oid => '3452', descr => 'convert jsonb to int8', prosupport => 'jsonb_cast_support',
proname => 'int8', prorettype => 'int8', proargtypes => 'jsonb',
prosrc => 'jsonb_int8' },
-{ oid => '3453', descr => 'convert jsonb to float4',
+{ oid => '3453', descr => 'convert jsonb to float4', prosupport => 'jsonb_cast_support',
proname => 'float4', prorettype => 'float4', proargtypes => 'jsonb',
prosrc => 'jsonb_float4' },
-{ oid => '2580', descr => 'convert jsonb to float8',
+{ oid => '2580', descr => 'convert jsonb to float8', prosupport => 'jsonb_cast_support',
proname => 'float8', prorettype => 'float8', proargtypes => 'jsonb',
prosrc => 'jsonb_float8' },
@@ -9928,6 +9929,13 @@
proname => 'jsonb_object_field_text', prorettype => 'text',
proargtypes => 'jsonb text', proargnames => '{from_json, field_name}',
prosrc => 'jsonb_object_field_text' },
+{ oid => '3813', descr => 'return a given type specified in desired_type from jsonb field',
+ proname => 'jsonb_object_field_type', prorettype => 'anyelement',
+ proargtypes => 'jsonb text anyelement', proargnames => '{from_json, field_name, desired_type}',
+ prosrc => 'jsonb_object_field_type'},
+{ oid => '3814', descr => 'planner support for numeric(jsonb)',
+ proname => 'jsonb_cast_support', prorettype => 'internal',
+ proargtypes => 'internal', prosrc => 'jsonb_cast_support' },
{ oid => '3215',
proname => 'jsonb_array_element', prorettype => 'jsonb',
proargtypes => 'jsonb int4', proargnames => '{from_json, element_index}',
@@ -9936,6 +9944,10 @@
proname => 'jsonb_array_element_text', prorettype => 'text',
proargtypes => 'jsonb int4', proargnames => '{from_json, element_index}',
prosrc => 'jsonb_array_element_text' },
+{ oid => '4549', descr => 'cast an array element to given type',
+ proname => 'jsonb_array_element_type', prorettype => 'anyelement',
+ proargtypes => 'jsonb int4 anyelement', proargnames => '{from_json, element_index, desired_type}',
+ prosrc => 'jsonb_array_element_type' },
{ oid => '3217', descr => 'get value from jsonb with path elements',
proname => 'jsonb_extract_path', provariadic => 'text', prorettype => 'jsonb',
proargtypes => 'jsonb _text', proallargtypes => '{jsonb,_text}',
@@ -9947,6 +9959,12 @@
proallargtypes => '{jsonb,_text}', proargmodes => '{i,v}',
proargnames => '{from_json,path_elems}',
prosrc => 'jsonb_extract_path_text' },
+{ oid => '4551', descr => 'cast value from jsonb as text with path elements to given type',
+ proname => 'jsonb_extract_path_type', provariadic => 'text',
+ prorettype => 'anyelement', proargtypes => 'jsonb _text anyelement',
+ proallargtypes => '{jsonb,_text,anyelement}', proargmodes => '{i,v,i}',
+ proargnames => '{from_json,path_elems,target_oid}',
+ prosrc => 'jsonb_extract_path_type' },
{ oid => '3219', descr => 'elements of a jsonb array',
proname => 'jsonb_array_elements', prorows => '100', proretset => 't',
prorettype => 'jsonb', proargtypes => 'jsonb',
diff --git a/src/include/utils/jsonb.h b/src/include/utils/jsonb.h
index 649a1644f24..532225314a9 100644
--- a/src/include/utils/jsonb.h
+++ b/src/include/utils/jsonb.h
@@ -435,5 +435,6 @@ extern Datum jsonb_build_object_worker(int nargs, Datum *args, bool *nulls,
bool unique_keys);
extern Datum jsonb_build_array_worker(int nargs, Datum *args, bool *nulls,
Oid *types, bool absent_on_null);
+extern Datum cast_jsonbvalue_to_type(JsonbValue *v, Oid target_oid);
#endif /* __JSONB_H__ */
diff --git a/src/test/regress/expected/jsonb.out b/src/test/regress/expected/jsonb.out
index 4a16d0dbafb..9ceae059560 100644
--- a/src/test/regress/expected/jsonb.out
+++ b/src/test/regress/expected/jsonb.out
@@ -457,6 +457,7 @@ CREATE TEMP TABLE test_jsonb (
);
INSERT INTO test_jsonb VALUES
('scalar','"a scalar"'),
+('scalarint','2'),
('array','["zero", "one","two",null,"four","five", [1,2,3],{"f1":9}]'),
('object','{"field1":"val1","field2":"val2","field3":null, "field4": 4, "field5": [1,2,3], "field6": {"f1":9}}');
SELECT test_json -> 'x' FROM test_jsonb WHERE json_type = 'scalar';
@@ -501,10 +502,25 @@ SELECT test_json ->> 'field2' FROM test_jsonb WHERE json_type = 'object';
val2
(1 row)
-SELECT test_json -> 2 FROM test_jsonb WHERE json_type = 'scalar';
+SELECT test_json -> 2, test_json -> 0 FROM test_jsonb WHERE json_type = 'scalar';
+ ?column? | ?column?
+----------+------------
+ | "a scalar"
+(1 row)
+
+explain (verbose, costs off)
+SELECT (test_json -> 0)::int4, test_json -> 0 FROM test_jsonb WHERE json_type = 'scalarint';
+ QUERY PLAN
+-----------------------------------------------------------------------
+ Seq Scan on pg_temp.test_jsonb
+ Output: jsonb_array_element_type(test_json, 0, 0), (test_json -> 0)
+ Filter: (test_jsonb.json_type = 'scalarint'::text)
+(3 rows)
+
+SELECT test_json -> 0 FROM test_jsonb WHERE json_type = 'scalarint';
?column?
----------
-
+ 2
(1 row)
SELECT test_json -> 2 FROM test_jsonb WHERE json_type = 'array';
@@ -1786,6 +1802,12 @@ select '{"a": {"b":{"c": "foo"}}}'::jsonb #> '{}';
{"a": {"b": {"c": "foo"}}}
(1 row)
+select ('2'::jsonb #> '{}')::int2, ('{"a":2}'::jsonb #> '{"b"}'), ('{"a":2}'::jsonb #> '{"b"}')::int2;
+ int2 | ?column? | int2
+------+----------+------
+ 2 | |
+(1 row)
+
select '[1,2,3]'::jsonb #> '{}';
?column?
-----------
@@ -5471,107 +5493,113 @@ select ts_headline('[]'::jsonb, tsquery('aaa & bbb'));
(1 row)
-- casts
-select 'true'::jsonb::bool;
- bool
-------
- t
+select 'true'::jsonb::bool, ('{"a": true}'::jsonb->'a')::bool;
+ bool | bool
+------+------
+ t | t
(1 row)
select '[]'::jsonb::bool;
ERROR: cannot cast jsonb array to type boolean
-select '1.0'::jsonb::float;
- float8
---------
- 1
+select ('{"a": []}'::jsonb->'a')::bool;
+ERROR: cannot cast jsonb array to type boolean
+select '1.0'::jsonb::float, ('{"a": 1.0}'::jsonb->'a')::float;
+ float8 | float8
+--------+--------
+ 1 | 1
(1 row)
select '[1.0]'::jsonb::float;
ERROR: cannot cast jsonb array to type double precision
-select '12345'::jsonb::int4;
- int4
--------
- 12345
+select ('{"a": [1.0]}'::jsonb->'a')::float;
+ERROR: cannot cast jsonb array to type double precision
+select '12345'::jsonb::int4, ('{"a": 12345}'::jsonb->'a')::int4;
+ int4 | int4
+-------+-------
+ 12345 | 12345
(1 row)
select '"hello"'::jsonb::int4;
ERROR: cannot cast jsonb string to type integer
-select '12345'::jsonb::numeric;
- numeric
----------
- 12345
+select ('{"a": "hello"}'::jsonb->'a')::int4;
+ERROR: cannot cast jsonb string to type integer
+select '12345'::jsonb::numeric, ('{"a": 12345}'::jsonb->'a')::numeric;
+ numeric | numeric
+---------+---------
+ 12345 | 12345
(1 row)
select '{}'::jsonb::numeric;
ERROR: cannot cast jsonb object to type numeric
-select '12345.05'::jsonb::numeric;
- numeric
-----------
- 12345.05
+select '12345.05'::jsonb::numeric, ('{"a": 12345.05}'::jsonb->'a')::numeric;
+ numeric | numeric
+----------+----------
+ 12345.05 | 12345.05
(1 row)
-select '12345.05'::jsonb::float4;
- float4
-----------
- 12345.05
+select '12345.05'::jsonb::float4, ('{"a": 12345.05}'::jsonb->'a')::float4;
+ float4 | float4
+----------+----------
+ 12345.05 | 12345.05
(1 row)
-select '12345.05'::jsonb::float8;
- float8
-----------
- 12345.05
+select '12345.05'::jsonb::float8, ('{"a": 12345.05}'::jsonb->'a')::float8;
+ float8 | float8
+----------+----------
+ 12345.05 | 12345.05
(1 row)
-select '12345.05'::jsonb::int2;
- int2
--------
- 12345
+select '12345.05'::jsonb::int2, ('{"a": 12345.05}'::jsonb->'a')::int2;
+ int2 | int2
+-------+-------
+ 12345 | 12345
(1 row)
-select '12345.05'::jsonb::int4;
- int4
--------
- 12345
+select '12345.05'::jsonb::int4, ('{"a": 12345.05}'::jsonb->'a')::int4;
+ int4 | int4
+-------+-------
+ 12345 | 12345
(1 row)
-select '12345.05'::jsonb::int8;
- int8
--------
- 12345
+select '12345.05'::jsonb::int8, ('{"a": 12345.05}'::jsonb->'a')::int8;
+ int8 | int8
+-------+-------
+ 12345 | 12345
(1 row)
-select '12345.0000000000000000000000000000000000000000000005'::jsonb::numeric;
- numeric
-------------------------------------------------------
- 12345.0000000000000000000000000000000000000000000005
+select '12345.0000000000000000000000000000000000000000000005'::jsonb::numeric, ('{"a": 12345.0000000000000000000000000000000000000000000005}'::jsonb->'a')::numeric;
+ numeric | numeric
+------------------------------------------------------+------------------------------------------------------
+ 12345.0000000000000000000000000000000000000000000005 | 12345.0000000000000000000000000000000000000000000005
(1 row)
-select '12345.0000000000000000000000000000000000000000000005'::jsonb::float4;
- float4
---------
- 12345
+select '12345.0000000000000000000000000000000000000000000005'::jsonb::float4, ('{"a": 12345.0000000000000000000000000000000000000000000005}'::jsonb->'a')::float4;
+ float4 | float4
+--------+--------
+ 12345 | 12345
(1 row)
-select '12345.0000000000000000000000000000000000000000000005'::jsonb::float8;
- float8
---------
- 12345
+select '12345.0000000000000000000000000000000000000000000005'::jsonb::float8, ('{"a": 12345.0000000000000000000000000000000000000000000005}'::jsonb->'a')::float8;
+ float8 | float8
+--------+--------
+ 12345 | 12345
(1 row)
-select '12345.0000000000000000000000000000000000000000000005'::jsonb::int2;
- int2
--------
- 12345
+select '12345.0000000000000000000000000000000000000000000005'::jsonb::int2, ('{"a": 12345.0000000000000000000000000000000000000000000005}'::jsonb->'a')::int2;
+ int2 | int2
+-------+-------
+ 12345 | 12345
(1 row)
-select '12345.0000000000000000000000000000000000000000000005'::jsonb::int4;
- int4
--------
- 12345
+select '12345.0000000000000000000000000000000000000000000005'::jsonb::int4, ('{"a": 12345.0000000000000000000000000000000000000000000005}'::jsonb->'a')::int4;
+ int4 | int4
+-------+-------
+ 12345 | 12345
(1 row)
-select '12345.0000000000000000000000000000000000000000000005'::jsonb::int8;
- int8
--------
- 12345
+select '12345.0000000000000000000000000000000000000000000005'::jsonb::int8, ('{"a": 12345.0000000000000000000000000000000000000000000005}'::jsonb->'a')::int8;
+ int8 | int8
+-------+-------
+ 12345 | 12345
(1 row)
diff --git a/src/test/regress/sql/jsonb.sql b/src/test/regress/sql/jsonb.sql
index e4b7cdf703d..5fece987bf0 100644
--- a/src/test/regress/sql/jsonb.sql
+++ b/src/test/regress/sql/jsonb.sql
@@ -154,6 +154,7 @@ CREATE TEMP TABLE test_jsonb (
INSERT INTO test_jsonb VALUES
('scalar','"a scalar"'),
+('scalarint','2'),
('array','["zero", "one","two",null,"four","five", [1,2,3],{"f1":9}]'),
('object','{"field1":"val1","field2":"val2","field3":null, "field4": 4, "field5": [1,2,3], "field6": {"f1":9}}');
@@ -166,7 +167,10 @@ SELECT test_json ->> 'field2' FROM test_jsonb WHERE json_type = 'scalar';
SELECT test_json ->> 'field2' FROM test_jsonb WHERE json_type = 'array';
SELECT test_json ->> 'field2' FROM test_jsonb WHERE json_type = 'object';
-SELECT test_json -> 2 FROM test_jsonb WHERE json_type = 'scalar';
+SELECT test_json -> 2, test_json -> 0 FROM test_jsonb WHERE json_type = 'scalar';
+explain (verbose, costs off)
+SELECT (test_json -> 0)::int4, test_json -> 0 FROM test_jsonb WHERE json_type = 'scalarint';
+SELECT test_json -> 0 FROM test_jsonb WHERE json_type = 'scalarint';
SELECT test_json -> 2 FROM test_jsonb WHERE json_type = 'array';
SELECT test_json -> 9 FROM test_jsonb WHERE json_type = 'array';
SELECT test_json -> 2 FROM test_jsonb WHERE json_type = 'object';
@@ -491,6 +495,7 @@ SELECT '{"f2":["f3",1],"f4":{"f5":99,"f6":"stringy"}}'::jsonb#>>array['f2','1'];
-- corner cases for same
select '{"a": {"b":{"c": "foo"}}}'::jsonb #> '{}';
+select ('2'::jsonb #> '{}')::int2, ('{"a":2}'::jsonb #> '{"b"}'), ('{"a":2}'::jsonb #> '{"b"}')::int2;
select '[1,2,3]'::jsonb #> '{}';
select '"foo"'::jsonb #> '{}';
select '42'::jsonb #> '{}';
@@ -1496,23 +1501,27 @@ select ts_headline('{}'::jsonb, tsquery('aaa & bbb'));
select ts_headline('[]'::jsonb, tsquery('aaa & bbb'));
-- casts
-select 'true'::jsonb::bool;
+select 'true'::jsonb::bool, ('{"a": true}'::jsonb->'a')::bool;
select '[]'::jsonb::bool;
-select '1.0'::jsonb::float;
+select ('{"a": []}'::jsonb->'a')::bool;
+select '1.0'::jsonb::float, ('{"a": 1.0}'::jsonb->'a')::float;
select '[1.0]'::jsonb::float;
-select '12345'::jsonb::int4;
+select ('{"a": [1.0]}'::jsonb->'a')::float;
+select '12345'::jsonb::int4, ('{"a": 12345}'::jsonb->'a')::int4;
select '"hello"'::jsonb::int4;
-select '12345'::jsonb::numeric;
+select ('{"a": "hello"}'::jsonb->'a')::int4;
+
+select '12345'::jsonb::numeric, ('{"a": 12345}'::jsonb->'a')::numeric;
select '{}'::jsonb::numeric;
-select '12345.05'::jsonb::numeric;
-select '12345.05'::jsonb::float4;
-select '12345.05'::jsonb::float8;
-select '12345.05'::jsonb::int2;
-select '12345.05'::jsonb::int4;
-select '12345.05'::jsonb::int8;
-select '12345.0000000000000000000000000000000000000000000005'::jsonb::numeric;
-select '12345.0000000000000000000000000000000000000000000005'::jsonb::float4;
-select '12345.0000000000000000000000000000000000000000000005'::jsonb::float8;
-select '12345.0000000000000000000000000000000000000000000005'::jsonb::int2;
-select '12345.0000000000000000000000000000000000000000000005'::jsonb::int4;
-select '12345.0000000000000000000000000000000000000000000005'::jsonb::int8;
+select '12345.05'::jsonb::numeric, ('{"a": 12345.05}'::jsonb->'a')::numeric;
+select '12345.05'::jsonb::float4, ('{"a": 12345.05}'::jsonb->'a')::float4;
+select '12345.05'::jsonb::float8, ('{"a": 12345.05}'::jsonb->'a')::float8;
+select '12345.05'::jsonb::int2, ('{"a": 12345.05}'::jsonb->'a')::int2;
+select '12345.05'::jsonb::int4, ('{"a": 12345.05}'::jsonb->'a')::int4;
+select '12345.05'::jsonb::int8, ('{"a": 12345.05}'::jsonb->'a')::int8;
+select '12345.0000000000000000000000000000000000000000000005'::jsonb::numeric, ('{"a": 12345.0000000000000000000000000000000000000000000005}'::jsonb->'a')::numeric;
+select '12345.0000000000000000000000000000000000000000000005'::jsonb::float4, ('{"a": 12345.0000000000000000000000000000000000000000000005}'::jsonb->'a')::float4;
+select '12345.0000000000000000000000000000000000000000000005'::jsonb::float8, ('{"a": 12345.0000000000000000000000000000000000000000000005}'::jsonb->'a')::float8;
+select '12345.0000000000000000000000000000000000000000000005'::jsonb::int2, ('{"a": 12345.0000000000000000000000000000000000000000000005}'::jsonb->'a')::int2;
+select '12345.0000000000000000000000000000000000000000000005'::jsonb::int4, ('{"a": 12345.0000000000000000000000000000000000000000000005}'::jsonb->'a')::int4;
+select '12345.0000000000000000000000000000000000000000000005'::jsonb::int8, ('{"a": 12345.0000000000000000000000000000000000000000000005}'::jsonb->'a')::int8;
--
2.21.0
view thread (14+ messages) latest in thread
reply
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: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: Extract numeric filed in JSONB more effectively
In-Reply-To: <CAKU4AWrBY9GHj9oZbvhiOG1BgiWyZC8FGPAET-CfRKDhYyv1HQ@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox