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, 15 Sep 2023 09:53:20 +0800
Message-ID: <CAKU4AWrGci1GyJ-WhwwT05+6hEWYuH_jFc8Vc=oNp0OzKCEDDg@mail.gmail.com> (raw)
In-Reply-To: <CAKU4AWqQ8QQ=QABALQMAUaAkT3BaReujJgPm3QOqDHxk_=abnA@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>
<CAKU4AWoXjErs4FXTqCcvr_gobx4sjPmWyyTDiN3qUDNjrYO8Xg@mail.gmail.com>
<[email protected]>
<CAKU4AWqQ8QQ=QABALQMAUaAkT3BaReujJgPm3QOqDHxk_=abnA@mail.gmail.com>
> Is there a reason not to transform the _tz flavors of
>> jsonb_path_query and jsonb_path-query_first?
>>
>
> I misunderstood the _tz flavors return timestamp, after some deep
> reading of these functions, they just work at the comparisons part.
> so I will add them in the following version.
>
_tz favors did return timestamp.. the reason is stated in the commit
messge of patch 2.
try to apply jsonb extraction optimization to _tz functions.
both jsonb_path_query_tz and jsonb_path_query_tz_first returns
the elements which are timestamp comparable, but such elements
are impossible to be cast to numeric or boolean IIUC. Just provides
this commit for communication purpose only.
so v14 is attached, changes include:
1. Change the typmod for internal type from 0 to -1.
2. return NULL for non-simplify expressions from the planner
support function, hence shallow copy is removed as well.
Things are not addressed yet:
1. the error message handling.
2. if we have chances to optimize _tz functions, I guess no.
3. function naming issue. I think I can get it modified once after
all the other issues are addressed.
--
Best Regards
Andy Fan
Attachments:
[application/octet-stream] v14-0002-try-to-apply-jsonb-extraction-optimization-to-_t.patch (3.9K, 3-v14-0002-try-to-apply-jsonb-extraction-optimization-to-_t.patch)
download | inline diff:
From 29a2ca508541628df9e902be60b3ec1ea81424cf Mon Sep 17 00:00:00 2001
From: Andy Fan <[email protected]>
Date: Fri, 15 Sep 2023 09:37:32 +0800
Subject: [PATCH v14 2/2] try to apply jsonb extraction optimization to _tz
functions.
both jsonb_path_query_tz and jsonb_path_query_tz_first returns
the element which is timestamp comparable, but such element is
impossible to be cast to numeric or boolean IIUC. Just provides
this commit for communication purpose only.
---
src/backend/utils/adt/jsonb.c | 8 +++++++-
src/backend/utils/adt/jsonpath_exec.c | 12 ++++++++++++
src/include/catalog/pg_proc.dat | 8 ++++++++
3 files changed, 27 insertions(+), 1 deletion(-)
diff --git a/src/backend/utils/adt/jsonb.c b/src/backend/utils/adt/jsonb.c
index e2efb28a685..aad3b7f542f 100644
--- a/src/backend/utils/adt/jsonb.c
+++ b/src/backend/utils/adt/jsonb.c
@@ -2089,7 +2089,6 @@ jsonb_cast_support(PG_FUNCTION_ARGS)
/* not the desired pattern. */
PG_RETURN_POINTER(NULL);
-
switch (input_func_id)
{
case F_JSONB_OBJECT_FIELD:
@@ -2106,9 +2105,16 @@ jsonb_cast_support(PG_FUNCTION_ARGS)
new_func_id = F_JSONB_PATH_QUERY_START;
retset = true;
break;
+ case F_JSONB_PATH_QUERY_TZ:
+ new_func_id = F_JSONB_PATH_QUERY_TZ_START;
+ retset = true;
+ break;
case F_JSONB_PATH_QUERY_FIRST:
new_func_id = F_JSONB_PATH_QUERY_FIRST_START;
break;
+ case F_JSONB_PATH_QUERY_FIRST_TZ:
+ new_func_id = F_JSONB_PATH_QUERY_FIRST_TZ_START;
+ break;
default:
new_func_id = InvalidOid;
break;
diff --git a/src/backend/utils/adt/jsonpath_exec.c b/src/backend/utils/adt/jsonpath_exec.c
index 5eef14f93ed..9b7108418be 100644
--- a/src/backend/utils/adt/jsonpath_exec.c
+++ b/src/backend/utils/adt/jsonpath_exec.c
@@ -456,6 +456,12 @@ jsonb_path_query_start(PG_FUNCTION_ARGS)
return jsonb_path_query_internal(fcinfo, false, JsonbValue_AsJsonbValue);
}
+Datum
+jsonb_path_query_tz_start(PG_FUNCTION_ARGS)
+{
+ return jsonb_path_query_internal(fcinfo, true, JsonbValue_AsJsonbValue);
+}
+
/*
* jsonb_path_query_array
* Executes jsonpath for given jsonb document and returns result as
@@ -530,6 +536,12 @@ jsonb_path_query_first_start(PG_FUNCTION_ARGS)
return jsonb_path_query_first_internal(fcinfo, false, JsonbValue_AsJsonbValue);
}
+Datum
+jsonb_path_query_first_tz_start(PG_FUNCTION_ARGS)
+{
+ return jsonb_path_query_first_internal(fcinfo, true, JsonbValue_AsJsonbValue);
+}
+
/********************Execute functions for JsonPath**************************/
/*
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index ef719aac6bb..7e76d611c06 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -10167,6 +10167,10 @@
proname => 'jsonb_path_query_start', prorows => '1000', proretset => 't',
prorettype => 'internal', proargtypes => 'internal jsonpath jsonb bool',
prosrc => 'jsonb_path_query_start' },
+{ oid => '4556', descr => 'jsonpath query tz as jsonbvalue',
+ proname => 'jsonb_path_query_tz_start', prorows => '1000', proretset => 't',
+ prorettype => 'internal', proargtypes => 'internal jsonpath jsonb bool',
+ prosrc => 'jsonb_path_query_tz_start' },
{ oid => '4007', descr => 'jsonpath query wrapped into array',
proname => 'jsonb_path_query_array', prorettype => 'jsonb',
proargtypes => 'jsonb jsonpath jsonb bool',
@@ -10179,6 +10183,10 @@
proname => 'jsonb_path_query_first_start', prorettype => 'internal',
proargtypes => 'internal jsonpath jsonb bool',
prosrc => 'jsonb_path_query_first_start' },
+{ oid => '4551', descr => 'jsonpath query tz first item as jsonbvalue',
+ proname => 'jsonb_path_query_first_tz_start', prorettype => 'internal',
+ proargtypes => 'internal jsonpath jsonb bool',
+ prosrc => 'jsonb_path_query_first_tz_start'},
{ oid => '4009', descr => 'jsonpath match',
proname => 'jsonb_path_match', prorettype => 'bool',
proargtypes => 'jsonb jsonpath jsonb bool', prosrc => 'jsonb_path_match' },
--
2.21.0
[application/octet-stream] v14-0001-optimize-casting-jsonb-to-a-given-type.patch (36.3K, 4-v14-0001-optimize-casting-jsonb-to-a-given-type.patch)
download | inline diff:
From ff58d54ca52d87a2594793c7a904fa9982c95374 Mon Sep 17 00:00:00 2001
From: Andy Fan <[email protected]>
Date: Thu, 14 Sep 2023 16:58:13 +0800
Subject: [PATCH v14 1/2] 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 | 152 ++++++++++++++++++++++++++
src/backend/utils/adt/jsonbsubs.c | 4 +-
src/backend/utils/adt/jsonfuncs.c | 125 ++++++++++++++-------
src/backend/utils/adt/jsonpath_exec.c | 31 ++++--
src/include/catalog/catversion.h | 3 +-
src/include/catalog/pg_proc.dat | 46 ++++++--
src/include/utils/jsonb.h | 11 +-
src/test/regress/expected/jsonb.out | 112 ++++++++++++++++++-
src/test/regress/sql/jsonb.sql | 66 ++++++++++-
9 files changed, 485 insertions(+), 65 deletions(-)
diff --git a/src/backend/utils/adt/jsonb.c b/src/backend/utils/adt/jsonb.c
index 9781852b0cb..e2efb28a685 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,154 @@ cannotCastJsonbValue(enum jbvType type, const char *sqltype)
elog(ERROR, "unknown jsonb type: %d", (int) type);
}
+
+/*
+ * jsonb_cast_support()
+ *
+ * Planner support function for casting a jsonb extraction to a numeric
+ * or bool data type. Instead of converting a jsonbvalue to jsonb, the new
+ * method will cast the jsonbvalue to the desired data type directly.
+ */
+Datum
+jsonb_cast_support(PG_FUNCTION_ARGS)
+{
+ Node *rawreq = (Node *) PG_GETARG_POINTER(0);
+
+ if (IsA(rawreq, SupportRequestSimplify))
+ {
+ SupportRequestSimplify *req = (SupportRequestSimplify *) rawreq;
+ FuncExpr *fexpr = req->fcall;
+ 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;
+
+ 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(NULL);
+
+
+ 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(NULL);
+
+ 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, -1,
+ 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(NULL);
+ }
+ }
+
+ 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/jsonbsubs.c b/src/backend/utils/adt/jsonbsubs.c
index de0ae3604ff..cb050c65ef7 100644
--- a/src/backend/utils/adt/jsonbsubs.c
+++ b/src/backend/utils/adt/jsonbsubs.c
@@ -251,7 +251,7 @@ jsonb_subscript_fetch(ExprState *state,
workspace->index,
sbsrefstate->numupper,
op->resnull,
- false);
+ JsonbValue_AsJsonb);
}
/*
@@ -343,7 +343,7 @@ jsonb_subscript_fetch_old(ExprState *state,
sbsrefstate->upperindex,
sbsrefstate->numupper,
&sbsrefstate->prevnull,
- false);
+ JsonbValue_AsJsonb);
}
}
diff --git a/src/backend/utils/adt/jsonfuncs.c b/src/backend/utils/adt/jsonfuncs.c
index a4bfa5e4040..eded8e4f32b 100644
--- a/src/backend/utils/adt/jsonfuncs.c
+++ b/src/backend/utils/adt/jsonfuncs.c
@@ -357,7 +357,7 @@ static JsonParseErrorType get_scalar(void *state, char *token, JsonTokenType tok
static Datum get_path_all(FunctionCallInfo fcinfo, bool as_text);
static text *get_worker(text *json, char **tpath, int *ipath, int npath,
bool normalize_results);
-static Datum get_jsonb_path_all(FunctionCallInfo fcinfo, bool as_text);
+static Datum get_jsonb_path_all(FunctionCallInfo fcinfo, JsonbValueTarget target);
static text *JsonbValueAsText(JsonbValue *v);
/* semantic action functions for json_array_length */
@@ -492,6 +492,20 @@ 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);
+Datum
+jsonbvalue_covert(JsonbValue *jbv, JsonbValueTarget target)
+{
+ switch (target)
+ {
+ case JsonbValue_AsJsonbValue:
+ PG_RETURN_POINTER(jbv);
+ case JsonbValue_AsJsonb:
+ PG_RETURN_JSONB_P(JsonbValueToJsonb(jbv));
+ case JsonbValue_AsText:
+ PG_RETURN_TEXT_P(JsonbValueAsText(jbv));
+ }
+ PG_RETURN_POINTER(NULL);
+}
/*
* pg_parse_json_or_errsave
@@ -848,13 +862,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, JsonbValueTarget target)
{
Jsonb *jb = PG_GETARG_JSONB_P(0);
text *key = PG_GETARG_TEXT_PP(1);
JsonbValue *v;
- JsonbValue vbuf;
if (!JB_ROOT_IS_OBJECT(jb))
PG_RETURN_NULL();
@@ -862,12 +875,23 @@ 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)
- PG_RETURN_JSONB_P(JsonbValueToJsonb(v));
+ return jsonbvalue_covert(v, target);
+}
- PG_RETURN_NULL();
+Datum
+jsonb_object_field(PG_FUNCTION_ARGS)
+{
+ return jsonb_object_field_internal(fcinfo, JsonbValue_AsJsonb);
+}
+
+Datum
+jsonb_object_field_start(PG_FUNCTION_ARGS)
+{
+ return jsonb_object_field_internal(fcinfo, JsonbValue_AsJsonbValue);
}
Datum
@@ -923,8 +947,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, JsonbValueTarget target)
{
Jsonb *jb = PG_GETARG_JSONB_P(0);
int element = PG_GETARG_INT32(1);
@@ -945,10 +969,22 @@ jsonb_array_element(PG_FUNCTION_ARGS)
}
v = getIthJsonbValueFromContainer(&jb->root, element);
- if (v != NULL)
- PG_RETURN_JSONB_P(JsonbValueToJsonb(v));
+ if (v == NULL)
+ PG_RETURN_NULL();
- PG_RETURN_NULL();
+ return jsonbvalue_covert(v, target);
+}
+
+Datum
+jsonb_array_element(PG_FUNCTION_ARGS)
+{
+ return jsonb_array_element_internal(fcinfo, JsonbValue_AsJsonb);
+}
+
+Datum
+jsonb_array_element_start(PG_FUNCTION_ARGS)
+{
+ return jsonb_array_element_internal(fcinfo, JsonbValue_AsJsonbValue);
}
Datum
@@ -1476,17 +1512,23 @@ get_scalar(void *state, char *token, JsonTokenType tokentype)
Datum
jsonb_extract_path(PG_FUNCTION_ARGS)
{
- return get_jsonb_path_all(fcinfo, false);
+ return get_jsonb_path_all(fcinfo, JsonbValue_AsJsonb);
}
Datum
jsonb_extract_path_text(PG_FUNCTION_ARGS)
{
- return get_jsonb_path_all(fcinfo, true);
+ return get_jsonb_path_all(fcinfo, JsonbValue_AsText);
+}
+
+Datum
+jsonb_extract_path_start(PG_FUNCTION_ARGS)
+{
+ return get_jsonb_path_all(fcinfo, JsonbValue_AsJsonbValue);
}
static Datum
-get_jsonb_path_all(FunctionCallInfo fcinfo, bool as_text)
+get_jsonb_path_all(FunctionCallInfo fcinfo, JsonbValueTarget target)
{
Jsonb *jb = PG_GETARG_JSONB_P(0);
ArrayType *path = PG_GETARG_ARRAYTYPE_P(1);
@@ -1508,7 +1550,7 @@ get_jsonb_path_all(FunctionCallInfo fcinfo, bool as_text)
deconstruct_array_builtin(path, TEXTOID, &pathtext, &pathnulls, &npath);
- res = jsonb_get_element(jb, pathtext, npath, &isnull, as_text);
+ res = jsonb_get_element(jb, pathtext, npath, &isnull, target);
if (isnull)
PG_RETURN_NULL();
@@ -1517,7 +1559,7 @@ get_jsonb_path_all(FunctionCallInfo fcinfo, bool as_text)
}
Datum
-jsonb_get_element(Jsonb *jb, Datum *path, int npath, bool *isnull, bool as_text)
+jsonb_get_element(Jsonb *jb, Datum *path, int npath, bool *isnull, JsonbValueTarget target)
{
JsonbContainer *container = &jb->root;
JsonbValue *jbvp = NULL;
@@ -1550,16 +1592,26 @@ jsonb_get_element(Jsonb *jb, Datum *path, int npath, bool *isnull, bool as_text)
*/
if (npath <= 0 && jbvp == NULL)
{
- if (as_text)
- {
- return PointerGetDatum(cstring_to_text(JsonbToCString(NULL,
- container,
- VARSIZE(jb))));
- }
- else
+ switch(target)
{
- /* not text mode - just hand back the jsonb */
- PG_RETURN_JSONB_P(jb);
+ case JsonbValue_AsText:
+ return PointerGetDatum(cstring_to_text(JsonbToCString(NULL,
+ container,
+ VARSIZE(jb))));
+ case JsonbValue_AsJsonb:
+ /* just hand back the jsonb */
+ PG_RETURN_JSONB_P(jb);
+ case JsonbValue_AsJsonbValue:
+ {
+ /* just hand back the jsonb */
+ JsonbValue *jbv = NULL;
+ if (JB_ROOT_IS_ARRAY(jb) && JB_ROOT_IS_SCALAR(jb))
+ PG_RETURN_POINTER(getIthJsonbValueFromContainer(container, 0));
+
+ jbv = palloc0(sizeof(JsonbValue));
+ JsonbToJsonbValue(jb, jbv);
+ PG_RETURN_POINTER(jbv);
+ }
}
}
@@ -1645,23 +1697,14 @@ jsonb_get_element(Jsonb *jb, Datum *path, int npath, bool *isnull, bool as_text)
}
}
- if (as_text)
- {
- if (jbvp->type == jbvNull)
- {
- *isnull = true;
- return PointerGetDatum(NULL);
- }
- return PointerGetDatum(JsonbValueAsText(jbvp));
- }
- else
+ if (target == JsonbValue_AsText && jbvp->type == jbvNull)
{
- Jsonb *res = JsonbValueToJsonb(jbvp);
-
- /* not text mode - just hand back the jsonb */
- PG_RETURN_JSONB_P(res);
+ *isnull = true;
+ return PointerGetDatum(NULL);
}
+
+ return jsonbvalue_covert(jbvp, target);
}
Datum
diff --git a/src/backend/utils/adt/jsonpath_exec.c b/src/backend/utils/adt/jsonpath_exec.c
index 2d0599b4aaa..5eef14f93ed 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, JsonbValueTarget target)
{
FuncCallContext *funcctx;
List *found;
@@ -435,19 +435,25 @@ 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)));
+ SRF_RETURN_NEXT(funcctx, jsonbvalue_covert(v, target));
}
Datum
jsonb_path_query(PG_FUNCTION_ARGS)
{
- return jsonb_path_query_internal(fcinfo, false);
+ return jsonb_path_query_internal(fcinfo, false, JsonbValue_AsJsonb);
}
Datum
jsonb_path_query_tz(PG_FUNCTION_ARGS)
{
- return jsonb_path_query_internal(fcinfo, true);
+ return jsonb_path_query_internal(fcinfo, true, JsonbValue_AsJsonb);
+}
+
+Datum
+jsonb_path_query_start(PG_FUNCTION_ARGS)
+{
+ return jsonb_path_query_internal(fcinfo, false, JsonbValue_AsJsonbValue);
}
/*
@@ -487,7 +493,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, JsonbValueTarget target)
{
Jsonb *jb = PG_GETARG_JSONB_P(0);
JsonPath *jp = PG_GETARG_JSONPATH_P(1);
@@ -498,7 +504,10 @@ 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)));
+ {
+ JsonbValue *jbv = JsonValueListHead(&found);
+ return jsonbvalue_covert(jbv, target);
+ }
else
PG_RETURN_NULL();
}
@@ -506,13 +515,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, JsonbValue_AsJsonb);
}
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, JsonbValue_AsJsonb);
+}
+
+Datum
+jsonb_path_query_first_start(PG_FUNCTION_ARGS)
+{
+ return jsonb_path_query_first_internal(fcinfo, false, JsonbValue_AsJsonbValue);
}
/********************Execute functions for JsonPath**************************/
diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h
index ab9a7ac1f79..fb83a7a29df 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 202309151
#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/include/utils/jsonb.h b/src/include/utils/jsonb.h
index 649a1644f24..50b3c65557f 100644
--- a/src/include/utils/jsonb.h
+++ b/src/include/utils/jsonb.h
@@ -294,6 +294,13 @@ struct JsonbValue
} val;
};
+typedef enum JsonbValueTarget
+{
+ JsonbValue_AsJsonbValue,
+ JsonbValue_AsJsonb,
+ JsonbValue_AsText
+} JsonbValueTarget;
+
#define IsAJsonbScalar(jsonbval) (((jsonbval)->type >= jbvNull && \
(jsonbval)->type <= jbvBool) || \
(jsonbval)->type == jbvDatetime)
@@ -428,12 +435,12 @@ extern const char *JsonbTypeName(JsonbValue *val);
extern Datum jsonb_set_element(Jsonb *jb, Datum *path, int path_len,
JsonbValue *newval);
extern Datum jsonb_get_element(Jsonb *jb, Datum *path, int npath,
- bool *isnull, bool as_text);
+ bool *isnull, JsonbValueTarget target);
extern bool to_jsonb_is_immutable(Oid typoid);
extern Datum jsonb_build_object_worker(int nargs, Datum *args, bool *nulls,
Oid *types, bool absent_on_null,
bool unique_keys);
extern Datum jsonb_build_array_worker(int nargs, Datum *args, bool *nulls,
Oid *types, bool absent_on_null);
-
+extern Datum jsonbvalue_covert(JsonbValue *jbv, JsonbValueTarget target);
#endif /* __JSONB_H__ */
diff --git a/src/test/regress/expected/jsonb.out b/src/test/regress/expected/jsonb.out
index 4a16d0dbafb..c6af7a59ccb 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, 'field4'::text), '1700'::oid), (jsonb_finish_numeric(jsonb_object_field_start((test_json)::internal, 'field4'::text), '21'::oid))::smallint, (jsonb_finish_numeric(jsonb_object_field_start((test_json)::internal, 'field4'::text), '23'::oid))::integer, (jsonb_finish_numeric(jsonb_object_field_start((test_json)::internal, 'field4'::text), '20'::oid))::bigint, (jsonb_finish_numeric(jsonb_object_field_start((test_json)::internal, 'field4'::text), '700'::oid))::real, (jsonb_finish_numeric(jsonb_object_field_start((test_json)::internal, 'field4'::text), '701'::oid))::double precision, jsonb_finish_numeric(jsonb_array_element_start(((test_json -> 'field5'::text))::internal, 0), '1700'::oid), jsonb_finish_numeric(jsonb_array_element_start(((test_json -> 'field5'::text))::internal, 10), '1700'::oid), jsonb_finish_numeric(jsonb_extract_path_start((test_json)::internal, VARIADIC '{field6,f1}'::text[]), '1700'::oid), jsonb_finish_numeric(jsonb_extract_path_start((test_json)::internal, VARIADIC '{field6,f2}'::text[]), '1700'::oid), jsonb_finish_bool(jsonb_extract_path_start((test_json)::internal, 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, '$."field8"[*]?(@ >= $"min" && @ <= $"max")'::jsonpath, '{"max": 4, "min": 2}'::jsonb, false)), '21'::oid))::smallint, (jsonb_finish_numeric(jsonb_path_query_first_start((test_json)::internal, '$."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, '$."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, 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, '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: <CAKU4AWrGci1GyJ-WhwwT05+6hEWYuH_jFc8Vc=oNp0OzKCEDDg@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