On 2026-03-27 Fr 3:24 PM, Andrew Dunstan wrote:
On 2026-02-01 Su 10:23 AM, David E. Wheeler wrote:
On Jan 31, 2026, at 11:31, David E. Wheeler<[email protected]> wrote:
At this point, I’d say it’s ready for committer review.
Me too, I’ve updated the status[0].
[0]:https://commitfest.postgresql.org/patch/5270/
Patch needs rework following commit 5a2043bf713
I have fixed that. Some key_name entries were missing, which was an
issue. I also removed some unused variables and some duplicate tests,
and did some general tidying.
cheers
andrew
--
Andrew Dunstan
EDB: https://www.enterprisedb.com
From 038568c8001ca684efb9ef8017fe9072b9060305 Mon Sep 17 00:00:00 2001
From: "David E. Wheeler" <[email protected]>
Date: Mon, 2 Jun 2025 17:14:10 -0400
Subject: [PATCH v18 1/2] Rename jsonpath method arg tokens
Rename the `csv_` tokens to `int_`, because they represent signed or
unsigned integers, as follows:
* `csv_elem` => `int_elem`
* `csv_list` => `int_list`
* `opt_csv_list` => `opt_int_list`
Rename the `datetime_precision` tokens to `uint_arg`, as they represent
unsigned integers and will be useful for other methods in the future, as
follows:
* `datetime_precision` => `uint_elem`
* `opt_datetime_precision` => `opt_uint_arg`
Rename the `datetime_template` tokens to `str_arg`, as they represent
strings and will be useful for other methods in the future, as follows:
* `datetime_template` => `str_elem`
* `opt_datetime_template` => `opt_str_arg`
---
src/backend/utils/adt/jsonpath_gram.y | 42 +++++++++++++--------------
1 file changed, 21 insertions(+), 21 deletions(-)
diff --git a/src/backend/utils/adt/jsonpath_gram.y b/src/backend/utils/adt/jsonpath_gram.y
index 87070235d11..eb449aa3709 100644
--- a/src/backend/utils/adt/jsonpath_gram.y
+++ b/src/backend/utils/adt/jsonpath_gram.y
@@ -92,10 +92,10 @@ static bool makeItemLikeRegex(JsonPathParseItem *expr,
%type <value> scalar_value path_primary expr array_accessor
any_path accessor_op key predicate delimited_predicate
index_elem starts_with_initial expr_or_predicate
- datetime_template opt_datetime_template csv_elem
- datetime_precision opt_datetime_precision
+ str_elem opt_str_arg int_elem
+ uint_elem opt_uint_arg
-%type <elems> accessor_expr csv_list opt_csv_list
+%type <elems> accessor_expr int_list opt_int_list
%type <indexs> index_list
@@ -254,7 +254,7 @@ accessor_op:
| '.' any_path { $$ = $2; }
| '.' method '(' ')' { $$ = makeItemType($2); }
| '?' '(' predicate ')' { $$ = makeItemUnary(jpiFilter, $3); }
- | '.' DECIMAL_P '(' opt_csv_list ')'
+ | '.' DECIMAL_P '(' opt_int_list ')'
{
if (list_length($4) == 0)
$$ = makeItemBinary(jpiDecimal, NULL, NULL);
@@ -268,19 +268,19 @@ accessor_op:
errmsg("invalid input syntax for type %s", "jsonpath"),
errdetail(".decimal() can only have an optional precision[,scale].")));
}
- | '.' DATETIME_P '(' opt_datetime_template ')'
+ | '.' DATETIME_P '(' opt_str_arg ')'
{ $$ = makeItemUnary(jpiDatetime, $4); }
- | '.' TIME_P '(' opt_datetime_precision ')'
+ | '.' TIME_P '(' opt_uint_arg ')'
{ $$ = makeItemUnary(jpiTime, $4); }
- | '.' TIME_TZ_P '(' opt_datetime_precision ')'
+ | '.' TIME_TZ_P '(' opt_uint_arg ')'
{ $$ = makeItemUnary(jpiTimeTz, $4); }
- | '.' TIMESTAMP_P '(' opt_datetime_precision ')'
+ | '.' TIMESTAMP_P '(' opt_uint_arg ')'
{ $$ = makeItemUnary(jpiTimestamp, $4); }
- | '.' TIMESTAMP_TZ_P '(' opt_datetime_precision ')'
+ | '.' TIMESTAMP_TZ_P '(' opt_uint_arg ')'
{ $$ = makeItemUnary(jpiTimestampTz, $4); }
;
-csv_elem:
+int_elem:
INT_P
{ $$ = makeItemNumeric(&$1); }
| '+' INT_P %prec UMINUS
@@ -289,31 +289,31 @@ csv_elem:
{ $$ = makeItemUnary(jpiMinus, makeItemNumeric(&$2)); }
;
-csv_list:
- csv_elem { $$ = list_make1($1); }
- | csv_list ',' csv_elem { $$ = lappend($1, $3); }
+int_list:
+ int_elem { $$ = list_make1($1); }
+ | int_list ',' int_elem { $$ = lappend($1, $3); }
;
-opt_csv_list:
- csv_list { $$ = $1; }
+opt_int_list:
+ int_list { $$ = $1; }
| /* EMPTY */ { $$ = NULL; }
;
-datetime_precision:
+uint_elem:
INT_P { $$ = makeItemNumeric(&$1); }
;
-opt_datetime_precision:
- datetime_precision { $$ = $1; }
+opt_uint_arg:
+ uint_elem { $$ = $1; }
| /* EMPTY */ { $$ = NULL; }
;
-datetime_template:
+str_elem:
STRING_P { $$ = makeItemString(&$1); }
;
-opt_datetime_template:
- datetime_template { $$ = $1; }
+opt_str_arg:
+ str_elem { $$ = $1; }
| /* EMPTY */ { $$ = NULL; }
;
--
2.43.0
From f4ce5658e9c575e29d070d50c3f9baa44d01b675 Mon Sep 17 00:00:00 2001
From: Florents Tselai <[email protected]>
Date: Sun, 4 Jan 2026 15:49:04 -0500
Subject: [PATCH v18 2/2] Add additional jsonpath string methods
Add the following jsonpath methods:
* l/r/btrim()
* lower(), upper()
* initcap()
* replace()
* split_part()
Each simply dispatches to the standard string processing functions.
These depend on the locale, but since it's set at `initdb`, they can be
considered immutable and therefore allowed in any jsonpath expression.
---
doc/src/sgml/func/func-json.sgml | 140 +++++++
src/backend/utils/adt/jsonpath.c | 117 +++++-
src/backend/utils/adt/jsonpath_exec.c | 178 ++++++++
src/backend/utils/adt/jsonpath_gram.y | 33 +-
src/backend/utils/adt/jsonpath_scan.l | 8 +
src/include/utils/jsonpath.h | 8 +
src/test/regress/expected/jsonb_jsonpath.out | 381 ++++++++++++++++++
src/test/regress/expected/jsonpath.out | 186 +++++++++
.../regress/expected/sqljson_queryfuncs.out | 10 +-
src/test/regress/sql/jsonb_jsonpath.sql | 106 +++++
src/test/regress/sql/jsonpath.sql | 41 ++
src/test/regress/sql/sqljson_queryfuncs.sql | 11 +-
12 files changed, 1212 insertions(+), 7 deletions(-)
diff --git a/doc/src/sgml/func/func-json.sgml b/doc/src/sgml/func/func-json.sgml
index 1ec73cff464..86c308454a8 100644
--- a/doc/src/sgml/func/func-json.sgml
+++ b/doc/src/sgml/func/func-json.sgml
@@ -2781,6 +2781,146 @@ ERROR: jsonpath member accessor can only be applied to an object
<returnvalue>[{"id": 0, "key": "x", "value": "20"}, {"id": 0, "key": "y", "value": 32}]</returnvalue>
</para></entry>
</row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <replaceable>string</replaceable> <literal>.</literal> <literal>lower()</literal>
+ <returnvalue><replaceable>string</replaceable></returnvalue>
+ </para>
+ <para>
+ String converted to all lower case according to the rules of the database's locale.
+ </para>
+ <para>
+ <literal>jsonb_path_query('"TOM"', '$.lower()')</literal>
+ <returnvalue>"tom"</returnvalue>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <replaceable>string</replaceable> <literal>.</literal> <literal>upper()</literal>
+ <returnvalue><replaceable>string</replaceable></returnvalue>
+ </para>
+ <para>
+ String converted to all upper case according to the rules of the database's locale.
+ </para>
+ <para>
+ <literal>jsonb_path_query('"tom"', '$.upper()')</literal>
+ <returnvalue>"TOM"</returnvalue>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <replaceable>string</replaceable> <literal>.</literal> <literal>initcap()</literal>
+ <returnvalue><replaceable>string</replaceable></returnvalue>
+ </para>
+ <para>
+ String with the first letter of each word converted to upper case
+ according to the rules of the database's locale. Words are sequences
+ of alphanumeric characters separated by non-alphanumeric characters.
+ </para>
+ <para>
+ <literal>jsonb_path_query('"hi THOMAS"', '$.initcap()')</literal>
+ <returnvalue>"Hi Thomas"</returnvalue>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <replaceable>string</replaceable> <literal>.</literal> <literal>replace(<replaceable>from</replaceable>, <replaceable>to</replaceable>)</literal>
+ <returnvalue><replaceable>string</replaceable></returnvalue>
+ </para>
+ <para>
+ String with all occurrences of substring from replaced with substring to.
+ </para>
+ <para>
+ <literal>jsonb_path_query('"abcdefabcdef"', '$.replace("cd", "XX")')</literal>
+ <returnvalue>"abXXefabXXef"</returnvalue>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <replaceable>string</replaceable> <literal>.</literal> <literal>split_part(<replaceable>delimiter</replaceable>, <replaceable>n</replaceable>)</literal>
+ <returnvalue><replaceable>string</replaceable></returnvalue>
+ </para>
+ <para>
+ String split at occurrences of <replaceable>delimiter</replaceable>
+ and returns the <replaceable>n</replaceable>'th field (counting from
+ one) or, when <replaceable>n</replaceable> is negative, returns the
+ |<replaceable>n</replaceable>|'th-from-last field.
+ </para>
+ <para>
+ <literal>jsonb_path_query('"abc~@~def~@~ghi"', '$.split_part("~@~", 2)')</literal>
+ <returnvalue>"def"</returnvalue>
+ </para>
+ <para>
+ <literal>jsonb_path_query('"abc,def,ghi,jkl"', '$.split_part(",", 3)')</literal>
+ <returnvalue>"ghi"</returnvalue>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <replaceable>string</replaceable> <literal>.</literal> <literal>ltrim([ <replaceable>characters</replaceable> ])</literal>
+ <returnvalue><replaceable>string</replaceable></returnvalue>
+ </para>
+ <para>
+ String with the longest string containing only spaces or the
+ characters in <replaceable>characters</replaceable> removed from the
+ start of <replaceable>string</replaceable>
+ </para>
+ <para>
+ <literal> jsonb_path_query('" hello"', '$.ltrim()')</literal>
+ <returnvalue>"hello"</returnvalue>
+ </para>
+ <para>
+ <literal>jsonb_path_query('"zzzytest"', '$.ltrim("xyz")')</literal>
+ <returnvalue>"test"</returnvalue>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <replaceable>string</replaceable> <literal>.</literal> <literal>rtrim([ <replaceable>characters</replaceable> ])</literal>
+ <returnvalue><replaceable>string</replaceable></returnvalue>
+ </para>
+ <para>
+ String with the longest string containing only spaces or the
+ characters in <replaceable>characters</replaceable> removed from the
+ end of <replaceable>string</replaceable>
+ </para>
+ <para>
+ <literal>jsonb_path_query('"hello "', '$.rtrim()')</literal>
+ <returnvalue>"hello"</returnvalue>
+ </para>
+ <para>
+ <literal>jsonb_path_query('"testxxzx"', '$.rtrim("xyz")')</literal>
+ <returnvalue>"test"</returnvalue>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <replaceable>string</replaceable> <literal>.</literal> <literal>btrim([ <replaceable>characters</replaceable> ])</literal>
+ <returnvalue><replaceable>string</replaceable></returnvalue>
+ </para>
+ <para>
+ String with the longest string containing only spaces or the
+ characters in <replaceable>characters</replaceable> removed from the
+ start and end of <replaceable>string</replaceable>
+ </para>
+ <para>
+ <literal>jsonb_path_query('" hello "', '$.btrim()')</literal>
+ <returnvalue>"hello"</returnvalue>
+ </para>
+ <para>
+ <literal>jsonb_path_query('"xyxtrimyyx"', '$.btrim("xyz")')</literal>
+ <returnvalue>"trim"</returnvalue>
+ </para></entry>
+ </row>
+
</tbody>
</tgroup>
</table>
diff --git a/src/backend/utils/adt/jsonpath.c b/src/backend/utils/adt/jsonpath.c
index d70ff1eaa54..7bfc18c9888 100644
--- a/src/backend/utils/adt/jsonpath.c
+++ b/src/backend/utils/adt/jsonpath.c
@@ -298,6 +298,8 @@ flattenJsonPathParseItem(StringInfo buf, int *result, struct Node *escontext,
case jpiMod:
case jpiStartsWith:
case jpiDecimal:
+ case jpiStrReplace:
+ case jpiStrSplitPart:
{
/*
* First, reserve place for left/right arg's positions, then
@@ -362,6 +364,9 @@ flattenJsonPathParseItem(StringInfo buf, int *result, struct Node *escontext,
case jpiTimeTz:
case jpiTimestamp:
case jpiTimestampTz:
+ case jpiStrLtrim:
+ case jpiStrRtrim:
+ case jpiStrBtrim:
{
int32 arg = reserveSpaceForItemPointer(buf);
@@ -457,6 +462,9 @@ flattenJsonPathParseItem(StringInfo buf, int *result, struct Node *escontext,
case jpiInteger:
case jpiNumber:
case jpiStringFunc:
+ case jpiStrLower:
+ case jpiStrUpper:
+ case jpiStrInitcap:
break;
default:
elog(ERROR, "unrecognized jsonpath item type: %d", item->type);
@@ -831,6 +839,60 @@ printJsonPathItem(StringInfo buf, JsonPathItem *v, bool inKey,
}
appendStringInfoChar(buf, ')');
break;
+ case jpiStrReplace:
+ appendStringInfoString(buf, ".replace(");
+ jspGetLeftArg(v, &elem);
+ printJsonPathItem(buf, &elem, false, false);
+ appendStringInfoChar(buf, ',');
+ jspGetRightArg(v, &elem);
+ printJsonPathItem(buf, &elem, false, false);
+ appendStringInfoChar(buf, ')');
+ break;
+ case jpiStrLower:
+ appendStringInfoString(buf, ".lower()");
+ break;
+ case jpiStrUpper:
+ appendStringInfoString(buf, ".upper()");
+ break;
+ case jpiStrSplitPart:
+ appendStringInfoString(buf, ".split_part(");
+ jspGetLeftArg(v, &elem);
+ printJsonPathItem(buf, &elem, false, false);
+ appendStringInfoChar(buf, ',');
+ jspGetRightArg(v, &elem);
+ printJsonPathItem(buf, &elem, false, false);
+ appendStringInfoChar(buf, ')');
+ break;
+ case jpiStrLtrim:
+ appendStringInfoString(buf, ".ltrim(");
+ if (v->content.arg)
+ {
+ jspGetArg(v, &elem);
+ printJsonPathItem(buf, &elem, false, false);
+ }
+ appendStringInfoChar(buf, ')');
+ break;
+ case jpiStrRtrim:
+ appendStringInfoString(buf, ".rtrim(");
+ if (v->content.arg)
+ {
+ jspGetArg(v, &elem);
+ printJsonPathItem(buf, &elem, false, false);
+ }
+ appendStringInfoChar(buf, ')');
+ break;
+ case jpiStrBtrim:
+ appendStringInfoString(buf, ".btrim(");
+ if (v->content.arg)
+ {
+ jspGetArg(v, &elem);
+ printJsonPathItem(buf, &elem, false, false);
+ }
+ appendStringInfoChar(buf, ')');
+ break;
+ case jpiStrInitcap:
+ appendStringInfoString(buf, ".initcap()");
+ break;
default:
elog(ERROR, "unrecognized jsonpath item type: %d", v->type);
}
@@ -914,6 +976,22 @@ jspOperationName(JsonPathItemType type)
return "timestamp";
case jpiTimestampTz:
return "timestamp_tz";
+ case jpiStrReplace:
+ return "replace";
+ case jpiStrLower:
+ return "lower";
+ case jpiStrUpper:
+ return "upper";
+ case jpiStrLtrim:
+ return "ltrim";
+ case jpiStrRtrim:
+ return "rtrim";
+ case jpiStrBtrim:
+ return "btrim";
+ case jpiStrInitcap:
+ return "initcap";
+ case jpiStrSplitPart:
+ return "split_part";
default:
elog(ERROR, "unrecognized jsonpath item type: %d", type);
return NULL;
@@ -1016,6 +1094,9 @@ jspInitByBuffer(JsonPathItem *v, char *base, int32 pos)
case jpiInteger:
case jpiNumber:
case jpiStringFunc:
+ case jpiStrLower:
+ case jpiStrUpper:
+ case jpiStrInitcap:
break;
case jpiString:
case jpiKey:
@@ -1041,6 +1122,8 @@ jspInitByBuffer(JsonPathItem *v, char *base, int32 pos)
case jpiMod:
case jpiStartsWith:
case jpiDecimal:
+ case jpiStrReplace:
+ case jpiStrSplitPart:
read_int32(v->content.args.left, base, pos);
read_int32(v->content.args.right, base, pos);
break;
@@ -1055,6 +1138,9 @@ jspInitByBuffer(JsonPathItem *v, char *base, int32 pos)
case jpiTimeTz:
case jpiTimestamp:
case jpiTimestampTz:
+ case jpiStrLtrim:
+ case jpiStrRtrim:
+ case jpiStrBtrim:
read_int32(v->content.arg, base, pos);
break;
case jpiIndexArray:
@@ -1090,7 +1176,10 @@ jspGetArg(JsonPathItem *v, JsonPathItem *a)
v->type == jpiTime ||
v->type == jpiTimeTz ||
v->type == jpiTimestamp ||
- v->type == jpiTimestampTz);
+ v->type == jpiTimestampTz ||
+ v->type == jpiStrLtrim ||
+ v->type == jpiStrRtrim ||
+ v->type == jpiStrBtrim);
jspInitByBuffer(a, v->base, v->content.arg);
}
@@ -1152,7 +1241,15 @@ jspGetNext(JsonPathItem *v, JsonPathItem *a)
v->type == jpiTime ||
v->type == jpiTimeTz ||
v->type == jpiTimestamp ||
- v->type == jpiTimestampTz);
+ v->type == jpiTimestampTz ||
+ v->type == jpiStrReplace ||
+ v->type == jpiStrLower ||
+ v->type == jpiStrUpper ||
+ v->type == jpiStrLtrim ||
+ v->type == jpiStrRtrim ||
+ v->type == jpiStrBtrim ||
+ v->type == jpiStrInitcap ||
+ v->type == jpiStrSplitPart);
if (a)
jspInitByBuffer(a, v->base, v->nextPos);
@@ -1179,7 +1276,9 @@ jspGetLeftArg(JsonPathItem *v, JsonPathItem *a)
v->type == jpiDiv ||
v->type == jpiMod ||
v->type == jpiStartsWith ||
- v->type == jpiDecimal);
+ v->type == jpiDecimal ||
+ v->type == jpiStrReplace ||
+ v->type == jpiStrSplitPart);
jspInitByBuffer(a, v->base, v->content.args.left);
}
@@ -1201,7 +1300,9 @@ jspGetRightArg(JsonPathItem *v, JsonPathItem *a)
v->type == jpiDiv ||
v->type == jpiMod ||
v->type == jpiStartsWith ||
- v->type == jpiDecimal);
+ v->type == jpiDecimal ||
+ v->type == jpiStrReplace ||
+ v->type == jpiStrSplitPart);
jspInitByBuffer(a, v->base, v->content.args.right);
}
@@ -1501,6 +1602,14 @@ jspIsMutableWalker(JsonPathItem *jpi, struct JsonPathMutableContext *cxt)
case jpiInteger:
case jpiNumber:
case jpiStringFunc:
+ case jpiStrReplace:
+ case jpiStrLower:
+ case jpiStrUpper:
+ case jpiStrLtrim:
+ case jpiStrRtrim:
+ case jpiStrBtrim:
+ case jpiStrInitcap:
+ case jpiStrSplitPart:
status = jpdsNonDateTime;
break;
diff --git a/src/backend/utils/adt/jsonpath_exec.c b/src/backend/utils/adt/jsonpath_exec.c
index f65ad529113..770840a0611 100644
--- a/src/backend/utils/adt/jsonpath_exec.c
+++ b/src/backend/utils/adt/jsonpath_exec.c
@@ -329,6 +329,8 @@ static JsonPathExecResult executeNumericItemMethod(JsonPathExecContext *cxt,
JsonValueList *found);
static JsonPathExecResult executeDateTimeMethod(JsonPathExecContext *cxt, JsonPathItem *jsp,
JsonbValue *jb, JsonValueList *found);
+static JsonPathExecResult executeStringInternalMethod(JsonPathExecContext *cxt, JsonPathItem *jsp,
+ JsonbValue *jb, JsonValueList *found);
static JsonPathExecResult executeKeyValueMethod(JsonPathExecContext *cxt,
JsonPathItem *jsp, JsonbValue *jb, JsonValueList *found);
static JsonPathExecResult appendBoolResult(JsonPathExecContext *cxt,
@@ -1680,6 +1682,22 @@ executeItemOptUnwrapTarget(JsonPathExecContext *cxt, JsonPathItem *jsp,
}
break;
+ case jpiStrReplace:
+ case jpiStrLower:
+ case jpiStrUpper:
+ case jpiStrLtrim:
+ case jpiStrRtrim:
+ case jpiStrBtrim:
+ case jpiStrInitcap:
+ case jpiStrSplitPart:
+ {
+ if (unwrap && JsonbType(jb) == jbvArray)
+ return executeItemUnwrapTargetArray(cxt, jsp, jb, found, false);
+
+ return executeStringInternalMethod(cxt, jsp, jb, found);
+ }
+ break;
+
default:
elog(ERROR, "unrecognized jsonpath item type: %d", jsp->type);
}
@@ -2879,6 +2897,166 @@ executeDateTimeMethod(JsonPathExecContext *cxt, JsonPathItem *jsp,
return executeNextItem(cxt, jsp, &elem, &jbv, found);
}
+/*
+ * Implementation of .upper(), .lower() et al. string methods,
+ * that forward their actual implementation to internal functions.
+ */
+static JsonPathExecResult
+executeStringInternalMethod(JsonPathExecContext *cxt, JsonPathItem *jsp,
+ JsonbValue *jb, JsonValueList *found)
+{
+ JsonbValue jbv;
+ bool hasNext;
+ JsonPathExecResult res = jperNotFound;
+ JsonPathItem elem;
+ Datum str; /* Datum representation for the current string
+ * value. The first argument to internal
+ * functions */
+ char *resStr = NULL;
+
+ Assert(jsp->type == jpiStrReplace ||
+ jsp->type == jpiStrLower ||
+ jsp->type == jpiStrUpper ||
+ jsp->type == jpiStrLtrim ||
+ jsp->type == jpiStrRtrim ||
+ jsp->type == jpiStrBtrim ||
+ jsp->type == jpiStrInitcap ||
+ jsp->type == jpiStrSplitPart);
+
+ if (!(jb = getScalar(jb, jbvString)))
+ RETURN_ERROR(ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("jsonpath item method .%s() can only be applied to a string",
+ jspOperationName(jsp->type)))));
+
+ str = PointerGetDatum(cstring_to_text_with_len(jb->val.string.val, jb->val.string.len));
+
+ /* Dispatch to the appropriate internal string function */
+ switch (jsp->type)
+ {
+ case jpiStrReplace:
+ {
+ char *from_str,
+ *to_str;
+
+ jspGetLeftArg(jsp, &elem);
+ if (elem.type != jpiString)
+ elog(ERROR, "invalid jsonpath item type for .replace() from");
+
+ from_str = jspGetString(&elem, NULL);
+
+ jspGetRightArg(jsp, &elem);
+ if (elem.type != jpiString)
+ elog(ERROR, "invalid jsonpath item type for .replace() to");
+
+ to_str = jspGetString(&elem, NULL);
+
+ resStr = TextDatumGetCString(DirectFunctionCall3Coll(replace_text,
+ DEFAULT_COLLATION_OID,
+ str,
+ CStringGetTextDatum(from_str),
+ CStringGetTextDatum(to_str)));
+ break;
+ }
+ case jpiStrLower:
+ resStr = TextDatumGetCString(DirectFunctionCall1Coll(lower, DEFAULT_COLLATION_OID, str));
+ break;
+ case jpiStrUpper:
+ resStr = TextDatumGetCString(DirectFunctionCall1Coll(upper, DEFAULT_COLLATION_OID, str));
+ break;
+ case jpiStrLtrim:
+ case jpiStrRtrim:
+ case jpiStrBtrim:
+ {
+ PGFunction func1 = NULL;
+ PGFunction func2 = NULL;
+
+ switch (jsp->type)
+ {
+ case jpiStrLtrim:
+ func1 = ltrim1;
+ func2 = ltrim;
+ break;
+ case jpiStrRtrim:
+ func1 = rtrim1;
+ func2 = rtrim;
+ break;
+ case jpiStrBtrim:
+ func1 = btrim1;
+ func2 = btrim;
+ break;
+ default:
+ break;
+ }
+
+ if (jsp->content.arg)
+ {
+ char *characters_str;
+
+ jspGetArg(jsp, &elem);
+ if (elem.type != jpiString)
+ elog(ERROR, "invalid jsonpath item type for .%s() argument",
+ jspOperationName(jsp->type));
+
+ characters_str = jspGetString(&elem, NULL);
+ resStr = TextDatumGetCString(DirectFunctionCall2Coll(func2,
+ DEFAULT_COLLATION_OID, str,
+ CStringGetTextDatum(characters_str)));
+ }
+ else
+ {
+ resStr = TextDatumGetCString(DirectFunctionCall1Coll(func1,
+ DEFAULT_COLLATION_OID, str));
+ }
+ break;
+ }
+
+ case jpiStrInitcap:
+ resStr = TextDatumGetCString(DirectFunctionCall1Coll(initcap, DEFAULT_COLLATION_OID, str));
+ break;
+ case jpiStrSplitPart:
+ {
+ char *from_str;
+ Numeric n;
+
+ jspGetLeftArg(jsp, &elem);
+ if (elem.type != jpiString)
+ elog(ERROR, "invalid jsonpath item type for .split_part()");
+
+ from_str = jspGetString(&elem, NULL);
+
+ jspGetRightArg(jsp, &elem);
+ if (elem.type != jpiNumeric)
+ elog(ERROR, "invalid jsonpath item type for .split_part()");
+
+ n = jspGetNumeric(&elem);
+
+ resStr = TextDatumGetCString(DirectFunctionCall3Coll(split_part,
+ DEFAULT_COLLATION_OID,
+ str,
+ CStringGetTextDatum(from_str),
+ DirectFunctionCall1(numeric_int4, NumericGetDatum(n))));
+ break;
+ }
+ default:
+ elog(ERROR, "unsupported jsonpath item type: %d", jsp->type);
+ }
+
+ if (resStr)
+ res = jperOk;
+
+ hasNext = jspGetNext(jsp, &elem);
+
+ if (!hasNext && !found)
+ return res;
+
+ jbv.type = jbvString;
+ jbv.val.string.val = resStr;
+ jbv.val.string.len = strlen(resStr);
+
+ return executeNextItem(cxt, jsp, &elem, &jbv, found);
+}
+
/*
* Implementation of .keyvalue() method.
*
diff --git a/src/backend/utils/adt/jsonpath_gram.y b/src/backend/utils/adt/jsonpath_gram.y
index eb449aa3709..f826697d098 100644
--- a/src/backend/utils/adt/jsonpath_gram.y
+++ b/src/backend/utils/adt/jsonpath_gram.y
@@ -86,6 +86,8 @@ static bool makeItemLikeRegex(JsonPathParseItem *expr,
%token <str> DATETIME_P
%token <str> BIGINT_P BOOLEAN_P DATE_P DECIMAL_P INTEGER_P NUMBER_P
%token <str> STRINGFUNC_P TIME_P TIME_TZ_P TIMESTAMP_P TIMESTAMP_TZ_P
+%token <str> STR_REPLACE_P STR_LOWER_P STR_UPPER_P STR_LTRIM_P STR_RTRIM_P STR_BTRIM_P
+ STR_INITCAP_P STR_SPLIT_PART_P
%type <result> result
@@ -95,7 +97,7 @@ static bool makeItemLikeRegex(JsonPathParseItem *expr,
str_elem opt_str_arg int_elem
uint_elem opt_uint_arg
-%type <elems> accessor_expr int_list opt_int_list
+%type <elems> accessor_expr int_list opt_int_list str_int_args str_str_args
%type <indexs> index_list
@@ -278,6 +280,16 @@ accessor_op:
{ $$ = makeItemUnary(jpiTimestamp, $4); }
| '.' TIMESTAMP_TZ_P '(' opt_uint_arg ')'
{ $$ = makeItemUnary(jpiTimestampTz, $4); }
+ | '.' STR_REPLACE_P '(' str_str_args ')'
+ { $$ = makeItemBinary(jpiStrReplace, linitial($4), lsecond($4)); }
+ | '.' STR_SPLIT_PART_P '(' str_int_args ')'
+ { $$ = makeItemBinary(jpiStrSplitPart, linitial($4), lsecond($4)); }
+ | '.' STR_LTRIM_P '(' opt_str_arg ')'
+ { $$ = makeItemUnary(jpiStrLtrim, $4); }
+ | '.' STR_RTRIM_P '(' opt_str_arg ')'
+ { $$ = makeItemUnary(jpiStrRtrim, $4); }
+ | '.' STR_BTRIM_P '(' opt_str_arg ')'
+ { $$ = makeItemUnary(jpiStrBtrim, $4); }
;
int_elem:
@@ -317,6 +329,14 @@ opt_str_arg:
| /* EMPTY */ { $$ = NULL; }
;
+str_int_args:
+ str_elem ',' int_elem { $$ = list_make2($1, $3); }
+ ;
+
+str_str_args:
+ str_elem ',' str_elem { $$ = list_make2($1, $3); }
+ ;
+
key:
key_name { $$ = makeItemKey(&$1); }
;
@@ -357,6 +377,14 @@ key_name:
| TIME_TZ_P
| TIMESTAMP_P
| TIMESTAMP_TZ_P
+ | STR_LOWER_P
+ | STR_UPPER_P
+ | STR_INITCAP_P
+ | STR_REPLACE_P
+ | STR_SPLIT_PART_P
+ | STR_LTRIM_P
+ | STR_RTRIM_P
+ | STR_BTRIM_P
;
method:
@@ -373,6 +401,9 @@ method:
| INTEGER_P { $$ = jpiInteger; }
| NUMBER_P { $$ = jpiNumber; }
| STRINGFUNC_P { $$ = jpiStringFunc; }
+ | STR_LOWER_P { $$ = jpiStrLower; }
+ | STR_UPPER_P { $$ = jpiStrUpper; }
+ | STR_INITCAP_P { $$ = jpiStrInitcap; }
;
%%
diff --git a/src/backend/utils/adt/jsonpath_scan.l b/src/backend/utils/adt/jsonpath_scan.l
index 38c5841e879..e4fadcc2e69 100644
--- a/src/backend/utils/adt/jsonpath_scan.l
+++ b/src/backend/utils/adt/jsonpath_scan.l
@@ -413,8 +413,13 @@ static const JsonPathKeyword keywords[] = {
{4, true, TRUE_P, "true"},
{4, false, TYPE_P, "type"},
{4, false, WITH_P, "with"},
+ {5, false, STR_BTRIM_P, "btrim"},
{5, true, FALSE_P, "false"},
{5, false, FLOOR_P, "floor"},
+ {5, false, STR_LOWER_P, "lower"},
+ {5, false, STR_LTRIM_P, "ltrim"},
+ {5, false, STR_RTRIM_P, "rtrim"},
+ {5, false, STR_UPPER_P, "upper"},
{6, false, BIGINT_P, "bigint"},
{6, false, DOUBLE_P, "double"},
{6, false, EXISTS_P, "exists"},
@@ -425,13 +430,16 @@ static const JsonPathKeyword keywords[] = {
{7, false, BOOLEAN_P, "boolean"},
{7, false, CEILING_P, "ceiling"},
{7, false, DECIMAL_P, "decimal"},
+ {7, false, STR_INITCAP_P, "initcap"},
{7, false, INTEGER_P, "integer"},
+ {7, false, STR_REPLACE_P, "replace"},
{7, false, TIME_TZ_P, "time_tz"},
{7, false, UNKNOWN_P, "unknown"},
{8, false, DATETIME_P, "datetime"},
{8, false, KEYVALUE_P, "keyvalue"},
{9, false, TIMESTAMP_P, "timestamp"},
{10, false, LIKE_REGEX_P, "like_regex"},
+ {10, false, STR_SPLIT_PART_P, "split_part"},
{12, false, TIMESTAMP_TZ_P, "timestamp_tz"},
};
diff --git a/src/include/utils/jsonpath.h b/src/include/utils/jsonpath.h
index 6f529d74dcd..8d27206e242 100644
--- a/src/include/utils/jsonpath.h
+++ b/src/include/utils/jsonpath.h
@@ -115,6 +115,14 @@ typedef enum JsonPathItemType
jpiTimeTz, /* .time_tz() item method */
jpiTimestamp, /* .timestamp() item method */
jpiTimestampTz, /* .timestamp_tz() item method */
+ jpiStrReplace, /* .replace() item method */
+ jpiStrLower, /* .lower() item method */
+ jpiStrUpper, /* .upper() item method */
+ jpiStrLtrim, /* .ltrim() item method */
+ jpiStrRtrim, /* .rtrim() item method */
+ jpiStrBtrim, /* .btrim() item method */
+ jpiStrInitcap, /* .initcap() item method */
+ jpiStrSplitPart, /* .split_part() item method */
} JsonPathItemType;
/* XQuery regex mode flags for LIKE_REGEX predicate */
diff --git a/src/test/regress/expected/jsonb_jsonpath.out b/src/test/regress/expected/jsonb_jsonpath.out
index 4bcd4e91a29..afa6c4cb529 100644
--- a/src/test/regress/expected/jsonb_jsonpath.out
+++ b/src/test/regress/expected/jsonb_jsonpath.out
@@ -2723,6 +2723,387 @@ select jsonb_path_query('"2023-08-15 12:34:56"', '$.timestamp().string()');
(1 row)
rollback;
+-- test .ltrim()
+select jsonb_path_query('" hello "', '$.ltrim(" ")');
+ jsonb_path_query
+------------------
+ "hello "
+(1 row)
+
+select jsonb_path_query('" hello "', '$.ltrim()');
+ jsonb_path_query
+------------------
+ "hello "
+(1 row)
+
+select jsonb_path_query('"zzzytest"', '$.ltrim("xyz")');
+ jsonb_path_query
+------------------
+ "test"
+(1 row)
+
+select jsonb_path_query('null', '$.ltrim()');
+ERROR: jsonpath item method .ltrim() can only be applied to a string
+select jsonb_path_query('null', '$.ltrim()', silent => true);
+ jsonb_path_query
+------------------
+(0 rows)
+
+select jsonb_path_query('[]', '$.ltrim()');
+ jsonb_path_query
+------------------
+(0 rows)
+
+select jsonb_path_query('[]', 'strict $.ltrim()');
+ERROR: jsonpath item method .ltrim() can only be applied to a string
+select jsonb_path_query('{}', '$.ltrim()');
+ERROR: jsonpath item method .ltrim() can only be applied to a string
+select jsonb_path_query('[]', 'strict $.ltrim()', silent => true);
+ jsonb_path_query
+------------------
+(0 rows)
+
+select jsonb_path_query('{}', '$.ltrim()', silent => true);
+ jsonb_path_query
+------------------
+(0 rows)
+
+select jsonb_path_query('1.23', '$.ltrim()');
+ERROR: jsonpath item method .ltrim() can only be applied to a string
+select jsonb_path_query('"1.23"', '$.ltrim()');
+ jsonb_path_query
+------------------
+ "1.23"
+(1 row)
+
+select jsonb_path_query('"1.23aaa"', '$.ltrim()');
+ jsonb_path_query
+------------------
+ "1.23aaa"
+(1 row)
+
+select jsonb_path_query('1234', '$.ltrim()');
+ERROR: jsonpath item method .ltrim() can only be applied to a string
+select jsonb_path_query('true', '$.ltrim()');
+ERROR: jsonpath item method .ltrim() can only be applied to a string
+select jsonb_path_query('1234', '$.ltrim().type()');
+ERROR: jsonpath item method .ltrim() can only be applied to a string
+select jsonb_path_query('[2, true]', '$.ltrim()');
+ERROR: jsonpath item method .ltrim() can only be applied to a string
+select jsonb_path_query_array('[" maybe ", " yes", " no"]', '$[*].ltrim()');
+ jsonb_path_query_array
+--------------------------
+ ["maybe ", "yes", "no"]
+(1 row)
+
+select jsonb_path_query_array('[" maybe ", " yes", " no"]', '$[*].ltrim().type()');
+ jsonb_path_query_array
+--------------------------------
+ ["string", "string", "string"]
+(1 row)
+
+-- test .rtrim()
+select jsonb_path_query('" hello "', '$.rtrim(" ")');
+ jsonb_path_query
+------------------
+ " hello"
+(1 row)
+
+select jsonb_path_query('"testxxzx"', '$.rtrim("xyz")');
+ jsonb_path_query
+------------------
+ "test"
+(1 row)
+
+select jsonb_path_query('" hello "', '$.rtrim()');
+ jsonb_path_query
+------------------
+ " hello"
+(1 row)
+
+-- test .btrim()
+select jsonb_path_query('" hello "', '$.btrim(" ")');
+ jsonb_path_query
+------------------
+ "hello"
+(1 row)
+
+select jsonb_path_query('"xyxtrimyyx"', '$.btrim("xyz")');
+ jsonb_path_query
+------------------
+ "trim"
+(1 row)
+
+select jsonb_path_query('" hello "', '$.btrim()');
+ jsonb_path_query
+------------------
+ "hello"
+(1 row)
+
+-- test .lower()
+select jsonb_path_query('null', '$.lower()');
+ERROR: jsonpath item method .lower() can only be applied to a string
+select jsonb_path_query('null', '$.lower()', silent => true);
+ jsonb_path_query
+------------------
+(0 rows)
+
+select jsonb_path_query('[]', '$.lower()');
+ jsonb_path_query
+------------------
+(0 rows)
+
+select jsonb_path_query('[]', 'strict $.lower()');
+ERROR: jsonpath item method .lower() can only be applied to a string
+select jsonb_path_query('{}', '$.lower()');
+ERROR: jsonpath item method .lower() can only be applied to a string
+select jsonb_path_query('[]', 'strict $.lower()', silent => true);
+ jsonb_path_query
+------------------
+(0 rows)
+
+select jsonb_path_query('{}', '$.lower()', silent => true);
+ jsonb_path_query
+------------------
+(0 rows)
+
+select jsonb_path_query('1.23', '$.lower()');
+ERROR: jsonpath item method .lower() can only be applied to a string
+select jsonb_path_query('"1.23"', '$.lower()');
+ jsonb_path_query
+------------------
+ "1.23"
+(1 row)
+
+select jsonb_path_query('"1.23aaa"', '$.lower()');
+ jsonb_path_query
+------------------
+ "1.23aaa"
+(1 row)
+
+select jsonb_path_query('1234', '$.lower()');
+ERROR: jsonpath item method .lower() can only be applied to a string
+select jsonb_path_query('true', '$.lower()');
+ERROR: jsonpath item method .lower() can only be applied to a string
+select jsonb_path_query('1234', '$.lower().type()');
+ERROR: jsonpath item method .lower() can only be applied to a string
+select jsonb_path_query('[2, true]', '$.lower()');
+ERROR: jsonpath item method .lower() can only be applied to a string
+select jsonb_path_query_array('["maybe", "yes", "no"]', '$[*].lower()');
+ jsonb_path_query_array
+------------------------
+ ["maybe", "yes", "no"]
+(1 row)
+
+select jsonb_path_query_array('["maybe", "yes", "no"]', '$[*].lower().type()');
+ jsonb_path_query_array
+--------------------------------
+ ["string", "string", "string"]
+(1 row)
+
+-- test .upper()
+select jsonb_path_query('null', '$.upper()');
+ERROR: jsonpath item method .upper() can only be applied to a string
+select jsonb_path_query('null', '$.upper()', silent => true);
+ jsonb_path_query
+------------------
+(0 rows)
+
+select jsonb_path_query('[]', '$.upper()');
+ jsonb_path_query
+------------------
+(0 rows)
+
+select jsonb_path_query('[]', 'strict $.upper()');
+ERROR: jsonpath item method .upper() can only be applied to a string
+select jsonb_path_query('{}', '$.upper()');
+ERROR: jsonpath item method .upper() can only be applied to a string
+select jsonb_path_query('[]', 'strict $.upper()', silent => true);
+ jsonb_path_query
+------------------
+(0 rows)
+
+select jsonb_path_query('{}', '$.upper()', silent => true);
+ jsonb_path_query
+------------------
+(0 rows)
+
+select jsonb_path_query('1.23', '$.upper()');
+ERROR: jsonpath item method .upper() can only be applied to a string
+select jsonb_path_query('"1.23"', '$.upper()');
+ jsonb_path_query
+------------------
+ "1.23"
+(1 row)
+
+select jsonb_path_query('"1.23aaa"', '$.upper()');
+ jsonb_path_query
+------------------
+ "1.23AAA"
+(1 row)
+
+select jsonb_path_query('1234', '$.upper()');
+ERROR: jsonpath item method .upper() can only be applied to a string
+select jsonb_path_query('true', '$.upper()');
+ERROR: jsonpath item method .upper() can only be applied to a string
+select jsonb_path_query('1234', '$.upper().type()');
+ERROR: jsonpath item method .upper() can only be applied to a string
+select jsonb_path_query('[2, true]', '$.upper()');
+ERROR: jsonpath item method .upper() can only be applied to a string
+select jsonb_path_query_array('["maybe", "yes", "no"]', '$[*].upper()');
+ jsonb_path_query_array
+------------------------
+ ["MAYBE", "YES", "NO"]
+(1 row)
+
+select jsonb_path_query_array('["maybe", "yes", "no"]', '$[*].upper().type()');
+ jsonb_path_query_array
+--------------------------------
+ ["string", "string", "string"]
+(1 row)
+
+-- test .initcap()
+select jsonb_path_query('null', '$.initcap()');
+ERROR: jsonpath item method .initcap() can only be applied to a string
+select jsonb_path_query('null', '$.initcap()', silent => true);
+ jsonb_path_query
+------------------
+(0 rows)
+
+select jsonb_path_query('[]', '$.initcap()');
+ jsonb_path_query
+------------------
+(0 rows)
+
+select jsonb_path_query('[]', 'strict $.initcap()');
+ERROR: jsonpath item method .initcap() can only be applied to a string
+select jsonb_path_query('{}', '$.initcap()');
+ERROR: jsonpath item method .initcap() can only be applied to a string
+select jsonb_path_query('[]', 'strict $.initcap()', silent => true);
+ jsonb_path_query
+------------------
+(0 rows)
+
+select jsonb_path_query('{}', '$.initcap()', silent => true);
+ jsonb_path_query
+------------------
+(0 rows)
+
+select jsonb_path_query('1.23', '$.initcap()');
+ERROR: jsonpath item method .initcap() can only be applied to a string
+select jsonb_path_query('"1.23"', '$.initcap()');
+ jsonb_path_query
+------------------
+ "1.23"
+(1 row)
+
+select jsonb_path_query('"1.23aaa"', '$.initcap()');
+ jsonb_path_query
+------------------
+ "1.23aaa"
+(1 row)
+
+select jsonb_path_query('1234', '$.initcap()');
+ERROR: jsonpath item method .initcap() can only be applied to a string
+select jsonb_path_query('true', '$.initcap()');
+ERROR: jsonpath item method .initcap() can only be applied to a string
+select jsonb_path_query('1234', '$.initcap().type()');
+ERROR: jsonpath item method .initcap() can only be applied to a string
+select jsonb_path_query('[2, true]', '$.initcap()');
+ERROR: jsonpath item method .initcap() can only be applied to a string
+select jsonb_path_query('["maybe yes", "probably no"]', '$.initcap()');
+ jsonb_path_query
+------------------
+ "Maybe Yes"
+ "Probably No"
+(2 rows)
+
+-- Test .replace()
+select jsonb_path_query('null', '$.replace("x", "bye")');
+ERROR: jsonpath item method .replace() can only be applied to a string
+select jsonb_path_query('null', '$.replace("x", "bye")', silent => true);
+ jsonb_path_query
+------------------
+(0 rows)
+
+select jsonb_path_query('["x", "y", "z"]', '$.replace("x", "bye")');
+ jsonb_path_query
+------------------
+ "bye"
+ "y"
+ "z"
+(3 rows)
+
+select jsonb_path_query('{}', '$.replace("x", "bye")');
+ERROR: jsonpath item method .replace() can only be applied to a string
+select jsonb_path_query('[]', 'strict $.replace("x", "bye")', silent => true);
+ jsonb_path_query
+------------------
+(0 rows)
+
+select jsonb_path_query('{}', '$.replace("x", "bye")', silent => true);
+ jsonb_path_query
+------------------
+(0 rows)
+
+select jsonb_path_query('1.23', '$.replace("x", "bye")');
+ERROR: jsonpath item method .replace() can only be applied to a string
+select jsonb_path_query('"hello world"', '$.replace("hello","bye")');
+ jsonb_path_query
+------------------
+ "bye world"
+(1 row)
+
+select jsonb_path_query('"hello world"', '$.replace("hello","bye") starts with "bye"');
+ jsonb_path_query
+------------------
+ true
+(1 row)
+
+-- Test .split_part()
+select jsonb_path_query('"abc~@~def~@~ghi"', '$.split_part("~@~", 2)');
+ jsonb_path_query
+------------------
+ "def"
+(1 row)
+
+select jsonb_path_query('"abc,def,ghi,jkl"', '$.split_part(",", -2)');
+ jsonb_path_query
+------------------
+ "ghi"
+(1 row)
+
+-- Test string methods play nicely together
+select jsonb_path_query('"hello world"', '$.replace("hello","bye").upper()');
+ jsonb_path_query
+------------------
+ "BYE WORLD"
+(1 row)
+
+select jsonb_path_query('"hElLo WorlD"', '$.lower().upper().lower().replace("hello","bye")');
+ jsonb_path_query
+------------------
+ "bye world"
+(1 row)
+
+select jsonb_path_query('"hElLo WorlD"', '$.upper().lower().upper().replace("HELLO", "BYE")');
+ jsonb_path_query
+------------------
+ "BYE WORLD"
+(1 row)
+
+select jsonb_path_query('"hElLo WorlD"', '$.lower().upper().lower().replace("hello","bye") starts with "bye"');
+ jsonb_path_query
+------------------
+ true
+(1 row)
+
+select jsonb_path_query('" hElLo WorlD "', '$.btrim().lower().upper().lower().replace("hello","bye") starts with "bye"');
+ jsonb_path_query
+------------------
+ true
+(1 row)
+
-- Test .time()
select jsonb_path_query('null', '$.time()');
ERROR: jsonpath item method .time() can only be applied to a string
diff --git a/src/test/regress/expected/jsonpath.out b/src/test/regress/expected/jsonpath.out
index fd9bd755f52..ea971e79854 100644
--- a/src/test/regress/expected/jsonpath.out
+++ b/src/test/regress/expected/jsonpath.out
@@ -435,6 +435,192 @@ select '$.string()'::jsonpath;
$.string()
(1 row)
+select '$.replace("hello","bye")'::jsonpath;
+ jsonpath
+--------------------------
+ $.replace("hello","bye")
+(1 row)
+
+select '$.lower()'::jsonpath;
+ jsonpath
+-----------
+ $.lower()
+(1 row)
+
+select '$.upper()'::jsonpath;
+ jsonpath
+-----------
+ $.upper()
+(1 row)
+
+select '$.lower().upper().lower().replace("hello","bye")'::jsonpath;
+ jsonpath
+--------------------------------------------------
+ $.lower().upper().lower().replace("hello","bye")
+(1 row)
+
+select '$.ltrim()'::jsonpath;
+ jsonpath
+-----------
+ $.ltrim()
+(1 row)
+
+select '$.ltrim("xyz")'::jsonpath;
+ jsonpath
+----------------
+ $.ltrim("xyz")
+(1 row)
+
+select '$.rtrim()'::jsonpath;
+ jsonpath
+-----------
+ $.rtrim()
+(1 row)
+
+select '$.rtrim("xyz")'::jsonpath;
+ jsonpath
+----------------
+ $.rtrim("xyz")
+(1 row)
+
+select '$.btrim()'::jsonpath;
+ jsonpath
+-----------
+ $.btrim()
+(1 row)
+
+select '$.btrim("xyz")'::jsonpath;
+ jsonpath
+----------------
+ $.btrim("xyz")
+(1 row)
+
+select '$.initcap()'::jsonpath;
+ jsonpath
+-------------
+ $.initcap()
+(1 row)
+
+select '$.split_part("~@~", 2)'::jsonpath;
+ jsonpath
+-----------------------
+ $.split_part("~@~",2)
+(1 row)
+
+-- Parse errors
+select '$.replace("hello")'::jsonpath;
+ERROR: syntax error at or near ")" of jsonpath input
+LINE 1: select '$.replace("hello")'::jsonpath;
+ ^
+select '$.replace()'::jsonpath;
+ERROR: syntax error at or near ")" of jsonpath input
+LINE 1: select '$.replace()'::jsonpath;
+ ^
+select '$.replace("hello","bye","extra")'::jsonpath;
+ERROR: syntax error at or near "," of jsonpath input
+LINE 1: select '$.replace("hello","bye","extra")'::jsonpath;
+ ^
+select '$.split_part("~@~")'::jsonpath;
+ERROR: syntax error at or near ")" of jsonpath input
+LINE 1: select '$.split_part("~@~")'::jsonpath;
+ ^
+select '$.split_part()'::jsonpath;
+ERROR: syntax error at or near ")" of jsonpath input
+LINE 1: select '$.split_part()'::jsonpath;
+ ^
+select '$.split_part("~@~", "hi")'::jsonpath;
+ERROR: syntax error at or near """ of jsonpath input
+LINE 1: select '$.split_part("~@~", "hi")'::jsonpath;
+ ^
+select '$.split_part("~@~", 2, "extra")'::jsonpath;
+ERROR: syntax error at or near "," of jsonpath input
+LINE 1: select '$.split_part("~@~", 2, "extra")'::jsonpath;
+ ^
+select '$.lower("hi")'::jsonpath;
+ERROR: syntax error at or near """ of jsonpath input
+LINE 1: select '$.lower("hi")'::jsonpath;
+ ^
+select '$.upper("hi")'::jsonpath;
+ERROR: syntax error at or near """ of jsonpath input
+LINE 1: select '$.upper("hi")'::jsonpath;
+ ^
+select '$.initcap("hi")'::jsonpath;
+ERROR: syntax error at or near """ of jsonpath input
+LINE 1: select '$.initcap("hi")'::jsonpath;
+ ^
+select '$.ltrim(42)'::jsonpath;
+ERROR: syntax error at or near "42" of jsonpath input
+LINE 1: select '$.ltrim(42)'::jsonpath;
+ ^
+select '$.ltrim("x", "y")'::jsonpath;
+ERROR: syntax error at or near "," of jsonpath input
+LINE 1: select '$.ltrim("x", "y")'::jsonpath;
+ ^
+select '$.rtrim(42)'::jsonpath;
+ERROR: syntax error at or near "42" of jsonpath input
+LINE 1: select '$.rtrim(42)'::jsonpath;
+ ^
+select '$.rtrim("x", "y")'::jsonpath;
+ERROR: syntax error at or near "," of jsonpath input
+LINE 1: select '$.rtrim("x", "y")'::jsonpath;
+ ^
+select '$.trim(42)'::jsonpath;
+ERROR: syntax error at or near "(" of jsonpath input
+LINE 1: select '$.trim(42)'::jsonpath;
+ ^
+select '$.trim("x", "y")'::jsonpath;
+ERROR: syntax error at or near "(" of jsonpath input
+LINE 1: select '$.trim("x", "y")'::jsonpath;
+ ^
+-- Verify method keywords work as object key names
+select '$.lower'::jsonpath;
+ jsonpath
+-----------
+ $."lower"
+(1 row)
+
+select '$.upper'::jsonpath;
+ jsonpath
+-----------
+ $."upper"
+(1 row)
+
+select '$.initcap'::jsonpath;
+ jsonpath
+-------------
+ $."initcap"
+(1 row)
+
+select '$.replace'::jsonpath;
+ jsonpath
+-------------
+ $."replace"
+(1 row)
+
+select '$.split_part'::jsonpath;
+ jsonpath
+----------------
+ $."split_part"
+(1 row)
+
+select '$.ltrim'::jsonpath;
+ jsonpath
+-----------
+ $."ltrim"
+(1 row)
+
+select '$.rtrim'::jsonpath;
+ jsonpath
+-----------
+ $."rtrim"
+(1 row)
+
+select '$.btrim'::jsonpath;
+ jsonpath
+-----------
+ $."btrim"
+(1 row)
+
select '$.time()'::jsonpath;
jsonpath
----------
diff --git a/src/test/regress/expected/sqljson_queryfuncs.out b/src/test/regress/expected/sqljson_queryfuncs.out
index d1b4b8d99f4..57e52e963f6 100644
--- a/src/test/regress/expected/sqljson_queryfuncs.out
+++ b/src/test/regress/expected/sqljson_queryfuncs.out
@@ -1147,7 +1147,7 @@ INSERT INTO test_jsonb_constraints VALUES ('{"a": 10}', 1);
ERROR: new row for relation "test_jsonb_constraints" violates check constraint "test_jsonb_constraint4"
DETAIL: Failing row contains ({"a": 10}, 1, [1, 2]).
DROP TABLE test_jsonb_constraints;
--- Test mutabilily of query functions
+-- Test mutability of query functions
CREATE TABLE test_jsonb_mutability(js jsonb, b int);
CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$'));
CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.a[0]'));
@@ -1272,6 +1272,14 @@ CREATE INDEX ON test_jsonb_mutability (JSON_VALUE(js, '$' DEFAULT random()::int
ERROR: functions in index expression must be marked IMMUTABLE
LINE 1: CREATE INDEX ON test_jsonb_mutability (JSON_VALUE(js, '$' DE...
^
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.rtrim()'));
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.ltrim()'));
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.btrim()'));
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.lower()'));
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.upper()'));
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.initcap()'));
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.replace("hello", "bye")'));
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.split_part(",", 2)'));
-- DEFAULT expression
CREATE OR REPLACE FUNCTION ret_setint() RETURNS SETOF integer AS
$$
diff --git a/src/test/regress/sql/jsonb_jsonpath.sql b/src/test/regress/sql/jsonb_jsonpath.sql
index 3e8929a5269..d3a38c57791 100644
--- a/src/test/regress/sql/jsonb_jsonpath.sql
+++ b/src/test/regress/sql/jsonb_jsonpath.sql
@@ -623,6 +623,112 @@ select jsonb_path_query('"2023-08-15 12:34:56 +5:30"', '$.timestamp_tz().string(
select jsonb_path_query('"2023-08-15 12:34:56"', '$.timestamp().string()');
rollback;
+-- test .ltrim()
+select jsonb_path_query('" hello "', '$.ltrim(" ")');
+select jsonb_path_query('" hello "', '$.ltrim()');
+select jsonb_path_query('"zzzytest"', '$.ltrim("xyz")');
+select jsonb_path_query('null', '$.ltrim()');
+select jsonb_path_query('null', '$.ltrim()', silent => true);
+select jsonb_path_query('[]', '$.ltrim()');
+select jsonb_path_query('[]', 'strict $.ltrim()');
+select jsonb_path_query('{}', '$.ltrim()');
+select jsonb_path_query('[]', 'strict $.ltrim()', silent => true);
+select jsonb_path_query('{}', '$.ltrim()', silent => true);
+select jsonb_path_query('1.23', '$.ltrim()');
+select jsonb_path_query('"1.23"', '$.ltrim()');
+select jsonb_path_query('"1.23aaa"', '$.ltrim()');
+select jsonb_path_query('1234', '$.ltrim()');
+select jsonb_path_query('true', '$.ltrim()');
+select jsonb_path_query('1234', '$.ltrim().type()');
+select jsonb_path_query('[2, true]', '$.ltrim()');
+select jsonb_path_query_array('[" maybe ", " yes", " no"]', '$[*].ltrim()');
+select jsonb_path_query_array('[" maybe ", " yes", " no"]', '$[*].ltrim().type()');
+
+-- test .rtrim()
+select jsonb_path_query('" hello "', '$.rtrim(" ")');
+select jsonb_path_query('"testxxzx"', '$.rtrim("xyz")');
+select jsonb_path_query('" hello "', '$.rtrim()');
+
+-- test .btrim()
+select jsonb_path_query('" hello "', '$.btrim(" ")');
+select jsonb_path_query('"xyxtrimyyx"', '$.btrim("xyz")');
+select jsonb_path_query('" hello "', '$.btrim()');
+
+-- test .lower()
+select jsonb_path_query('null', '$.lower()');
+select jsonb_path_query('null', '$.lower()', silent => true);
+select jsonb_path_query('[]', '$.lower()');
+select jsonb_path_query('[]', 'strict $.lower()');
+select jsonb_path_query('{}', '$.lower()');
+select jsonb_path_query('[]', 'strict $.lower()', silent => true);
+select jsonb_path_query('{}', '$.lower()', silent => true);
+select jsonb_path_query('1.23', '$.lower()');
+select jsonb_path_query('"1.23"', '$.lower()');
+select jsonb_path_query('"1.23aaa"', '$.lower()');
+select jsonb_path_query('1234', '$.lower()');
+select jsonb_path_query('true', '$.lower()');
+select jsonb_path_query('1234', '$.lower().type()');
+select jsonb_path_query('[2, true]', '$.lower()');
+select jsonb_path_query_array('["maybe", "yes", "no"]', '$[*].lower()');
+select jsonb_path_query_array('["maybe", "yes", "no"]', '$[*].lower().type()');
+
+-- test .upper()
+select jsonb_path_query('null', '$.upper()');
+select jsonb_path_query('null', '$.upper()', silent => true);
+select jsonb_path_query('[]', '$.upper()');
+select jsonb_path_query('[]', 'strict $.upper()');
+select jsonb_path_query('{}', '$.upper()');
+select jsonb_path_query('[]', 'strict $.upper()', silent => true);
+select jsonb_path_query('{}', '$.upper()', silent => true);
+select jsonb_path_query('1.23', '$.upper()');
+select jsonb_path_query('"1.23"', '$.upper()');
+select jsonb_path_query('"1.23aaa"', '$.upper()');
+select jsonb_path_query('1234', '$.upper()');
+select jsonb_path_query('true', '$.upper()');
+select jsonb_path_query('1234', '$.upper().type()');
+select jsonb_path_query('[2, true]', '$.upper()');
+select jsonb_path_query_array('["maybe", "yes", "no"]', '$[*].upper()');
+select jsonb_path_query_array('["maybe", "yes", "no"]', '$[*].upper().type()');
+
+-- test .initcap()
+select jsonb_path_query('null', '$.initcap()');
+select jsonb_path_query('null', '$.initcap()', silent => true);
+select jsonb_path_query('[]', '$.initcap()');
+select jsonb_path_query('[]', 'strict $.initcap()');
+select jsonb_path_query('{}', '$.initcap()');
+select jsonb_path_query('[]', 'strict $.initcap()', silent => true);
+select jsonb_path_query('{}', '$.initcap()', silent => true);
+select jsonb_path_query('1.23', '$.initcap()');
+select jsonb_path_query('"1.23"', '$.initcap()');
+select jsonb_path_query('"1.23aaa"', '$.initcap()');
+select jsonb_path_query('1234', '$.initcap()');
+select jsonb_path_query('true', '$.initcap()');
+select jsonb_path_query('1234', '$.initcap().type()');
+select jsonb_path_query('[2, true]', '$.initcap()');
+select jsonb_path_query('["maybe yes", "probably no"]', '$.initcap()');
+
+-- Test .replace()
+select jsonb_path_query('null', '$.replace("x", "bye")');
+select jsonb_path_query('null', '$.replace("x", "bye")', silent => true);
+select jsonb_path_query('["x", "y", "z"]', '$.replace("x", "bye")');
+select jsonb_path_query('{}', '$.replace("x", "bye")');
+select jsonb_path_query('[]', 'strict $.replace("x", "bye")', silent => true);
+select jsonb_path_query('{}', '$.replace("x", "bye")', silent => true);
+select jsonb_path_query('1.23', '$.replace("x", "bye")');
+select jsonb_path_query('"hello world"', '$.replace("hello","bye")');
+select jsonb_path_query('"hello world"', '$.replace("hello","bye") starts with "bye"');
+
+-- Test .split_part()
+select jsonb_path_query('"abc~@~def~@~ghi"', '$.split_part("~@~", 2)');
+select jsonb_path_query('"abc,def,ghi,jkl"', '$.split_part(",", -2)');
+
+-- Test string methods play nicely together
+select jsonb_path_query('"hello world"', '$.replace("hello","bye").upper()');
+select jsonb_path_query('"hElLo WorlD"', '$.lower().upper().lower().replace("hello","bye")');
+select jsonb_path_query('"hElLo WorlD"', '$.upper().lower().upper().replace("HELLO", "BYE")');
+select jsonb_path_query('"hElLo WorlD"', '$.lower().upper().lower().replace("hello","bye") starts with "bye"');
+select jsonb_path_query('" hElLo WorlD "', '$.btrim().lower().upper().lower().replace("hello","bye") starts with "bye"');
+
-- Test .time()
select jsonb_path_query('null', '$.time()');
select jsonb_path_query('true', '$.time()');
diff --git a/src/test/regress/sql/jsonpath.sql b/src/test/regress/sql/jsonpath.sql
index 61a5270d4e8..44178d8b45a 100644
--- a/src/test/regress/sql/jsonpath.sql
+++ b/src/test/regress/sql/jsonpath.sql
@@ -78,6 +78,47 @@ select '$.boolean()'::jsonpath;
select '$.date()'::jsonpath;
select '$.decimal(4,2)'::jsonpath;
select '$.string()'::jsonpath;
+select '$.replace("hello","bye")'::jsonpath;
+select '$.lower()'::jsonpath;
+select '$.upper()'::jsonpath;
+select '$.lower().upper().lower().replace("hello","bye")'::jsonpath;
+select '$.ltrim()'::jsonpath;
+select '$.ltrim("xyz")'::jsonpath;
+select '$.rtrim()'::jsonpath;
+select '$.rtrim("xyz")'::jsonpath;
+select '$.btrim()'::jsonpath;
+select '$.btrim("xyz")'::jsonpath;
+select '$.initcap()'::jsonpath;
+select '$.split_part("~@~", 2)'::jsonpath;
+
+-- Parse errors
+select '$.replace("hello")'::jsonpath;
+select '$.replace()'::jsonpath;
+select '$.replace("hello","bye","extra")'::jsonpath;
+select '$.split_part("~@~")'::jsonpath;
+select '$.split_part()'::jsonpath;
+select '$.split_part("~@~", "hi")'::jsonpath;
+select '$.split_part("~@~", 2, "extra")'::jsonpath;
+select '$.lower("hi")'::jsonpath;
+select '$.upper("hi")'::jsonpath;
+select '$.initcap("hi")'::jsonpath;
+select '$.ltrim(42)'::jsonpath;
+select '$.ltrim("x", "y")'::jsonpath;
+select '$.rtrim(42)'::jsonpath;
+select '$.rtrim("x", "y")'::jsonpath;
+select '$.trim(42)'::jsonpath;
+select '$.trim("x", "y")'::jsonpath;
+
+-- Verify method keywords work as object key names
+select '$.lower'::jsonpath;
+select '$.upper'::jsonpath;
+select '$.initcap'::jsonpath;
+select '$.replace'::jsonpath;
+select '$.split_part'::jsonpath;
+select '$.ltrim'::jsonpath;
+select '$.rtrim'::jsonpath;
+select '$.btrim'::jsonpath;
+
select '$.time()'::jsonpath;
select '$.time(6)'::jsonpath;
select '$.time_tz()'::jsonpath;
diff --git a/src/test/regress/sql/sqljson_queryfuncs.sql b/src/test/regress/sql/sqljson_queryfuncs.sql
index a5d5e256d7f..d218b44ea47 100644
--- a/src/test/regress/sql/sqljson_queryfuncs.sql
+++ b/src/test/regress/sql/sqljson_queryfuncs.sql
@@ -357,7 +357,7 @@ INSERT INTO test_jsonb_constraints VALUES ('{"a": 10}', 1);
DROP TABLE test_jsonb_constraints;
--- Test mutabilily of query functions
+-- Test mutability of query functions
CREATE TABLE test_jsonb_mutability(js jsonb, b int);
CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$'));
CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.a[0]'));
@@ -402,6 +402,15 @@ CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$[1, 0 to $.a ? (@.dateti
CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$[1, $.a ? (@.datetime("HH:MI") == $x)]' PASSING '12:34'::time AS x));
CREATE INDEX ON test_jsonb_mutability (JSON_VALUE(js, '$' DEFAULT random()::int ON ERROR));
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.rtrim()'));
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.ltrim()'));
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.btrim()'));
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.lower()'));
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.upper()'));
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.initcap()'));
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.replace("hello", "bye")'));
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.split_part(",", 2)'));
+
-- DEFAULT expression
CREATE OR REPLACE FUNCTION ret_setint() RETURNS SETOF integer AS
$$
--
2.43.0
