public inbox for [email protected]
help / color / mirror / Atom feedFrom: Andy Fan <[email protected]>
To: Chapman Flack <[email protected]>
Cc: jian he <[email protected]>
Cc: Pavel Stehule <[email protected]>
Cc: Tom Lane <[email protected]>
Cc: pgsql-hackers <[email protected]>
Subject: Re: Extract numeric filed in JSONB more effectively
Date: Fri, 18 Aug 2023 15:41:13 +0800
Message-ID: <CAKU4AWoLgC5ejOF8jxskd5oq52D-eR_1Q-HM5+e8OBVLak=qTg@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <CAKU4AWoqAVya6PBhn+BCbFaBMt3z-2=i5fKO3bW=6HPhbid2Dw@mail.gmail.com>
<CACJufxHASOEpngQ8V2tbXgs4VZC3ETrVbS=uk0KC_B_J1j7ejQ@mail.gmail.com>
<CAKU4AWrap1zpYqunJwWTN=CdP7E8e0U4mYmwn7hvTW3ERuENVg@mail.gmail.com>
<CAFj8pRD-R-GsGCjeYApbhZoiW8TV6zACaYStMBMM0=--+WgN_A@mail.gmail.com>
<CAKU4AWpDdFXAD+dMC1HeErXSKBUUBRGWkf=dAcX3wZgBNsWM=g@mail.gmail.com>
<CAFj8pRAO3oEiBaJJ9=HZp6CoP2ffbwSgrKkLKjPfYZwx9wOOuQ@mail.gmail.com>
<CAKU4AWoCHpKAVuQeOrk44cVPy_dVxn1aHrMUvHy5Ag-daFCSsQ@mail.gmail.com>
<CAFj8pRD4cdUmK0RG4oN5B2KRSeDhwfMYaL=XpfEu4iaLeZ_Kow@mail.gmail.com>
<CAKU4AWp8ab61e96v57OaB-Gm1bMfBNVLVy+s17U6_Ne3veB84g@mail.gmail.com>
<[email protected]>
<CAKU4AWp+KLes8g=BWLqZfDmW9+=ZY0UC4G0i3qVcYEviK_dDTA@mail.gmail.com>
<[email protected]>
<[email protected]>
<CAKU4AWrBY9GHj9oZbvhiOG1BgiWyZC8FGPAET-CfRKDhYyv1HQ@mail.gmail.com>
<CAKU4AWrGM5bK7wi4Y8bTYhKgh=A1fW=X00eC_jfk6_JXyaEURQ@mail.gmail.com>
<CAFj8pRC+4pvSuibB2xcNKJ=6PSF=TAcOtRNpdLPBXZjijFg7ag@mail.gmail.com>
<CAKU4AWrxHFVZM-gGPpOrVPreZMePAOoY580Tq-+CvxDWHmP_uA@mail.gmail.com>
<CAKU4AWp3410VpYFdCxpFdaHc8he6zj_=Fvww53TnU+g-bvvvsQ@mail.gmail.com>
<CACJufxH7ftu9HD+h_gDWPDvq1ZO8vGm81JomSKjvQacCLMLcxg@mail.gmail.com>
<CAKU4AWqbd_oDwXyK7=yMKbhAR=CQtVOWszcgft+cTG6JCTKmzQ@mail.gmail.com>
<[email protected]>
<CAKU4AWr1bsGaWWzQHJwB=WXboJrCM242=x6XbHO06vuhWsY4Ww@mail.gmail.com>
<[email protected]>
> because as long as it has parameters declared internal,
> no SQL can ever call it.
I was confused about the difference between anyelement and
internal, and I want to know a way to create a function which
is disallowed to be called by the user. Your above words
resolved two questions of mine!
So it can only appear in an expression
> tree because your SupportRequestSimplify put it there properly
> typed, after the SQL query was parsed but before evaluation.
>
> The thing about 'internal' is it doesn't represent any specific
> type, it doesn't necessarily represent the same type every time it
> is mentioned, and it often means something that isn't a cataloged
> type at all, such as a pointer to some kind of struct.
I should have noticed this during the study planner support function,
but highlighting this is pretty amazing.
> If there isn't, one might have to be invented. So it might be that
> if we go down the "use polymorphic resolution" road, we have to
> invent dummy Consts, and down the "internal" road we also have to
> invent something.
I think you might already feel that putting an internal function
into an expression would cause something wrong. I just have
a quick hack on this, and crash happens at the simplest case.
If something already exists to fix this, I am inclined
to use 'internal', but I didn't find the way. I'm thinking if we
should clarify "internal" should only be used internally and
should never be used in expression by design?
> (And I'm not even sure anything has to be invented. If there's an
> existing node for no-op binary casts, I think I'd first try
> putting that there and see if anything complains.)
>
> If this thread is being followed by others more familiar with
> the relevant code or who see obvious problems I'm missing,
> please chime in!
>
Thank you wise & modest gentleman, I would really hope Tom can
chime in at this time.
In general, the current decision we need to make is shall we use
'internal' or 'anyelement' to present the target OID. the internal way
would be more straight but have troubles to be in the expression tree.
the 'anyelement' way is compatible with expression, but it introduces
the makeDummyConst overhead and I'm not pretty sure it is a correct
implementation in makeDummyConst. see the XXX part.
+/*
+ * makeDummyConst
+ * create a Const node with the specified type/typmod.
+ *
+ * This is a convenience routine to create a Const which only the
+ * type is interested 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 const by value
type.*/
+ c = makeConst(consttype, consttypmod, 0, (int) typLen, val, false,
typByVal);
+
+ return c;
+}
--
Best Regards
Andy Fan
Attachments:
[application/octet-stream] 0001-convert-anyelement-to-internal.patch (4.5K, 3-0001-convert-anyelement-to-internal.patch)
download | inline diff:
From edca83956c65b060437cab55b50dcfe76414065b Mon Sep 17 00:00:00 2001
From: Andy Fan <[email protected]>
Date: Fri, 18 Aug 2023 15:38:50 +0800
Subject: [PATCH] convert anyelement to internal.
---
src/backend/utils/adt/jsonb.c | 9 +++++----
src/backend/utils/adt/jsonfuncs.c | 2 +-
src/include/catalog/pg_proc.dat | 12 ++++++------
3 files changed, 12 insertions(+), 11 deletions(-)
diff --git a/src/backend/utils/adt/jsonb.c b/src/backend/utils/adt/jsonb.c
index 148c1e2e195..7c1faa3d7f7 100644
--- a/src/backend/utils/adt/jsonb.c
+++ b/src/backend/utils/adt/jsonb.c
@@ -2091,10 +2091,11 @@ jsonb_cast_support(PG_FUNCTION_ARGS)
if (OidIsValid(new_func_id))
{
- Const *target = makeDummyConst(fexpr->funcresulttype, 0, InvalidOid);
+ // Const *target = makeDummyConst(fexpr->funcresulttype, 0, InvalidOid);
+ int64 target_typ = fexpr->funcresulttype;
fexpr->funcid = new_func_id;
fexpr->args = opexpr->args;
- fexpr->args = list_insert_nth(fexpr->args, 0, target);
+ fexpr->args = list_insert_nth(fexpr->args, 0, (void *) target_typ);
}
PG_RETURN_POINTER(fexpr);
@@ -2164,7 +2165,7 @@ cast_jsonbvalue_to_type(JsonbValue *v, Oid targetOid)
Datum
jsonb_object_field_type(PG_FUNCTION_ARGS)
{
- Oid targetOid = get_fn_expr_argtype(fcinfo->flinfo, 0);
+ Oid targetOid = DatumGetObjectId(0);
Jsonb *jb = PG_GETARG_JSONB_P(1);
text *key = PG_GETARG_TEXT_PP(2);
@@ -2188,7 +2189,7 @@ jsonb_object_field_type(PG_FUNCTION_ARGS)
Datum
jsonb_array_element_type(PG_FUNCTION_ARGS)
{
- Oid targetOid = get_fn_expr_argtype(fcinfo->flinfo, 0);
+ Oid targetOid = DatumGetObjectId(0);
Jsonb *jb = PG_GETARG_JSONB_P(1);
int element = PG_GETARG_INT32(2);
diff --git a/src/backend/utils/adt/jsonfuncs.c b/src/backend/utils/adt/jsonfuncs.c
index bb4ca807d74..54138258bfc 100644
--- a/src/backend/utils/adt/jsonfuncs.c
+++ b/src/backend/utils/adt/jsonfuncs.c
@@ -1477,7 +1477,7 @@ get_scalar(void *state, char *token, JsonTokenType tokentype)
Datum
jsonb_extract_path_type(PG_FUNCTION_ARGS)
{
- Oid targetOid = get_fn_expr_argtype(fcinfo->flinfo, 0);
+ Oid targetOid = DatumGetObjectId(0);
Jsonb *jb = PG_GETARG_JSONB_P(1);
ArrayType *path = PG_GETARG_ARRAYTYPE_P(2);
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index b6844537529..66d1af71586 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -9930,8 +9930,8 @@
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 => 'anyelement jsonb text', proargnames => '{target_type, from_json, field_name}',
+ proname => 'jsonb_object_field_type', prorettype => 'internal',
+ proargtypes => 'internal jsonb text', proargnames => '{target_type, from_json, field_name}',
prosrc => 'jsonb_object_field_type'},
{ oid => '3814', descr => 'planner support for numeric(jsonb)',
proname => 'jsonb_cast_support', prorettype => 'internal',
@@ -9945,8 +9945,8 @@
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 => 'anyelement jsonb int4', proargnames => '{target_type, from_json, element_index}',
+ proname => 'jsonb_array_element_type', prorettype => 'internal',
+ proargtypes => 'internal jsonb int4', proargnames => '{target_type, from_json, element_index}',
prosrc => 'jsonb_array_element_type' },
{ oid => '3217', descr => 'get value from jsonb with path elements',
proname => 'jsonb_extract_path', provariadic => 'text', prorettype => 'jsonb',
@@ -9961,8 +9961,8 @@
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 => 'anyelement jsonb _text',
- proallargtypes => '{anyelement,jsonb,_text}', proargmodes => '{i,i,v}',
+ prorettype => 'internal', proargtypes => 'internal jsonb _text',
+ proallargtypes => '{internal,jsonb,_text}', proargmodes => '{i,i,v}',
proargnames => '{target_type,from_json,path_elems}',
prosrc => 'jsonb_extract_path_type' },
{ oid => '3219', descr => 'elements of a jsonb array',
--
2.21.0
[application/octet-stream] v9-0001-optimize-casting-jsonb-to-a-given-type.patch (31.7K, 4-v9-0001-optimize-casting-jsonb-to-a-given-type.patch)
download | inline diff:
From c4b1ae13a0f4ba28972835ffa4c9850e2e0dbda6 Mon Sep 17 00:00:00 2001
From: Andy Fan <[email protected]>
Date: Wed, 16 Aug 2023 14:04:27 +0800
Subject: [PATCH v9] 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 | 177 +++++++++++++++++++++++++++
src/backend/utils/adt/jsonfuncs.c | 115 +++++++++++-------
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 | 178 +++++++++++++++++-----------
src/test/regress/sql/jsonb.sql | 56 ++++++---
9 files changed, 462 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..148c1e2e195 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,180 @@ 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 = list_insert_nth(fexpr->args, 0, 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:
+ elog(ERROR, "cast jsonb to type %u is not allowed", targetOid);
+ break;
+ }
+
+ PG_RETURN_POINTER(NULL);
+}
+
+Datum
+jsonb_object_field_type(PG_FUNCTION_ARGS)
+{
+ Oid targetOid = get_fn_expr_argtype(fcinfo->flinfo, 0);
+ Jsonb *jb = PG_GETARG_JSONB_P(1);
+ text *key = PG_GETARG_TEXT_PP(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)
+{
+ Oid targetOid = get_fn_expr_argtype(fcinfo->flinfo, 0);
+ Jsonb *jb = PG_GETARG_JSONB_P(1);
+ int element = PG_GETARG_INT32(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..bb4ca807d74 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,40 @@ get_scalar(void *state, char *token, JsonTokenType tokentype)
return JSON_SUCCESS;
}
+Datum
+jsonb_extract_path_type(PG_FUNCTION_ARGS)
+{
+ Oid targetOid = get_fn_expr_argtype(fcinfo->flinfo, 0);
+ Jsonb *jb = PG_GETARG_JSONB_P(1);
+ ArrayType *path = PG_GETARG_ARRAYTYPE_P(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 +1551,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 +1605,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 +1626,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 +1638,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 +1663,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..8a896f9aad2 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 202308171
#endif
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 6996073989a..b6844537529 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 => 'anyelement jsonb text', proargnames => '{target_type, from_json, field_name}',
+ 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 => 'anyelement jsonb int4', proargnames => '{target_type, from_json, element_index}',
+ 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 => 'anyelement jsonb _text',
+ proallargtypes => '{anyelement,jsonb,_text}', proargmodes => '{i,i,v}',
+ proargnames => '{target_type,from_json,path_elems}',
+ 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..12daacb3b80 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(0, test_json, 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?
-----------
@@ -3537,6 +3559,24 @@ SELECT count(*) FROM testjsonb WHERE j @? '$.bar';
RESET enable_seqscan;
DROP INDEX jidx;
+-- test the supported function for jsonb cast.
+EXPLAIN (COSTS OFF, VERBOSE)
+SELECT (j->'a')::numeric,
+(j->'a')::int2,
+(j->'a')::int4,
+(j->'a')::int8,
+(j->'a')::float4,
+(j->'a')::float8,
+(j->'a')::bool,
+(j #> '{"a"}')::numeric,
+(j->0)::numeric
+FROM testjsonb;
+ QUERY PLAN
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Seq Scan on public.testjsonb
+ Output: jsonb_object_field_type('0'::numeric, j, 'a'::text), jsonb_object_field_type('0'::smallint, j, 'a'::text), jsonb_object_field_type(0, j, 'a'::text), jsonb_object_field_type('0'::bigint, j, 'a'::text), jsonb_object_field_type('0'::real, j, 'a'::text), jsonb_object_field_type('0'::double precision, j, 'a'::text), jsonb_object_field_type(false, j, 'a'::text), pg_catalog.jsonb_extract_path_type('0'::numeric, j, '{a}'::text[]), jsonb_array_element_type('0'::numeric, j, 0)
+(2 rows)
+
-- nested tests
SELECT '{"ff":{"a":12,"b":16}}'::jsonb;
jsonb
@@ -5471,107 +5511,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..8634d154efe 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 #> '{}';
@@ -939,6 +944,19 @@ SELECT count(*) FROM testjsonb WHERE j @? '$.bar';
RESET enable_seqscan;
DROP INDEX jidx;
+-- test the supported function for jsonb cast.
+EXPLAIN (COSTS OFF, VERBOSE)
+SELECT (j->'a')::numeric,
+(j->'a')::int2,
+(j->'a')::int4,
+(j->'a')::int8,
+(j->'a')::float4,
+(j->'a')::float8,
+(j->'a')::bool,
+(j #> '{"a"}')::numeric,
+(j->0)::numeric
+FROM testjsonb;
+
-- nested tests
SELECT '{"ff":{"a":12,"b":16}}'::jsonb;
SELECT '{"ff":{"a":12,"b":16},"qq":123}'::jsonb;
@@ -1496,23 +1514,27 @@ select ts_headline('{}'::jsonb, tsquery('aaa & bbb'));
select ts_headline('[]'::jsonb, tsquery('aaa & bbb'));
-- casts
-select 'true'::jsonb::bool;
+select 'true'::jsonb::bool, ('{"a": true}'::jsonb->'a')::bool;
select '[]'::jsonb::bool;
-select '1.0'::jsonb::float;
+select ('{"a": []}'::jsonb->'a')::bool;
+select '1.0'::jsonb::float, ('{"a": 1.0}'::jsonb->'a')::float;
select '[1.0]'::jsonb::float;
-select '12345'::jsonb::int4;
+select ('{"a": [1.0]}'::jsonb->'a')::float;
+select '12345'::jsonb::int4, ('{"a": 12345}'::jsonb->'a')::int4;
select '"hello"'::jsonb::int4;
-select '12345'::jsonb::numeric;
+select ('{"a": "hello"}'::jsonb->'a')::int4;
+
+select '12345'::jsonb::numeric, ('{"a": 12345}'::jsonb->'a')::numeric;
select '{}'::jsonb::numeric;
-select '12345.05'::jsonb::numeric;
-select '12345.05'::jsonb::float4;
-select '12345.05'::jsonb::float8;
-select '12345.05'::jsonb::int2;
-select '12345.05'::jsonb::int4;
-select '12345.05'::jsonb::int8;
-select '12345.0000000000000000000000000000000000000000000005'::jsonb::numeric;
-select '12345.0000000000000000000000000000000000000000000005'::jsonb::float4;
-select '12345.0000000000000000000000000000000000000000000005'::jsonb::float8;
-select '12345.0000000000000000000000000000000000000000000005'::jsonb::int2;
-select '12345.0000000000000000000000000000000000000000000005'::jsonb::int4;
-select '12345.0000000000000000000000000000000000000000000005'::jsonb::int8;
+select '12345.05'::jsonb::numeric, ('{"a": 12345.05}'::jsonb->'a')::numeric;
+select '12345.05'::jsonb::float4, ('{"a": 12345.05}'::jsonb->'a')::float4;
+select '12345.05'::jsonb::float8, ('{"a": 12345.05}'::jsonb->'a')::float8;
+select '12345.05'::jsonb::int2, ('{"a": 12345.05}'::jsonb->'a')::int2;
+select '12345.05'::jsonb::int4, ('{"a": 12345.05}'::jsonb->'a')::int4;
+select '12345.05'::jsonb::int8, ('{"a": 12345.05}'::jsonb->'a')::int8;
+select '12345.0000000000000000000000000000000000000000000005'::jsonb::numeric, ('{"a": 12345.0000000000000000000000000000000000000000000005}'::jsonb->'a')::numeric;
+select '12345.0000000000000000000000000000000000000000000005'::jsonb::float4, ('{"a": 12345.0000000000000000000000000000000000000000000005}'::jsonb->'a')::float4;
+select '12345.0000000000000000000000000000000000000000000005'::jsonb::float8, ('{"a": 12345.0000000000000000000000000000000000000000000005}'::jsonb->'a')::float8;
+select '12345.0000000000000000000000000000000000000000000005'::jsonb::int2, ('{"a": 12345.0000000000000000000000000000000000000000000005}'::jsonb->'a')::int2;
+select '12345.0000000000000000000000000000000000000000000005'::jsonb::int4, ('{"a": 12345.0000000000000000000000000000000000000000000005}'::jsonb->'a')::int4;
+select '12345.0000000000000000000000000000000000000000000005'::jsonb::int8, ('{"a": 12345.0000000000000000000000000000000000000000000005}'::jsonb->'a')::int8;
--
2.21.0
view thread (35+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: Extract numeric filed in JSONB more effectively
In-Reply-To: <CAKU4AWoLgC5ejOF8jxskd5oq52D-eR_1Q-HM5+e8OBVLak=qTg@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox