PATCH: jsonpath string methods: lower, upper, initcap, l/r/btrim, replace, split_part

public inbox for [email protected]  
help / color / mirror / Atom feed

From: Florents Tselai <[email protected]>
To: pgsql-hackers <[email protected]>
Subject: PATCH: jsonpath string methods: lower, upper, initcap, l/r/btrim, replace, split_part
Date: Wed, 25 Sep 2024 21:17:20 +0300
Message-ID: <CA+v5N40sJF39m0v7h=QN86zGp0CUf9F1WKasnZy9nNVj_VhCZQ@mail.gmail.com> (raw)

Hello hackers,

This patch is a follow-up and generalization to [0].

It adds the following jsonpath methods:  lower, upper, initcap, l/r/btrim,
replace, split_part.

It makes jsonpath able to support expressions like these:

select jsonb_path_query('"   hElLo WorlD "',
 '$.btrim().lower().upper().lower().replace("hello","bye") starts with
"bye"');
select jsonb_path_query('"abc~@~def~@~ghi"', '$.split_part("~@~", 2)')

They, of course, forward their implementation to the internal
pg_proc-registered function.

As a first wip/poc I've picked the functions I typically need to clean up
JSON data.
I've also added a README.jsonpath with documentation on how to add a new
jsonpath method.
If I had this available when I started, it would have saved me some time.
So, I am leaving it here for the next hacker.

This patch is not particularly intrusive to existing code:
Afaict, the only struct I've touched is JsonPathParseItem , where I added {
JsonPathParseItem *arg0, *arg1; } method_args.
Up until now, most of the jsonpath methods that accept arguments rely on
left/right operands,
which works, but it could be more convenient for future more complex
methods.
I've also added the appropriate jspGetArgX(JsonPathItem *v, JsonPathItem
*a).

Open items
- What happens if the jsonpath standard adds a new method by the same name?
A.D. mentioned this in [0] with the proposal of having a prefix like pg_ or
initial-upper letter.
- Still using the default collation like the rest of the jsonpath code.
- documentation N/A yet
- I do realize that the process of adding a new method sketches an
imaginary.
CREATE JSONPATH FUNCTION. This has been on the back of my mind for some
time now,
but I can't say I have an action plan for this yet.

GitHub PR view if you prefer:
https://github.com/Florents-Tselai/postgres/pull/18

[0]
https://www.postgresql.org/message-id/flat/185BF814-9225-46DB-B1A1-6468CF2C8B63%40justatheory.com#18...

All the best,
Flo


Attachments:

  [application/octet-stream] v1-0001-This-patch-adds-the-following-string-processing-m.patch (43.1K, 3-v1-0001-This-patch-adds-the-following-string-processing-m.patch)
  download | inline diff:
From 3cf338d0e894a39884a2c26f819b173781a2f5c2 Mon Sep 17 00:00:00 2001
From: Florents Tselai <[email protected]>
Date: Wed, 25 Sep 2024 21:10:07 +0300
Subject: [PATCH v1] This patch adds the following string processing methods to
 jsonpath: * l/r/btrim() * lower(), upper(), initcap() * replace(),
 split_part()

The actual implementation of these, is routed to the standard string processing functions.

Also adds a jsonpath.README documenting the process of adding a new method,
for future reference.
---
 src/backend/utils/adt/jsonpath.c             | 176 ++++++++-
 src/backend/utils/adt/jsonpath_exec.c        | 189 +++++++++
 src/backend/utils/adt/jsonpath_gram.y        |  71 +++-
 src/backend/utils/adt/jsonpath_scan.l        |   8 +
 src/include/utils/jsonpath.h                 |  22 ++
 src/test/regress/expected/jsonb_jsonpath.out | 393 +++++++++++++++++++
 src/test/regress/expected/jsonpath.out       |  72 ++++
 src/test/regress/sql/jsonb_jsonpath.sql      | 108 +++++
 src/test/regress/sql/jsonpath.sql            |  13 +
 9 files changed, 1048 insertions(+), 4 deletions(-)

