Here is latest version, nothing changed besides the rebase to the latest
master. The most recent 3 questions should be addressed.
- The error message compatible issue [1] and the Peter's answer at [2].
- Peter's new question at [2] and my answer at [3].
Any effrot to move this patch ahead is welcome and thanks all the people
who provided high quaility feedback so far, especially chapman!
[1] https://www.postgresql.org/message-id/87r0hmvuvr....@163.com
[2]
https://www.postgresql.org/message-id/8102ff5b-b156-409e-a48f-e53e63a39b36%40eisentraut.org
[3] https://www.postgresql.org/message-id/8734t6c5rh.fsf%40163.com
--
Best Regards
Andy Fan
>From fb38be5addb93d7c0b8c1a3e8376751c9b3be5f5 Mon Sep 17 00:00:00 2001
From: "yizhi.fzh" <yizhi....@alibaba-inc.com>
Date: Mon, 1 Apr 2024 09:36:08 +0800
Subject: [PATCH v17 1/1] Improve the performance of Jsonb numeric/bool
extraction.
JSONB object uses a binary compatible numeric format with the numeric
data type in SQL. However in the past, extracting a numeric value from a
JSONB field still needs to find the corresponding JsonbValue first,
then convert the JsonbValue to Jsonb, and finally use the cast system to
convert the Jsonb to a Numeric data type. This approach was very
inefficient in terms of performance.
In the current patch, It is handled that the JsonbValue is converted to
numeric data type directly. This is done by the planner support
function which detects the above case and simplify the expression.
Because the boolean type and numeric type share certain similarities in
their attributes, we have implemented the same optimization approach for
both. In the ideal test case, the performance can be 2x than before.
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 | 206 ++++++++++++++++++++++++++
src/backend/utils/adt/jsonbsubs.c | 4 +-
src/backend/utils/adt/jsonfuncs.c | 123 ++++++++++-----
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 | 112 +++++++++++++-
src/test/regress/sql/jsonb.sql | 66 ++++++++-
src/tools/pgindent/typedefs.list | 1 +
9 files changed, 542 insertions(+), 59 deletions(-)
diff --git a/src/backend/utils/adt/jsonb.c b/src/backend/utils/adt/jsonb.c
index a5e48744ac..6e93b34fd6 100644
--- a/src/backend/utils/adt/jsonb.c
+++ b/src/backend/utils/adt/jsonb.c
@@ -16,9 +16,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"
@@ -2035,6 +2041,206 @@ cannotCastJsonbValue(enum jbvType type, const char *sqltype)
elog(ERROR, "unknown jsonb type: %d", (int) type);
}
+
+/*
+ * jsonb_cast_support()
+ *
+ * Planner support function for extracting numeric or bool data type more
+ * effectively. After finding out the corresponding JsonbValue, instead of
+ * casting it to Jsonb as an intermediate type, we covert it 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);
+
+ /*
+ * All the simplified functions have the same arguments as the
+ * original one and return an internal object (actually a JsonbValue)
+ * which will be casted desired type in the later function call.
+ */
+ 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' to follow our current
+ * function signature checking system where if a function returns a
+ * internal type, one of its arguments must be an internal type.
+ */
+ 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 another function to cast 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 cast the numeric to the
+ * 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);
+}
+
+
+/*
+ * jsonb_finish_numeric
+ * convert the JsonbValue to a numeric datum and raise error if
+ * necessary.
+ */
+Datum
+jsonb_finish_numeric(PG_FUNCTION_ARGS)
+{
+ JsonbValue *v = (JsonbValue *) PG_GETARG_POINTER(0);
+
+ /*
+ * XXX: when converting a non jbvNumeric JsonbValue to numeric, some error
+ * like "cannot cast jsonb xxx to type yyy" should be raised, here the xxx
+ * is the real type of jsonbvalue, yyy is the desired type. if we just
+ * want to say yyy is "numeric", arg(1) is not needed, but if we have to
+ * insist on the error message compatible, we have to input this extra
+ * argument.
+ */
+ 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);
+}
+
+/*
+ * jsonb_finish_numeric
+ * convert the JsonbValue to a bool datum and raise error if
+ * necessary.
+ */
+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 d3edb010ed..1c86cca6e4 100644
--- a/src/backend/utils/adt/jsonbsubs.c
+++ b/src/backend/utils/adt/jsonbsubs.c
@@ -248,7 +248,7 @@ jsonb_subscript_fetch(ExprState *state,
workspace->index,
sbsrefstate->numupper,
op->resnull,
- false);
+ JsonbValue_AsJsonb);
}
/*
@@ -340,7 +340,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 1b0f494329..5a89989b38 100644
--- a/src/backend/utils/adt/jsonfuncs.c
+++ b/src/backend/utils/adt/jsonfuncs.c
@@ -358,7 +358,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 */
@@ -500,6 +500,24 @@ 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);
+/*
+ * convert_jsonbvalue
+ * convert the JsonbValue to Text, Jsonb or just a pointer datum.
+ */
+Datum
+convert_jsonbvalue(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
@@ -855,13 +873,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();
@@ -869,14 +886,26 @@ jsonb_object_field(PG_FUNCTION_ARGS)
v = getKeyJsonValueFromContainer(&jb->root,
VARDATA_ANY(key),
VARSIZE_ANY_EXHDR(key),
- &vbuf);
+ NULL);
if (v != NULL)
- PG_RETURN_JSONB_P(JsonbValueToJsonb(v));
+ return convert_jsonbvalue(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
json_object_field_text(PG_FUNCTION_ARGS)
{
@@ -930,8 +959,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);
@@ -953,11 +982,23 @@ jsonb_array_element(PG_FUNCTION_ARGS)
v = getIthJsonbValueFromContainer(&jb->root, element);
if (v != NULL)
- PG_RETURN_JSONB_P(JsonbValueToJsonb(v));
+ return convert_jsonbvalue(v, target);
PG_RETURN_NULL();
}
+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)
{
@@ -1484,17 +1525,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);
@@ -1516,7 +1563,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();
@@ -1525,7 +1572,8 @@ 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;
@@ -1558,16 +1606,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);
+ }
}
}
@@ -1653,23 +1711,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 convert_jsonbvalue(jbvp, target);
}
Datum
diff --git a/src/backend/utils/adt/jsonpath_exec.c b/src/backend/utils/adt/jsonpath_exec.c
index 6c8bd57503..c806bdf1ac 100644
--- a/src/backend/utils/adt/jsonpath_exec.c
+++ b/src/backend/utils/adt/jsonpath_exec.c
@@ -408,7 +408,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;
@@ -452,19 +452,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, convert_jsonbvalue(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);
}
/*
@@ -506,7 +512,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);
@@ -519,7 +525,11 @@ jsonb_path_query_first_internal(FunctionCallInfo fcinfo, bool tz)
jb, !silent, &found, tz);
if (JsonValueListLength(&found) >= 1)
- PG_RETURN_JSONB_P(JsonbValueToJsonb(JsonValueListHead(&found)));
+ {
+ JsonbValue *jbv = JsonValueListHead(&found);
+
+ return convert_jsonbvalue(jbv, target);
+ }
else
PG_RETURN_NULL();
}
@@ -527,13 +537,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 291ed876fc..c786edaeaf 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' },
@@ -9976,6 +9976,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 => '8688', 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}',
@@ -10172,6 +10196,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',
@@ -10180,6 +10208,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 e38dfd4901..1bcf72e351 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 convert_jsonbvalue(JsonbValue *jbv, JsonbValueTarget target);
#endif /* __JSONB_H__ */
diff --git a/src/test/regress/expected/jsonb.out b/src/test/regress/expected/jsonb.out
index 66bee5162b..44f2db574d 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 97bc2242a1..9886044c82 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';
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index 95ae7845d8..9ea3f11a7b 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -1273,6 +1273,7 @@ JsonArrayAgg
JsonArrayConstructor
JsonArrayQueryConstructor
JsonBaseObjectInfo
+JsonbValueTarget
JsonConstructorExpr
JsonConstructorExprState
JsonConstructorType
--
2.34.1
