Hi,
Here is the update of this patch.
1. What is it for?
commit f7b93acc24b4a152984048fefc6d71db606e3204 (HEAD -> jsonb_numeric)
Author: yizhi.fzh <yizhi....@alibaba-inc.com>
Date: Fri Feb 9 16:54:06 2024 +0800
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
example:
create table tb(a jsonb);
insert into tb select '{"a": 1, "b": "a"}'::jsonb;
master:
explain (costs off, verbose) select * from tb where (a->'a')::numeric >
3::numeric;
QUERY PLAN
-----------------------------------------------------------
Seq Scan on public.tb
Output: a
Filter: (((tb.a -> 'a'::text))::numeric > '3'::numeric)
(3 rows)
patched:
postgres=# explain (costs off, verbose) select * from tb where
(a->'a')::numeric > 3::numeric;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
Seq Scan on public.tb
Output: a
Filter: (jsonb_finish_numeric(jsonb_object_field_start((tb.a)::internal,
'a'::text), '1700'::oid) > '3'::numeric)
(3 rows)
The final expression generated by planner support function includes:
1).
jsonb_object_field_start((tb.a)::internal, 'a'::text) first, this
function returns the internal datum which is JsonbValue in fact.
2).
jsonb_finish_numeric(internal (jsonbvalue), '1700::oid) convert the
jsonbvalue to numeric directly without the jsonb as a intermedia result.
the reason why "1700::oid" will be explained later, that's the key issue
right now.
The reason why we need the 2 steps rather than 1 step is because the
code can be better abstracted, the idea comes from Chap, the detailed
explaination is at [1]. You can search "Now, it would make me happy to
further reduce some of the code duplication" and read the following
graph.
2. Where is the current feature blocked for the past few months?
It's error message compatible issue! Continue with above setup:
master:
select * from tb where (a->'b')::numeric > 3::numeric;
ERROR: cannot cast jsonb string to type numeric
select * from tb where (a->'b')::int4 > 3::numeric;
ERROR: cannot cast jsonb string to type integer
You can see the error message is different (numeric vs integer).
Patched:
We still can get the same error message as master BUT the code
looks odd.
select * from tb where (a->'b')::int4 > 3;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
Seq Scan on public.tb
Output: a
Filter: ((jsonb_finish_numeric(jsonb_object_field_start((tb.a)::internal,
'b'::text), '23'::oid))::integer > 3)
(3 rows)
You can see "jsonb_finish_numeric(.., '23::oid)" the '23::oid' is just
for the *"integer"* output in error message:
"cannot cast jsonb string to type *integer*"
Now the sistuation is either we use the odd argument (23::oid) in
jsonb_finish_numeric, or we use a incompatible error message with the
previous version. I'm not sure which way is better, but this is the
place the current feature is blocked.
3. what do I want now?
Since this feature uses the planner support function which needs some
catalog changes, so it is better that we can merge this feature in PG17,
or else, we have to target it in PG18. So if some senior developers can
chime in, for the current blocking issue at least, will be pretty
helpful.
[1]
https://www.postgresql.org/message-id/5138c6b5fd239e7ce4e1a4e63826ac27%40anastigmatix.net
--
Best Regards
Andy Fan
>From f7b93acc24b4a152984048fefc6d71db606e3204 Mon Sep 17 00:00:00 2001
From: "yizhi.fzh" <yizhi....@alibaba-inc.com>
Date: Fri, 9 Feb 2024 16:54:06 +0800
Subject: [PATCH v16 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 | 204 ++++++++++++++++++++++++++
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, 540 insertions(+), 59 deletions(-)
diff --git a/src/backend/utils/adt/jsonb.c b/src/backend/utils/adt/jsonb.c
index c10b3fbedf..49ad44c5b3 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,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 79c5d16ff1..37be80d165 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 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 573b6ce2ba..0bdfcfd515 100644
--- a/src/backend/utils/adt/jsonpath_exec.c
+++ b/src/backend/utils/adt/jsonpath_exec.c
@@ -411,7 +411,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;
@@ -455,19 +455,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);
}
/*
@@ -509,7 +515,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);
@@ -522,7 +528,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();
}
@@ -530,13 +540,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 29af4ce65d..c38f6e6b44 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 => '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}',
@@ -10179,6 +10203,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',
@@ -10187,6 +10215,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 91433d439b..2e5880c6a4 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -1271,6 +1271,7 @@ JsonArrayAgg
JsonArrayConstructor
JsonArrayQueryConstructor
JsonBaseObjectInfo
+JsonbValueTarget
JsonConstructorExpr
JsonConstructorExprState
JsonConstructorType
--
2.34.1
