public inbox for [email protected]
help / color / mirror / Atom feedFrom: David E. Wheeler <[email protected]>
To: Florents Tselai <[email protected]>
Cc: Tom Lane <[email protected]>
Cc: Peter Eisentraut <[email protected]>
Cc: Robert Haas <[email protected]>
Cc: Alexander Korotkov <[email protected]>
Cc: pgsql-hackers <[email protected]>
Cc: Andrew Dunstan <[email protected]>
Subject: Re: PATCH: jsonpath string methods: lower, upper, initcap, l/r/btrim, replace, split_part
Date: Tue, 3 Jun 2025 15:02:07 -0400
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <CA+v5N40sJF39m0v7h=QN86zGp0CUf9F1WKasnZy9nNVj_VhCZQ@mail.gmail.com>
<[email protected]>
<CAPpHfdtGhn_5jfLoepOScyqT+FXYB9QtV-OEprychDcMJco7mw@mail.gmail.com>
<CA+v5N42PVJH3HbwLE1yC75XR6E5zGnCCdtSUXfgFwtGyPP8XYg@mail.gmail.com>
<CA+Tgmob03B6h1SMsi7vs9uOX+vrqg_tyhh--mKC3BaTJ08qKYA@mail.gmail.com>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
Hackers,
On May 26, 2025, at 18:00, David E. Wheeler <[email protected]> wrote:
> Yes, I think it would be best if the grammar was a bit stricter --- and therefore more self-explanatory --- by making the args closer to what the functions actually expect.
I chatted with Florents and went ahead and simplified the grammar and fixed the other issues I identified in my original review. Note that there are two commits, now:
`v6-0001-Rename-jsonpath-method-arg-tokens.patch` Renames some of the symbols in the jsonpath grammar so that they’re less generic (`csv*`) and more specific to their contents. This is with the expectation that they will be used by other methods in the next patch and in the future. I thought it best to separate this refactoring from the feature patch.
`v6-0002-Add-additional-jsonpath-string-methods.patch` is that feature patch. The grammar now parses the exact number and types of each method argument, eliminating the need for explicit error checking. It also uses the existing patterns for handling methods with two parameters, removing a bunch of duplicate code.
Overall I think this is ready for committer review, although now that I’m not just reviewing but hacking on this thing, maybe someone else should review it first.
Patches attached, GitHub PR here:
https://github.com/theory/postgres/pull/12
Best,
David
Attachments:
[application/octet-stream] v6-0001-Rename-jsonpath-method-arg-tokens.patch (3.9K, 2-v6-0001-Rename-jsonpath-method-arg-tokens.patch)
download | inline diff:
From 86704d1c079b205ccdcb79da974a690d4dca2ad3 Mon Sep 17 00:00:00 2001
From: "David E. Wheeler" <[email protected]>
Date: Mon, 2 Jun 2025 17:14:10 -0400
Subject: [PATCH v6 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 499745a8fef..0b16cec18c4 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.49.0
[application/octet-stream] v6-0002-Add-additional-jsonpath-string-methods.patch (48.4K, 3-v6-0002-Add-additional-jsonpath-string-methods.patch)
download | inline diff:
From fc273e5445a38ff77ecdb77757dc2ff4fba42091 Mon Sep 17 00:00:00 2001
From: Florents Tselai <[email protected]>
Date: Tue, 3 Jun 2025 14:58:36 -0400
Subject: [PATCH v6 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.sgml | 140 +++++++
src/backend/utils/adt/jsonpath.c | 126 +++++-
src/backend/utils/adt/jsonpath_exec.c | 200 ++++++++++
src/backend/utils/adt/jsonpath_gram.y | 29 +-
src/backend/utils/adt/jsonpath_scan.l | 8 +
src/include/utils/jsonpath.h | 8 +
src/test/regress/expected/jsonb_jsonpath.out | 399 +++++++++++++++++++
src/test/regress/expected/jsonpath.out | 137 +++++++
src/test/regress/sql/jsonb_jsonpath.sql | 109 +++++
src/test/regress/sql/jsonpath.sql | 31 ++
10 files changed, 1183 insertions(+), 4 deletions(-)
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index c67688cbf5f..674c22c78d8 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -18627,6 +18627,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(",", 2)')</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 762f7e8a09d..524f0f8daec 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,72 @@ printJsonPathItem(StringInfo buf, JsonPathItem *v, bool inKey,
}
appendStringInfoChar(buf, ')');
break;
+ case jpiStrReplace:
+ appendStringInfoString(buf, ".replace(");
+ if (v->content.args.left)
+ {
+ jspGetLeftArg(v, &elem);
+ printJsonPathItem(buf, &elem, false, false);
+ }
+ if (v->content.args.right)
+ {
+ appendStringInfoChar(buf, ',');
+ jspGetRightArg(v, &elem);
+ printJsonPathItem(buf, &elem, false, false);
+ }
+ appendStringInfoChar(buf, ')');
+ break;
+ case jpiStrSplitPart:
+ appendStringInfoString(buf, ".split_part(");
+ if (v->content.args.left)
+ {
+ jspGetLeftArg(v, &elem);
+ printJsonPathItem(buf, &elem, false, false);
+ }
+ if (v->content.args.right)
+ {
+ 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 jpiStrInitcap:
+ appendStringInfoString(buf, ".initcap()");
+ 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;
default:
elog(ERROR, "unrecognized jsonpath item type: %d", v->type);
}
@@ -906,6 +980,12 @@ jspOperationName(JsonPathItemType type)
return "number";
case jpiStringFunc:
return "string";
+ case jpiStrReplace:
+ return "replace";
+ case jpiStrLower:
+ return "lower";
+ case jpiStrUpper:
+ return "upper";
case jpiTime:
return "time";
case jpiTimeTz:
@@ -914,6 +994,16 @@ jspOperationName(JsonPathItemType type)
return "timestamp";
case jpiTimestampTz:
return "timestamp_tz";
+ 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 +1106,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 +1134,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 +1150,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 +1188,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);
}
@@ -1149,10 +1250,18 @@ jspGetNext(JsonPathItem *v, JsonPathItem *a)
v->type == jpiInteger ||
v->type == jpiNumber ||
v->type == jpiStringFunc ||
+ v->type == jpiStrReplace ||
+ v->type == jpiStrLower ||
+ v->type == jpiStrUpper ||
v->type == jpiTime ||
v->type == jpiTimeTz ||
v->type == jpiTimestamp ||
- v->type == jpiTimestampTz);
+ v->type == jpiTimestampTz ||
+ v->type == jpiStrLtrim ||
+ v->type == jpiStrRtrim ||
+ v->type == jpiStrBtrim ||
+ v->type == jpiStrInitcap ||
+ v->type == jpiStrSplitPart);
if (a)
jspInitByBuffer(a, v->base, v->nextPos);
@@ -1201,11 +1310,14 @@ 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);
}
+
bool
jspGetBool(JsonPathItem *v)
{
@@ -1501,6 +1613,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 dbab24737ef..2095f2a9c21 100644
--- a/src/backend/utils/adt/jsonpath_exec.c
+++ b/src/backend/utils/adt/jsonpath_exec.c
@@ -301,6 +301,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,
@@ -1659,6 +1661,23 @@ executeItemOptUnwrapTarget(JsonPathExecContext *cxt, JsonPathItem *jsp,
}
break;
+ case jpiStrLtrim:
+ case jpiStrLower:
+ case jpiStrUpper:
+ case jpiStrReplace:
+ 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);
}
@@ -2792,6 +2811,187 @@ 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 == jpiStrLower ||
+ jsp->type == jpiStrUpper ||
+ jsp->type == jpiStrReplace ||
+ jsp->type == jpiStrLtrim ||
+ jsp->type == jpiStrRtrim ||
+ jsp->type == jpiStrBtrim ||
+ jsp->type == jpiStrInitcap ||
+ jsp->type == jpiStrSplitPart);
+ 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 jpiStrLtrim:
+ case jpiStrRtrim:
+ case jpiStrBtrim:
+ {
+ char *characters_str;
+ int characters_len;
+ PGFunction func = NULL;
+
+ if (jsp->content.arg)
+ {
+ switch (jsp->type)
+ {
+ case jpiStrLtrim:
+ func = ltrim;
+ break;
+ case jpiStrRtrim:
+ func = rtrim;
+ break;
+ case jpiStrBtrim:
+ func = btrim;
+ break;
+ default: ;
+ }
+ 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;
+ }
+
+ switch (jsp->type)
+ {
+ case jpiStrLtrim:
+ func = ltrim1;
+ break;
+ case jpiStrRtrim:
+ func = rtrim1;
+ break;
+ case jpiStrBtrim:
+ func = btrim1;
+ break;
+ default: ;
+ }
+ resStr = TextDatumGetCString(DirectFunctionCall1Coll(func,
+ DEFAULT_COLLATION_OID, 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 jpiStrInitcap:
+ resStr = TextDatumGetCString(DirectFunctionCall1Coll(initcap, DEFAULT_COLLATION_OID, str));
+ break;
+ case jpiStrReplace:
+ {
+ char *from_str, *to_str;
+ int from_len, to_len;
+
+ jspGetLeftArg(jsp, &elem);
+ if (elem.type != jpiString)
+ elog(ERROR, "invalid jsonpath item type for .replace() from");
+
+ from_str = jspGetString(&elem, &from_len);
+
+ jspGetRightArg(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 jpiStrSplitPart:
+ {
+ char *from_str;
+ Numeric n;
+ int from_len;
+
+ jspGetLeftArg(jsp, &elem);
+ if (elem.type != jpiString)
+ elog(ERROR, "invalid jsonpath item type for .split_part()");
+
+ from_str = jspGetString(&elem, &from_len);
+
+ jspGetRightArg(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 jpiStrLower:
+ case jpiStrUpper:
+ case jpiStrReplace:
+ case jpiStrLtrim:
+ case jpiStrRtrim:
+ case jpiStrBtrim:
+ case jpiStrInitcap:
+ case jpiStrSplitPart:
+ 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 0b16cec18c4..a48d80f560f 100644
--- a/src/backend/utils/adt/jsonpath_gram.y
+++ b/src/backend/utils/adt/jsonpath_gram.y
@@ -44,6 +44,7 @@ static bool makeItemLikeRegex(JsonPathParseItem *expr,
JsonPathParseItem ** result,
struct Node *escontext);
+
/*
* Bison doesn't allocate anything that needs to live across parser calls,
* so we can easily have it use palloc instead of malloc. This prevents
@@ -86,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_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 +98,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 +281,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 +330,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 +378,9 @@ key_name:
| TIME_TZ_P
| TIMESTAMP_P
| TIMESTAMP_TZ_P
+ | STR_LTRIM_P
+ | STR_RTRIM_P
+ | STR_BTRIM_P
;
method:
@@ -373,6 +397,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 c7aab83eeb4..69c61f98500 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 23a76d233e9..2d0c53f7b06 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..cea539717f9 100644
--- a/src/test/regress/expected/jsonb_jsonpath.out
+++ b/src/test/regress/expected/jsonb_jsonpath.out
@@ -2723,6 +2723,405 @@ 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('"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)
+
+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)
+
+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..a13022feabd 100644
--- a/src/test/regress/expected/jsonpath.out
+++ b/src/test/regress/expected/jsonpath.out
@@ -435,6 +435,143 @@ 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;
+ ^
select '$.time()'::jsonpath;
jsonpath
----------
diff --git a/src/test/regress/sql/jsonb_jsonpath.sql b/src/test/regress/sql/jsonb_jsonpath.sql
index 3e8929a5269..8169719343e 100644
--- a/src/test/regress/sql/jsonb_jsonpath.sql
+++ b/src/test/regress/sql/jsonb_jsonpath.sql
@@ -623,6 +623,115 @@ 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('"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()');
+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()');
+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..7dd2e57e7a6 100644
--- a/src/test/regress/sql/jsonpath.sql
+++ b/src/test/regress/sql/jsonpath.sql
@@ -78,6 +78,37 @@ 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;
+
select '$.time()'::jsonpath;
select '$.time(6)'::jsonpath;
select '$.time_tz()'::jsonpath;
--
2.49.0
[application/pgp-signature] signature.asc (833B, 5-signature.asc)
download
view thread (56+ messages) latest in thread
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], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: PATCH: jsonpath string methods: lower, upper, initcap, l/r/btrim, replace, split_part
In-Reply-To: <[email protected]>
* 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