public inbox for [email protected]
help / color / mirror / Atom feedFrom: Andy Fan <[email protected]>
To: jian he <[email protected]>
Cc: Chapman Flack <[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: Mon, 4 Sep 2023 22:35:16 +0800
Message-ID: <CAKU4AWoXjErs4FXTqCcvr_gobx4sjPmWyyTDiN3qUDNjrYO8Xg@mail.gmail.com> (raw)
In-Reply-To: <CAKU4AWqQjMREZpOOECC6duV-VbEYYAdP6EL+VaK=-X7q-JQjFw@mail.gmail.com>
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]>
<CAKU4AWoLgC5ejOF8jxskd5oq52D-eR_1Q-HM5+e8OBVLak=qTg@mail.gmail.com>
<[email protected]>
<[email protected]>
<CAKU4AWpnFXYXtfOQp_QdQ7RwCD-d+qzyZKdNygc+9DY8bveVYQ@mail.gmail.com>
<[email protected]>
<CAKU4AWrysndLra+SZLsODZF-bet1JnPMLU9HsiFH75ZnSPK2zw@mail.gmail.com>
<CAKU4AWrs4Pzajm2_tgtUTf=CWfDJEx=3h45Lhqg7tNOVZw5YxA@mail.gmail.com>
<CAKU4AWryj3pFG87mfQGS-K6XGOiyeYsMLOgajZ337NRV1F6Wfw@mail.gmail.com>
<[email protected]>
<[email protected]>
<CAKU4AWrxBXomhGcubsLqBj4FRbOf1AEXqpwBsm-mGJEJ0G_ypA@mail.gmail.com>
<[email protected]>
<CAKU4AWpM=C5NxumoHQB1=id0OBc63kUgMUWyRNO2=OrHj6ROyQ@mail.gmail.com>
<CAKU4AWrYOYSXMmotwhqiErci7aMz--8LxfpLiSg+0+7XNJBcuA@mail.gmail.com>
<CACJufxHdDkdR13nkhxybOUDKSB9kgsDhZU8xiGg1t_rVP-==nA@mail.gmail.com>
<CAKU4AWqQjMREZpOOECC6duV-VbEYYAdP6EL+VaK=-X7q-JQjFw@mail.gmail.com>
Hi,
v13 attached. Changes includes:
1. fix the bug Jian provides.
2. reduce more code duplication without DirectFunctionCall.
3. add the overlooked jsonb_path_query and jsonb_path_query_first as
candidates
--
Best Regards
Andy Fan
Attachments:
[application/octet-stream] v13-0001-optimize-casting-jsonb-to-a-given-type.patch (34.9K, 3-v13-0001-optimize-casting-jsonb-to-a-given-type.patch)
download | inline diff:
From 9b1b4446d41b587e0dd635a7668b42e00ab4e79b Mon Sep 17 00:00:00 2001
From: Andy Fan <[email protected]>
Date: Mon, 4 Sep 2023 22:29:18 +0800
Subject: [PATCH v13] 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 | 145 +++++++++++++++++++++++
src/backend/utils/adt/jsonfuncs.c | 164 ++++++++++++++++++--------
src/backend/utils/adt/jsonpath_exec.c | 36 ++++--
src/include/catalog/catversion.h | 3 +-
src/include/catalog/pg_proc.dat | 46 ++++++--
src/test/regress/expected/jsonb.out | 112 +++++++++++++++++-
src/test/regress/sql/jsonb.sql | 66 ++++++++++-
7 files changed, 501 insertions(+), 71 deletions(-)
diff --git a/src/backend/utils/adt/jsonb.c b/src/backend/utils/adt/jsonb.c
index 9781852b0cb..31656d8a11e 100644
--- a/src/backend/utils/adt/jsonb.c
+++ b/src/backend/utils/adt/jsonb.c
@@ -17,11 +17,15 @@
#include "catalog/pg_proc.h"
#include "catalog/pg_type.h"
#include "funcapi.h"
+#include "nodes/makefuncs.h"
+#include "nodes/supportnodes.h"
+#include "parser/parse_coerce.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 +2042,147 @@ cannotCastJsonbValue(enum jbvType type, const char *sqltype)
elog(ERROR, "unknown jsonb type: %d", (int) type);
}
+Datum
+jsonb_cast_support(PG_FUNCTION_ARGS)
+{
+ Node *rawreq = (Node *) PG_GETARG_POINTER(0);
+
+ if (IsA(rawreq, SupportRequestSimplify))
+ {
+ SupportRequestSimplify *req = (SupportRequestSimplify *) rawreq;
+ FuncExpr *fexpr = palloc0(sizeof(FuncExpr));
+ FuncExpr *jsonb_start_func = NULL, *jsonb_finish_func = NULL, *final_func = NULL;
+ Node *input;
+ Oid new_func_id = InvalidOid;
+ List *args;
+ Oid input_func_id, collid, inputcollid;
+ bool retset = false, variadic = false;
+
+ memcpy(fexpr, req->fcall, sizeof(FuncExpr));
+ Assert(list_length(fexpr->args) == 1);
+ input = (Node *) linitial(fexpr->args);
+
+ if (IsA(input, OpExpr))
+ {
+ OpExpr *opExpr = castNode(OpExpr, input);
+ input_func_id = opExpr->opfuncid;
+ collid = opExpr->opcollid;
+ inputcollid = opExpr->inputcollid;
+ args = opExpr->args;
+ }
+ else if (IsA(input, FuncExpr))
+ {
+ FuncExpr *funcExpr = castNode(FuncExpr, input);
+ input_func_id = funcExpr->funcid;
+ collid = funcExpr->funccollid;
+ inputcollid = funcExpr->inputcollid;
+ args = funcExpr->args;
+ }
+ else
+ /* not the desired pattern. */
+ PG_RETURN_POINTER(fexpr);
+
+
+ switch (input_func_id)
+ {
+ case F_JSONB_OBJECT_FIELD:
+ new_func_id = F_JSONB_OBJECT_FIELD_START;
+ break;
+ case F_JSONB_ARRAY_ELEMENT:
+ new_func_id = F_JSONB_ARRAY_ELEMENT_START;
+ break;
+ case F_JSONB_EXTRACT_PATH:
+ new_func_id = F_JSONB_EXTRACT_PATH_START;
+ variadic = true;
+ break;
+ case F_JSONB_PATH_QUERY:
+ new_func_id = F_JSONB_PATH_QUERY_START;
+ retset = true;
+ break;
+ case F_JSONB_PATH_QUERY_FIRST:
+ new_func_id = F_JSONB_PATH_QUERY_FIRST_START;
+ break;
+ default:
+ new_func_id = InvalidOid;
+ break;
+ }
+
+ if (!OidIsValid(new_func_id))
+ PG_RETURN_POINTER(fexpr);
+
+ jsonb_start_func = makeFuncExpr(new_func_id, INTERNALOID, args,
+ collid, inputcollid,
+ COERCE_EXPLICIT_CALL);
+ jsonb_start_func->funcretset = retset;
+ jsonb_start_func->funcvariadic = variadic;
+
+ /* relabel the first arguments as 'internal'. */
+ linitial(jsonb_start_func->args) = makeRelabelType(linitial(jsonb_start_func->args),
+ INTERNALOID, 0,
+ InvalidOid,
+ COERCE_IMPLICIT_CAST);
+ switch (fexpr->funcresulttype)
+ {
+ case INT2OID:
+ case INT4OID:
+ case INT8OID:
+ case FLOAT4OID:
+ case FLOAT8OID:
+ case NUMERICOID:
+ jsonb_finish_func = makeFuncExpr(F_JSONB_FINISH_NUMERIC, NUMERICOID,
+ list_make2(jsonb_start_func,
+ makeConst(OIDOID,
+ -1,
+ InvalidOid,
+ sizeof(Oid),
+ ObjectIdGetDatum(fexpr->funcresulttype),
+ false,
+ true)),
+ collid, inputcollid, COERCE_EXPLICIT_CALL);
+
+ if (fexpr->funcresulttype != NUMERICOID)
+ {
+ final_func = (FuncExpr *)coerce_type(NULL, (Node *)jsonb_finish_func, NUMERICOID,
+ fexpr->funcresulttype, 0, COERCION_EXPLICIT,
+ COERCE_EXPLICIT_CAST, fexpr->location);
+ }
+ else
+ final_func = jsonb_finish_func;
+
+ PG_RETURN_POINTER(final_func);
+ case BOOLOID:
+ final_func = makeFuncExpr(F_JSONB_FINISH_BOOL, BOOLOID,
+ list_make1(jsonb_start_func), collid,
+ inputcollid, COERCE_EXPLICIT_CALL);
+ PG_RETURN_POINTER(final_func);
+ default:
+ PG_RETURN_POINTER(fexpr);
+ }
+ }
+
+ PG_RETURN_POINTER(NULL);
+}
+
+
+Datum
+jsonb_finish_numeric(PG_FUNCTION_ARGS)
+{
+ JsonbValue *v = (JsonbValue *)PG_GETARG_POINTER(0);
+ Oid final_oid = PG_GETARG_OID(1);
+ if (v->type != jbvNumeric)
+ cannotCastJsonbValue(v->type, format_type_be(final_oid));
+ PG_RETURN_NUMERIC(v->val.numeric);
+}
+
+Datum
+jsonb_finish_bool(PG_FUNCTION_ARGS)
+{
+ JsonbValue *v = (JsonbValue *)PG_GETARG_POINTER(0);
+ if (v->type != jbvBool)
+ cannotCastJsonbValue(v->type, "boolean");
+ PG_RETURN_BOOL(v->val.boolean);
+}
+
Datum
jsonb_bool(PG_FUNCTION_ARGS)
{
diff --git a/src/backend/utils/adt/jsonfuncs.c b/src/backend/utils/adt/jsonfuncs.c
index a4bfa5e4040..e3f6fea4e19 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
@@ -848,13 +849,12 @@ json_object_field(PG_FUNCTION_ARGS)
PG_RETURN_NULL();
}
-Datum
-jsonb_object_field(PG_FUNCTION_ARGS)
+static Datum
+jsonb_object_field_internal(FunctionCallInfo fcinfo, bool as_jsonb)
{
Jsonb *jb = PG_GETARG_JSONB_P(0);
text *key = PG_GETARG_TEXT_PP(1);
- JsonbValue *v;
- JsonbValue vbuf;
+ JsonbValue *v;
if (!JB_ROOT_IS_OBJECT(jb))
PG_RETURN_NULL();
@@ -862,12 +862,26 @@ jsonb_object_field(PG_FUNCTION_ARGS)
v = getKeyJsonValueFromContainer(&jb->root,
VARDATA_ANY(key),
VARSIZE_ANY_EXHDR(key),
- &vbuf);
+ NULL);
+ if (v == NULL)
+ PG_RETURN_NULL();
- if (v != NULL)
+ if (as_jsonb)
PG_RETURN_JSONB_P(JsonbValueToJsonb(v));
- PG_RETURN_NULL();
+ PG_RETURN_POINTER(v);
+}
+
+Datum
+jsonb_object_field(PG_FUNCTION_ARGS)
+{
+ return jsonb_object_field_internal(fcinfo, true);
+}
+
+Datum
+jsonb_object_field_start(PG_FUNCTION_ARGS)
+{
+ return jsonb_object_field_internal(fcinfo, false);
}
Datum
@@ -923,8 +937,8 @@ json_array_element(PG_FUNCTION_ARGS)
PG_RETURN_NULL();
}
-Datum
-jsonb_array_element(PG_FUNCTION_ARGS)
+static Datum
+jsonb_array_element_internal(FunctionCallInfo fcinfo, bool as_jsonb)
{
Jsonb *jb = PG_GETARG_JSONB_P(0);
int element = PG_GETARG_INT32(1);
@@ -945,10 +959,25 @@ jsonb_array_element(PG_FUNCTION_ARGS)
}
v = getIthJsonbValueFromContainer(&jb->root, element);
- if (v != NULL)
+ if (v == NULL)
+ PG_RETURN_NULL();
+
+ if (as_jsonb)
PG_RETURN_JSONB_P(JsonbValueToJsonb(v));
- PG_RETURN_NULL();
+ PG_RETURN_POINTER(v);
+}
+
+Datum
+jsonb_array_element(PG_FUNCTION_ARGS)
+{
+ return jsonb_array_element_internal(fcinfo, true);
+}
+
+Datum
+jsonb_array_element_start(PG_FUNCTION_ARGS)
+{
+ return jsonb_array_element_internal(fcinfo, false);
}
Datum
@@ -1473,6 +1502,39 @@ get_scalar(void *state, char *token, JsonTokenType tokentype)
return JSON_SUCCESS;
}
+Datum
+jsonb_extract_path_start(PG_FUNCTION_ARGS)
+{
+ Jsonb *jb = PG_GETARG_JSONB_P(0);
+ ArrayType *path = PG_GETARG_ARRAYTYPE_P(1);
+
+ 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();
+
+ PG_RETURN_POINTER(v);
+}
+
Datum
jsonb_extract_path(PG_FUNCTION_ARGS)
{
@@ -1516,52 +1578,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 +1632,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 +1653,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 +1665,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 +1690,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/backend/utils/adt/jsonpath_exec.c b/src/backend/utils/adt/jsonpath_exec.c
index 2d0599b4aaa..973adb9981b 100644
--- a/src/backend/utils/adt/jsonpath_exec.c
+++ b/src/backend/utils/adt/jsonpath_exec.c
@@ -393,7 +393,7 @@ jsonb_path_match_opr(PG_FUNCTION_ARGS)
* rowset.
*/
static Datum
-jsonb_path_query_internal(FunctionCallInfo fcinfo, bool tz)
+jsonb_path_query_internal(FunctionCallInfo fcinfo, bool tz, bool as_jsonb)
{
FuncCallContext *funcctx;
List *found;
@@ -435,19 +435,28 @@ jsonb_path_query_internal(FunctionCallInfo fcinfo, bool tz)
v = lfirst(c);
funcctx->user_fctx = list_delete_first(found);
- SRF_RETURN_NEXT(funcctx, JsonbPGetDatum(JsonbValueToJsonb(v)));
+ if (as_jsonb)
+ SRF_RETURN_NEXT(funcctx, JsonbPGetDatum(JsonbValueToJsonb(v)));
+ else
+ SRF_RETURN_NEXT(funcctx, PointerGetDatum(v));
}
Datum
jsonb_path_query(PG_FUNCTION_ARGS)
{
- return jsonb_path_query_internal(fcinfo, false);
+ return jsonb_path_query_internal(fcinfo, false, true);
}
Datum
jsonb_path_query_tz(PG_FUNCTION_ARGS)
{
- return jsonb_path_query_internal(fcinfo, true);
+ return jsonb_path_query_internal(fcinfo, true, true);
+}
+
+Datum
+jsonb_path_query_start(PG_FUNCTION_ARGS)
+{
+ return jsonb_path_query_internal(fcinfo, false, false);
}
/*
@@ -487,7 +496,7 @@ jsonb_path_query_array_tz(PG_FUNCTION_ARGS)
* item. If there are no items, NULL returned.
*/
static Datum
-jsonb_path_query_first_internal(FunctionCallInfo fcinfo, bool tz)
+jsonb_path_query_first_internal(FunctionCallInfo fcinfo, bool tz, bool as_jsonb)
{
Jsonb *jb = PG_GETARG_JSONB_P(0);
JsonPath *jp = PG_GETARG_JSONPATH_P(1);
@@ -498,7 +507,12 @@ jsonb_path_query_first_internal(FunctionCallInfo fcinfo, bool tz)
(void) executeJsonPath(jp, vars, jb, !silent, &found, tz);
if (JsonValueListLength(&found) >= 1)
- PG_RETURN_JSONB_P(JsonbValueToJsonb(JsonValueListHead(&found)));
+ {
+ if (as_jsonb)
+ PG_RETURN_JSONB_P(JsonbValueToJsonb(JsonValueListHead(&found)));
+ else
+ PG_RETURN_POINTER(JsonValueListHead(&found));
+ }
else
PG_RETURN_NULL();
}
@@ -506,13 +520,19 @@ jsonb_path_query_first_internal(FunctionCallInfo fcinfo, bool tz)
Datum
jsonb_path_query_first(PG_FUNCTION_ARGS)
{
- return jsonb_path_query_first_internal(fcinfo, false);
+ return jsonb_path_query_first_internal(fcinfo, false, true);
}
Datum
jsonb_path_query_first_tz(PG_FUNCTION_ARGS)
{
- return jsonb_path_query_first_internal(fcinfo, true);
+ return jsonb_path_query_first_internal(fcinfo, true, true);
+}
+
+Datum
+jsonb_path_query_first_start(PG_FUNCTION_ARGS)
+{
+ return jsonb_path_query_first_internal(fcinfo, false, false);
}
/********************Execute functions for JsonPath**************************/
diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h
index ab9a7ac1f79..1bf0b828853 100644
--- a/src/include/catalog/catversion.h
+++ b/src/include/catalog/catversion.h
@@ -57,6 +57,5 @@
*/
/* yyyymmddN */
-#define CATALOG_VERSION_NO 202308251
-
+#define CATALOG_VERSION_NO 202309041
#endif
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 9805bc61180..ef719aac6bb 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -4587,25 +4587,25 @@
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', prorettype => 'numeric', proargtypes => 'jsonb', prosupport => 'jsonb_cast_support',
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' },
@@ -9947,6 +9947,30 @@
proname => 'jsonb_object_field_text', prorettype => 'text',
proargtypes => 'jsonb text', proargnames => '{from_json, field_name}',
prosrc => 'jsonb_object_field_text' },
+{ oid => '4552', descr => 'extract jsonbvalue from jsonb for the given field',
+ proname => 'jsonb_object_field_start', prorettype => 'internal',
+ proargtypes => 'internal text', proargnames => '{from_json, field_name}',
+ prosrc => 'jsonb_object_field_start' },
+{ oid => '3813', descr => 'extract josnbvalue from jsonb array for the given index',
+ proname => 'jsonb_array_element_start', prorettype => 'internal',
+ proargtypes => 'internal int4', proargnames => '{from_jsonb, element_index}',
+ prosrc => 'jsonb_array_element_start' },
+{ oid => '4549', descr => 'extract jsonbvalue from jsonb for the given paths',
+ proname => 'jsonb_extract_path_start', provariadic => 'text', prorettype => 'internal',
+ proargtypes => 'internal _text', proallargtypes => '{internal,_text}',
+ proargmodes => '{i,v}', proargnames => '{from_jsonb,path_elems}',
+ prosrc => 'jsonb_extract_path_start'},
+{ oid => '4553', descr => 'convert a jsonbvalue to numeric',
+ proname => 'jsonb_finish_numeric', prorettype => 'numeric',
+ proargtypes => 'internal oid', proargnames => '{from_jsonvalue,target_oid}',
+ prosrc => 'jsonb_finish_numeric' },
+{ oid => '4554', descr => 'convert a jsonbvalue to boolean',
+ proname => 'jsonb_finish_bool', prorettype => 'bool',
+ proargtypes => 'internal', proargnames => '{jsonvalue}',
+ prosrc => 'jsonb_finish_bool' },
+{ 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}',
@@ -10139,6 +10163,10 @@
proname => 'jsonb_path_query', prorows => '1000', proretset => 't',
prorettype => 'jsonb', proargtypes => 'jsonb jsonpath jsonb bool',
prosrc => 'jsonb_path_query' },
+{ oid => '4557', descr => 'jsonpath query as jsonbvalue',
+ proname => 'jsonb_path_query_start', prorows => '1000', proretset => 't',
+ prorettype => 'internal', proargtypes => 'internal jsonpath jsonb bool',
+ prosrc => 'jsonb_path_query_start' },
{ oid => '4007', descr => 'jsonpath query wrapped into array',
proname => 'jsonb_path_query_array', prorettype => 'jsonb',
proargtypes => 'jsonb jsonpath jsonb bool',
@@ -10147,6 +10175,10 @@
proname => 'jsonb_path_query_first', prorettype => 'jsonb',
proargtypes => 'jsonb jsonpath jsonb bool',
prosrc => 'jsonb_path_query_first' },
+{ oid => '4555', descr => 'jsonpath query first item as jsonbvalue',
+ proname => 'jsonb_path_query_first_start', prorettype => 'internal',
+ proargtypes => 'internal jsonpath jsonb bool',
+ prosrc => 'jsonb_path_query_first_start' },
{ oid => '4009', descr => 'jsonpath match',
proname => 'jsonb_path_match', prorettype => 'bool',
proargtypes => 'jsonb jsonpath jsonb bool', prosrc => 'jsonb_path_match' },
diff --git a/src/test/regress/expected/jsonb.out b/src/test/regress/expected/jsonb.out
index 4a16d0dbafb..b612fb85ec2 100644
--- a/src/test/regress/expected/jsonb.out
+++ b/src/test/regress/expected/jsonb.out
@@ -457,8 +457,114 @@ 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}}');
+('object','{"field1":"val1","field2":"val2","field3":null, "field4": 4, "field5": [1,2,3], "field6": {"f1":9}, "field7": true, "field8": [1,2,3,4,5]}');
+\pset null NULL
+EXPLAIN (COSTS OFF, VERBOSE)
+SELECT
+(test_json -> 'field4')::numeric,
+(test_json -> 'field4')::int2,
+(test_json -> 'field4')::int4,
+(test_json -> 'field4')::int8,
+(test_json -> 'field4')::float4,
+(test_json -> 'field4')::float8,
+(test_json->'field5' -> 0)::numeric,
+(test_json->'field5' -> 10)::numeric,
+(test_json#>'{"field6", "f1"}')::numeric,
+(test_json#>'{"field6", "f2"}')::numeric,
+(test_json#>'{"field7"}')::bool
+FROM test_jsonb
+WHERE json_type = 'object';
+ QUERY PLAN
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Seq Scan on pg_temp.test_jsonb
+ Output: jsonb_finish_numeric(jsonb_object_field_start((test_json)::internal(0), 'field4'::text), '1700'::oid), (jsonb_finish_numeric(jsonb_object_field_start((test_json)::internal(0), 'field4'::text), '21'::oid))::smallint, (jsonb_finish_numeric(jsonb_object_field_start((test_json)::internal(0), 'field4'::text), '23'::oid))::integer, (jsonb_finish_numeric(jsonb_object_field_start((test_json)::internal(0), 'field4'::text), '20'::oid))::bigint, (jsonb_finish_numeric(jsonb_object_field_start((test_json)::internal(0), 'field4'::text), '700'::oid))::real, (jsonb_finish_numeric(jsonb_object_field_start((test_json)::internal(0), 'field4'::text), '701'::oid))::double precision, jsonb_finish_numeric(jsonb_array_element_start(((test_json -> 'field5'::text))::internal(0), 0), '1700'::oid), jsonb_finish_numeric(jsonb_array_element_start(((test_json -> 'field5'::text))::internal(0), 10), '1700'::oid), jsonb_finish_numeric(jsonb_extract_path_start((test_json)::internal(0), VARIADIC '{field6,f1}'::text[]), '1700'::oid), jsonb_finish_numeric(jsonb_extract_path_start((test_json)::internal(0), VARIADIC '{field6,f2}'::text[]), '1700'::oid), jsonb_finish_bool(jsonb_extract_path_start((test_json)::internal(0), VARIADIC '{field7}'::text[]))
+ Filter: (test_jsonb.json_type = 'object'::text)
+(3 rows)
+
+SELECT
+(test_json -> 'field4')::numeric,
+(test_json -> 'field4')::int2,
+(test_json -> 'field4')::int4,
+(test_json -> 'field4')::int8,
+(test_json -> 'field4')::float4,
+(test_json -> 'field4')::float8,
+(test_json -> 'field5' -> 0)::numeric,
+(test_json -> 'field5' -> 10)::numeric,
+(test_json #> '{"field6", "f1"}')::numeric,
+(test_json #> '{"field6", "f2"}')::numeric,
+(test_json#>'{"field7"}')::bool
+FROM test_jsonb
+WHERE json_type = 'object';
+ numeric | int2 | int4 | int8 | float4 | float8 | numeric | numeric | numeric | numeric | bool
+---------+------+------+------+--------+--------+---------+---------+---------+---------+------
+ 4 | 4 | 4 | 4 | 4 | 4 | 1 | NULL | 9 | NULL | t
+(1 row)
+
+EXPLAIN (COSTS OFF, VERBOSE)
+SELECT
+jsonb_path_query(test_json,'$.field8[*] ? (@ >= $min && @ <= $max)', '{"min":2, "max":4}'),
+jsonb_path_query_first(test_json, '$.field8[*] ? (@ >= $min && @ <= $max)', '{"min":2, "max":4}'),
+jsonb_path_query(test_json,'$.field8[*] ? (@ >= $min && @ <= $max)', '{"min":2, "max":4}')::int2,
+jsonb_path_query_first(test_json, '$.field8[*] ? (@ >= $min && @ <= $max)', '{"min":2, "max":4}')::int2
+FROM test_jsonb
+WHERE json_type = 'object';
+ QUERY PLAN
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Result
+ Output: (jsonb_path_query(test_json, '$."field8"[*]?(@ >= $"min" && @ <= $"max")'::jsonpath, '{"max": 4, "min": 2}'::jsonb, false)), jsonb_path_query_first(test_json, '$."field8"[*]?(@ >= $"min" && @ <= $"max")'::jsonpath, '{"max": 4, "min": 2}'::jsonb, false), (jsonb_finish_numeric((jsonb_path_query_start((test_json)::internal(0), '$."field8"[*]?(@ >= $"min" && @ <= $"max")'::jsonpath, '{"max": 4, "min": 2}'::jsonb, false)), '21'::oid))::smallint, (jsonb_finish_numeric(jsonb_path_query_first_start((test_json)::internal(0), '$."field8"[*]?(@ >= $"min" && @ <= $"max")'::jsonpath, '{"max": 4, "min": 2}'::jsonb, false), '21'::oid))::smallint
+ -> ProjectSet
+ Output: jsonb_path_query(test_json, '$."field8"[*]?(@ >= $"min" && @ <= $"max")'::jsonpath, '{"max": 4, "min": 2}'::jsonb, false), jsonb_path_query_start((test_json)::internal(0), '$."field8"[*]?(@ >= $"min" && @ <= $"max")'::jsonpath, '{"max": 4, "min": 2}'::jsonb, false), test_json
+ -> Seq Scan on pg_temp.test_jsonb
+ Output: json_type, test_json
+ Filter: (test_jsonb.json_type = 'object'::text)
+(7 rows)
+
+SELECT
+jsonb_path_query(test_json,'$.field8[*] ? (@ >= $min && @ <= $max)', '{"min":2, "max":4}'),
+jsonb_path_query_first(test_json, '$.field8[*] ? (@ >= $min && @ <= $max)', '{"min":2, "max":4}'),
+jsonb_path_query(test_json,'$.field8[*] ? (@ >= $min && @ <= $max)', '{"min":2, "max":4}')::int2,
+jsonb_path_query_first(test_json, '$.field8[*] ? (@ >= $min && @ <= $max)', '{"min":2, "max":4}')::int2
+FROM test_jsonb
+WHERE json_type = 'object';
+ jsonb_path_query | jsonb_path_query_first | jsonb_path_query | jsonb_path_query_first
+------------------+------------------------+------------------+------------------------
+ 2 | 2 | 2 | 2
+ 3 | 2 | 3 | 2
+ 4 | 2 | 4 | 2
+(3 rows)
+
+EXPLAIN (COSTS OFF, VERBOSE)
+SELECT (test_json #> '{}')::numeric FROM test_jsonb WHERE json_type = 'scalarint';
+ QUERY PLAN
+------------------------------------------------------------------------------------------------------------------------
+ Seq Scan on pg_temp.test_jsonb
+ Output: jsonb_finish_numeric(jsonb_extract_path_start((test_json)::internal(0), VARIADIC '{}'::text[]), '1700'::oid)
+ Filter: (test_jsonb.json_type = 'scalarint'::text)
+(3 rows)
+
+SELECT (test_json #> '{}')::numeric FROM test_jsonb WHERE json_type = 'scalarint';
+ numeric
+---------
+ 2
+(1 row)
+
+-- let raise some errors.
+EXPLAIN (COSTS OFF, VERBOSE)
+SELECT (test_json -> 'field1')::int4 FROM test_jsonb WHERE json_type = 'object';
+ QUERY PLAN
+--------------------------------------------------------------------------------------------------------------------------
+ Seq Scan on pg_temp.test_jsonb
+ Output: (jsonb_finish_numeric(jsonb_object_field_start((test_json)::internal(0), 'field1'::text), '23'::oid))::integer
+ Filter: (test_jsonb.json_type = 'object'::text)
+(3 rows)
+
+SELECT (test_json -> 'field1')::int4 FROM test_jsonb WHERE json_type = 'object';
+ERROR: cannot cast jsonb string to type integer
+SELECT (test_json -> 'field1')::bool FROM test_jsonb WHERE json_type = 'object';
+ERROR: cannot cast jsonb string to type boolean
+\pset null ''
SELECT test_json -> 'x' FROM test_jsonb WHERE json_type = 'scalar';
?column?
----------
@@ -586,7 +692,9 @@ SELECT jsonb_object_keys(test_json) FROM test_jsonb WHERE json_type = 'object';
field4
field5
field6
-(6 rows)
+ field7
+ field8
+(8 rows)
-- nulls
SELECT (test_json->'field3') IS NULL AS expect_false FROM test_jsonb WHERE json_type = 'object';
diff --git a/src/test/regress/sql/jsonb.sql b/src/test/regress/sql/jsonb.sql
index e4b7cdf703d..500d04936d9 100644
--- a/src/test/regress/sql/jsonb.sql
+++ b/src/test/regress/sql/jsonb.sql
@@ -154,8 +154,72 @@ 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}}');
+('object','{"field1":"val1","field2":"val2","field3":null, "field4": 4, "field5": [1,2,3], "field6": {"f1":9}, "field7": true, "field8": [1,2,3,4,5]}');
+
+\pset null NULL
+EXPLAIN (COSTS OFF, VERBOSE)
+SELECT
+(test_json -> 'field4')::numeric,
+(test_json -> 'field4')::int2,
+(test_json -> 'field4')::int4,
+(test_json -> 'field4')::int8,
+(test_json -> 'field4')::float4,
+(test_json -> 'field4')::float8,
+(test_json->'field5' -> 0)::numeric,
+(test_json->'field5' -> 10)::numeric,
+(test_json#>'{"field6", "f1"}')::numeric,
+(test_json#>'{"field6", "f2"}')::numeric,
+(test_json#>'{"field7"}')::bool
+FROM test_jsonb
+WHERE json_type = 'object';
+
+SELECT
+(test_json -> 'field4')::numeric,
+(test_json -> 'field4')::int2,
+(test_json -> 'field4')::int4,
+(test_json -> 'field4')::int8,
+(test_json -> 'field4')::float4,
+(test_json -> 'field4')::float8,
+(test_json -> 'field5' -> 0)::numeric,
+(test_json -> 'field5' -> 10)::numeric,
+(test_json #> '{"field6", "f1"}')::numeric,
+(test_json #> '{"field6", "f2"}')::numeric,
+(test_json#>'{"field7"}')::bool
+FROM test_jsonb
+WHERE json_type = 'object';
+
+EXPLAIN (COSTS OFF, VERBOSE)
+SELECT
+jsonb_path_query(test_json,'$.field8[*] ? (@ >= $min && @ <= $max)', '{"min":2, "max":4}'),
+jsonb_path_query_first(test_json, '$.field8[*] ? (@ >= $min && @ <= $max)', '{"min":2, "max":4}'),
+jsonb_path_query(test_json,'$.field8[*] ? (@ >= $min && @ <= $max)', '{"min":2, "max":4}')::int2,
+jsonb_path_query_first(test_json, '$.field8[*] ? (@ >= $min && @ <= $max)', '{"min":2, "max":4}')::int2
+FROM test_jsonb
+WHERE json_type = 'object';
+
+SELECT
+jsonb_path_query(test_json,'$.field8[*] ? (@ >= $min && @ <= $max)', '{"min":2, "max":4}'),
+jsonb_path_query_first(test_json, '$.field8[*] ? (@ >= $min && @ <= $max)', '{"min":2, "max":4}'),
+jsonb_path_query(test_json,'$.field8[*] ? (@ >= $min && @ <= $max)', '{"min":2, "max":4}')::int2,
+jsonb_path_query_first(test_json, '$.field8[*] ? (@ >= $min && @ <= $max)', '{"min":2, "max":4}')::int2
+FROM test_jsonb
+WHERE json_type = 'object';
+
+
+EXPLAIN (COSTS OFF, VERBOSE)
+SELECT (test_json #> '{}')::numeric FROM test_jsonb WHERE json_type = 'scalarint';
+SELECT (test_json #> '{}')::numeric FROM test_jsonb WHERE json_type = 'scalarint';
+
+-- let raise some errors.
+EXPLAIN (COSTS OFF, VERBOSE)
+SELECT (test_json -> 'field1')::int4 FROM test_jsonb WHERE json_type = 'object';
+SELECT (test_json -> 'field1')::int4 FROM test_jsonb WHERE json_type = 'object';
+
+SELECT (test_json -> 'field1')::bool FROM test_jsonb WHERE json_type = 'object';
+
+\pset null ''
SELECT test_json -> 'x' FROM test_jsonb WHERE json_type = 'scalar';
SELECT test_json -> 'x' FROM test_jsonb WHERE json_type = 'array';
--
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: <CAKU4AWoXjErs4FXTqCcvr_gobx4sjPmWyyTDiN3qUDNjrYO8Xg@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