diff --git a/src/backend/utils/adt/jsonpath.c b/src/backend/utils/adt/jsonpath.c
index 0f691bc5f0..887d95d13f 100644
--- a/src/backend/utils/adt/jsonpath.c
+++ b/src/backend/utils/adt/jsonpath.c
@@ -326,6 +326,38 @@ flattenJsonPathParseItem(StringInfo buf, int *result, struct Node *escontext,
 				*(int32 *) (buf->data + right) = chld - pos;
 			}
 			break;
+		case jpiReplaceFunc:
+		case jpiStrSplitPartFunc:
+		{
+			{
+				/*
+				 * First, reserve place for left/right arg's positions, then
+				 * record both args and sets actual position in reserved
+				 * places.
+				 */
+				int32		arg0 = reserveSpaceForItemPointer(buf);
+				int32		arg1 = reserveSpaceForItemPointer(buf);
+
+				if (!item->value.method_args.arg0)
+					chld = pos;
+				else if (!flattenJsonPathParseItem(buf, &chld, escontext,
+												   item->value.method_args.arg0,
+												   nestingLevel + argNestingLevel,
+												   insideArraySubscript))
+					return false;
+				*(int32 *) (buf->data + arg0) = chld - pos;
+
+				if (!item->value.method_args.arg1)
+					chld = pos;
+				else if (!flattenJsonPathParseItem(buf, &chld, escontext,
+												   item->value.method_args.arg1,
+												   nestingLevel + argNestingLevel,
+												   insideArraySubscript))
+					return false;
+				*(int32 *) (buf->data + arg1) = chld - pos;
+			}
+		}
+		break;
 		case jpiLikeRegex:
 			{
 				int32		offs;
@@ -362,6 +394,9 @@ flattenJsonPathParseItem(StringInfo buf, int *result, struct Node *escontext,
 		case jpiTimeTz:
 		case jpiTimestamp:
 		case jpiTimestampTz:
+		case jpiStrLtrimFunc:
+		case jpiStrRtrimFunc:
+		case jpiStrBtrimFunc:
 			{
 				int32		arg = reserveSpaceForItemPointer(buf);
 
@@ -457,6 +492,9 @@ flattenJsonPathParseItem(StringInfo buf, int *result, struct Node *escontext,
 		case jpiInteger:
 		case jpiNumber:
 		case jpiStringFunc:
+		case jpiStrLowerFunc:
+		case jpiStrUpperFunc:
+		case jpiStrInitcapFunc:
 			break;
 		default:
 			elog(ERROR, "unrecognized jsonpath item type: %d", item->type);
@@ -831,6 +869,72 @@ printJsonPathItem(StringInfo buf, JsonPathItem *v, bool inKey,
 			}
 			appendStringInfoChar(buf, ')');
 			break;
+		case jpiReplaceFunc:
+			appendStringInfoString(buf, ".replace(");
+			if (v->content.method_args.arg0)
+			{
+				jspGetArg0(v, &elem);
+				printJsonPathItem(buf, &elem, false, false);
+			}
+			if (v->content.method_args.arg1)
+			{
+				appendStringInfoChar(buf, ',');
+				jspGetArg1(v, &elem);
+				printJsonPathItem(buf, &elem, false, false);
+			}
+			appendStringInfoChar(buf, ')');
+			break;
+		case jpiStrSplitPartFunc:
+			appendStringInfoString(buf, ".split_part(");
+			if (v->content.method_args.arg0)
+			{
+				jspGetArg0(v, &elem);
+				printJsonPathItem(buf, &elem, false, false);
+			}
+			if (v->content.method_args.arg1)
+			{
+				appendStringInfoChar(buf, ',');
+				jspGetArg1(v, &elem);
+				printJsonPathItem(buf, &elem, false, false);
+			}
+			appendStringInfoChar(buf, ')');
+			break;
+		case jpiStrLowerFunc:
+			appendStringInfoString(buf, ".lower()");
+			break;
+		case jpiStrUpperFunc:
+			appendStringInfoString(buf, ".upper()");
+			break;
+		case jpiStrInitcapFunc:
+			appendStringInfoString(buf, ".initcap()");
+			break;
+		case jpiStrLtrimFunc:
+			appendStringInfoString(buf, ".ltrim(");
+			if (v->content.arg)
+			{
+				jspGetArg(v, &elem);
+				printJsonPathItem(buf, &elem, false, false);
+			}
+			appendStringInfoChar(buf, ')');
+			break;
+		case jpiStrRtrimFunc:
+			appendStringInfoString(buf, ".rtrim(");
+			if (v->content.arg)
+			{
+				jspGetArg(v, &elem);
+				printJsonPathItem(buf, &elem, false, false);
+			}
+			appendStringInfoChar(buf, ')');
+			break;
+		case jpiStrBtrimFunc:
+			appendStringInfoString(buf, ".btrim(");
+			if (v->content.arg)
+			{
+				jspGetArg(v, &elem);
+				printJsonPathItem(buf, &elem, false, false);
+			}
+			appendStringInfoChar(buf, ')');
+			break;
 		default:
 			elog(ERROR, "unrecognized jsonpath item type: %d", v->type);
 	}
@@ -906,6 +1010,12 @@ jspOperationName(JsonPathItemType type)
 			return "number";
 		case jpiStringFunc:
 			return "string";
+		case jpiReplaceFunc:
+			return "replace";
+		case jpiStrLowerFunc:
+			return "lower";
+		case jpiStrUpperFunc:
+			return "upper";
 		case jpiTime:
 			return "time";
 		case jpiTimeTz:
@@ -914,6 +1024,16 @@ jspOperationName(JsonPathItemType type)
 			return "timestamp";
 		case jpiTimestampTz:
 			return "timestamp_tz";
+		case jpiStrLtrimFunc:
+			return "ltrim";
+		case jpiStrRtrimFunc:
+			return "rtrim";
+		case jpiStrBtrimFunc:
+			return "btrim";
+		case jpiStrInitcapFunc:
+			return "initcap";
+		case jpiStrSplitPartFunc:
+			return "split_part";
 		default:
 			elog(ERROR, "unrecognized jsonpath item type: %d", type);
 			return NULL;
@@ -1016,6 +1136,9 @@ jspInitByBuffer(JsonPathItem *v, char *base, int32 pos)
 		case jpiInteger:
 		case jpiNumber:
 		case jpiStringFunc:
+		case jpiStrLowerFunc:
+		case jpiStrUpperFunc:
+		case jpiStrInitcapFunc:
 			break;
 		case jpiString:
 		case jpiKey:
@@ -1044,6 +1167,11 @@ jspInitByBuffer(JsonPathItem *v, char *base, int32 pos)
 			read_int32(v->content.args.left, base, pos);
 			read_int32(v->content.args.right, base, pos);
 			break;
+		case jpiReplaceFunc:
+		case jpiStrSplitPartFunc:
+			read_int32(v->content.method_args.arg0, base, pos);
+			read_int32(v->content.method_args.arg1, base, pos);
+		break;
 		case jpiNot:
 		case jpiIsUnknown:
 		case jpiExists:
@@ -1055,6 +1183,9 @@ jspInitByBuffer(JsonPathItem *v, char *base, int32 pos)
 		case jpiTimeTz:
 		case jpiTimestamp:
 		case jpiTimestampTz:
+		case jpiStrLtrimFunc:
+		case jpiStrRtrimFunc:
+		case jpiStrBtrimFunc:
 			read_int32(v->content.arg, base, pos);
 			break;
 		case jpiIndexArray:
@@ -1090,7 +1221,10 @@ jspGetArg(JsonPathItem *v, JsonPathItem *a)
 		   v->type == jpiTime ||
 		   v->type == jpiTimeTz ||
 		   v->type == jpiTimestamp ||
-		   v->type == jpiTimestampTz);
+		   v->type == jpiTimestampTz ||
+		   v->type == jpiStrLtrimFunc ||
+		   v->type == jpiStrRtrimFunc ||
+		   v->type == jpiStrBtrimFunc);
 
 	jspInitByBuffer(a, v->base, v->content.arg);
 }
