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: Sat, 24 May 2025 17:39:01 -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]>
On May 24, 2025, at 12:51, Florents Tselai <[email protected]> wrote:
> I think the patch is still in reasonably good shape and hasn’t changed much since September 24.So yes, I’d hope there are still some valid points to consider or improve.
Okay, here’s a review.
Patch applies cleanly.
All tests pass.
I'm curious why you added the `arg0` and `arg1` fields to the `method_args` union. Is there some reason that the existing `left` and `right` fields wouldn't work? Admittedly these are not formally binary operators, but I don't see that it matters much.
The existing string() method operates on a "JSON boolean, number, string, or datetime"; should these functions also operate on all those data types?
The argument to the trim methods appears to be ignored:
```
postgres=# select jsonb_path_query('"zzzytest"', '$.ltrim("xyz")');
jsonb_path_query
------------------
"zzzytest"
```
I'm wondering if the issue is the use of the opt_datetime_template in the grammar?
```
| '.' 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); }
```
I realize it resolves to a string, but for some reason it doesn't get picked up. But also, do you want to support variables for either of these arguments? If so, maybe rename and use starts_with_initial:
```
starts_with_initial:
STRING_P { $$ = makeItemString(&$1); }
| VARIABLE_P { $$ = makeItemVariable(&$1); }
;
```
split_part() does not support a negative n value:
```
postgres=# select split_part('abc,def,ghi,jkl', ',', -2) ;
split_part
------------
ghi
select jsonb_path_query('"abc,def,ghi,jkl"', '$.split_part(",", -2)');
ERROR: syntax error at or near "-" of jsonpath input
LINE 1: select jsonb_path_query('"abc,def,ghi,jkl"', '$.split_part("...
```
Nor does a `+` work. I think you’d be better served using `csv_elem`, something like:
```
| '.' STR_SPLIT_PART_P '(' STRING_P csv_elem ‘)’
```
I'm not sure how well these functions comply with the SQL spec. Does it have a provision for implementation-specific methods? I *think* all existing methods are defined by the spec, but someone with access to its contents would have to say for sure. And maybe we don't care, consider this a natural extension?
I’ve attached a new patch with docs.
Best,
David
Attachments:
[application/octet-stream] v3-0001-Add-additional-jsonpath-string-methods.patch (51.1K, 2-v3-0001-Add-additional-jsonpath-string-methods.patch)
download | inline diff:
From 411a72f1d8654696b81a2386169a46ff67d3a818 Mon Sep 17 00:00:00 2001
From: Florents Tselai <[email protected]>
Date: Sat, 24 May 2025 17:33:56 -0400
Subject: [PATCH v3] 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 | 171 ++++++++
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 | 41 ++
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 +
10 files changed, 1252 insertions(+), 4 deletions(-)
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index c67688cbf5f..25d0d920dbf 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -18627,6 +18627,177 @@ 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()</literal>
+ <returnvalue><replaceable>string</replaceable></returnvalue>
+ </para>
+ <para>
+ String with spaces removed from the start of <replaceable>string</replaceable>
+ </para>
+ <para>
+ <literal> jsonb_path_query('" hello"', '$.ltrim()')</literal>
+ <returnvalue>"hello"</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 characters in
+ <replaceable>characters</replaceable> removed from the start of
+ <replaceable>string</replaceable>
+ </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()</literal>
+ <returnvalue><replaceable>string</replaceable></returnvalue>
+ </para>
+ <para>
+ String with spaces removed from the end of <replaceable>string</replaceable>
+ </para>
+ <para>
+ <literal>jsonb_path_query('"hello "', '$.rtrim()')</literal>
+ <returnvalue>"hello"</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 characters in
+ <replaceable>characters</replaceable> removed from the end of
+ <replaceable>string</replaceable>
+ </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()</literal>
+ <returnvalue><replaceable>string</replaceable></returnvalue>
+ </para>
+ <para>
+ String with spaces removed from the start and end of
+ <replaceable>string</replaceable>
+ </para>
+ <para>
+ <literal>jsonb_path_query('" hello "', '$.btrim()')</literal>
+ <returnvalue>"hello"</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 characters in
+ <replaceable>characters</replaceable> removed from the start and end
+ of <replaceable>string</replaceable>
+ </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..646c7b460a3 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 dbab24737ef..51239121f18 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 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);
}
@@ -2792,6 +2811,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 499745a8fef..cf7a4ff19fd 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,
@@ -86,6 +89,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
@@ -94,8 +99,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
@@ -278,6 +284,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 '(' STRING_P csv_elem ')'
+ {
+ 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:
@@ -317,6 +349,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); }
;
@@ -357,6 +398,9 @@ key_name:
| TIME_TZ_P
| TIMESTAMP_P
| TIMESTAMP_TZ_P
+ | STR_LTRIM_P
+ | STR_RTRIM_P
+ | STR_BTRIM_P
;
method:
@@ -373,6 +417,9 @@ method:
| INTEGER_P { $$ = jpiInteger; }
| NUMBER_P { $$ = jpiNumber; }
| STRINGFUNC_P { $$ = jpiStringFunc; }
+ | STR_LOWER_P { $$ = jpiStrLowerFunc; }
+ | STR_UPPER_P { $$ = jpiStrUpperFunc; }
+ | STR_INITCAP_P { $$ = jpiStrInitcapFunc; }
;
%%
@@ -472,6 +519,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 c7aab83eeb4..f20e9e5f1d3 100644
--- a/src/backend/utils/adt/jsonpath_scan.l
+++ b/src/backend/utils/adt/jsonpath_scan.l
@@ -433,6 +433,47 @@ static const JsonPathKeyword keywords[] = {
{9, false, TIMESTAMP_P, "timestamp"},
{10, false, LIKE_REGEX_P, "like_regex"},
{12, false, TIMESTAMP_TZ_P, "timestamp_tz"},
+ { 2, false, IS_P, "is"},
+ { 2, false, TO_P, "to"},
+ { 3, false, ABS_P, "abs"},
+ { 3, false, LAX_P, "lax"},
+ { 4, false, DATE_P, "date"},
+ { 4, false, FLAG_P, "flag"},
+ { 4, false, LAST_P, "last"},
+ { 4, true, NULL_P, "null"},
+ { 4, false, SIZE_P, "size"},
+ { 4, false, TIME_P, "time"},
+ { 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"},
+ { 6, false, NUMBER_P, "number"},
+ { 6, false, STARTS_P, "starts"},
+ { 6, false, STRICT_P, "strict"},
+ { 6, false, STRINGFUNC_P, "string"},
+ { 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 23a76d233e9..4c60f9d1826 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 4bcd4e91a29..2dbaec92129 100644
--- a/src/test/regress/expected/jsonb_jsonpath.out
+++ b/src/test/regress/expected/jsonb_jsonpath.out
@@ -2723,6 +2723,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 fd9bd755f52..1b122e877d5 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 3e8929a5269..e229e0a0d04 100644
--- a/src/test/regress/sql/jsonb_jsonpath.sql
+++ b/src/test/regress/sql/jsonb_jsonpath.sql
@@ -623,6 +623,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 61a5270d4e8..8752ffcf08c 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.49.0
[application/pgp-signature] signature.asc (833B, 4-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