Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qzrCX-001XZU-Ja for pgsql-hackers@arkaria.postgresql.org; Mon, 06 Nov 2023 04:27:02 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1qzrCV-00CFdf-EF for pgsql-hackers@arkaria.postgresql.org; Mon, 06 Nov 2023 04:26:59 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qzrCU-00CFdX-HM for pgsql-hackers@lists.postgresql.org; Mon, 06 Nov 2023 04:26:59 +0000 Received: from m12.mail.163.com ([220.181.12.216]) by magus.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1qzrCL-004w82-VH for pgsql-hackers@lists.postgresql.org; Mon, 06 Nov 2023 04:26:56 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=163.com; s=s110527; h=From:Subject:Date:Message-ID:MIME-Version: Content-Type; bh=LrRiZ4Skr+L5fJjxLs90gDY7tcEbJ4yChkRfHOFGecA=; b=WSzrqNSfcRYM7k+VsFoqTUrsY7pd2P45d1/1aysVaPnu61s02hct+TDLkCa4FB KYAp8pTM90WhhxjlsqF7o8U9QEuKbwQn25GzjDxpmObMqcR3+W7TZ3g4VCANVVYl gpIDFUUEbapMcJQU6pqyH408W44x7WLnVykaSoiME/MDo= Received: from 66ed4ed24c7c (unknown [140.205.118.92]) by zwqz-smtp-mta-g3-1 (Coremail) with SMTP id _____wCXr97Qakhl+BjGCQ--.48269S3; Mon, 06 Nov 2023 12:25:55 +0800 (CST) References: <169880504467.94392.3769687331705514588.pgcf@coridan.postgresql.org> User-agent: mu4e 1.10.7; emacs 29.1 From: zhihuifan1213@163.com To: Chapman Flack Cc: Andy Fan , pgsql-hackers@lists.postgresql.org Subject: Re: Extract numeric filed in JSONB more effectively Date: Mon, 06 Nov 2023 11:26:28 +0800 In-reply-to: <169880504467.94392.3769687331705514588.pgcf@coridan.postgresql.org> Message-ID: <87a5rry0bz.fsf@163.com> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="=-=-=" X-CM-TRANSID:_____wCXr97Qakhl+BjGCQ--.48269S3 X-Coremail-Antispam: 1Uf129KBjvJXoWxAF4UKFW8uF4UKFy8JrW8Zwb_yoW5CF4rp3 yrK3ZIkw48ta17Cr40vw1UWa4SgFnYka15Xr4fCr1UAa90gF47XrsavryFgFWIvrWrW3yU Xa1vkry0k3Wq9FJanT9S1TB71UUUUUUqnTZGkaVYY2UrUUUUjbIjqfuFe4nvWSU5nxnvy2 9KBjDUYxBIdaVFxhVjvjDU0xZFpf9x07jnfQiUUUUU= X-Originating-IP: [140.205.118.92] X-CM-SenderInfo: x2klx3xlid0iqsrtqiywtou0bp/xtbBZxQgU1euBJMSWAAAsk List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --=-=-= Content-Type: text/plain Chapman Flack writes: (This is Andy Fan and I just switch to my new email address). Hi Chap, Thanks for alway keep an eye on this! > Adding this comment via the CF app so it isn't lost, while an > improperly-interpreted-DKIM-headers issue is still preventing me from > mailing directly to -hackers. > > It was my view that the patch was getting close by the end of the last > commitfest, but still contained a bit of a logic wart made necessary by > a questionable choice of error message wording, such that in my view it > would be better to determine whether a different error message would > better conform to ISO SQL in the first place, and obviate the need for > the logic wart. > > There seemed to be some progress possible on that when petere had time > to weigh in on the standard shortly after the last CF ended. > > So, it would not have been my choice to assign RfC status before > getting to a resolution on that. I agree with this. > > Also, it is possible for a JsonbValue to hold a timestamp (as a result > of a jsonpath evaluation, I don't think that can happen any other > way), I believe this is where our disagreement lies. CREATE TABLE employees ( id serial PRIMARY KEY, data jsonb ); INSERT INTO employees (data) VALUES ( '{ "employees":[ { "firstName":"John", "lastName":"Doe", "hireDate":"2022-01-01T09:00:00Z", "age": 30 }, { "firstName":"Jane", "lastName":"Smith", "hireDate":"2022-02-01T10:00:00Z", "age": 25 } ] }' ); select jsonb_path_query_tz(data, '$.employees[*] ? (@.hireDate >= "2022-02-01T00:00:00Z" && @.hireDate < "2022-03-01T00:00:00Z")') from employees; select jsonb_path_query_tz(data, '$.employees[*].hireDate ? (@ >= "2022-02-01T00:00:00Z" && @ < "2022-03-01T00:00:00Z")') from employees; select pg_typeof(jsonb_path_query_tz(data, '$.employees[*].hireDate ? (@ >= "2022-02-01T00:00:00Z" && @ < "2022-03-01T00:00:00Z")')) from employees; select jsonb_path_query_tz(data, '$.employees[*].hireDate ? (@ >= "2022-02-01T00:00:00Z" && @ < "2022-03-01T00:00:00Z")')::timestamp from employees; select jsonb_path_query_tz(data, '$.employees[*].hireDate ? (@ >= "2022-02-01T00:00:00Z" && @ < "2022-03-01T00:00:00Z")')::timestamptz from employees; I tried all of the above queires and can't find a place where this optimization would apply. am I miss something? > and if such a jsonpath evaluation were to be the source expression of a > cast to SQL timestamp, that situation seems exactly analogous to the > other situations being optimized here and would require only a few more > lines in the exact pattern here introduced. Could you provide an example of this? > While that could be called > out of scope when this patch's title refers to "numeric field" > specifically, it might be worth considering for completeness. The patch > does, after all, handle boolean already, as well as numeric. I'd never arugment for this, at this point at least. v15 is provides without any fundamental changes. Just rebase to the lastest code and prepared a better commit message. --=-=-= Content-Type: text/x-diff Content-Disposition: attachment; filename=v15-0001-Improve-the-performance-of-Jsonb-extraction.patch Content-Transfer-Encoding: quoted-printable From 4d53fda4974fa18827350a5f42482f0eec29d6ba Mon Sep 17 00:00:00 2001 From: "yizhi.fzh" Date: Mon, 6 Nov 2023 11:09:14 +0800 Subject: [PATCH v15 1/1] Improve the performance of Jsonb extraction. In the past, when we needed to extract a numeric value from a field in a JSONB object, even though the JSONB object already contained a binary matching numeric type, we would first find the corresponding JsonbValue, then convert the JsonbValue to Jsonb, and finally use the cast system to convert the Jsonb to a Numeric-like data type. This approach was very inefficient in terms of performance. In the current patch, if we encounter a function or operator that needs to extract a JSONB field and cast it to a numeric-like data type, the request will be automatically converted into extracting the field as a JsonbValue data type from the JSONB field, then, convert the JsonbValue to a numeric data type. If necessary, the final conversion from the numeric data type to another numeric-like data type is done through the casting system. This series of conversions is implemented through the planner support function. By utilizing these methods, the cumbersome JSONB-related operations are avoided. Because the boolean type and numeric type share certain similarities in their attributes, we have implemented the same optimization approach for both. At the implementation level, considering that we have multiple operators and various target data types, and to avoid an excessive number of functions, we have deconstructed the two steps mentioned earlier into two categories of functions. The first category of functions extracts the data as a JsonbValue type, while the second category of functions converts the JsonbValue type into the desired data type. In specific scenarios, we utilize planner support functions to automatically assemble these functions. The optimized functions and operators includes: 1. jsonb_object_field / -> 2. jsonb_array_element / -> 3. jsonb_extract_path / #> 4. jsonb_path_query 5. jsonb_path_query_first --- src/backend/utils/adt/jsonb.c | 166 ++++++++++++++++++++++++++ src/backend/utils/adt/jsonbsubs.c | 4 +- src/backend/utils/adt/jsonfuncs.c | 118 ++++++++++++------ src/backend/utils/adt/jsonpath_exec.c | 32 +++-- src/include/catalog/catversion.h | 2 +- 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 +++++++++- src/tools/pgindent/typedefs.list | 1 + 10 files changed, 498 insertions(+), 60 deletions(-) diff --git a/src/backend/utils/adt/jsonb.c b/src/backend/utils/adt/jsonb.c index 6f445f5c2b..685f4e3e6b 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" @@ -2039,6 +2043,168 @@ cannotCastJsonbValue(enum jbvType type, const char = *sqltype) elog(ERROR, "unknown jsonb type: %d", (int) type); } =20 + +/* + * 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 =3D (Node *) PG_GETARG_POINTER(0); + + if (IsA(rawreq, SupportRequestSimplify)) + { + SupportRequestSimplify *req =3D (SupportRequestSimplify *) rawreq; + FuncExpr *fexpr =3D req->fcall; + FuncExpr *jsonb_start_func =3D NULL, + *jsonb_finish_func =3D NULL, + *final_func =3D NULL; + Node *input; + Oid new_func_id =3D InvalidOid; + List *args; + Oid input_func_id, + collid, + inputcollid; + bool retset =3D false, + variadic =3D false; + + Assert(list_length(fexpr->args) =3D=3D 1); + input =3D (Node *) linitial(fexpr->args); + + if (IsA(input, OpExpr)) + { + OpExpr *opExpr =3D castNode(OpExpr, input); + + input_func_id =3D opExpr->opfuncid; + collid =3D opExpr->opcollid; + inputcollid =3D opExpr->inputcollid; + args =3D opExpr->args; + } + else if (IsA(input, FuncExpr)) + { + FuncExpr *funcExpr =3D castNode(FuncExpr, input); + + input_func_id =3D funcExpr->funcid; + collid =3D funcExpr->funccollid; + inputcollid =3D funcExpr->inputcollid; + args =3D funcExpr->args; + } + else + /* not the desired pattern. */ + PG_RETURN_POINTER(NULL); + + /* build a function to return the JsonbValue directly. */ + switch (input_func_id) + { + case F_JSONB_OBJECT_FIELD: + new_func_id =3D F_JSONB_OBJECT_FIELD_START; + break; + case F_JSONB_ARRAY_ELEMENT: + new_func_id =3D F_JSONB_ARRAY_ELEMENT_START; + break; + case F_JSONB_EXTRACT_PATH: + new_func_id =3D F_JSONB_EXTRACT_PATH_START; + variadic =3D true; + break; + case F_JSONB_PATH_QUERY: + new_func_id =3D F_JSONB_PATH_QUERY_START; + retset =3D true; + break; + case F_JSONB_PATH_QUERY_FIRST: + new_func_id =3D F_JSONB_PATH_QUERY_FIRST_START; + break; + default: + new_func_id =3D InvalidOid; + break; + } + + if (!OidIsValid(new_func_id)) + PG_RETURN_POINTER(NULL); + + jsonb_start_func =3D makeFuncExpr(new_func_id, INTERNALOID, args, + collid, inputcollid, + COERCE_EXPLICIT_CALL); + jsonb_start_func->funcretset =3D retset; + jsonb_start_func->funcvariadic =3D variadic; + + /* relabel the first argument as 'internal'. */ + linitial(jsonb_start_func->args) =3D makeRelabelType(linitial(jsonb_star= t_func->args), + INTERNALOID, -1, + InvalidOid, + COERCE_IMPLICIT_CAST); + switch (fexpr->funcresulttype) + { + case INT2OID: + case INT4OID: + case INT8OID: + case FLOAT4OID: + case FLOAT8OID: + case NUMERICOID: + /* build the function to turn the JsonbValue into numeric */ + jsonb_finish_func =3D 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 !=3D NUMERICOID) + { + /* + * leverage the casting system to turn the numeric to + * desired type. + */ + final_func =3D (FuncExpr *) coerce_type(NULL, (Node *) jsonb_finish_f= unc, NUMERICOID, + fexpr->funcresulttype, 0, COERCION_EXPLICIT, + COERCE_EXPLICIT_CAST, fexpr->location); + } + else + final_func =3D jsonb_finish_func; + + PG_RETURN_POINTER(final_func); + case BOOLOID: + final_func =3D 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 =3D (JsonbValue *) PG_GETARG_POINTER(0); + Oid final_oid =3D PG_GETARG_OID(1); + + if (v->type !=3D jbvNumeric) + cannotCastJsonbValue(v->type, format_type_be(final_oid)); + PG_RETURN_NUMERIC(v->val.numeric); +} + +Datum +jsonb_finish_bool(PG_FUNCTION_ARGS) +{ + JsonbValue *v =3D (JsonbValue *) PG_GETARG_POINTER(0); + + if (v->type !=3D 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/json= bsubs.c index de0ae3604f..cb050c65ef 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); } =20 /* @@ -343,7 +343,7 @@ jsonb_subscript_fetch_old(ExprState *state, sbsrefstate->upperindex, sbsrefstate->numupper, &sbsrefstate->prevnull, - false); + JsonbValue_AsJsonb); } } =20 diff --git a/src/backend/utils/adt/jsonfuncs.c b/src/backend/utils/adt/json= funcs.c index aa37c401e5..4e73c0f5c7 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); =20 /* semantic action functions for json_array_length */ @@ -492,6 +492,20 @@ static JsonParseErrorType transform_string_values_obje= ct_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); =20 +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); +} =20 /* * pg_parse_json_or_errsave @@ -847,13 +861,12 @@ json_object_field(PG_FUNCTION_ARGS) PG_RETURN_NULL(); } =20 -Datum -jsonb_object_field(PG_FUNCTION_ARGS) +static Datum +jsonb_object_field_internal(FunctionCallInfo fcinfo, JsonbValueTarget targ= et) { Jsonb *jb =3D PG_GETARG_JSONB_P(0); text *key =3D PG_GETARG_TEXT_PP(1); JsonbValue *v; - JsonbValue vbuf; =20 if (!JB_ROOT_IS_OBJECT(jb)) PG_RETURN_NULL(); @@ -861,14 +874,26 @@ jsonb_object_field(PG_FUNCTION_ARGS) v =3D getKeyJsonValueFromContainer(&jb->root, VARDATA_ANY(key), VARSIZE_ANY_EXHDR(key), - &vbuf); + NULL); =20 if (v !=3D NULL) - PG_RETURN_JSONB_P(JsonbValueToJsonb(v)); + return jsonbvalue_covert(v, target); =20 PG_RETURN_NULL(); } =20 +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 json_object_field_text(PG_FUNCTION_ARGS) { @@ -922,8 +947,8 @@ json_array_element(PG_FUNCTION_ARGS) PG_RETURN_NULL(); } =20 -Datum -jsonb_array_element(PG_FUNCTION_ARGS) +static Datum +jsonb_array_element_internal(FunctionCallInfo fcinfo, JsonbValueTarget tar= get) { Jsonb *jb =3D PG_GETARG_JSONB_P(0); int element =3D PG_GETARG_INT32(1); @@ -945,11 +970,23 @@ jsonb_array_element(PG_FUNCTION_ARGS) =20 v =3D getIthJsonbValueFromContainer(&jb->root, element); if (v !=3D NULL) - PG_RETURN_JSONB_P(JsonbValueToJsonb(v)); + return jsonbvalue_covert(v, target); =20 PG_RETURN_NULL(); } =20 +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 json_array_element_text(PG_FUNCTION_ARGS) { @@ -1476,17 +1513,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); } =20 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); } =20 static Datum -get_jsonb_path_all(FunctionCallInfo fcinfo, bool as_text) +get_jsonb_path_all(FunctionCallInfo fcinfo, JsonbValueTarget target) { Jsonb *jb =3D PG_GETARG_JSONB_P(0); ArrayType *path =3D PG_GETARG_ARRAYTYPE_P(1); @@ -1508,7 +1551,7 @@ get_jsonb_path_all(FunctionCallInfo fcinfo, bool as_t= ext) =20 deconstruct_array_builtin(path, TEXTOID, &pathtext, &pathnulls, &npath); =20 - res =3D jsonb_get_element(jb, pathtext, npath, &isnull, as_text); + res =3D jsonb_get_element(jb, pathtext, npath, &isnull, target); =20 if (isnull) PG_RETURN_NULL(); @@ -1517,7 +1560,7 @@ get_jsonb_path_all(FunctionCallInfo fcinfo, bool as_t= ext) } =20 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, JsonbVa= lueTarget target) { JsonbContainer *container =3D &jb->root; JsonbValue *jbvp =3D NULL; @@ -1550,16 +1593,26 @@ jsonb_get_element(Jsonb *jb, Datum *path, int npath= , bool *isnull, bool as_text) */ if (npath <=3D 0 && jbvp =3D=3D 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)))); + /* not text mode - just hand back the jsonb */ + case JsonbValue_AsJsonb: + PG_RETURN_JSONB_P(jb); + case JsonbValue_AsJsonbValue: + { + JsonbValue *jbv =3D NULL; + + if (JB_ROOT_IS_ARRAY(jb) && JB_ROOT_IS_SCALAR(jb)) + PG_RETURN_POINTER(getIthJsonbValueFromContainer(container, 0)); + + jbv =3D palloc0(sizeof(JsonbValue)); + JsonbToJsonbValue(jb, jbv); + PG_RETURN_POINTER(jbv); + } } } =20 @@ -1645,23 +1698,14 @@ jsonb_get_element(Jsonb *jb, Datum *path, int npath= , bool *isnull, bool as_text) } } =20 - if (as_text) - { - if (jbvp->type =3D=3D jbvNull) - { - *isnull =3D true; - return PointerGetDatum(NULL); - } =20 - return PointerGetDatum(JsonbValueAsText(jbvp)); - } - else + if (target =3D=3D JsonbValue_AsText && jbvp->type =3D=3D jbvNull) { - Jsonb *res =3D JsonbValueToJsonb(jbvp); - - /* not text mode - just hand back the jsonb */ - PG_RETURN_JSONB_P(res); + *isnull =3D true; + return PointerGetDatum(NULL); } + + return jsonbvalue_covert(jbvp, target); } =20 Datum diff --git a/src/backend/utils/adt/jsonpath_exec.c b/src/backend/utils/adt/= jsonpath_exec.c index 2d0599b4aa..03045c7ba9 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, JsonbValueTarg= et target) { FuncCallContext *funcctx; List *found; @@ -435,19 +435,25 @@ jsonb_path_query_internal(FunctionCallInfo fcinfo, bo= ol tz) v =3D lfirst(c); funcctx->user_fctx =3D list_delete_first(found); =20 - SRF_RETURN_NEXT(funcctx, JsonbPGetDatum(JsonbValueToJsonb(v))); + SRF_RETURN_NEXT(funcctx, jsonbvalue_covert(v, target)); } =20 Datum jsonb_path_query(PG_FUNCTION_ARGS) { - return jsonb_path_query_internal(fcinfo, false); + return jsonb_path_query_internal(fcinfo, false, JsonbValue_AsJsonb); } =20 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); } =20 /* @@ -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, JsonbVal= ueTarget target) { Jsonb *jb =3D PG_GETARG_JSONB_P(0); JsonPath *jp =3D PG_GETARG_JSONPATH_P(1); @@ -498,7 +504,11 @@ jsonb_path_query_first_internal(FunctionCallInfo fcinf= o, bool tz) (void) executeJsonPath(jp, vars, jb, !silent, &found, tz); =20 if (JsonValueListLength(&found) >=3D 1) - PG_RETURN_JSONB_P(JsonbValueToJsonb(JsonValueListHead(&found))); + { + JsonbValue *jbv =3D JsonValueListHead(&found); + + return jsonbvalue_covert(jbv, target); + } else PG_RETURN_NULL(); } @@ -506,13 +516,19 @@ jsonb_path_query_first_internal(FunctionCallInfo fcin= fo, 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); } =20 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_AsJsonbV= alue); } =20 /********************Execute functions for JsonPath***********************= ***/ diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catvers= ion.h index bee21befda..802c6f1925 100644 --- a/src/include/catalog/catversion.h +++ b/src/include/catalog/catversion.h @@ -57,6 +57,6 @@ */ =20 /* yyyymmddN */ -#define CATALOG_VERSION_NO 202310301 +#define CATALOG_VERSION_NO 202311061 =20 #endif diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.= dat index 091f7e343c..6934d0894d 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -4590,25 +4590,25 @@ proname =3D> 'pg_lsn', prorettype =3D> 'pg_lsn', proargtypes =3D> 'numer= ic', prosrc =3D> 'numeric_pg_lsn' }, =20 -{ oid =3D> '3556', descr =3D> 'convert jsonb to boolean', +{ oid =3D> '3556', descr =3D> 'convert jsonb to boolean', prosupport =3D> = 'jsonb_cast_support', proname =3D> 'bool', prorettype =3D> 'bool', proargtypes =3D> 'jsonb', prosrc =3D> 'jsonb_bool' }, { oid =3D> '3449', descr =3D> 'convert jsonb to numeric', - proname =3D> 'numeric', prorettype =3D> 'numeric', proargtypes =3D> 'jso= nb', + proname =3D> 'numeric', prorettype =3D> 'numeric', proargtypes =3D> 'jso= nb', prosupport =3D> 'jsonb_cast_support', prosrc =3D> 'jsonb_numeric' }, -{ oid =3D> '3450', descr =3D> 'convert jsonb to int2', +{ oid =3D> '3450', descr =3D> 'convert jsonb to int2', prosupport =3D> 'js= onb_cast_support', proname =3D> 'int2', prorettype =3D> 'int2', proargtypes =3D> 'jsonb', prosrc =3D> 'jsonb_int2' }, -{ oid =3D> '3451', descr =3D> 'convert jsonb to int4', +{ oid =3D> '3451', descr =3D> 'convert jsonb to int4', prosupport =3D> 'js= onb_cast_support', proname =3D> 'int4', prorettype =3D> 'int4', proargtypes =3D> 'jsonb', prosrc =3D> 'jsonb_int4' }, -{ oid =3D> '3452', descr =3D> 'convert jsonb to int8', +{ oid =3D> '3452', descr =3D> 'convert jsonb to int8', prosupport =3D> 'js= onb_cast_support', proname =3D> 'int8', prorettype =3D> 'int8', proargtypes =3D> 'jsonb', prosrc =3D> 'jsonb_int8' }, -{ oid =3D> '3453', descr =3D> 'convert jsonb to float4', +{ oid =3D> '3453', descr =3D> 'convert jsonb to float4', prosupport =3D> '= jsonb_cast_support', proname =3D> 'float4', prorettype =3D> 'float4', proargtypes =3D> 'jsonb= ', prosrc =3D> 'jsonb_float4' }, -{ oid =3D> '2580', descr =3D> 'convert jsonb to float8', +{ oid =3D> '2580', descr =3D> 'convert jsonb to float8', prosupport =3D> '= jsonb_cast_support', proname =3D> 'float8', prorettype =3D> 'float8', proargtypes =3D> 'jsonb= ', prosrc =3D> 'jsonb_float8' }, =20 @@ -9951,6 +9951,30 @@ proname =3D> 'jsonb_object_field_text', prorettype =3D> 'text', proargtypes =3D> 'jsonb text', proargnames =3D> '{from_json, field_name}= ', prosrc =3D> 'jsonb_object_field_text' }, +{ oid =3D> '4552', descr =3D> 'extract jsonbvalue from jsonb for the given= field', + proname =3D> 'jsonb_object_field_start', prorettype =3D> 'internal', + proargtypes =3D> 'internal text', proargnames =3D> '{from_json, field_na= me}', + prosrc =3D> 'jsonb_object_field_start' }, +{ oid =3D> '3813', descr =3D> 'extract josnbvalue from jsonb array for the= given index', + proname =3D> 'jsonb_array_element_start', prorettype =3D> 'internal', + proargtypes =3D> 'internal int4', proargnames =3D> '{from_jsonb, element= _index}', + prosrc =3D> 'jsonb_array_element_start' }, +{ oid =3D> '4551', descr =3D> 'extract jsonbvalue from jsonb for the given= paths', + proname =3D> 'jsonb_extract_path_start', provariadic =3D> 'text', proret= type =3D> 'internal', + proargtypes =3D> 'internal _text', proallargtypes =3D> '{internal,_text}= ', + proargmodes =3D> '{i,v}', proargnames =3D> '{from_jsonb,path_elems}', + prosrc =3D> 'jsonb_extract_path_start'}, +{ oid =3D> '4553', descr =3D> 'convert a jsonbvalue to numeric', + proname =3D> 'jsonb_finish_numeric', prorettype =3D> 'numeric', + proargtypes =3D> 'internal oid', proargnames =3D> '{from_jsonvalue,targe= t_oid}', + prosrc =3D> 'jsonb_finish_numeric' }, +{ oid =3D> '4554', descr =3D> 'convert a jsonbvalue to boolean', + proname =3D> 'jsonb_finish_bool', prorettype =3D> 'bool', + proargtypes =3D> 'internal', proargnames =3D> '{jsonvalue}', + prosrc =3D> 'jsonb_finish_bool' }, +{ oid =3D> '3814', descr =3D> 'planner support for numeric(jsonb)', + proname =3D> 'jsonb_cast_support', prorettype =3D> 'internal', + proargtypes =3D> 'internal', prosrc =3D> 'jsonb_cast_support' }, { oid =3D> '3215', proname =3D> 'jsonb_array_element', prorettype =3D> 'jsonb', proargtypes =3D> 'jsonb int4', proargnames =3D> '{from_json, element_ind= ex}', @@ -10143,6 +10167,10 @@ proname =3D> 'jsonb_path_query', prorows =3D> '1000', proretset =3D> 't', prorettype =3D> 'jsonb', proargtypes =3D> 'jsonb jsonpath jsonb bool', prosrc =3D> 'jsonb_path_query' }, +{ oid =3D> '4557', descr =3D> 'jsonpath query as jsonbvalue', + proname =3D> 'jsonb_path_query_start', prorows =3D> '1000', proretset = =3D> 't', + prorettype =3D> 'internal', proargtypes =3D> 'internal jsonpath jsonb bo= ol', + prosrc =3D> 'jsonb_path_query_start' }, { oid =3D> '4007', descr =3D> 'jsonpath query wrapped into array', proname =3D> 'jsonb_path_query_array', prorettype =3D> 'jsonb', proargtypes =3D> 'jsonb jsonpath jsonb bool', @@ -10151,6 +10179,10 @@ proname =3D> 'jsonb_path_query_first', prorettype =3D> 'jsonb', proargtypes =3D> 'jsonb jsonpath jsonb bool', prosrc =3D> 'jsonb_path_query_first' }, +{ oid =3D> '4555', descr =3D> 'jsonpath query first item as jsonbvalue', + proname =3D> 'jsonb_path_query_first_start', prorettype =3D> 'internal', + proargtypes =3D> 'internal jsonpath jsonb bool', + prosrc =3D> 'jsonb_path_query_first_start' }, { oid =3D> '4009', descr =3D> 'jsonpath match', proname =3D> 'jsonb_path_match', prorettype =3D> 'bool', proargtypes =3D> 'jsonb jsonpath jsonb bool', prosrc =3D> 'jsonb_path_ma= tch' }, diff --git a/src/include/utils/jsonb.h b/src/include/utils/jsonb.h index addc9b608e..5e0014f040 100644 --- a/src/include/utils/jsonb.h +++ b/src/include/utils/jsonb.h @@ -294,6 +294,13 @@ struct JsonbValue } val; }; =20 +typedef enum JsonbValueTarget +{ + JsonbValue_AsJsonbValue, + JsonbValue_AsJsonb, + JsonbValue_AsText +} JsonbValueTarget; + #define IsAJsonbScalar(jsonbval) (((jsonbval)->type >=3D jbvNull && \ (jsonbval)->type <=3D jbvBool) || \ (jsonbval)->type =3D=3D 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, const Datum *args, const= bool *nulls, const Oid *types, bool absent_on_null, bool unique_keys); extern Datum jsonb_build_array_worker(int nargs, const Datum *args, const = bool *nulls, const 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/expecte= d/jsonb.out index 4a16d0dbaf..c6af7a59cc 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, "f= ield5": [1,2,3], "field6": {"f1":9}}'); +('object','{"field1":"val1","field2":"val2","field3":null, "field4": 4, "f= ield5": [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 =3D 'object'; + = = = = = = = = QUERY PLAN=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20 +--------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------- + Seq Scan on pg_temp.test_jsonb + Output: jsonb_finish_numeric(jsonb_object_field_start((test_json)::inte= rnal, 'field4'::text), '1700'::oid), (jsonb_finish_numeric(jsonb_object_fie= ld_start((test_json)::internal, 'field4'::text), '21'::oid))::smallint, (js= onb_finish_numeric(jsonb_object_field_start((test_json)::internal, 'field4'= ::text), '23'::oid))::integer, (jsonb_finish_numeric(jsonb_object_field_sta= rt((test_json)::internal, 'field4'::text), '20'::oid))::bigint, (jsonb_fini= sh_numeric(jsonb_object_field_start((test_json)::internal, 'field4'::text),= '700'::oid))::real, (jsonb_finish_numeric(jsonb_object_field_start((test_j= son)::internal, 'field4'::text), '701'::oid))::double precision, jsonb_fini= sh_numeric(jsonb_array_element_start(((test_json -> 'field5'::text))::inter= nal, 0), '1700'::oid), jsonb_finish_numeric(jsonb_array_element_start(((tes= t_json -> 'field5'::text))::internal, 10), '1700'::oid), jsonb_finish_numer= ic(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_finis= h_bool(jsonb_extract_path_start((test_json)::internal, VARIADIC '{field7}':= :text[])) + Filter: (test_jsonb.json_type =3D '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 =3D 'object'; + numeric | int2 | int4 | int8 | float4 | float8 | numeric | numeric | nume= ric | numeric | bool=20 +---------+------+------+------+--------+--------+---------+---------+-----= ----+---------+------ + 4 | 4 | 4 | 4 | 4 | 4 | 1 | NULL | = 9 | NULL | t +(1 row) + +EXPLAIN (COSTS OFF, VERBOSE) +SELECT +jsonb_path_query(test_json,'$.field8[*] ? (@ >=3D $min && @ <=3D $max)', '= {"min":2, "max":4}'), +jsonb_path_query_first(test_json, '$.field8[*] ? (@ >=3D $min && @ <=3D $m= ax)', '{"min":2, "max":4}'), +jsonb_path_query(test_json,'$.field8[*] ? (@ >=3D $min && @ <=3D $max)', '= {"min":2, "max":4}')::int2, +jsonb_path_query_first(test_json, '$.field8[*] ? (@ >=3D $min && @ <=3D $m= ax)', '{"min":2, "max":4}')::int2 +FROM test_jsonb +WHERE json_type =3D 'object'; + = = = = QUERY PLAN=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20 +--------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= --------------------------------------------- + Result + Output: (jsonb_path_query(test_json, '$."field8"[*]?(@ >=3D $"min" && @= <=3D $"max")'::jsonpath, '{"max": 4, "min": 2}'::jsonb, false)), jsonb_pat= h_query_first(test_json, '$."field8"[*]?(@ >=3D $"min" && @ <=3D $"max")'::= jsonpath, '{"max": 4, "min": 2}'::jsonb, false), (jsonb_finish_numeric((jso= nb_path_query_start((test_json)::internal, '$."field8"[*]?(@ >=3D $"min" &&= @ <=3D $"max")'::jsonpath, '{"max": 4, "min": 2}'::jsonb, false)), '21'::o= id))::smallint, (jsonb_finish_numeric(jsonb_path_query_first_start((test_js= on)::internal, '$."field8"[*]?(@ >=3D $"min" && @ <=3D $"max")'::jsonpath, = '{"max": 4, "min": 2}'::jsonb, false), '21'::oid))::smallint + -> ProjectSet + Output: jsonb_path_query(test_json, '$."field8"[*]?(@ >=3D $"min"= && @ <=3D $"max")'::jsonpath, '{"max": 4, "min": 2}'::jsonb, false), jsonb= _path_query_start((test_json)::internal, '$."field8"[*]?(@ >=3D $"min" && @= <=3D $"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 =3D 'object'::text) +(7 rows) + +SELECT +jsonb_path_query(test_json,'$.field8[*] ? (@ >=3D $min && @ <=3D $max)', '= {"min":2, "max":4}'), +jsonb_path_query_first(test_json, '$.field8[*] ? (@ >=3D $min && @ <=3D $m= ax)', '{"min":2, "max":4}'), +jsonb_path_query(test_json,'$.field8[*] ? (@ >=3D $min && @ <=3D $max)', '= {"min":2, "max":4}')::int2, +jsonb_path_query_first(test_json, '$.field8[*] ? (@ >=3D $min && @ <=3D $m= ax)', '{"min":2, "max":4}')::int2 +FROM test_jsonb +WHERE json_type =3D 'object'; + jsonb_path_query | jsonb_path_query_first | jsonb_path_query | jsonb_path= _query_first=20 +------------------+------------------------+------------------+-----------= ------------- + 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 =3D 's= calarint'; + QUERY PLAN=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20 +--------------------------------------------------------------------------= ------------------------------------------- + Seq Scan on pg_temp.test_jsonb + Output: jsonb_finish_numeric(jsonb_extract_path_start((test_json)::inte= rnal, VARIADIC '{}'::text[]), '1700'::oid) + Filter: (test_jsonb.json_type =3D 'scalarint'::text) +(3 rows) + +SELECT (test_json #> '{}')::numeric FROM test_jsonb WHERE json_type =3D 's= calarint'; + numeric=20 +--------- + 2 +(1 row) + +-- let raise some errors. +EXPLAIN (COSTS OFF, VERBOSE) +SELECT (test_json -> 'field1')::int4 FROM test_jsonb WHERE json_type =3D '= object'; + QUERY PLAN=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20 +--------------------------------------------------------------------------= --------------------------------------------- + Seq Scan on pg_temp.test_jsonb + Output: (jsonb_finish_numeric(jsonb_object_field_start((test_json)::int= ernal, 'field1'::text), '23'::oid))::integer + Filter: (test_jsonb.json_type =3D 'object'::text) +(3 rows) + +SELECT (test_json -> 'field1')::int4 FROM test_jsonb WHERE json_type =3D '= object'; +ERROR: cannot cast jsonb string to type integer +SELECT (test_json -> 'field1')::bool FROM test_jsonb WHERE json_type =3D '= object'; +ERROR: cannot cast jsonb string to type boolean +\pset null '' SELECT test_json -> 'x' FROM test_jsonb WHERE json_type =3D 'scalar'; ?column?=20 ---------- @@ -586,7 +692,9 @@ SELECT jsonb_object_keys(test_json) FROM test_jsonb WHE= RE json_type =3D 'object'; field4 field5 field6 -(6 rows) + field7 + field8 +(8 rows) =20 -- nulls SELECT (test_json->'field3') IS NULL AS expect_false FROM test_jsonb WHERE= json_type =3D 'object'; diff --git a/src/test/regress/sql/jsonb.sql b/src/test/regress/sql/jsonb.sql index e4b7cdf703..500d04936d 100644 --- a/src/test/regress/sql/jsonb.sql +++ b/src/test/regress/sql/jsonb.sql @@ -154,8 +154,72 @@ CREATE TEMP TABLE test_jsonb ( =20 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, "f= ield5": [1,2,3], "field6": {"f1":9}}'); +('object','{"field1":"val1","field2":"val2","field3":null, "field4": 4, "f= ield5": [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 =3D '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 =3D 'object'; + +EXPLAIN (COSTS OFF, VERBOSE) +SELECT +jsonb_path_query(test_json,'$.field8[*] ? (@ >=3D $min && @ <=3D $max)', '= {"min":2, "max":4}'), +jsonb_path_query_first(test_json, '$.field8[*] ? (@ >=3D $min && @ <=3D $m= ax)', '{"min":2, "max":4}'), +jsonb_path_query(test_json,'$.field8[*] ? (@ >=3D $min && @ <=3D $max)', '= {"min":2, "max":4}')::int2, +jsonb_path_query_first(test_json, '$.field8[*] ? (@ >=3D $min && @ <=3D $m= ax)', '{"min":2, "max":4}')::int2 +FROM test_jsonb +WHERE json_type =3D 'object'; + +SELECT +jsonb_path_query(test_json,'$.field8[*] ? (@ >=3D $min && @ <=3D $max)', '= {"min":2, "max":4}'), +jsonb_path_query_first(test_json, '$.field8[*] ? (@ >=3D $min && @ <=3D $m= ax)', '{"min":2, "max":4}'), +jsonb_path_query(test_json,'$.field8[*] ? (@ >=3D $min && @ <=3D $max)', '= {"min":2, "max":4}')::int2, +jsonb_path_query_first(test_json, '$.field8[*] ? (@ >=3D $min && @ <=3D $m= ax)', '{"min":2, "max":4}')::int2 +FROM test_jsonb +WHERE json_type =3D 'object'; + + +EXPLAIN (COSTS OFF, VERBOSE) +SELECT (test_json #> '{}')::numeric FROM test_jsonb WHERE json_type =3D 's= calarint'; +SELECT (test_json #> '{}')::numeric FROM test_jsonb WHERE json_type =3D 's= calarint'; + +-- let raise some errors. +EXPLAIN (COSTS OFF, VERBOSE) +SELECT (test_json -> 'field1')::int4 FROM test_jsonb WHERE json_type =3D '= object'; +SELECT (test_json -> 'field1')::int4 FROM test_jsonb WHERE json_type =3D '= object'; + +SELECT (test_json -> 'field1')::bool FROM test_jsonb WHERE json_type =3D '= object'; + +\pset null '' =20 SELECT test_json -> 'x' FROM test_jsonb WHERE json_type =3D 'scalar'; SELECT test_json -> 'x' FROM test_jsonb WHERE json_type =3D 'array'; diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs= .list index 87c1aee379..b0f66589e8 100644 --- a/src/tools/pgindent/typedefs.list +++ b/src/tools/pgindent/typedefs.list @@ -1256,6 +1256,7 @@ JsonArrayAgg JsonArrayConstructor JsonArrayQueryConstructor JsonBaseObjectInfo +JsonbValueTarget JsonConstructorExpr JsonConstructorExprState JsonConstructorType --=20 2.34.1 --=-=-= Content-Type: text/plain -- Best Regards Andy Fan --=-=-=--