@@ -1149,10 +1283,18 @@ jspGetNext(JsonPathItem *v, JsonPathItem *a)
 			   v->type == jpiInteger ||
 			   v->type == jpiNumber ||
 			   v->type == jpiStringFunc ||
+			   v->type == jpiReplaceFunc ||
+			   v->type == jpiStrLowerFunc ||
+			   v->type == jpiStrUpperFunc ||
 			   v->type == jpiTime ||
 			   v->type == jpiTimeTz ||
 			   v->type == jpiTimestamp ||
-			   v->type == jpiTimestampTz);
+			   v->type == jpiTimestampTz ||
+			   v->type == jpiStrLtrimFunc ||
+			   v->type == jpiStrRtrimFunc ||
+			   v->type == jpiStrBtrimFunc ||
+			   v->type == jpiStrInitcapFunc ||
+			   v->type == jpiStrSplitPartFunc);
 
 		if (a)
 			jspInitByBuffer(a, v->base, v->nextPos);
@@ -1184,6 +1326,24 @@ jspGetLeftArg(JsonPathItem *v, JsonPathItem *a)
 	jspInitByBuffer(a, v->base, v->content.args.left);
 }
 
+void
+jspGetArg0(JsonPathItem *v, JsonPathItem *a)
+{
+	Assert(v->type == jpiReplaceFunc ||
+			v->type == jpiStrSplitPartFunc);
+
+	jspInitByBuffer(a, v->base, v->content.method_args.arg0);
+}
+
+void
+jspGetArg1(JsonPathItem *v, JsonPathItem *a)
+{
+	Assert(v->type == jpiReplaceFunc ||
+			v->type == jpiStrSplitPartFunc);
+
+	jspInitByBuffer(a, v->base, v->content.method_args.arg1);
+}
+
 void
 jspGetRightArg(JsonPathItem *v, JsonPathItem *a)
 {
@@ -1206,6 +1366,7 @@ jspGetRightArg(JsonPathItem *v, JsonPathItem *a)
 	jspInitByBuffer(a, v->base, v->content.args.right);
 }
 
