public inbox for [email protected]
help / color / mirror / Atom feedRe: Extract numeric filed in JSONB more effectively
14+ messages / 4 participants
[nested] [flat]
* Re: Extract numeric filed in JSONB more effectively
@ 2023-08-14 07:06 Andy Fan <[email protected]>
2023-08-14 08:01 ` Re: Extract numeric filed in JSONB more effectively Pavel Stehule <[email protected]>
2023-08-14 14:04 ` Re: Extract numeric filed in JSONB more effectively Chapman Flack <[email protected]>
0 siblings, 2 replies; 14+ messages in thread
From: Andy Fan @ 2023-08-14 07:06 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: Pavel Stehule <[email protected]>; jian he <[email protected]>; pgsql-hackers
>
>
> We'd still have functions like jsonb_field_as_numeric() under the
> hood, but there's not an expectation that users call them explicitly.
>
To avoid the lots of functions like jsonb_field_as_int2/int4, I defined
Datum jsonb_object_field_type(.., Oid target_oid) at last, so the
function must return "internal" or "anyelement". Then we can see:
select jsonb_object_field_type(tb.a, 'a'::text, 1700) from tb;
ERROR: cannot display a value of type anyelement.
The reason is clear to me, but I'm not sure how to fix that or deserves
a fix? Or shall I define jsonb_object_field_int2/int8 to avoid this?
This is an unresolved issue at the latest patch.
--
Best Regards
Andy Fan
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Extract numeric filed in JSONB more effectively
2023-08-14 07:06 Re: Extract numeric filed in JSONB more effectively Andy Fan <[email protected]>
@ 2023-08-14 08:01 ` Pavel Stehule <[email protected]>
1 sibling, 0 replies; 14+ messages in thread
From: Pavel Stehule @ 2023-08-14 08:01 UTC (permalink / raw)
To: Andy Fan <[email protected]>; +Cc: Tom Lane <[email protected]>; jian he <[email protected]>; pgsql-hackers
po 14. 8. 2023 v 9:06 odesílatel Andy Fan <[email protected]> napsal:
>
>> We'd still have functions like jsonb_field_as_numeric() under the
>> hood, but there's not an expectation that users call them explicitly.
>>
>
> To avoid the lots of functions like jsonb_field_as_int2/int4, I defined
> Datum jsonb_object_field_type(.., Oid target_oid) at last, so the
> function must return "internal" or "anyelement". Then we can see:
>
> select jsonb_object_field_type(tb.a, 'a'::text, 1700) from tb;
> ERROR: cannot display a value of type anyelement.
>
you cannot to use type as parameter. There should be some typed value - like
jsonb_object_field, '{"a":10}', 'a', NULL::int)
and return type should be anyelement.
Another solution should be more deeper change like implementation of
"coalesce"
>
> The reason is clear to me, but I'm not sure how to fix that or deserves
> a fix? Or shall I define jsonb_object_field_int2/int8 to avoid this?
>
> This is an unresolved issue at the latest patch.
> --
> Best Regards
> Andy Fan
>
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Extract numeric filed in JSONB more effectively
2023-08-14 07:06 Re: Extract numeric filed in JSONB more effectively Andy Fan <[email protected]>
@ 2023-08-14 14:04 ` Chapman Flack <[email protected]>
2023-08-14 14:10 ` Re: Extract numeric filed in JSONB more effectively Tom Lane <[email protected]>
1 sibling, 1 reply; 14+ messages in thread
From: Chapman Flack @ 2023-08-14 14:04 UTC (permalink / raw)
To: Andy Fan <[email protected]>; +Cc: Tom Lane <[email protected]>; Pavel Stehule <[email protected]>; jian he <[email protected]>; pgsql-hackers
On 2023-08-14 03:06, Andy Fan wrote:
>> We'd still have functions like jsonb_field_as_numeric() under the
>> hood, but there's not an expectation that users call them explicitly.
>
> To avoid the lots of functions like jsonb_field_as_int2/int4, I defined
> Datum jsonb_object_field_type(.., Oid target_oid) at last, so the
> function must return "internal" or "anyelement".
> ...
> I'm not sure how to fix that or deserves
> a fix? Or shall I define jsonb_object_field_int2/int8 to avoid this?
As far as I'm concerned, if the intent is for this to be a function
that is swapped in by SupportRequestSimplify and not necessarily to
be called by users directly, I don't mind if users can't call it
directly. As long as there is a nice familiar jsonb function the user
can call in a nice familiar way and knows it will be handled
efficiently behind the curtain, that seems to be good enough for
the user--better, even, than having a new oddball function to
remember.
However, I believe the rule is that a function declared to return
internal must also declare at least one parameter as internal.
That way, a user won't be shown errors about displaying its
returned value, because the user won't be able to call it
in the first place, having no values of type 'internal' lying
around to pass to it. It could simply have that trailing oid
parameter declared as internal, and there you have a strictly
internal-use function.
Providing a function with return type declared internal but
with no parameter of that type is not good, 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.
Regards,
-Chap
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Extract numeric filed in JSONB more effectively
2023-08-14 07:06 Re: Extract numeric filed in JSONB more effectively Andy Fan <[email protected]>
2023-08-14 14:04 ` Re: Extract numeric filed in JSONB more effectively Chapman Flack <[email protected]>
@ 2023-08-14 14:10 ` Tom Lane <[email protected]>
2023-08-14 15:42 ` Re: Extract numeric filed in JSONB more effectively Andy Fan <[email protected]>
0 siblings, 1 reply; 14+ messages in thread
From: Tom Lane @ 2023-08-14 14:10 UTC (permalink / raw)
To: Chapman Flack <[email protected]>; +Cc: Andy Fan <[email protected]>; Pavel Stehule <[email protected]>; jian he <[email protected]>; pgsql-hackers
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".
> 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.
You could argue that we should never have abused "internal"
to this extent in the first place, compared to inventing a
plethora of internal-ish types to correspond to each of the
things "internal" is used for. But here we are so we'd
better be darn careful with it.
regards, tom lane
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Extract numeric filed in JSONB more effectively
2023-08-14 07:06 Re: Extract numeric filed in JSONB more effectively Andy Fan <[email protected]>
2023-08-14 14:04 ` Re: Extract numeric filed in JSONB more effectively Chapman Flack <[email protected]>
2023-08-14 14:10 ` Re: Extract numeric filed in JSONB more effectively Tom Lane <[email protected]>
@ 2023-08-14 15:42 ` Andy Fan <[email protected]>
2023-08-15 03:24 ` Re: Extract numeric filed in JSONB more effectively Andy Fan <[email protected]>
0 siblings, 1 reply; 14+ messages in thread
From: Andy Fan @ 2023-08-14 15:42 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: Chapman Flack <[email protected]>; Pavel Stehule <[email protected]>; jian he <[email protected]>; pgsql-hackers
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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Extract numeric filed in JSONB more effectively
2023-08-14 07:06 Re: Extract numeric filed in JSONB more effectively Andy Fan <[email protected]>
2023-08-14 14:04 ` Re: Extract numeric filed in JSONB more effectively Chapman Flack <[email protected]>
2023-08-14 14:10 ` Re: Extract numeric filed in JSONB more effectively Tom Lane <[email protected]>
2023-08-14 15:42 ` Re: Extract numeric filed in JSONB more effectively Andy Fan <[email protected]>
@ 2023-08-15 03:24 ` Andy Fan <[email protected]>
2023-08-15 05:23 ` Re: Extract numeric filed in JSONB more effectively Pavel Stehule <[email protected]>
0 siblings, 1 reply; 14+ messages in thread
From: Andy Fan @ 2023-08-15 03:24 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: Chapman Flack <[email protected]>; Pavel Stehule <[email protected]>; jian he <[email protected]>; pgsql-hackers
>
>
> 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.
>
The exception is numeric data type, the constvalue can't be 0.
so hack it with the below line. maybe not good enough, but I
have no better solution now.
+ Const *target =
makeNullConst(fexpr->funcresulttype,
+
-1,
+
InvalidOid);
+ /*
+ * Since all the above functions are strict, we
can't input
+ * a NULL value.
+ */
+ target->constisnull = false;
+
+ Assert(target->constbyval || target->consttype ==
NUMERICOID);
+
+ /* Mock a valid datum for !constbyval type. */
+ if (fexpr->funcresulttype == NUMERICOID)
+ target->constvalue =
DirectFunctionCall1(numeric_in, CStringGetDatum("0"));
--
Best Regards
Andy Fan
Attachments:
[application/octet-stream] v7-0001-optimize-casting-jsonb-to-a-given-type.patch (27.6K, 3-v7-0001-optimize-casting-jsonb-to-a-given-type.patch)
download | inline diff:
From ccb0e4b2be01dfced9b74d72da8e745b823f5b43 Mon Sep 17 00:00:00 2001
From: Andy Fan <[email protected]>
Date: Thu, 10 Aug 2023 15:15:25 +0800
Subject: [PATCH v7] 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 | 190 ++++++++++++++++++++++++++++
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, 411 insertions(+), 131 deletions(-)
diff --git a/src/backend/utils/adt/jsonb.c b/src/backend/utils/adt/jsonb.c
index 9781852b0cb..4d427e50a98 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,193 @@ 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;
+
+ Assert(target->constbyval || target->consttype == NUMERICOID);
+
+ /* Mock a valid datum for !constbyval type. */
+ if (fexpr->funcresulttype == NUMERICOID)
+ target->constvalue = DirectFunctionCall1(numeric_in, CStringGetDatum("0"));
+
+ 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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Extract numeric filed in JSONB more effectively
2023-08-14 07:06 Re: Extract numeric filed in JSONB more effectively Andy Fan <[email protected]>
2023-08-14 14:04 ` Re: Extract numeric filed in JSONB more effectively Chapman Flack <[email protected]>
2023-08-14 14:10 ` Re: Extract numeric filed in JSONB more effectively Tom Lane <[email protected]>
2023-08-14 15:42 ` Re: Extract numeric filed in JSONB more effectively Andy Fan <[email protected]>
2023-08-15 03:24 ` Re: Extract numeric filed in JSONB more effectively Andy Fan <[email protected]>
@ 2023-08-15 05:23 ` Pavel Stehule <[email protected]>
2023-08-15 05:33 ` Re: Extract numeric filed in JSONB more effectively Pavel Stehule <[email protected]>
2023-08-15 06:04 ` Re: Extract numeric filed in JSONB more effectively Andy Fan <[email protected]>
2023-08-16 06:12 ` Re: Extract numeric filed in JSONB more effectively Andy Fan <[email protected]>
0 siblings, 3 replies; 14+ messages in thread
From: Pavel Stehule @ 2023-08-15 05:23 UTC (permalink / raw)
To: Andy Fan <[email protected]>; +Cc: Tom Lane <[email protected]>; Chapman Flack <[email protected]>; jian he <[email protected]>; pgsql-hackers
Hi
út 15. 8. 2023 v 5:24 odesílatel Andy Fan <[email protected]> napsal:
>
>> 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.
>>
>
> The exception is numeric data type, the constvalue can't be 0.
> so hack it with the below line. maybe not good enough, but I
> have no better solution now.
>
> + Const *target =
> makeNullConst(fexpr->funcresulttype,
> +
> -1,
> +
> InvalidOid);
> + /*
> + * Since all the above functions are strict, we
> can't input
> + * a NULL value.
> + */
> + target->constisnull = false;
> +
> + Assert(target->constbyval || target->consttype ==
> NUMERICOID);
> +
> + /* Mock a valid datum for !constbyval type. */
> + if (fexpr->funcresulttype == NUMERICOID)
> + target->constvalue =
> DirectFunctionCall1(numeric_in, CStringGetDatum("0"));
>
>
Personally I think this workaround is too dirty, and better to use a strict
function (I believe so the overhead for NULL values is acceptable), or
introduce a different mechanism.
Your design is workable, and I think acceptable, but I don't think it is an
ideal or final solution. It is not really generic. It doesn't help with XML
or Hstore. You need to touch cast functions, which I think is not best,
because cast functions should not cooperate on optimization of execution of
another function.
My idea of an ideal solution is the introduction of the possibility to use
"any" pseudotype as return type with possibility to set default return
type. Now, "any" is allowed only for arguments. The planner can set the
expected type when it knows it, or can use the default type.
so for extraction of jsonb field we can use FUNCTION
jsonb_extract_field(jsonb, text) RETURNS "any" DEFAULT jsonb
if we call SELECT jsonb_extract_field(..., 'x') -> then it returns jsonb,
if we use SELECT jsonb_extract_field('...', 'x')::date, then it returns date
With this possibility we don't need to touch to cast functions, and we can
simply implement similar functions for other non atomic types.
--
> Best Regards
> Andy Fan
>
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Extract numeric filed in JSONB more effectively
2023-08-14 07:06 Re: Extract numeric filed in JSONB more effectively Andy Fan <[email protected]>
2023-08-14 14:04 ` Re: Extract numeric filed in JSONB more effectively Chapman Flack <[email protected]>
2023-08-14 14:10 ` Re: Extract numeric filed in JSONB more effectively Tom Lane <[email protected]>
2023-08-14 15:42 ` Re: Extract numeric filed in JSONB more effectively Andy Fan <[email protected]>
2023-08-15 03:24 ` Re: Extract numeric filed in JSONB more effectively Andy Fan <[email protected]>
2023-08-15 05:23 ` Re: Extract numeric filed in JSONB more effectively Pavel Stehule <[email protected]>
@ 2023-08-15 05:33 ` Pavel Stehule <[email protected]>
2 siblings, 0 replies; 14+ messages in thread
From: Pavel Stehule @ 2023-08-15 05:33 UTC (permalink / raw)
To: Andy Fan <[email protected]>; +Cc: Tom Lane <[email protected]>; Chapman Flack <[email protected]>; jian he <[email protected]>; pgsql-hackers
út 15. 8. 2023 v 7:23 odesílatel Pavel Stehule <[email protected]>
napsal:
> Hi
>
> út 15. 8. 2023 v 5:24 odesílatel Andy Fan <[email protected]>
> napsal:
>
>>
>>> 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.
>>>
>>
>> The exception is numeric data type, the constvalue can't be 0.
>> so hack it with the below line. maybe not good enough, but I
>> have no better solution now.
>>
>> + Const *target =
>> makeNullConst(fexpr->funcresulttype,
>> +
>> -1,
>> +
>> InvalidOid);
>> + /*
>> + * Since all the above functions are strict, we
>> can't input
>> + * a NULL value.
>> + */
>> + target->constisnull = false;
>> +
>> + Assert(target->constbyval || target->consttype ==
>> NUMERICOID);
>> +
>> + /* Mock a valid datum for !constbyval type. */
>> + if (fexpr->funcresulttype == NUMERICOID)
>> + target->constvalue =
>> DirectFunctionCall1(numeric_in, CStringGetDatum("0"));
>>
>>
> Personally I think this workaround is too dirty, and better to use a
> strict function (I believe so the overhead for NULL values is acceptable),
> or introduce a different mechanism.
>
> Your design is workable, and I think acceptable, but I don't think it is
> an ideal or final solution. It is not really generic. It doesn't help with
> XML or Hstore. You need to touch cast functions, which I think is not best,
> because cast functions should not cooperate on optimization of execution of
> another function.
>
> My idea of an ideal solution is the introduction of the possibility to use
> "any" pseudotype as return type with possibility to set default return
> type. Now, "any" is allowed only for arguments. The planner can set the
> expected type when it knows it, or can use the default type.
>
> so for extraction of jsonb field we can use FUNCTION
> jsonb_extract_field(jsonb, text) RETURNS "any" DEFAULT jsonb
>
> if we call SELECT jsonb_extract_field(..., 'x') -> then it returns jsonb,
> if we use SELECT jsonb_extract_field('...', 'x')::date, then it returns date
>
> With this possibility we don't need to touch to cast functions, and we can
> simply implement similar functions for other non atomic types.
>
this syntax can be used instead NULL::type trick
like
SELECT jsonb_populate_record('{...}')::pg_class;
instead
SELECT jsonb_populate_record(NULL::pg_class, '{...}')
>
>
>
> --
>> Best Regards
>> Andy Fan
>>
>
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Extract numeric filed in JSONB more effectively
2023-08-14 07:06 Re: Extract numeric filed in JSONB more effectively Andy Fan <[email protected]>
2023-08-14 14:04 ` Re: Extract numeric filed in JSONB more effectively Chapman Flack <[email protected]>
2023-08-14 14:10 ` Re: Extract numeric filed in JSONB more effectively Tom Lane <[email protected]>
2023-08-14 15:42 ` Re: Extract numeric filed in JSONB more effectively Andy Fan <[email protected]>
2023-08-15 03:24 ` Re: Extract numeric filed in JSONB more effectively Andy Fan <[email protected]>
2023-08-15 05:23 ` Re: Extract numeric filed in JSONB more effectively Pavel Stehule <[email protected]>
@ 2023-08-15 06:04 ` Andy Fan <[email protected]>
2023-08-15 06:50 ` Re: Extract numeric filed in JSONB more effectively Pavel Stehule <[email protected]>
2 siblings, 1 reply; 14+ messages in thread
From: Andy Fan @ 2023-08-15 06:04 UTC (permalink / raw)
To: Pavel Stehule <[email protected]>; +Cc: Tom Lane <[email protected]>; Chapman Flack <[email protected]>; jian he <[email protected]>; pgsql-hackers
>
>
> My idea of an ideal solution is the introduction of the possibility to use
> "any" pseudotype as return type with possibility to set default return
> type. Now, "any" is allowed only for arguments. The planner can set the
> expected type when it knows it, or can use the default type.
>
> so for extraction of jsonb field we can use FUNCTION
> jsonb_extract_field(jsonb, text) RETURNS "any" DEFAULT jsonb
>
Is this an existing framework or do you want to create something new?
>
> if we call SELECT jsonb_extract_field(..., 'x') -> then it returns jsonb,
> if we use SELECT jsonb_extract_field('...', 'x')::date, then it returns date
>
If so, what is the difference from the current jsonb->'f' and
(jsonb->'f' )::date?
>
> With this possibility we don't need to touch to cast functions, and we can
> simply implement similar functions for other non atomic types.
>
What do you mean by "atomic type" here? If you want to introduce some new
framework, I think we need a very clear benefit.
--
Best Regards
Andy Fan
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Extract numeric filed in JSONB more effectively
2023-08-14 07:06 Re: Extract numeric filed in JSONB more effectively Andy Fan <[email protected]>
2023-08-14 14:04 ` Re: Extract numeric filed in JSONB more effectively Chapman Flack <[email protected]>
2023-08-14 14:10 ` Re: Extract numeric filed in JSONB more effectively Tom Lane <[email protected]>
2023-08-14 15:42 ` Re: Extract numeric filed in JSONB more effectively Andy Fan <[email protected]>
2023-08-15 03:24 ` Re: Extract numeric filed in JSONB more effectively Andy Fan <[email protected]>
2023-08-15 05:23 ` Re: Extract numeric filed in JSONB more effectively Pavel Stehule <[email protected]>
2023-08-15 06:04 ` Re: Extract numeric filed in JSONB more effectively Andy Fan <[email protected]>
@ 2023-08-15 06:50 ` Pavel Stehule <[email protected]>
2023-08-15 07:05 ` Re: Extract numeric filed in JSONB more effectively Andy Fan <[email protected]>
0 siblings, 1 reply; 14+ messages in thread
From: Pavel Stehule @ 2023-08-15 06:50 UTC (permalink / raw)
To: Andy Fan <[email protected]>; +Cc: Tom Lane <[email protected]>; Chapman Flack <[email protected]>; jian he <[email protected]>; pgsql-hackers
út 15. 8. 2023 v 8:04 odesílatel Andy Fan <[email protected]> napsal:
>
>> My idea of an ideal solution is the introduction of the possibility to
>> use "any" pseudotype as return type with possibility to set default return
>> type. Now, "any" is allowed only for arguments. The planner can set the
>> expected type when it knows it, or can use the default type.
>>
>> so for extraction of jsonb field we can use FUNCTION
>> jsonb_extract_field(jsonb, text) RETURNS "any" DEFAULT jsonb
>>
>
>
Is this an existing framework or do you want to create something new?
>
This should be created
>
>> if we call SELECT jsonb_extract_field(..., 'x') -> then it returns jsonb,
>> if we use SELECT jsonb_extract_field('...', 'x')::date, then it returns date
>>
>
> If so, what is the difference from the current jsonb->'f' and
> (jsonb->'f' )::date?
>
a) effectiveness. The ending performance should be similar like your
current patch, but without necessity to use planner support API.
b) more generic usage. For example, the expressions in plpgsql are executed
a little bit differently than SQL queries. So there the optimization from
your patch probably should not work, because you can write only var :=
j->'f', and plpgsql forces cast function execution, but not via planner.
c) nothing else. It should not to require to modify cast function
definitions
>> With this possibility we don't need to touch to cast functions, and we
>> can simply implement similar functions for other non atomic types.
>>
>
> What do you mean by "atomic type" here? If you want to introduce some
> new framework, I think we need a very clear benefit.
>
Atomic types (skalar types like int, varchar, date), nonatomic types -
array, composite, xml, jsonb, hstore or arrays of composite types.
>
> --
> Best Regards
> Andy Fan
>
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Extract numeric filed in JSONB more effectively
2023-08-14 07:06 Re: Extract numeric filed in JSONB more effectively Andy Fan <[email protected]>
2023-08-14 14:04 ` Re: Extract numeric filed in JSONB more effectively Chapman Flack <[email protected]>
2023-08-14 14:10 ` Re: Extract numeric filed in JSONB more effectively Tom Lane <[email protected]>
2023-08-14 15:42 ` Re: Extract numeric filed in JSONB more effectively Andy Fan <[email protected]>
2023-08-15 03:24 ` Re: Extract numeric filed in JSONB more effectively Andy Fan <[email protected]>
2023-08-15 05:23 ` Re: Extract numeric filed in JSONB more effectively Pavel Stehule <[email protected]>
2023-08-15 06:04 ` Re: Extract numeric filed in JSONB more effectively Andy Fan <[email protected]>
2023-08-15 06:50 ` Re: Extract numeric filed in JSONB more effectively Pavel Stehule <[email protected]>
@ 2023-08-15 07:05 ` Andy Fan <[email protected]>
2023-08-15 07:45 ` Re: Extract numeric filed in JSONB more effectively Pavel Stehule <[email protected]>
0 siblings, 1 reply; 14+ messages in thread
From: Andy Fan @ 2023-08-15 07:05 UTC (permalink / raw)
To: Pavel Stehule <[email protected]>; +Cc: Tom Lane <[email protected]>; Chapman Flack <[email protected]>; jian he <[email protected]>; pgsql-hackers
>
> a) effectiveness. The ending performance should be similar like your
> current patch, but without necessity to use planner support API.
>
So the cost is we need to create a new & different framework.
>
>
b) because you can write only var := j->'f', and plpgsql forces cast
> function execution, but not via planner.
>
var a := 1 needs going with planner, IIUC, same with j->'f'.
c) nothing else. It should not to require to modify cast function
> definitions
>
If you look at how the planner support function works, that is
pretty simple, just modify the prosupport attribute. I'm not sure
this should be called an issue or avoiding it can be described
as a benefit.
I don't think the current case is as bad as the other ones like
users needing to modify their queries or type-safety system
being broken. So personally I'm not willing to creating some
thing new & heavy. However I'm open to see what others say.
--
Best Regards
Andy Fan
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Extract numeric filed in JSONB more effectively
2023-08-14 07:06 Re: Extract numeric filed in JSONB more effectively Andy Fan <[email protected]>
2023-08-14 14:04 ` Re: Extract numeric filed in JSONB more effectively Chapman Flack <[email protected]>
2023-08-14 14:10 ` Re: Extract numeric filed in JSONB more effectively Tom Lane <[email protected]>
2023-08-14 15:42 ` Re: Extract numeric filed in JSONB more effectively Andy Fan <[email protected]>
2023-08-15 03:24 ` Re: Extract numeric filed in JSONB more effectively Andy Fan <[email protected]>
2023-08-15 05:23 ` Re: Extract numeric filed in JSONB more effectively Pavel Stehule <[email protected]>
2023-08-15 06:04 ` Re: Extract numeric filed in JSONB more effectively Andy Fan <[email protected]>
2023-08-15 06:50 ` Re: Extract numeric filed in JSONB more effectively Pavel Stehule <[email protected]>
2023-08-15 07:05 ` Re: Extract numeric filed in JSONB more effectively Andy Fan <[email protected]>
@ 2023-08-15 07:45 ` Pavel Stehule <[email protected]>
0 siblings, 0 replies; 14+ messages in thread
From: Pavel Stehule @ 2023-08-15 07:45 UTC (permalink / raw)
To: Andy Fan <[email protected]>; +Cc: Tom Lane <[email protected]>; Chapman Flack <[email protected]>; jian he <[email protected]>; pgsql-hackers
út 15. 8. 2023 v 9:05 odesílatel Andy Fan <[email protected]> napsal:
>
>
>> a) effectiveness. The ending performance should be similar like your
>> current patch, but without necessity to use planner support API.
>>
>
> So the cost is we need to create a new & different framework.
>
yes, it can be less work, code than for example introduction of
"anycompatible".
>
>>
> b) because you can write only var := j->'f', and plpgsql forces cast
>> function execution, but not via planner.
>>
>
> var a := 1 needs going with planner, IIUC, same with j->'f'.
>
i was wrong, the planner is full, but the executor is reduced.
>
> c) nothing else. It should not to require to modify cast function
>> definitions
>>
>
> If you look at how the planner support function works, that is
> pretty simple, just modify the prosupport attribute. I'm not sure
> this should be called an issue or avoiding it can be described
> as a benefit.
>
> I don't think the current case is as bad as the other ones like
> users needing to modify their queries or type-safety system
> being broken. So personally I'm not willing to creating some
> thing new & heavy. However I'm open to see what others say.
>
ok
regards
Pavel
>
> --
> Best Regards
> Andy Fan
>
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Extract numeric filed in JSONB more effectively
2023-08-14 07:06 Re: Extract numeric filed in JSONB more effectively Andy Fan <[email protected]>
2023-08-14 14:04 ` Re: Extract numeric filed in JSONB more effectively Chapman Flack <[email protected]>
2023-08-14 14:10 ` Re: Extract numeric filed in JSONB more effectively Tom Lane <[email protected]>
2023-08-14 15:42 ` Re: Extract numeric filed in JSONB more effectively Andy Fan <[email protected]>
2023-08-15 03:24 ` Re: Extract numeric filed in JSONB more effectively Andy Fan <[email protected]>
2023-08-15 05:23 ` Re: Extract numeric filed in JSONB more effectively Pavel Stehule <[email protected]>
@ 2023-08-16 06:12 ` Andy Fan <[email protected]>
2023-08-16 06:27 ` Re: Extract numeric filed in JSONB more effectively Andy Fan <[email protected]>
2 siblings, 1 reply; 14+ messages in thread
From: Andy Fan @ 2023-08-16 06:12 UTC (permalink / raw)
To: Pavel Stehule <[email protected]>; +Cc: Tom Lane <[email protected]>; Chapman Flack <[email protected]>; jian he <[email protected]>; pgsql-hackers
On Tue, Aug 15, 2023 at 1:24 PM Pavel Stehule <[email protected]>
wrote:
> Hi
>
> út 15. 8. 2023 v 5:24 odesílatel Andy Fan <[email protected]>
> napsal:
>
>>
>>> 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.
>>>
>>
>> The exception is numeric data type, the constvalue can't be 0.
>> so hack it with the below line. maybe not good enough, but I
>> have no better solution now.
>>
>> + Const *target =
>> makeNullConst(fexpr->funcresulttype,
>> +
>> -1,
>> +
>> InvalidOid);
>> + /*
>> + * Since all the above functions are strict, we
>> can't input
>> + * a NULL value.
>> + */
>> + target->constisnull = false;
>> +
>> + Assert(target->constbyval || target->consttype ==
>> NUMERICOID);
>> +
>> + /* Mock a valid datum for !constbyval type. */
>> + if (fexpr->funcresulttype == NUMERICOID)
>> + target->constvalue =
>> DirectFunctionCall1(numeric_in, CStringGetDatum("0"));
>>
>>
> Personally I think this workaround is too dirty, and better to use a
> strict function (I believe so the overhead for NULL values is acceptable).
>
In the patch v8, I created a new routine named makeDummyConst,
which just sits by makeNullConst. It may be helpful to some extent.
a). The code is self-document for the user/reader. b). We have a
central place to maintain this routine.
Besides the framework, the troubles for the reviewer may be if the
code has some corner case issue or behavior changes. Especially
I have some code refactor when working on jsonb_extract_path.
so the attached test.sql is designed for this. I have compared the
result between master and patched version and I think reviewer
can do some extra testing with it.
v8 is the finished version in my mind, so I think it is ready for review
now.
--
Best Regards
Andy Fan
Attachments:
[application/octet-stream] v8-0001-optimize-casting-jsonb-to-a-given-type.patch.bak (30.0K, 3-v8-0001-optimize-casting-jsonb-to-a-given-type.patch.bak)
download
[application/octet-stream] test.sql (4.4K, 4-test.sql)
download
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Extract numeric filed in JSONB more effectively
2023-08-14 07:06 Re: Extract numeric filed in JSONB more effectively Andy Fan <[email protected]>
2023-08-14 14:04 ` Re: Extract numeric filed in JSONB more effectively Chapman Flack <[email protected]>
2023-08-14 14:10 ` Re: Extract numeric filed in JSONB more effectively Tom Lane <[email protected]>
2023-08-14 15:42 ` Re: Extract numeric filed in JSONB more effectively Andy Fan <[email protected]>
2023-08-15 03:24 ` Re: Extract numeric filed in JSONB more effectively Andy Fan <[email protected]>
2023-08-15 05:23 ` Re: Extract numeric filed in JSONB more effectively Pavel Stehule <[email protected]>
2023-08-16 06:12 ` Re: Extract numeric filed in JSONB more effectively Andy Fan <[email protected]>
@ 2023-08-16 06:27 ` Andy Fan <[email protected]>
0 siblings, 0 replies; 14+ messages in thread
From: Andy Fan @ 2023-08-16 06:27 UTC (permalink / raw)
To: Pavel Stehule <[email protected]>; +Cc: Tom Lane <[email protected]>; Chapman Flack <[email protected]>; jian he <[email protected]>; pgsql-hackers
update with the correct patch..
Attachments:
[application/octet-stream] v8-0001-optimize-casting-jsonb-to-a-given-type.patch (29.2K, 3-v8-0001-optimize-casting-jsonb-to-a-given-type.patch)
download | inline diff:
From 45b2e3b79f787f401d7426743756ad06b81ccfb0 Mon Sep 17 00:00:00 2001
From: Andy Fan <[email protected]>
Date: Wed, 16 Aug 2023 14:04:27 +0800
Subject: [PATCH v8] 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/nodes/makefuncs.c | 30 +++++
src/backend/utils/adt/jsonb.c | 176 ++++++++++++++++++++++++++++
src/backend/utils/adt/jsonfuncs.c | 114 +++++++++++-------
src/include/catalog/catversion.h | 2 +-
src/include/catalog/pg_proc.dat | 32 +++--
src/include/nodes/makefuncs.h | 2 +
src/include/utils/jsonb.h | 1 +
src/test/regress/expected/jsonb.out | 160 ++++++++++++++-----------
src/test/regress/sql/jsonb.sql | 43 ++++---
9 files changed, 429 insertions(+), 131 deletions(-)
diff --git a/src/backend/nodes/makefuncs.c b/src/backend/nodes/makefuncs.c
index 0e7e6e46d94..9cb9178f01a 100644
--- a/src/backend/nodes/makefuncs.c
+++ b/src/backend/nodes/makefuncs.c
@@ -20,6 +20,7 @@
#include "nodes/makefuncs.h"
#include "nodes/nodeFuncs.h"
#include "utils/errcodes.h"
+#include "utils/fmgrprotos.h"
#include "utils/lsyscache.h"
@@ -352,6 +353,35 @@ makeNullConst(Oid consttype, int32 consttypmod, Oid constcollid)
typByVal);
}
+/*
+ * makeDummyConst
+ * create a Const node with the specified type/typmod.
+ *
+ * This is a convenience routine to create a Const which only the
+ * type is interesting but make sure the value is accessible.
+ */
+Const *
+makeDummyConst(Oid consttype, int32 consttypmod, Oid constcollid)
+{
+ int16 typLen;
+ bool typByVal;
+ Const *c;
+ Datum val = 0;
+
+
+ get_typlenbyval(consttype, &typLen, &typByVal);
+
+ if (consttype == NUMERICOID)
+ val = DirectFunctionCall1(numeric_in, CStringGetDatum("0"));
+ else if (!typByVal)
+ elog(ERROR, "create dummy const for type %u is not supported.", consttype);
+
+ /* XXX: here I assume constvalue=0 is accessible for constbyval.*/
+ c = makeConst(consttype, consttypmod, 0, (int) typLen, val, false, typByVal);
+
+ return c;
+}
+
/*
* makeBoolConst -
* creates a Const node representing a boolean value (can be NULL too)
diff --git a/src/backend/utils/adt/jsonb.c b/src/backend/utils/adt/jsonb.c
index 9781852b0cb..ccd6e6cc039 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,179 @@ 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 = makeDummyConst(fexpr->funcresulttype, 0, InvalidOid);
+ 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..3b120ab9521 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_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/nodes/makefuncs.h b/src/include/nodes/makefuncs.h
index 31807030055..cfbe5b26196 100644
--- a/src/include/nodes/makefuncs.h
+++ b/src/include/nodes/makefuncs.h
@@ -58,6 +58,8 @@ extern Const *makeConst(Oid consttype,
extern Const *makeNullConst(Oid consttype, int32 consttypmod, Oid constcollid);
+extern Const *makeDummyConst(Oid consttype, int32 consttypmod, Oid constcollid);
+
extern Node *makeBoolConst(bool value, bool isnull);
extern Expr *makeBoolExpr(BoolExprType boolop, List *args, int location);
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
^ permalink raw reply [nested|flat] 14+ messages in thread
end of thread, other threads:[~2023-08-16 06:27 UTC | newest]
Thread overview: 14+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-08-14 07:06 Re: Extract numeric filed in JSONB more effectively Andy Fan <[email protected]>
2023-08-14 08:01 ` Pavel Stehule <[email protected]>
2023-08-14 14:04 ` Chapman Flack <[email protected]>
2023-08-14 14:10 ` Tom Lane <[email protected]>
2023-08-14 15:42 ` Andy Fan <[email protected]>
2023-08-15 03:24 ` Andy Fan <[email protected]>
2023-08-15 05:23 ` Pavel Stehule <[email protected]>
2023-08-15 05:33 ` Pavel Stehule <[email protected]>
2023-08-15 06:04 ` Andy Fan <[email protected]>
2023-08-15 06:50 ` Pavel Stehule <[email protected]>
2023-08-15 07:05 ` Andy Fan <[email protected]>
2023-08-15 07:45 ` Pavel Stehule <[email protected]>
2023-08-16 06:12 ` Andy Fan <[email protected]>
2023-08-16 06:27 ` Andy Fan <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox