On Fri, Jun 19, 2026 at 11:43 PM Zsolt Parragi <[email protected]>
wrote:
> Hello
>
> + /* Validate target is an array */
> + if (JsonbType(jb) != jbvArray)
> + {
> + RETURN_ERROR(ereport(ERROR,
> +
> (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
> +
> errmsg("jsonpath item method .join() can only be applied
> to an array"))));
>
> Other methods seem to use ERRCODE_SQL_JSON_ARRAY_NOT_FOUND
>
> + else
> + {
> + /* Recursive Tree (jbvArray) */
> + for (int i = 0; i <
> jb->val.array.nElems; i++)
>
> Isn't this branch unreachable?
both of your points are correct.
Here's a v3 that address them .
While at it, I've added some additional edge cases about these 3 methods.
The semantics look good to me, and they pass without additional change in
the underlying src code in jsonpath_exec.c
https://commitfest.postgresql.org/patch/6673/
-
Cheers,
Flo
tselai.com
<http://tselai.com/?utm_source=email_signature&utm_medium=email&utm_campaign=Email_Signature>
Schedule a chat ☕ <https://calendly.com/florents-tselai/30min>
From a3d0abf3f43d71128c0fef031b074438ca0c5394 Mon Sep 17 00:00:00 2001
From: Florents Tselai <[email protected]>
Date: Sat, 20 Jun 2026 10:36:56 +0300
Subject: [PATCH v3] Add more jsonpath string methods (.translate, .split,
.join)
---
doc/src/sgml/func/func-json.sgml | 56 +++++
src/backend/utils/adt/jsonpath.c | 93 +++++++-
src/backend/utils/adt/jsonpath_exec.c | 190 ++++++++++++++-
src/backend/utils/adt/jsonpath_gram.y | 15 +-
src/backend/utils/adt/jsonpath_scan.l | 3 +
src/include/utils/jsonpath.h | 3 +
src/test/regress/expected/jsonb_jsonpath.out | 219 ++++++++++++++++++
src/test/regress/expected/jsonpath.out | 66 ++++++
.../regress/expected/sqljson_queryfuncs.out | 3 +
src/test/regress/sql/jsonb_jsonpath.sql | 67 ++++++
src/test/regress/sql/jsonpath.sql | 14 ++
src/test/regress/sql/sqljson_queryfuncs.sql | 3 +
12 files changed, 725 insertions(+), 7 deletions(-)
diff --git a/doc/src/sgml/func/func-json.sgml b/doc/src/sgml/func/func-json.sgml
index 3d97e2b5375..575111f9acb 100644
--- a/doc/src/sgml/func/func-json.sgml
+++ b/doc/src/sgml/func/func-json.sgml
@@ -2841,6 +2841,24 @@ ERROR: jsonpath member accessor can only be applied to an object
</para></entry>
</row>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <replaceable>string</replaceable> <literal>.</literal> <literal>translate(<replaceable>from</replaceable>, <replaceable>to</replaceable>)</literal>
+ <returnvalue><replaceable>string</replaceable></returnvalue>
+ </para>
+ <para>
+ String where each character that matches a character in the
+ <replaceable>from</replaceable> string is replaced with the corresponding
+ character in the <replaceable>to</replaceable> string. If <replaceable>from</replaceable>
+ is longer than <replaceable>to</replaceable>, occurrences of the extra characters in
+ <replaceable>from</replaceable> are deleted.
+ </para>
+ <para>
+ <literal>jsonb_path_query('"12345"', '$.translate("143", "ax")')</literal>
+ <returnvalue>"a2x5"</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>
@@ -2862,6 +2880,26 @@ ERROR: jsonpath member accessor can only be applied to an object
</para></entry>
</row>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <replaceable>string</replaceable> <literal>.</literal> <literal>split(<replaceable>delimiter</replaceable> <optional>, <replaceable>null_string</replaceable></optional>)</literal>
+ <returnvalue><replaceable>array</replaceable></returnvalue>
+ </para>
+ <para>
+ Splits the string at occurrences of <replaceable>delimiter</replaceable> and forms
+ the resulting fields into a JSON array. If <replaceable>null_string</replaceable>
+ is supplied, fields matching that string are replaced by JSON null values.
+ </para>
+ <para>
+ <literal>jsonb_path_query('"a,b,c"', '$.split(",")')</literal>
+ <returnvalue>["a", "b", "c"]</returnvalue>
+ </para>
+ <para>
+ <literal>jsonb_path_query('"xx~~yy~~zz"', '$.split("~~", "yy")')</literal>
+ <returnvalue>["xx", null, "zz"]</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>
@@ -2922,6 +2960,24 @@ ERROR: jsonpath member accessor can only be applied to an object
</para></entry>
</row>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <replaceable>array</replaceable> <literal>.</literal> <literal>join(separator [, null_string])</literal>
+ <returnvalue>string</returnvalue>
+ </para>
+ <para>
+ Concatenates the elements of an array into a single string using the
+ specified separator. If the optional <literal>null_string</literal>
+ argument is provided, it replaces JSON <literal>null</literal> values;
+ otherwise, <literal>null</literal> values are skipped. The input must
+ be an array consisting only of strings or <literal>null</literal> values.
+ </para>
+ <para>
+ <literal>jsonb_path_query('["a", null, "c"]', '$.join("-", "N/A")')</literal>
+ <returnvalue>"a-N/A-c"</returnvalue>
+ </para></entry>
+ </row>
+
</tbody>
</tgroup>
</table>
diff --git a/src/backend/utils/adt/jsonpath.c b/src/backend/utils/adt/jsonpath.c
index 7bfc18c9888..b501f197964 100644
--- a/src/backend/utils/adt/jsonpath.c
+++ b/src/backend/utils/adt/jsonpath.c
@@ -300,6 +300,7 @@ flattenJsonPathParseItem(StringInfo buf, int *result, struct Node *escontext,
case jpiDecimal:
case jpiStrReplace:
case jpiStrSplitPart:
+ case jpiStrTranslate:
{
/*
* First, reserve place for left/right arg's positions, then
@@ -328,6 +329,38 @@ flattenJsonPathParseItem(StringInfo buf, int *result, struct Node *escontext,
*(int32 *) (buf->data + right) = chld - pos;
}
break;
+ case jpiStrSplit:
+ case jpiStrJoin:
+ {
+ /* Reserve space for left and right arg positions */
+ int32 left = reserveSpaceForItemPointer(buf);
+ int32 right = reserveSpaceForItemPointer(buf);
+
+ /* Flatten the required left argument (the delimiter) */
+ if (!flattenJsonPathParseItem(buf, &chld, escontext,
+ item->value.args.left,
+ nestingLevel,
+ insideArraySubscript))
+ return false;
+ *(int32 *) (buf->data + left) = chld - pos;
+
+ /* Flatten the optional right argument only if provided */
+ if (item->value.args.right != NULL)
+ {
+ if (!flattenJsonPathParseItem(buf, &chld, escontext,
+ item->value.args.right,
+ nestingLevel,
+ insideArraySubscript))
+ return false;
+ *(int32 *) (buf->data + right) = chld - pos;
+ }
+ else
+ {
+ /* Default to 0 if missing */
+ *(int32 *) (buf->data + right) = 0;
+ }
+ }
+ break;
case jpiLikeRegex:
{
int32 offs;
@@ -863,6 +896,18 @@ printJsonPathItem(StringInfo buf, JsonPathItem *v, bool inKey,
printJsonPathItem(buf, &elem, false, false);
appendStringInfoChar(buf, ')');
break;
+ case jpiStrSplit:
+ appendStringInfoString(buf, ".split(");
+ jspGetLeftArg(v, &elem);
+ printJsonPathItem(buf, &elem, false, false);
+ if (v->content.args.right != 0)
+ {
+ appendStringInfoChar(buf, ',');
+ jspGetRightArg(v, &elem);
+ printJsonPathItem(buf, &elem, false, false);
+ }
+ appendStringInfoChar(buf, ')');
+ break;
case jpiStrLtrim:
appendStringInfoString(buf, ".ltrim(");
if (v->content.arg)
@@ -893,6 +938,29 @@ printJsonPathItem(StringInfo buf, JsonPathItem *v, bool inKey,
case jpiStrInitcap:
appendStringInfoString(buf, ".initcap()");
break;
+ case jpiStrTranslate:
+ appendStringInfoString(buf, ".translate(");
+ jspGetLeftArg(v, &elem);
+ printJsonPathItem(buf, &elem, false, false);
+ appendStringInfoChar(buf, ',');
+ jspGetRightArg(v, &elem);
+ printJsonPathItem(buf, &elem, false, false);
+ appendStringInfoChar(buf, ')');
+ break;
+ case jpiStrJoin:
+ appendStringInfoString(buf, ".join(");
+ jspGetLeftArg(v, &elem);
+ printJsonPathItem(buf, &elem, false, false);
+
+ /* Check if null_string was provided. */
+ if (v->content.args.right != 0)
+ {
+ appendStringInfoString(buf, ", ");
+ jspGetRightArg(v, &elem);
+ printJsonPathItem(buf, &elem, false, false);
+ }
+ appendStringInfoChar(buf, ')');
+ break;
default:
elog(ERROR, "unrecognized jsonpath item type: %d", v->type);
}
@@ -992,6 +1060,12 @@ jspOperationName(JsonPathItemType type)
return "initcap";
case jpiStrSplitPart:
return "split_part";
+ case jpiStrTranslate:
+ return "translate";
+ case jpiStrSplit:
+ return "split";
+ case jpiStrJoin:
+ return "join";
default:
elog(ERROR, "unrecognized jsonpath item type: %d", type);
return NULL;
@@ -1123,7 +1197,10 @@ jspInitByBuffer(JsonPathItem *v, char *base, int32 pos)
case jpiStartsWith:
case jpiDecimal:
case jpiStrReplace:
+ case jpiStrTranslate:
+ case jpiStrSplit:
case jpiStrSplitPart:
+ case jpiStrJoin:
read_int32(v->content.args.left, base, pos);
read_int32(v->content.args.right, base, pos);
break;
@@ -1249,7 +1326,10 @@ jspGetNext(JsonPathItem *v, JsonPathItem *a)
v->type == jpiStrRtrim ||
v->type == jpiStrBtrim ||
v->type == jpiStrInitcap ||
- v->type == jpiStrSplitPart);
+ v->type == jpiStrSplitPart ||
+ v->type == jpiStrSplit ||
+ v->type == jpiStrJoin ||
+ v->type == jpiStrTranslate);
if (a)
jspInitByBuffer(a, v->base, v->nextPos);
@@ -1278,6 +1358,9 @@ jspGetLeftArg(JsonPathItem *v, JsonPathItem *a)
v->type == jpiStartsWith ||
v->type == jpiDecimal ||
v->type == jpiStrReplace ||
+ v->type == jpiStrTranslate ||
+ v->type == jpiStrSplit ||
+ v->type == jpiStrJoin ||
v->type == jpiStrSplitPart);
jspInitByBuffer(a, v->base, v->content.args.left);
@@ -1302,7 +1385,10 @@ jspGetRightArg(JsonPathItem *v, JsonPathItem *a)
v->type == jpiStartsWith ||
v->type == jpiDecimal ||
v->type == jpiStrReplace ||
- v->type == jpiStrSplitPart);
+ v->type == jpiStrTranslate ||
+ v->type == jpiStrSplitPart ||
+ v->type == jpiStrJoin ||
+ v->type == jpiStrSplit);
jspInitByBuffer(a, v->base, v->content.args.right);
}
@@ -1610,6 +1696,9 @@ jspIsMutableWalker(JsonPathItem *jpi, struct JsonPathMutableContext *cxt)
case jpiStrBtrim:
case jpiStrInitcap:
case jpiStrSplitPart:
+ case jpiStrSplit:
+ case jpiStrTranslate:
+ case jpiStrJoin:
status = jpdsNonDateTime;
break;
diff --git a/src/backend/utils/adt/jsonpath_exec.c b/src/backend/utils/adt/jsonpath_exec.c
index 6cc2acb4254..923172c7bcf 100644
--- a/src/backend/utils/adt/jsonpath_exec.c
+++ b/src/backend/utils/adt/jsonpath_exec.c
@@ -1690,6 +1690,8 @@ executeItemOptUnwrapTarget(JsonPathExecContext *cxt, JsonPathItem *jsp,
case jpiStrBtrim:
case jpiStrInitcap:
case jpiStrSplitPart:
+ case jpiStrSplit:
+ case jpiStrTranslate:
{
if (unwrap && JsonbType(jb) == jbvArray)
return executeItemUnwrapTargetArray(cxt, jsp, jb, found, false);
@@ -1697,6 +1699,85 @@ executeItemOptUnwrapTarget(JsonPathExecContext *cxt, JsonPathItem *jsp,
return executeStringInternalMethod(cxt, jsp, jb, found);
}
break;
+ case jpiStrJoin:
+ {
+ JsonPathItem next_elem;
+ JsonbValue jbv_res;
+ char *sep;
+ char *null_replace = NULL;
+ StringInfoData buf;
+ bool first = true;
+ bool hasNext;
+
+ jspGetLeftArg(jsp, &elem);
+ sep = jspGetString(&elem, NULL);
+
+ if (jsp->content.args.right != 0)
+ {
+ jspGetRightArg(jsp, &elem);
+ null_replace = jspGetString(&elem, NULL);
+ }
+
+ /* Validate target is an array */
+ if (JsonbType(jb) != jbvArray)
+ {
+ RETURN_ERROR(ereport(ERROR,
+ (errcode(ERRCODE_SQL_JSON_ARRAY_NOT_FOUND),
+ errmsg("jsonpath item method .join() can only be applied to an array"))));
+ }
+
+ Assert(jb->type == jbvBinary);
+
+ initStringInfo(&buf);
+
+ /* Process the array elements */
+ {
+ JsonbIterator *it;
+ JsonbValue v;
+ JsonbIteratorToken tok;
+
+ it = JsonbIteratorInit(jb->val.binary.data);
+ while ((tok = JsonbIteratorNext(&it, &v, true)) != WJB_DONE)
+ {
+ if (tok != WJB_ELEM)
+ continue;
+
+ if (v.type == jbvString)
+ {
+ if (!first)
+ appendStringInfoString(&buf, sep);
+ appendBinaryStringInfo(&buf, v.val.string.val, v.val.string.len);
+ first = false;
+ }
+ else if (v.type == jbvNull)
+ {
+ if (null_replace)
+ {
+ if (!first)
+ appendStringInfoString(&buf, sep);
+ appendStringInfoString(&buf, null_replace);
+ first = false;
+ }
+ }
+ else
+ {
+ RETURN_ERROR(ereport(ERROR,
+ (errcode(ERRCODE_SQL_JSON_ITEM_CANNOT_BE_CAST_TO_TARGET_TYPE),
+ errmsg("jsonpath .join() array elements must be strings or nulls"))));
+ }
+ }
+ }
+
+ jbv_res.type = jbvString;
+ jbv_res.val.string.val = buf.data;
+ jbv_res.val.string.len = buf.len;
+
+ hasNext = jspGetNext(jsp, &next_elem);
+ if (!hasNext && !found)
+ return jperOk;
+
+ return executeNextItem(cxt, jsp, hasNext ? &next_elem : NULL, &jbv_res, found);
+ }
default:
elog(ERROR, "unrecognized jsonpath item type: %d", jsp->type);
@@ -2921,7 +3002,9 @@ executeStringInternalMethod(JsonPathExecContext *cxt, JsonPathItem *jsp,
jsp->type == jpiStrRtrim ||
jsp->type == jpiStrBtrim ||
jsp->type == jpiStrInitcap ||
- jsp->type == jpiStrSplitPart);
+ jsp->type == jpiStrTranslate ||
+ jsp->type == jpiStrSplitPart ||
+ jsp->type == jpiStrSplit);
if (!(jb = getScalar(jb, jbvString)))
RETURN_ERROR(ereport(ERROR,
@@ -2935,23 +3018,33 @@ executeStringInternalMethod(JsonPathExecContext *cxt, JsonPathItem *jsp,
switch (jsp->type)
{
case jpiStrReplace:
+ case jpiStrTranslate:
{
char *from_str,
*to_str;
+ PGFunction func;
jspGetLeftArg(jsp, &elem);
if (elem.type != jpiString)
- elog(ERROR, "invalid jsonpath item type for .replace() from");
+ elog(ERROR, "invalid jsonpath item type for .%s() from",
+ jspOperationName(jsp->type));
from_str = jspGetString(&elem, NULL);
jspGetRightArg(jsp, &elem);
if (elem.type != jpiString)
- elog(ERROR, "invalid jsonpath item type for .replace() to");
+ elog(ERROR, "invalid jsonpath item type for .%s() to",
+ jspOperationName(jsp->type));
to_str = jspGetString(&elem, NULL);
- resStr = TextDatumGetCString(DirectFunctionCall3Coll(replace_text,
+ /* Dispatch to the correct internal function */
+ if (jsp->type == jpiStrReplace)
+ func = replace_text;
+ else
+ func = translate;
+
+ resStr = TextDatumGetCString(DirectFunctionCall3Coll(func,
DEFAULT_COLLATION_OID,
str,
CStringGetTextDatum(from_str),
@@ -3051,6 +3144,95 @@ executeStringInternalMethod(JsonPathExecContext *cxt, JsonPathItem *jsp,
Int32GetDatum(n)));
break;
}
+ case jpiStrSplit:
+ {
+ char *delim_str;
+ char *null_str = NULL;
+ Datum arr_datum;
+ ArrayType *arr;
+ Datum *elems;
+ bool *nulls;
+ int nelems;
+ JsonbInState state = {0};
+
+ /* Extract the delimiter */
+ jspGetLeftArg(jsp, &elem);
+ if (elem.type != jpiString)
+ elog(ERROR, "invalid jsonpath item type for .split() delimiter");
+
+ delim_str = jspGetString(&elem, NULL);
+
+ /* Extract the optional null_string */
+ if (jsp->content.args.right != 0)
+ {
+ jspGetRightArg(jsp, &elem);
+ if (elem.type != jpiString)
+ elog(ERROR, "invalid jsonpath item type for .split() null_string");
+
+ null_str = jspGetString(&elem, NULL);
+ }
+
+ /* forward the execution to internal text_to_array functions */
+ if (null_str)
+ {
+ arr_datum = DirectFunctionCall3Coll(text_to_array_null,
+ DEFAULT_COLLATION_OID,
+ str,
+ CStringGetTextDatum(delim_str),
+ CStringGetTextDatum(null_str));
+ }
+ else
+ {
+ arr_datum = DirectFunctionCall2Coll(text_to_array,
+ DEFAULT_COLLATION_OID,
+ str,
+ CStringGetTextDatum(delim_str));
+ }
+
+ arr = DatumGetArrayTypeP(arr_datum);
+ deconstruct_array_builtin(arr, TEXTOID, &elems, &nulls, &nelems);
+
+ pushJsonbValue(&state, WJB_BEGIN_ARRAY, NULL);
+
+ for (int i = 0; i < nelems; i++)
+ {
+ JsonbValue v;
+
+ if (nulls[i])
+ {
+ v.type = jbvNull;
+ }
+ else
+ {
+ char *val = TextDatumGetCString(elems[i]);
+
+ v.type = jbvString;
+ v.val.string.val = val;
+ v.val.string.len = strlen(val);
+ }
+ pushJsonbValue(&state, WJB_ELEM, &v);
+ }
+
+ pushJsonbValue(&state, WJB_END_ARRAY, NULL);
+
+ {
+ Jsonb *jb_result = JsonbValueToJsonb(state.result);
+ JsonbValue binary_jbv;
+
+ binary_jbv.type = jbvBinary;
+ binary_jbv.val.binary.data = &jb_result->root;
+ binary_jbv.val.binary.len = VARSIZE(jb_result);
+
+ res = jperOk;
+ hasNext = jspGetNext(jsp, &elem);
+
+ if (!hasNext && !found)
+ return res;
+
+ return executeNextItem(cxt, jsp, &elem, &binary_jbv, found);
+ }
+ }
+ break;
default:
elog(ERROR, "unsupported jsonpath item type: %d", jsp->type);
}
diff --git a/src/backend/utils/adt/jsonpath_gram.y b/src/backend/utils/adt/jsonpath_gram.y
index f826697d098..eb2b952e311 100644
--- a/src/backend/utils/adt/jsonpath_gram.y
+++ b/src/backend/utils/adt/jsonpath_gram.y
@@ -87,7 +87,7 @@ static bool makeItemLikeRegex(JsonPathParseItem *expr,
%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
+ STR_INITCAP_P STR_SPLIT_P STR_SPLIT_PART_P STR_TRANSLATE_P STR_JOIN_P
%type <result> result
@@ -282,8 +282,18 @@ accessor_op:
{ $$ = makeItemUnary(jpiTimestampTz, $4); }
| '.' STR_REPLACE_P '(' str_str_args ')'
{ $$ = makeItemBinary(jpiStrReplace, linitial($4), lsecond($4)); }
+ | '.' STR_TRANSLATE_P '(' str_str_args ')'
+ { $$ = makeItemBinary(jpiStrTranslate, linitial($4), lsecond($4)); }
| '.' STR_SPLIT_PART_P '(' str_int_args ')'
{ $$ = makeItemBinary(jpiStrSplitPart, linitial($4), lsecond($4)); }
+ | '.' STR_SPLIT_P '(' str_elem ')'
+ { $$ = makeItemBinary(jpiStrSplit, $4, NULL); }
+ | '.' STR_SPLIT_P '(' str_str_args ')'
+ { $$ = makeItemBinary(jpiStrSplit, linitial($4), lsecond($4)); }
+ | '.' STR_JOIN_P '(' str_elem ')'
+ { $$ = makeItemBinary(jpiStrJoin, $4, NULL); }
+ | '.' STR_JOIN_P '(' str_str_args ')'
+ { $$ = makeItemBinary(jpiStrJoin, linitial($4), lsecond($4)); }
| '.' STR_LTRIM_P '(' opt_str_arg ')'
{ $$ = makeItemUnary(jpiStrLtrim, $4); }
| '.' STR_RTRIM_P '(' opt_str_arg ')'
@@ -381,10 +391,13 @@ key_name:
| STR_UPPER_P
| STR_INITCAP_P
| STR_REPLACE_P
+ | STR_TRANSLATE_P
| STR_SPLIT_PART_P
+ | STR_SPLIT_P
| STR_LTRIM_P
| STR_RTRIM_P
| STR_BTRIM_P
+ | STR_JOIN_P
;
method:
diff --git a/src/backend/utils/adt/jsonpath_scan.l b/src/backend/utils/adt/jsonpath_scan.l
index e4fadcc2e69..bd49941a8fc 100644
--- a/src/backend/utils/adt/jsonpath_scan.l
+++ b/src/backend/utils/adt/jsonpath_scan.l
@@ -406,6 +406,7 @@ static const JsonPathKeyword keywords[] = {
{3, false, LAX_P, "lax"},
{4, false, DATE_P, "date"},
{4, false, FLAG_P, "flag"},
+ {4, false, STR_JOIN_P, "join"},
{4, false, LAST_P, "last"},
{4, true, NULL_P, "null"},
{4, false, SIZE_P, "size"},
@@ -419,6 +420,7 @@ static const JsonPathKeyword keywords[] = {
{5, false, STR_LOWER_P, "lower"},
{5, false, STR_LTRIM_P, "ltrim"},
{5, false, STR_RTRIM_P, "rtrim"},
+ {5, false, STR_SPLIT_P, "split"},
{5, false, STR_UPPER_P, "upper"},
{6, false, BIGINT_P, "bigint"},
{6, false, DOUBLE_P, "double"},
@@ -438,6 +440,7 @@ static const JsonPathKeyword keywords[] = {
{8, false, DATETIME_P, "datetime"},
{8, false, KEYVALUE_P, "keyvalue"},
{9, false, TIMESTAMP_P, "timestamp"},
+ {9, false, STR_TRANSLATE_P, "translate"},
{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 8d27206e242..f61639e3767 100644
--- a/src/include/utils/jsonpath.h
+++ b/src/include/utils/jsonpath.h
@@ -115,6 +115,7 @@ typedef enum JsonPathItemType
jpiTimeTz, /* .time_tz() item method */
jpiTimestamp, /* .timestamp() item method */
jpiTimestampTz, /* .timestamp_tz() item method */
+ jpiStrJoin, /* .join() item method */
jpiStrReplace, /* .replace() item method */
jpiStrLower, /* .lower() item method */
jpiStrUpper, /* .upper() item method */
@@ -123,6 +124,8 @@ typedef enum JsonPathItemType
jpiStrBtrim, /* .btrim() item method */
jpiStrInitcap, /* .initcap() item method */
jpiStrSplitPart, /* .split_part() item method */
+ jpiStrSplit, /* .split() item method */
+ jpiStrTranslate, /* .translate() 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 81efebc3d0f..5430fc083fa 100644
--- a/src/test/regress/expected/jsonb_jsonpath.out
+++ b/src/test/regress/expected/jsonb_jsonpath.out
@@ -3060,6 +3060,69 @@ select jsonb_path_query('"hello world"', '$.replace("hello","bye") starts with "
true
(1 row)
+-- Test .translate()
+select jsonb_path_query('null', '$.translate("x", "bye")');
+ERROR: jsonpath item method .translate() can only be applied to a string
+select jsonb_path_query('null', '$.translate("x", "bye")', silent => true);
+ jsonb_path_query
+------------------
+(0 rows)
+
+select jsonb_path_query('["x", "y", "z"]', '$.translate("x", "bye")');
+ jsonb_path_query
+------------------
+ "b"
+ "y"
+ "z"
+(3 rows)
+
+select jsonb_path_query('{}', '$.translate("x", "bye")');
+ERROR: jsonpath item method .translate() can only be applied to a string
+select jsonb_path_query('[]', 'strict $.translate("x", "bye")', silent => true);
+ jsonb_path_query
+------------------
+(0 rows)
+
+select jsonb_path_query('{}', '$.translate("x", "bye")', silent => true);
+ jsonb_path_query
+------------------
+(0 rows)
+
+select jsonb_path_query('1.23', '$.translate("x", "bye")');
+ERROR: jsonpath item method .translate() can only be applied to a string
+select jsonb_path_query('"hello world"', '$.translate("hello","bye")');
+ jsonb_path_query
+------------------
+ "byee wred"
+(1 row)
+
+select jsonb_path_query('"hello world"', '$.translate("hello","bye") starts with "bye"');
+ jsonb_path_query
+------------------
+ true
+(1 row)
+
+-- Pure deletion (remove all hyphens)
+select jsonb_path_query('"12-34-56"', '$.translate("-", "")');
+ jsonb_path_query
+------------------
+ "123456"
+(1 row)
+
+-- Overlapping characters in 'from' argument (should use first occurrence mapping)
+select jsonb_path_query('"aab"', '$.translate("aab", "xy")');
+ jsonb_path_query
+------------------
+ "xx"
+(1 row)
+
+-- No match (should return original string)
+select jsonb_path_query('"xyz"', '$.translate("abc", "def")');
+ jsonb_path_query
+------------------
+ "xyz"
+(1 row)
+
-- Test .split_part()
select jsonb_path_query('"abc~@~def~@~ghi"', '$.split_part("~@~", 2)');
jsonb_path_query
@@ -3087,6 +3150,156 @@ select jsonb_path_query('"a,b"', '$.split_part(",", 2147483648)', silent => true
------------------
(0 rows)
+-- Test .split()
+select jsonb_path_query('"a,b,c"', '$.split(",")');
+ jsonb_path_query
+------------------
+ ["a", "b", "c"]
+(1 row)
+
+select jsonb_path_query('"a,,c"', '$.split(",", "")');
+ jsonb_path_query
+------------------
+ ["a", null, "c"]
+(1 row)
+
+-- proving the output is a real, indexable JSON array
+select jsonb_path_query('"a,b,c"', '$.split(",")[1]');
+ jsonb_path_query
+------------------
+ "b"
+(1 row)
+
+-- Empty string delimiter (splits into individual characters)
+select jsonb_path_query('"abc"', '$.split("")');
+ jsonb_path_query
+------------------
+ ["abc"]
+(1 row)
+
+-- Leading, trailing, and consecutive delimiters
+select jsonb_path_query('",a,,b,"', '$.split(",")');
+ jsonb_path_query
+------------------------
+ ["", "a", "", "b", ""]
+(1 row)
+
+-- Perfect match with null_string
+select jsonb_path_query('"N/A"', '$.split(",", "N/A")');
+ jsonb_path_query
+------------------
+ [null]
+(1 row)
+
+-- No delimiter match
+select jsonb_path_query('"abc"', '$.split(",")');
+ jsonb_path_query
+------------------
+ ["abc"]
+(1 row)
+
+-- Test .join() method
+select jsonb_path_query('["a", "b", "c"]', '$.join("-")');
+ jsonb_path_query
+------------------
+ "a-b-c"
+(1 row)
+
+-- Join with null replacement
+select jsonb_path_query('["a", "b", "c"]', '$.join("-", "N/A")');
+ jsonb_path_query
+------------------
+ "a-b-c"
+(1 row)
+
+-- Null handling: default (skip)
+select jsonb_path_query('["a", null, "c"]', '$.join("-")');
+ jsonb_path_query
+------------------
+ "a-c"
+(1 row)
+
+-- Null handling: replacement
+select jsonb_path_query('["a", null, "c"]', '$.join("-", "N/A")');
+ jsonb_path_query
+------------------
+ "a-N/A-c"
+(1 row)
+
+-- Empty array (should return empty string)
+select jsonb_path_query('[]', '$.join("-")');
+ jsonb_path_query
+------------------
+ ""
+(1 row)
+
+-- Pipeline integration: .join().upper()
+select jsonb_path_query('["hello", "world"]', '$.join(" ").upper()');
+ jsonb_path_query
+------------------
+ "HELLO WORLD"
+(1 row)
+
+-- Pipeline integration: .split().join()
+select jsonb_path_query('"a,b,c"', '$.split(",").join("|")');
+ jsonb_path_query
+------------------
+ "a|b|c"
+(1 row)
+
+-- Error case: Non-string element (should trigger our ereport)
+select jsonb_path_query('[1, "a"]', '$.join("-")');
+ERROR: jsonpath .join() array elements must be strings or nulls
+-- Error case: Nested object
+select jsonb_path_query('["a", {"b": 1}]', '$.join("-")');
+ERROR: jsonpath .join() array elements must be strings or nulls
+-- Error case: Applied to a scalar
+select jsonb_path_query('"not an array"', '$.join("-")');
+ERROR: jsonpath item method .join() can only be applied to an array
+-- Lax mode: should still error under current conservative implementation
+select jsonb_path_query('[1, "a"]', 'lax $.join("-")');
+ERROR: jsonpath .join() array elements must be strings or nulls
+select jsonb_path_query('"not an array"', 'lax $.join("-")');
+ERROR: jsonpath item method .join() can only be applied to an array
+-- Silent mode: should suppress errors and return no rows
+select jsonb_path_query('[1, "a"]', '$.join("-")', silent => true);
+ jsonb_path_query
+------------------
+(0 rows)
+
+select jsonb_path_query('"not an array"', '$.join("-")', silent => true);
+ jsonb_path_query
+------------------
+(0 rows)
+
+-- Empty separator
+select jsonb_path_query('["a", "b", "c"]', '$.join("")');
+ jsonb_path_query
+------------------
+ "abc"
+(1 row)
+
+-- Array with only nulls: default (skip, should return empty string)
+select jsonb_path_query('[null, null]', '$.join(",")');
+ jsonb_path_query
+------------------
+ ""
+(1 row)
+
+-- Array with only nulls: replacement
+select jsonb_path_query('[null, null]', '$.join(",", "N/A")');
+ jsonb_path_query
+------------------
+ "N/A,N/A"
+(1 row)
+
+-- Single element array
+select jsonb_path_query('["a"]', '$.join(",")');
+ jsonb_path_query
+------------------
+ "a"
+(1 row)
+
-- Test string methods play nicely together
select jsonb_path_query('"hello world"', '$.replace("hello","bye").upper()');
jsonb_path_query
@@ -3118,6 +3331,12 @@ select jsonb_path_query('" hElLo WorlD "', '$.btrim().lower().upper().lower().
true
(1 row)
+select jsonb_path_query('" A,b,C "', '$.btrim().lower().split(",").join("-").replace("a","x").upper() starts with "X-B"');
+ 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 ea971e79854..6b64603a0bf 100644
--- a/src/test/regress/expected/jsonpath.out
+++ b/src/test/regress/expected/jsonpath.out
@@ -441,6 +441,12 @@ select '$.replace("hello","bye")'::jsonpath;
$.replace("hello","bye")
(1 row)
+select '$.translate("hello","bye")'::jsonpath;
+ jsonpath
+----------------------------
+ $.translate("hello","bye")
+(1 row)
+
select '$.lower()'::jsonpath;
jsonpath
-----------
@@ -507,6 +513,24 @@ select '$.split_part("~@~", 2)'::jsonpath;
$.split_part("~@~",2)
(1 row)
+select '$.split(",")'::jsonpath;
+ jsonpath
+--------------
+ $.split(",")
+(1 row)
+
+select '$.join(",")'::jsonpath;
+ jsonpath
+-------------
+ $.join(",")
+(1 row)
+
+select '$.join(",", "N/A")'::jsonpath;
+ jsonpath
+--------------------
+ $.join(",", "N/A")
+(1 row)
+
-- Parse errors
select '$.replace("hello")'::jsonpath;
ERROR: syntax error at or near ")" of jsonpath input
@@ -536,6 +560,22 @@ select '$.split_part("~@~", 2, "extra")'::jsonpath;
ERROR: syntax error at or near "," of jsonpath input
LINE 1: select '$.split_part("~@~", 2, "extra")'::jsonpath;
^
+select '$.split()'::jsonpath;
+ERROR: syntax error at or near ")" of jsonpath input
+LINE 1: select '$.split()'::jsonpath;
+ ^
+select '$.split(",", "null", "extra")'::jsonpath;
+ERROR: syntax error at or near "," of jsonpath input
+LINE 1: select '$.split(",", "null", "extra")'::jsonpath;
+ ^
+select '$.split(1)'::jsonpath;
+ERROR: syntax error at or near "1" of jsonpath input
+LINE 1: select '$.split(1)'::jsonpath;
+ ^
+select '$.split(",", 1)'::jsonpath;
+ERROR: syntax error at or near "1" of jsonpath input
+LINE 1: select '$.split(",", 1)'::jsonpath;
+ ^
select '$.lower("hi")'::jsonpath;
ERROR: syntax error at or near """ of jsonpath input
LINE 1: select '$.lower("hi")'::jsonpath;
@@ -603,6 +643,12 @@ select '$.split_part'::jsonpath;
$."split_part"
(1 row)
+select '$.split'::jsonpath;
+ jsonpath
+-----------
+ $."split"
+(1 row)
+
select '$.ltrim'::jsonpath;
jsonpath
-----------
@@ -621,6 +667,26 @@ select '$.btrim'::jsonpath;
$."btrim"
(1 row)
+select '$.join()'::jsonpath;
+ERROR: syntax error at or near ")" of jsonpath input
+LINE 1: select '$.join()'::jsonpath;
+ ^
+select '$.join(",", "replacement", "extra")'::jsonpath;
+ERROR: syntax error at or near "," of jsonpath input
+LINE 1: select '$.join(",", "replacement", "extra")'::jsonpath;
+ ^
+select '$.join(42)'::jsonpath;
+ERROR: syntax error at or near "42" of jsonpath input
+LINE 1: select '$.join(42)'::jsonpath;
+ ^
+select '$.join(true)'::jsonpath;
+ERROR: syntax error at end of jsonpath input
+LINE 1: select '$.join(true)'::jsonpath;
+ ^
+select '$.join("x", 123)'::jsonpath;
+ERROR: syntax error at or near "123" of jsonpath input
+LINE 1: select '$.join("x", 123)'::jsonpath;
+ ^
select '$.time()'::jsonpath;
jsonpath
----------
diff --git a/src/test/regress/expected/sqljson_queryfuncs.out b/src/test/regress/expected/sqljson_queryfuncs.out
index 57e52e963f6..2df3e34d0e0 100644
--- a/src/test/regress/expected/sqljson_queryfuncs.out
+++ b/src/test/regress/expected/sqljson_queryfuncs.out
@@ -1279,7 +1279,10 @@ CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.lower()'));
CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.upper()'));
CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.initcap()'));
CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.replace("hello", "bye")'));
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.translate("hello", "bye")'));
CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.split_part(",", 2)'));
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.split(",")'));
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.join(",")'));
-- DEFAULT expression
CREATE OR REPLACE FUNCTION ret_setint() RETURNS SETOF integer AS
$$
diff --git a/src/test/regress/sql/jsonb_jsonpath.sql b/src/test/regress/sql/jsonb_jsonpath.sql
index c1f4ab5422e..a492e22f8b1 100644
--- a/src/test/regress/sql/jsonb_jsonpath.sql
+++ b/src/test/regress/sql/jsonb_jsonpath.sql
@@ -718,6 +718,23 @@ 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 .translate()
+select jsonb_path_query('null', '$.translate("x", "bye")');
+select jsonb_path_query('null', '$.translate("x", "bye")', silent => true);
+select jsonb_path_query('["x", "y", "z"]', '$.translate("x", "bye")');
+select jsonb_path_query('{}', '$.translate("x", "bye")');
+select jsonb_path_query('[]', 'strict $.translate("x", "bye")', silent => true);
+select jsonb_path_query('{}', '$.translate("x", "bye")', silent => true);
+select jsonb_path_query('1.23', '$.translate("x", "bye")');
+select jsonb_path_query('"hello world"', '$.translate("hello","bye")');
+select jsonb_path_query('"hello world"', '$.translate("hello","bye") starts with "bye"');
+-- Pure deletion (remove all hyphens)
+select jsonb_path_query('"12-34-56"', '$.translate("-", "")');
+-- Overlapping characters in 'from' argument (should use first occurrence mapping)
+select jsonb_path_query('"aab"', '$.translate("aab", "xy")');
+-- No match (should return original string)
+select jsonb_path_query('"xyz"', '$.translate("abc", "def")');
+
-- Test .split_part()
select jsonb_path_query('"abc~@~def~@~ghi"', '$.split_part("~@~", 2)');
select jsonb_path_query('"abc,def,ghi,jkl"', '$.split_part(",", -2)');
@@ -726,12 +743,62 @@ select jsonb_path_query('"a,b"', '$.split_part(",", 0)', silent => true);
select jsonb_path_query('"a,b"', '$.split_part(",", 2147483648)');
select jsonb_path_query('"a,b"', '$.split_part(",", 2147483648)', silent => true);
+-- Test .split()
+select jsonb_path_query('"a,b,c"', '$.split(",")');
+select jsonb_path_query('"a,,c"', '$.split(",", "")');
+-- proving the output is a real, indexable JSON array
+select jsonb_path_query('"a,b,c"', '$.split(",")[1]');
+-- Empty string delimiter (splits into individual characters)
+select jsonb_path_query('"abc"', '$.split("")');
+-- Leading, trailing, and consecutive delimiters
+select jsonb_path_query('",a,,b,"', '$.split(",")');
+-- Perfect match with null_string
+select jsonb_path_query('"N/A"', '$.split(",", "N/A")');
+-- No delimiter match
+select jsonb_path_query('"abc"', '$.split(",")');
+
+-- Test .join() method
+select jsonb_path_query('["a", "b", "c"]', '$.join("-")');
+-- Join with null replacement
+select jsonb_path_query('["a", "b", "c"]', '$.join("-", "N/A")');
+-- Null handling: default (skip)
+select jsonb_path_query('["a", null, "c"]', '$.join("-")');
+-- Null handling: replacement
+select jsonb_path_query('["a", null, "c"]', '$.join("-", "N/A")');
+-- Empty array (should return empty string)
+select jsonb_path_query('[]', '$.join("-")');
+-- Pipeline integration: .join().upper()
+select jsonb_path_query('["hello", "world"]', '$.join(" ").upper()');
+-- Pipeline integration: .split().join()
+select jsonb_path_query('"a,b,c"', '$.split(",").join("|")');
+-- Error case: Non-string element (should trigger our ereport)
+select jsonb_path_query('[1, "a"]', '$.join("-")');
+-- Error case: Nested object
+select jsonb_path_query('["a", {"b": 1}]', '$.join("-")');
+-- Error case: Applied to a scalar
+select jsonb_path_query('"not an array"', '$.join("-")');
+-- Lax mode: should still error under current conservative implementation
+select jsonb_path_query('[1, "a"]', 'lax $.join("-")');
+select jsonb_path_query('"not an array"', 'lax $.join("-")');
+-- Silent mode: should suppress errors and return no rows
+select jsonb_path_query('[1, "a"]', '$.join("-")', silent => true);
+select jsonb_path_query('"not an array"', '$.join("-")', silent => true);
+-- Empty separator
+select jsonb_path_query('["a", "b", "c"]', '$.join("")');
+-- Array with only nulls: default (skip, should return empty string)
+select jsonb_path_query('[null, null]', '$.join(",")');
+-- Array with only nulls: replacement
+select jsonb_path_query('[null, null]', '$.join(",", "N/A")');
+-- Single element array
+select jsonb_path_query('["a"]', '$.join(",")');
+
-- 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"');
+select jsonb_path_query('" A,b,C "', '$.btrim().lower().split(",").join("-").replace("a","x").upper() starts with "X-B"');
-- Test .time()
select jsonb_path_query('null', '$.time()');
diff --git a/src/test/regress/sql/jsonpath.sql b/src/test/regress/sql/jsonpath.sql
index 44178d8b45a..c524dc7a3d4 100644
--- a/src/test/regress/sql/jsonpath.sql
+++ b/src/test/regress/sql/jsonpath.sql
@@ -79,6 +79,7 @@ select '$.date()'::jsonpath;
select '$.decimal(4,2)'::jsonpath;
select '$.string()'::jsonpath;
select '$.replace("hello","bye")'::jsonpath;
+select '$.translate("hello","bye")'::jsonpath;
select '$.lower()'::jsonpath;
select '$.upper()'::jsonpath;
select '$.lower().upper().lower().replace("hello","bye")'::jsonpath;
@@ -90,6 +91,9 @@ select '$.btrim()'::jsonpath;
select '$.btrim("xyz")'::jsonpath;
select '$.initcap()'::jsonpath;
select '$.split_part("~@~", 2)'::jsonpath;
+select '$.split(",")'::jsonpath;
+select '$.join(",")'::jsonpath;
+select '$.join(",", "N/A")'::jsonpath;
-- Parse errors
select '$.replace("hello")'::jsonpath;
@@ -99,6 +103,10 @@ select '$.split_part("~@~")'::jsonpath;
select '$.split_part()'::jsonpath;
select '$.split_part("~@~", "hi")'::jsonpath;
select '$.split_part("~@~", 2, "extra")'::jsonpath;
+select '$.split()'::jsonpath;
+select '$.split(",", "null", "extra")'::jsonpath;
+select '$.split(1)'::jsonpath;
+select '$.split(",", 1)'::jsonpath;
select '$.lower("hi")'::jsonpath;
select '$.upper("hi")'::jsonpath;
select '$.initcap("hi")'::jsonpath;
@@ -115,9 +123,15 @@ select '$.upper'::jsonpath;
select '$.initcap'::jsonpath;
select '$.replace'::jsonpath;
select '$.split_part'::jsonpath;
+select '$.split'::jsonpath;
select '$.ltrim'::jsonpath;
select '$.rtrim'::jsonpath;
select '$.btrim'::jsonpath;
+select '$.join()'::jsonpath;
+select '$.join(",", "replacement", "extra")'::jsonpath;
+select '$.join(42)'::jsonpath;
+select '$.join(true)'::jsonpath;
+select '$.join("x", 123)'::jsonpath;
select '$.time()'::jsonpath;
select '$.time(6)'::jsonpath;
diff --git a/src/test/regress/sql/sqljson_queryfuncs.sql b/src/test/regress/sql/sqljson_queryfuncs.sql
index d218b44ea47..ba9d7b7ee47 100644
--- a/src/test/regress/sql/sqljson_queryfuncs.sql
+++ b/src/test/regress/sql/sqljson_queryfuncs.sql
@@ -409,7 +409,10 @@ CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.lower()'));
CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.upper()'));
CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.initcap()'));
CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.replace("hello", "bye")'));
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.translate("hello", "bye")'));
CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.split_part(",", 2)'));
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.split(",")'));
+CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.join(",")'));
-- DEFAULT expression
CREATE OR REPLACE FUNCTION ret_setint() RETURNS SETOF integer AS
--
2.54.0