+
 bool
 jspGetBool(JsonPathItem *v)
 {
@@ -1227,7 +1388,8 @@ jspGetString(JsonPathItem *v, int32 *len)
 {
 	Assert(v->type == jpiKey ||
 		   v->type == jpiString ||
-		   v->type == jpiVariable);
+		   v->type == jpiVariable ||
+		   v->type == jpiStringFunc);
 
 	if (len)
 		*len = v->content.value.datalen;
@@ -1501,6 +1663,14 @@ jspIsMutableWalker(JsonPathItem *jpi, struct JsonPathMutableContext *cxt)
 			case jpiInteger:
 			case jpiNumber:
 			case jpiStringFunc:
+			case jpiReplaceFunc:
+			case jpiStrLowerFunc:
+			case jpiStrUpperFunc:
+			case jpiStrLtrimFunc:
+			case jpiStrRtrimFunc:
+			case jpiStrBtrimFunc:
+			case jpiStrInitcapFunc:
+			case jpiStrSplitPartFunc:
 				status = jpdsNonDateTime;
 				break;
 
diff --git a/src/backend/utils/adt/jsonpath_exec.c b/src/backend/utils/adt/jsonpath_exec.c
index 1184cba983..eb9da33741 100644
--- a/src/backend/utils/adt/jsonpath_exec.c
+++ b/src/backend/utils/adt/jsonpath_exec.c
@@ -303,6 +303,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,
@@ -1660,6 +1662,23 @@ executeItemOptUnwrapTarget(JsonPathExecContext *cxt, JsonPathItem *jsp,
 			}
 			break;
 
+		case jpiStrLtrimFunc:
+		case jpiStrLowerFunc:
+		case jpiStrUpperFunc:
+		case jpiReplaceFunc:
+		case jpiStrRtrimFunc:
+		case jpiStrBtrimFunc:
+		case jpiStrInitcapFunc:
+		case jpiStrSplitPartFunc:
+		{
+			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);
 	}
@@ -2793,6 +2812,176 @@ executeDateTimeMethod(JsonPathExecContext *cxt, JsonPathItem *jsp,
 	return executeNextItem(cxt, jsp, &elem, jb, found, hasNext);
 }
 
