On Sun, Jan 7, 2024 at 3:26 PM Andy Fan <zhihuifan1...@163.com> wrote: > > > Hi, > > > hi. > > you don't need to change src/include/catalog/catversion.h > > as mentioned in https://wiki.postgresql.org/wiki/Committing_checklist > > Otherwise, cfbot will fail many times. > > Thanks for the wiki. > > I checked the wiki and search "catversion", the only message I got is: > > "Consider the need for a catversion bump." > > How could this be explained as "no need to change ../catversion.h"?
that means catversion.h changes is the committer's responsibility, IMHO. IMHO, main reason is every time the catversion.h change, cfbot http://cfbot.cputube.org will fail. one patch took very long time to be committable. you don't need update your patch for the every catversion.h changes. > > > > +typedef enum JsonbValueTarget > > +{ > > + JsonbValue_AsJsonbValue, > > + JsonbValue_AsJsonb, > > + JsonbValue_AsText > > +} JsonbValueTarget; > > > > change to > > > > +typedef enum JsonbValueTarget > > +{ > > + JsonbValue_AsJsonbValue, > > + JsonbValue_AsJsonb, > > + JsonbValue_AsText, > > +} JsonbValueTarget; > > reason: https://git.postgresql.org/cgit/postgresql.git/commit/?id=611806cd726fc92989ac918eac48fd8d684869c7 > > currently cannot do `git apply`. > > OK, I guess it's something about whitespaces, my git-commit hook has > been configured to capture this during commit. After we reach an > agreement about the 'catversion.h' stuff, the next version of patch > should fix this issue. Anyway, I made the following change: remove catversion.h changes. refactored the tests. Some of the explain(costs off, verbose) output is very very long. it's unreadable on the web browser. so I cut them into small pieces. resolve duplicate OID issues. slight refactored jsonbvalue_covert function, for the switch statement, add a default branch. see file v16-0001-Improve-the-performance-of-Jsonb-extraction.patch you made a lot of changes, that might not be easy to get committed, i think. Maybe we can split the patch into several pieces. The first part is the original idea that: pattern: (jsonb(object) -> 'key')::numerica_data_type can be optimized. The second part: is other cases where cast jsonb to scalar data type can also be optimized. So, I refactor your patch. only have optimized casts for: (jsonb(object) -> 'key')::numerica_data_type. We can optimize more cast cases, but IMHO, make it as minimal as possible, easier to review, easier to understand. If people think this performance gain is good, then later we can add more on top of it. summary: 2 files attached. v16-0001-Improve-the-performance-of-Jsonb-extraction.patch refactored of your patch, that covers all the cast optimization cases, this file will run the CI test. v1-0001-Improve-performance-of-Jsonb-extract-via-key-and-c.no-cfbot this one also based on your patch. but as a minimum patch to optimize (jsonb(object) -> 'key')::numerica_data_type case only. (this one will not run CI test).
From 5ac139d3eb213beaa53e6cd39c25b2ecb334ba56 Mon Sep 17 00:00:00 2001
From: jian he <jian.universal...@gmail.com>
Date: Sun, 7 Jan 2024 14:14:11 +0800
Subject: [PATCH v16 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.
---
src/backend/utils/adt/jsonb.c | 166 ++++++++++++++++++
src/backend/utils/adt/jsonbsubs.c | 4 +-
src/backend/utils/adt/jsonfuncs.c | 180 ++++++++++++-------
src/backend/utils/adt/jsonpath_exec.c | 32 +++-
src/include/catalog/pg_proc.dat | 46 ++++-
src/include/utils/jsonb.h | 11 +-
src/test/regress/expected/jsonb.out | 240 +++++++++++++++++++++++++-
src/test/regress/sql/jsonb.sql | 116 ++++++++++++-
src/tools/pgindent/typedefs.list | 1 +
9 files changed, 707 insertions(+), 89 deletions(-)
diff --git a/src/backend/utils/adt/jsonb.c b/src/backend/utils/adt/jsonb.c
index c10b3fbe..b5a09b61 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);
}
+
+/*
+ * 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);
+
+ /* build a function to return the JsonbValue directly. */
+ 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 argument 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:
+ /* build the function to turn the JsonbValue into numeric */
+ 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)
+ {
+ /*
+ * leverage the casting system to turn the numeric to
+ * desired type.
+ */
+ 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 79c5d16f..37be80d1 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 caaafb72..baeefabc 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,22 @@ 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));
+ default:
+ elog(ERROR, "invalid jsonbvalue cast target type");
+ }
+ pg_unreachable();
+}
/*
* pg_parse_json_or_errsave
@@ -847,26 +863,37 @@ json_object_field(PG_FUNCTION_ARGS)
PG_RETURN_NULL();
}
+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;
+
+ if (!JB_ROOT_IS_OBJECT(jb))
+ PG_RETURN_NULL();
+
+ v = getKeyJsonValueFromContainer(&jb->root,
+ VARDATA_ANY(key),
+ VARSIZE_ANY_EXHDR(key),
+ NULL);
+
+ if (v != NULL)
+ return jsonbvalue_covert(v, target);
+
+ PG_RETURN_NULL();
+}
+
Datum
jsonb_object_field(PG_FUNCTION_ARGS)
{
- Jsonb *jb = PG_GETARG_JSONB_P(0);
- text *key = PG_GETARG_TEXT_PP(1);
- JsonbValue *v;
- JsonbValue vbuf;
+ return jsonb_object_field_internal(fcinfo, JsonbValue_AsJsonb);
+}
- if (!JB_ROOT_IS_OBJECT(jb))
- PG_RETURN_NULL();
-
- v = getKeyJsonValueFromContainer(&jb->root,
- VARDATA_ANY(key),
- VARSIZE_ANY_EXHDR(key),
- &vbuf);
-
- if (v != NULL)
- PG_RETURN_JSONB_P(JsonbValueToJsonb(v));
-
- PG_RETURN_NULL();
+Datum
+jsonb_object_field_start(PG_FUNCTION_ARGS)
+{
+ return jsonb_object_field_internal(fcinfo, JsonbValue_AsJsonbValue);
}
Datum
@@ -922,32 +949,44 @@ json_array_element(PG_FUNCTION_ARGS)
PG_RETURN_NULL();
}
+static Datum
+jsonb_array_element_internal(FunctionCallInfo fcinfo, JsonbValueTarget target)
+{
+ Jsonb *jb = PG_GETARG_JSONB_P(0);
+ int element = PG_GETARG_INT32(1);
+ JsonbValue *v;
+
+ if (!JB_ROOT_IS_ARRAY(jb))
+ PG_RETURN_NULL();
+
+ /* Handle negative subscript */
+ if (element < 0)
+ {
+ uint32 nelements = JB_ROOT_COUNT(jb);
+
+ if (-element > nelements)
+ PG_RETURN_NULL();
+ else
+ element += nelements;
+ }
+
+ v = getIthJsonbValueFromContainer(&jb->root, element);
+ if (v != NULL)
+ return jsonbvalue_covert(v, target);
+
+ PG_RETURN_NULL();
+}
+
Datum
jsonb_array_element(PG_FUNCTION_ARGS)
{
- Jsonb *jb = PG_GETARG_JSONB_P(0);
- int element = PG_GETARG_INT32(1);
- JsonbValue *v;
+ return jsonb_array_element_internal(fcinfo, JsonbValue_AsJsonb);
+}
- if (!JB_ROOT_IS_ARRAY(jb))
- PG_RETURN_NULL();
-
- /* Handle negative subscript */
- if (element < 0)
- {
- uint32 nelements = JB_ROOT_COUNT(jb);
-
- if (-element > nelements)
- PG_RETURN_NULL();
- else
- element += nelements;
- }
-
- v = getIthJsonbValueFromContainer(&jb->root, element);
- if (v != NULL)
- PG_RETURN_JSONB_P(JsonbValueToJsonb(v));
-
- PG_RETURN_NULL();
+Datum
+jsonb_array_element_start(PG_FUNCTION_ARGS)
+{
+ return jsonb_array_element_internal(fcinfo, JsonbValue_AsJsonbValue);
}
Datum
@@ -1476,17 +1515,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 +1553,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 +1562,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 +1595,26 @@ jsonb_get_element(Jsonb *jb, Datum *path, int npath, bool *isnull, bool as_text)
*/
if (npath <= 0 && jbvp == NULL)
{
- if (as_text)
+ switch (target)
{
- return PointerGetDatum(cstring_to_text(JsonbToCString(NULL,
- container,
- VARSIZE(jb))));
- }
- else
- {
- /* 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 = 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 +1700,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 ac16f5c8..b121a6a3 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,11 @@ 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 +516,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/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 79793927..3922ac4d 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -4590,25 +4590,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' },
@@ -9983,6 +9983,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 => '9303', 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 => '4551', 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}',
@@ -10175,6 +10199,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',
@@ -10183,6 +10211,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 e38dfd49..53e06a20 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, 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/expected/jsonb.out
index b597d01a..0167b0e2 100644
--- a/src/test/regress/expected/jsonb.out
+++ b/src/test/regress/expected/jsonb.out
@@ -457,8 +457,242 @@ 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
+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)
+ Filter: (test_jsonb.json_type = 'object'::text)
+(3 rows)
+
+EXPLAIN (COSTS OFF, VERBOSE)
+SELECT (test_json -> 'field4')::int2
+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), '21'::oid))::smallint
+ Filter: (test_jsonb.json_type = 'object'::text)
+(3 rows)
+
+EXPLAIN (COSTS OFF, VERBOSE)
+SELECT (test_json -> 'field4')::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, 'field4'::text), '23'::oid))::integer
+ Filter: (test_jsonb.json_type = 'object'::text)
+(3 rows)
+
+EXPLAIN (COSTS OFF, VERBOSE)
+SELECT (test_json -> 'field4')::int8
+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), '20'::oid))::bigint
+ Filter: (test_jsonb.json_type = 'object'::text)
+(3 rows)
+
+EXPLAIN (COSTS OFF, VERBOSE)
+SELECT (test_json -> 'field4')::float4
+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), '700'::oid))::real
+ Filter: (test_jsonb.json_type = 'object'::text)
+(3 rows)
+
+EXPLAIN (COSTS OFF, VERBOSE)
+SELECT (test_json -> 'field4')::float8
+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), '701'::oid))::double precision
+ Filter: (test_jsonb.json_type = 'object'::text)
+(3 rows)
+
+EXPLAIN (COSTS OFF, VERBOSE)
+SELECT (test_json->'field5' -> 0)::numeric
+FROM test_jsonb
+WHERE json_type = 'object';
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------------------
+ Seq Scan on pg_temp.test_jsonb
+ Output: jsonb_finish_numeric(jsonb_array_element_start(((test_json -> 'field5'::text))::internal, 0), '1700'::oid)
+ Filter: (test_jsonb.json_type = 'object'::text)
+(3 rows)
+
+EXPLAIN (COSTS OFF, VERBOSE)
+SELECT (test_json->'field5' -> 10)::numeric
+FROM test_jsonb
+WHERE json_type = 'object';
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------
+ Seq Scan on pg_temp.test_jsonb
+ Output: jsonb_finish_numeric(jsonb_array_element_start(((test_json -> 'field5'::text))::internal, 10), '1700'::oid)
+ Filter: (test_jsonb.json_type = 'object'::text)
+(3 rows)
+
+EXPLAIN (COSTS OFF, VERBOSE)
+SELECT (test_json#>'{"field6", "f1"}')::numeric
+FROM test_jsonb
+WHERE json_type = 'object';
+ QUERY PLAN
+------------------------------------------------------------------------------------------------------------------------------
+ Seq Scan on pg_temp.test_jsonb
+ Output: jsonb_finish_numeric(jsonb_extract_path_start((test_json)::internal, VARIADIC '{field6,f1}'::text[]), '1700'::oid)
+ Filter: (test_jsonb.json_type = 'object'::text)
+(3 rows)
+
+EXPLAIN (COSTS OFF, VERBOSE)
+SELECT (test_json#>'{"field6", "f2"}')::numeric
+FROM test_jsonb
+WHERE json_type = 'object';
+ QUERY PLAN
+------------------------------------------------------------------------------------------------------------------------------
+ Seq Scan on pg_temp.test_jsonb
+ Output: jsonb_finish_numeric(jsonb_extract_path_start((test_json)::internal, VARIADIC '{field6,f2}'::text[]), '1700'::oid)
+ Filter: (test_jsonb.json_type = 'object'::text)
+(3 rows)
+
+EXPLAIN (COSTS OFF, VERBOSE)
+SELECT (test_json#>'{"field7"}')::bool
+FROM test_jsonb
+WHERE json_type = 'object';
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------
+ Seq Scan on pg_temp.test_jsonb
+ Output: 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}')
+FROM test_jsonb WHERE json_type = 'object';
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------------------------------------------
+ ProjectSet
+ Output: jsonb_path_query(test_json, '$."field8"[*]?(@ >= $"min" && @ <= $"max")'::jsonpath, '{"max": 4, "min": 2}'::jsonb, false)
+ -> Seq Scan on pg_temp.test_jsonb
+ Output: json_type, test_json
+ Filter: (test_jsonb.json_type = 'object'::text)
+(5 rows)
+
+EXPLAIN (COSTS OFF, VERBOSE)
+SELECT jsonb_path_query_first(test_json, '$.field8[*] ? (@ >= $min && @ <= $max)', '{"min":2, "max":4}')
+FROM test_jsonb WHERE json_type = 'object';
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------------------------------------------------
+ Seq Scan on pg_temp.test_jsonb
+ Output: jsonb_path_query_first(test_json, '$."field8"[*]?(@ >= $"min" && @ <= $"max")'::jsonpath, '{"max": 4, "min": 2}'::jsonb, false)
+ Filter: (test_jsonb.json_type = 'object'::text)
+(3 rows)
+
+EXPLAIN (COSTS OFF, VERBOSE)
+SELECT jsonb_path_query(test_json,'$.field8[*] ? (@ >= $min && @ <= $max)', '{"min":2, "max":4}')::int2
+FROM test_jsonb WHERE json_type = 'object';
+ QUERY PLAN
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Result
+ Output: (jsonb_finish_numeric((jsonb_path_query_start((test_json)::internal, '$."field8"[*]?(@ >= $"min" && @ <= $"max")'::jsonpath, '{"max": 4, "min": 2}'::jsonb, false)), '21'::oid))::smallint
+ -> ProjectSet
+ Output: jsonb_path_query_start((test_json)::internal, '$."field8"[*]?(@ >= $"min" && @ <= $"max")'::jsonpath, '{"max": 4, "min": 2}'::jsonb, false)
+ -> Seq Scan on pg_temp.test_jsonb
+ Output: json_type, test_json
+ Filter: (test_jsonb.json_type = 'object'::text)
+(7 rows)
+
+EXPLAIN (COSTS OFF, VERBOSE)
+SELECT jsonb_path_query_first(test_json, '$.field8[*] ? (@ >= $min && @ <= $max)', '{"min":2, "max":4}')::int2
+FROM test_jsonb WHERE json_type = 'object';
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Seq Scan on pg_temp.test_jsonb
+ Output: (jsonb_finish_numeric(jsonb_path_query_first_start((test_json)::internal, '$."field8"[*]?(@ >= $"min" && @ <= $"max")'::jsonpath, '{"max": 4, "min": 2}'::jsonb, false), '21'::oid))::smallint
+ Filter: (test_jsonb.json_type = 'object'::text)
+(3 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 +820,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 6dae715a..1b9959f9 100644
--- a/src/test/regress/sql/jsonb.sql
+++ b/src/test/regress/sql/jsonb.sql
@@ -154,8 +154,122 @@ 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
+FROM test_jsonb
+WHERE json_type = 'object';
+
+EXPLAIN (COSTS OFF, VERBOSE)
+SELECT (test_json -> 'field4')::int2
+FROM test_jsonb
+WHERE json_type = 'object';
+
+EXPLAIN (COSTS OFF, VERBOSE)
+SELECT (test_json -> 'field4')::int4
+FROM test_jsonb
+WHERE json_type = 'object';
+
+EXPLAIN (COSTS OFF, VERBOSE)
+SELECT (test_json -> 'field4')::int8
+FROM test_jsonb
+WHERE json_type = 'object';
+
+EXPLAIN (COSTS OFF, VERBOSE)
+SELECT (test_json -> 'field4')::float4
+FROM test_jsonb
+WHERE json_type = 'object';
+
+EXPLAIN (COSTS OFF, VERBOSE)
+SELECT (test_json -> 'field4')::float8
+FROM test_jsonb
+WHERE json_type = 'object';
+
+EXPLAIN (COSTS OFF, VERBOSE)
+SELECT (test_json->'field5' -> 0)::numeric
+FROM test_jsonb
+WHERE json_type = 'object';
+
+EXPLAIN (COSTS OFF, VERBOSE)
+SELECT (test_json->'field5' -> 10)::numeric
+FROM test_jsonb
+WHERE json_type = 'object';
+
+EXPLAIN (COSTS OFF, VERBOSE)
+SELECT (test_json#>'{"field6", "f1"}')::numeric
+FROM test_jsonb
+WHERE json_type = 'object';
+
+EXPLAIN (COSTS OFF, VERBOSE)
+SELECT (test_json#>'{"field6", "f2"}')::numeric
+FROM test_jsonb
+WHERE json_type = 'object';
+
+EXPLAIN (COSTS OFF, VERBOSE)
+SELECT (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}')
+FROM test_jsonb WHERE json_type = 'object';
+
+EXPLAIN (COSTS OFF, VERBOSE)
+SELECT jsonb_path_query_first(test_json, '$.field8[*] ? (@ >= $min && @ <= $max)', '{"min":2, "max":4}')
+FROM test_jsonb WHERE json_type = 'object';
+
+EXPLAIN (COSTS OFF, VERBOSE)
+SELECT jsonb_path_query(test_json,'$.field8[*] ? (@ >= $min && @ <= $max)', '{"min":2, "max":4}')::int2
+FROM test_jsonb WHERE json_type = 'object';
+
+EXPLAIN (COSTS OFF, VERBOSE)
+SELECT 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';
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index 5fd46b7b..4ca6679e 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -1265,6 +1265,7 @@ JsonArrayAgg
JsonArrayConstructor
JsonArrayQueryConstructor
JsonBaseObjectInfo
+JsonbValueTarget
JsonConstructorExpr
JsonConstructorExprState
JsonConstructorType
--
2.34.1
v1-0001-Improve-performance-of-Jsonb-extract-via-key-and-c.no-cfbot
Description: Binary data