+/*
+ * Implementation of .upper(), lower() et. al. methods,
+ * that forward their actual implementation to internal functions.
+ */
+static JsonPathExecResult executeStringInternalMethod(JsonPathExecContext *cxt, JsonPathItem *jsp,
+												JsonbValue *jb, JsonValueList *found) {
+	Assert(	jsp->type == jpiStrLowerFunc ||
+			jsp->type == jpiStrUpperFunc ||
+			jsp->type == jpiReplaceFunc ||
+			jsp->type == jpiStrLtrimFunc ||
+			jsp->type == jpiStrRtrimFunc ||
+			jsp->type == jpiStrBtrimFunc ||
+			jsp->type == jpiStrInitcapFunc ||
+			jsp->type == jpiStrSplitPartFunc);
+	JsonbValue	jbvbuf;
+	bool		hasNext;
+	JsonPathExecResult res = jperNotFound;
+	JsonPathItem elem;
+	Datum		str; /* Datum representation for the current string value. The first argument to internal functions */
+	char		*tmp = NULL;
+	char		*resStr = NULL;
+
+	if (!(jb = getScalar(jb, jbvString)))
+		RETURN_ERROR(ereport(ERROR,
+							 (errcode(ERRCODE_INVALID_ARGUMENT_FOR_SQL_JSON_DATETIME_FUNCTION),
+							  errmsg("jsonpath item method .%s() can only be applied to a string",
+									 jspOperationName(jsp->type)))));
+
+	tmp = pnstrdup(jb->val.string.val, jb->val.string.len);
+	str = CStringGetTextDatum(tmp);
+
+	/* Internal string functions that accept no arguments */
+	switch (jsp->type)
+	{
+		case jpiStrLtrimFunc:
+		case jpiStrRtrimFunc:
+		case jpiStrBtrimFunc:
+		{
+			char	   *characters_str;
+			int			characters_len;
+			PGFunction	func = NULL;
+
+			switch (jsp->type)
+			{
+				case jpiStrLtrimFunc:
+					func = ltrim1;
+					break;
+				case jpiStrRtrimFunc:
+					func = rtrim1;
+					break;
+				case jpiStrBtrimFunc:
+					func = btrim1;
+					break;
+				default: ;
+			}
+
+			if (jsp->content.arg)
+			{
+				jspGetArg(jsp, &elem);
+				if (elem.type != jpiString)
+					elog(ERROR, "invalid jsonpath item type for .%s() argument", jspOperationName(jsp->type));
+
+				characters_str = jspGetString(&elem, &characters_len);
+				resStr = TextDatumGetCString(DirectFunctionCall2Coll(func,
+					DEFAULT_COLLATION_OID, str,
+					CStringGetTextDatum(characters_str)));
+				break;
+			}
+
+			resStr = TextDatumGetCString(DirectFunctionCall2Coll(func,
+					DEFAULT_COLLATION_OID, str,
+					CStringGetTextDatum(" ")));
+			break;
+		}
+
+		case jpiStrLowerFunc:
+			resStr = TextDatumGetCString(DirectFunctionCall1Coll(lower, DEFAULT_COLLATION_OID, str));
+			break;
+		case jpiStrUpperFunc:
+			resStr = TextDatumGetCString(DirectFunctionCall1Coll(upper, DEFAULT_COLLATION_OID, str));
+			break;
+		case jpiStrInitcapFunc:
+			resStr = TextDatumGetCString(DirectFunctionCall1Coll(initcap, DEFAULT_COLLATION_OID, str));
+			break;
+		case jpiReplaceFunc:
+		{
+			char		*from_str, *to_str;
+			int			from_len, to_len;
+
+			jspGetArg0(jsp, &elem);
+			if (elem.type != jpiString)
+				elog(ERROR, "invalid jsonpath item type for .replace() from");
+
+			from_str = jspGetString(&elem, &from_len);
+
+			jspGetArg1(jsp, &elem);
+			if (elem.type != jpiString)
+				elog(ERROR, "invalid jsonpath item type for .replace() to");
+
+			to_str = jspGetString(&elem, &to_len);
+
+			resStr = TextDatumGetCString(DirectFunctionCall3Coll(replace_text,
+				C_COLLATION_OID,
+				CStringGetTextDatum(tmp),
+				CStringGetTextDatum(from_str),
+				CStringGetTextDatum(to_str)));
+			break;
+		}
+		case jpiStrSplitPartFunc:
+		{
+			char		*from_str;
+			Numeric		n;
+			int			from_len;
+
+			jspGetArg0(jsp, &elem);
+			if (elem.type != jpiString)
+				elog(ERROR, "invalid jsonpath item type for .split_part()");
+
+			from_str = jspGetString(&elem, &from_len);
+
+			jspGetArg1(jsp, &elem);
+			if (elem.type != jpiNumeric)
+				elog(ERROR, "invalid jsonpath item type for .split_part()");
+
+			n = jspGetNumeric(&elem);
+
+			resStr = TextDatumGetCString(DirectFunctionCall3Coll(split_part,
+				C_COLLATION_OID,
+				CStringGetTextDatum(tmp),
+				CStringGetTextDatum(from_str),
+				DirectFunctionCall1(numeric_int8, 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;
+
+	jb = hasNext ? &jbvbuf : palloc(sizeof(*jb));
+
+	/* Create the appropriate jb value to return */
+	switch (jsp->type)
+	{
+		/* Cases for functions that return text */
+		case jpiStrLowerFunc:
+		case jpiStrUpperFunc:
+		case jpiReplaceFunc:
+		case jpiStrLtrimFunc:
+		case jpiStrRtrimFunc:
+		case jpiStrBtrimFunc:
+		case jpiStrInitcapFunc:
+		case jpiStrSplitPartFunc:
+			jb->type = jbvString;
+			jb->val.string.val = resStr;
+			jb->val.string.len = strlen(jb->val.string.val);
+		default:
+			;
+			/* cant' happen */
+	}
+
+	return executeNextItem(cxt, jsp, &elem, jb, found, hasNext);
+}
+
 /*
  * Implementation of .keyvalue() method.
  *
diff --git a/src/backend/utils/adt/jsonpath_gram.y b/src/backend/utils/adt/jsonpath_gram.y
index 8733a0eac6..99114b78c8 100644
--- a/src/backend/utils/adt/jsonpath_gram.y
+++ b/src/backend/utils/adt/jsonpath_gram.y
@@ -43,6 +43,9 @@ static bool makeItemLikeRegex(JsonPathParseItem *expr,
 							  JsonPathString *flags,
 							  JsonPathParseItem ** result,
 							  struct Node *escontext);
+static JsonPathParseItem *makeItemReplaceFunc(JsonPathParseItem *arg0, JsonPathParseItem *arg1);
+static JsonPathParseItem *makeItemStrSplitPartFunc(JsonPathParseItem *arg0, JsonPathParseItem *arg1);
+
 
 /*
  * Bison doesn't allocate anything that needs to live across parser calls,
@@ -84,6 +87,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_REPLACEFUNC_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
 
@@ -92,8 +97,9 @@ static bool makeItemLikeRegex(JsonPathParseItem *expr,
 					index_elem starts_with_initial expr_or_predicate
 					datetime_template opt_datetime_template csv_elem
 					datetime_precision opt_datetime_precision
+					str_method_arg_elem
 
-%type	<elems>		accessor_expr csv_list opt_csv_list
+%type	<elems>		accessor_expr csv_list opt_csv_list str_method_arg_list
 
 %type	<indexs>	index_list
 
@@ -276,6 +282,32 @@ accessor_op:
 		{ $$ = makeItemUnary(jpiTimestamp, $4); }
 	| '.' TIMESTAMP_TZ_P '(' opt_datetime_precision ')'
 		{ $$ = makeItemUnary(jpiTimestampTz, $4); }
+	| '.' STR_REPLACEFUNC_P '(' str_method_arg_list ')'
+		{
+			if (list_length($4) == 2)
+				$$ = makeItemReplaceFunc(linitial($4), lsecond($4));
+			else
+				ereturn(escontext, false,
+						(errcode(ERRCODE_SYNTAX_ERROR),
+						 errmsg("invalid input syntax for type %s", "jsonpath"),
+						 errdetail(".replace() accepts two arguments.")));
+		}
+	| '.' STR_SPLIT_PART_P '(' str_method_arg_list ')'
+		{
+			if (list_length($4) == 2)
+				$$ = makeItemStrSplitPartFunc(linitial($4), lsecond($4));
+			else
+				ereturn(escontext, false,
+						(errcode(ERRCODE_SYNTAX_ERROR),
+						 errmsg("invalid input syntax for type %s", "jsonpath"),
+						 errdetail(".split_part() accepts two arguments.")));
+		}
+	| '.' STR_LTRIM_P '(' opt_datetime_template ')'
+		{ $$ = makeItemUnary(jpiStrLtrimFunc, $4); }
+	| '.' STR_RTRIM_P '(' opt_datetime_template ')'
+		{ $$ = makeItemUnary(jpiStrRtrimFunc, $4); }
+	| '.' STR_BTRIM_P '(' opt_datetime_template ')'
+		{ $$ = makeItemUnary(jpiStrBtrimFunc, $4); }
 	;
 
 csv_elem:
@@ -315,6 +347,15 @@ opt_datetime_template:
 	| /* EMPTY */					{ $$ = NULL; }
 	;
 
+str_method_arg_elem:
+	STRING_P						{ $$ = makeItemString(&$1); }
+	| INT_P							{ $$ = makeItemNumeric(&$1); }
+	;
+
+str_method_arg_list:
+	str_method_arg_elem							{ $$ = list_make1($1); }
+	| str_method_arg_list ',' str_method_arg_elem	{ $$ = lappend($1, $3); }
+	;
 key:
 	key_name						{ $$ = makeItemKey(&$1); }
 	;
@@ -355,6 +396,9 @@ key_name:
 	| TIME_TZ_P
 	| TIMESTAMP_P
 	| TIMESTAMP_TZ_P
+	| STR_LTRIM_P
+	| STR_RTRIM_P
+	| STR_BTRIM_P
 	;
 
 method:
@@ -371,6 +415,9 @@ method:
 	| INTEGER_P						{ $$ = jpiInteger; }
 	| NUMBER_P						{ $$ = jpiNumber; }
 	| STRINGFUNC_P					{ $$ = jpiStringFunc; }
+	| STR_LOWER_P					{ $$ = jpiStrLowerFunc; }
+	| STR_UPPER_P					{ $$ = jpiStrUpperFunc; }
+	| STR_INITCAP_P					{ $$ = jpiStrInitcapFunc; }
 	;
 %%
 
@@ -470,6 +517,28 @@ makeItemBinary(JsonPathItemType type, JsonPathParseItem *la, JsonPathParseItem *
 	return v;
 }
 
+static JsonPathParseItem *
+makeItemReplaceFunc(JsonPathParseItem *arg0, JsonPathParseItem *arg1)
+{
+	JsonPathParseItem *v = makeItemType(jpiReplaceFunc);
+
+	v->value.method_args.arg0 = arg0;
+	v->value.method_args.arg1 = arg1;
+
+	return v;
+}
+
+static JsonPathParseItem *
+makeItemStrSplitPartFunc(JsonPathParseItem *arg0, JsonPathParseItem *arg1)
+{
+	JsonPathParseItem *v = makeItemType(jpiStrSplitPartFunc);
+
+	v->value.method_args.arg0 = arg0;
+	v->value.method_args.arg1 = arg1;
+
+	return v;
+}
+
 static JsonPathParseItem *
 makeItemUnary(JsonPathItemType type, JsonPathParseItem *a)
 {
diff --git a/src/backend/utils/adt/jsonpath_scan.l b/src/backend/utils/adt/jsonpath_scan.l
index 7acda77837..32255646b0 100644
--- a/src/backend/utils/adt/jsonpath_scan.l
+++ b/src/backend/utils/adt/jsonpath_scan.l
@@ -410,8 +410,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"},
@@ -422,13 +427,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_REPLACEFUNC_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 ee35698d08..2ba5cd70c1 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 */
+	jpiReplaceFunc,				/* .replace() item method */
+	jpiStrLowerFunc,			/* .lower() item method */
+	jpiStrUpperFunc,			/* .upper() item method */
+	jpiStrLtrimFunc,			/* .ltrim() item method */
+	jpiStrRtrimFunc,			/* .rtrim() item method */
+	jpiStrBtrimFunc,			/* .btrim() item method */
+	jpiStrInitcapFunc,			/* .initcap() item method */
+	jpiStrSplitPartFunc,		/* .split_part() item method */
 } JsonPathItemType;
 
 /* XQuery regex mode flags for LIKE_REGEX predicate */
@@ -188,6 +196,12 @@ typedef struct JsonPathItem
 			int32		patternlen;
 			uint32		flags;
 		}			like_regex;
+
+		struct
+		{
+			int32		arg0;
+			int32		arg1;
+		}			method_args;
 	}			content;
 } JsonPathItem;
 
@@ -199,6 +213,8 @@ extern bool jspGetNext(JsonPathItem *v, JsonPathItem *a);
 extern void jspGetArg(JsonPathItem *v, JsonPathItem *a);
 extern void jspGetLeftArg(JsonPathItem *v, JsonPathItem *a);
 extern void jspGetRightArg(JsonPathItem *v, JsonPathItem *a);
+extern void jspGetArg0(JsonPathItem *v, JsonPathItem *a);
+extern void jspGetArg1(JsonPathItem *v, JsonPathItem *a);
 extern Numeric jspGetNumeric(JsonPathItem *v);
 extern bool jspGetBool(JsonPathItem *v);
 extern char *jspGetString(JsonPathItem *v, int32 *len);
@@ -266,6 +282,12 @@ struct JsonPathParseItem
 			uint32		len;
 			char	   *val;	/* could not be not null-terminated */
 		}			string;
+
+		struct
+		{
+			JsonPathParseItem *arg0;
+			JsonPathParseItem *arg1;
+		}			method_args;
 	}			value;
 };
 
diff --git a/src/test/regress/expected/jsonb_jsonpath.out b/src/test/regress/expected/jsonb_jsonpath.out
index acdf7e436f..a909eecd98 100644
--- a/src/test/regress/expected/jsonb_jsonpath.out
+++ b/src/test/regress/expected/jsonb_jsonpath.out
@@ -2719,6 +2719,399 @@ 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('"   hello   "', '$.ltrim()');
+ jsonb_path_query 
+------------------
+ "hello   "
+(1 row)
+
+select jsonb_path_query('"   hello   "', '$.ltrim()');
+ jsonb_path_query 
+------------------
+ "hello   "
+(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('"   hello   "', '$.rtrim(" ")');
+ jsonb_path_query 
+------------------
+ "   hello"
+(1 row)
+
+select jsonb_path_query('"   hello   "', '$.rtrim()');
+ jsonb_path_query 
+------------------
+ "   hello"
+(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('"   hello   "', '$.btrim(" ")');
+ jsonb_path_query 
+------------------
+ "hello"
+(1 row)
+
+select jsonb_path_query('"   hello   "', '$.btrim()');
+ jsonb_path_query 
+------------------
+ "hello"
+(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)
+
+-- 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 fd9bd755f5..1b122e877d 100644
--- a/src/test/regress/expected/jsonpath.out
+++ b/src/test/regress/expected/jsonpath.out
@@ -435,6 +435,78 @@ 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)
+
 select '$.time()'::jsonpath;
  jsonpath 
 ----------
diff --git a/src/test/regress/sql/jsonb_jsonpath.sql b/src/test/regress/sql/jsonb_jsonpath.sql
index da3f7969ca..e69f88f963 100644
--- a/src/test/regress/sql/jsonb_jsonpath.sql
+++ b/src/test/regress/sql/jsonb_jsonpath.sql
@@ -619,6 +619,114 @@ 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('"   hello   "', '$.ltrim()');
+select jsonb_path_query('"   hello   "', '$.ltrim()');
+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('"   hello   "', '$.rtrim(" ")');
+select jsonb_path_query('"   hello   "', '$.rtrim()');
+select jsonb_path_query('"   hello   "', '$.rtrim()');
+
+-- test .btrim()
+select jsonb_path_query('"   hello   "', '$.btrim(" ")');
+select jsonb_path_query('"   hello   "', '$.btrim(" ")');
+select jsonb_path_query('"   hello   "', '$.btrim()');
+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)');
+
+-- 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 61a5270d4e..8752ffcf08 100644
--- a/src/test/regress/sql/jsonpath.sql
+++ b/src/test/regress/sql/jsonpath.sql
@@ -78,6 +78,19 @@ 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;
+
 select '$.time()'::jsonpath;
 select '$.time(6)'::jsonpath;
 select '$.time_tz()'::jsonpath;
-- 
2.39.5 (Apple Git-154)

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: PATCH: jsonpath string methods: lower, upper, initcap, l/r/btrim, replace, split_part
  In-Reply-To: <CA+v5N40sJF39m0v7h=QN86zGp0CUf9F1WKasnZy9nNVj_VhCZQ@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox