Here is the latest version of the refinement of jsonb_replace into
jsonb_set. All known bugs have been fixed, and the only issue is the
default value of the fourth parameter. Currently it's set to false, but
I gather from the previous responses that the consensus is to make it true.
cheers
andrew
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 89a609f..a33f03d 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -10323,6 +10323,15 @@ table2-mapping
</tgroup>
</table>
+ <note>
+ <para>
+ The <literal>||</> operator concatenates the elements at the top level of
+ each of its operands. It does not operate recursively. For example, if
+ both operands are objects with a common key field name, the value of the
+ field in the result will just be the value from the right hand operand.
+ </para>
+ </note>
+
<para>
<xref linkend="functions-json-creation-table"> shows the functions that are
available for creating <type>json</type> and <type>jsonb</type> values.
@@ -10830,17 +10839,24 @@ table2-mapping
<entry><literal>[{"f1":1},2,null,3]</literal></entry>
</row>
<row>
- <entry><para><literal>jsonb_replace(target jsonb, path text[], replacement jsonb)</literal>
+ <entry><para><literal>jsonb_set(target jsonb, path text[], new_value jsonb<optional>, <parameter>create_missing</parameter> <type>boolean</type></optional>)</literal>
</para></entry>
<entry><para><type>jsonb</type></para></entry>
<entry>
Returns <replaceable>target</replaceable>
- with the section designated by <replaceable>path</replaceable>
- replaced by <replaceable>replacement</replaceable>.
- </entry>
- <entry><literal>jsonb_replace('[{"f1":1,"f2":null},2,null,3]', '{0,f1}','[2,3,4]')</literal></entry>
- <entry><literal>[{"f1":[2,3,4],"f2":null},2,null,3]</literal>
- </entry>
+ with the section designated by <replaceable>path</replaceable>
+ replaced by <replaceable>new_value</replaceable>, or with
+ <replaceable>new_value</replaceable> added if
+ <replaceable>create_missing</replaceable> is true ( default is
+ <literal>false</>) and the item
+ designated by <replaceable>path</replaceable> does not exist.
+ </entry>
+ <entry><para><literal>jsonb_set('[{"f1":1,"f2":null},2,null,3]', '{0,f1}','[2,3,4]')</literal>
+ </para><para><literal>jsonb_set('[{"f1":1,"f2":null},2]', '{0,f3}','[2,3,4]', true)</literal>
+ </para></entry>
+ <entry><para><literal>[{"f1":[2,3,4],"f2":null},2,null,3]</literal>
+ </para><para><literal>[{"f1": 1, "f2": null, "f3": [2, 3, 4]}, 2]</literal>
+ </para></entry>
</row>
<row>
<entry><para><literal>jsonb_pretty(from_json jsonb)</literal>
@@ -10893,6 +10909,27 @@ table2-mapping
<note>
<para>
+ All the items of the <literal>path</> parameter of <literal>jsonb_set</>
+ must be present in the <literal>target</>, except when
+ <literal>create_missing</> is true, in which case all but the last item
+ must be present. If these conditions are not met the <literal>target</>
+ is returned unchanged.
+ </para>
+ <para>
+ If the last path item is an object key, it will be created if it
+ is absent and given the new value. If the last path item is an array
+ index, if it is positive the item to set is found by counting from
+ the left, and if negative by counting from the right - <literal>-1</>
+ designates the rightmost element, and so on.
+ If the item is out of the range -array_length .. array_length -1,
+ and create_missing is true, the new value is added at the beginning
+ of the array if the item is negative, and at the end of the array if
+ it is positive.
+ </para>
+ </note>
+
+ <note>
+ <para>
The <literal>json_typeof</> function's <literal>null</> return value
should not be confused with a SQL NULL. While
calling <literal>json_typeof('null'::json)</> will
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 18921c4..3ff1437 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -922,3 +922,10 @@ RETURNS interval
LANGUAGE INTERNAL
STRICT IMMUTABLE
AS 'make_interval';
+
+CREATE OR REPLACE FUNCTION
+ jsonb_set(jsonb_in jsonb, path text[] , replacement jsonb, create_if_missing boolean DEFAULT false)
+RETURNS jsonb
+LANGUAGE INTERNAL
+STRICT IMMUTABLE
+AS 'jsonb_set';
diff --git a/src/backend/utils/adt/jsonfuncs.c b/src/backend/utils/adt/jsonfuncs.c
index 5143493..e41c869 100644
--- a/src/backend/utils/adt/jsonfuncs.c
+++ b/src/backend/utils/adt/jsonfuncs.c
@@ -124,18 +124,20 @@ static JsonbValue *findJsonbValueFromContainerLen(JsonbContainer *container,
char *key,
uint32 keylen);
-/* functions supporting jsonb_delete, jsonb_replace and jsonb_concat */
+/* functions supporting jsonb_delete, jsonb_set and jsonb_concat */
static JsonbValue *IteratorConcat(JsonbIterator **it1, JsonbIterator **it2,
JsonbParseState **state);
-static JsonbValue *replacePath(JsonbIterator **it, Datum *path_elems,
- bool *path_nulls, int path_len,
- JsonbParseState **st, int level, Jsonb *newval);
-static void replacePathObject(JsonbIterator **it, Datum *path_elems, bool *path_nulls,
- int path_len, JsonbParseState **st, int level,
- Jsonb *newval, uint32 nelems);
-static void replacePathArray(JsonbIterator **it, Datum *path_elems, bool *path_nulls,
- int path_len, JsonbParseState **st, int level,
- Jsonb *newval, uint32 npairs);
+static JsonbValue *setPath(JsonbIterator **it, Datum *path_elems,
+ bool *path_nulls, int path_len,
+ JsonbParseState **st, int level, Jsonb *newval,
+ bool create);
+static void setPathObject(JsonbIterator **it, Datum *path_elems,
+ bool *path_nulls, int path_len, JsonbParseState **st,
+ int level,
+ Jsonb *newval, uint32 npairs, bool create);
+static void setPathArray(JsonbIterator **it, Datum *path_elems,
+ bool *path_nulls, int path_len, JsonbParseState **st,
+ int level, Jsonb *newval, uint32 nelems, bool create);
static void addJsonbToParseState(JsonbParseState **jbps, Jsonb *jb);
/* state for json_object_keys */
@@ -3434,14 +3436,16 @@ jsonb_delete_idx(PG_FUNCTION_ARGS)
}
/*
- * SQL function jsonb_replace(jsonb, text[], jsonb)
+ * SQL function jsonb_set(jsonb, text[], jsonb, boolean)
+ *
*/
Datum
-jsonb_replace(PG_FUNCTION_ARGS)
+jsonb_set(PG_FUNCTION_ARGS)
{
Jsonb *in = PG_GETARG_JSONB(0);
ArrayType *path = PG_GETARG_ARRAYTYPE_P(1);
Jsonb *newval = PG_GETARG_JSONB(2);
+ bool create = PG_GETARG_BOOL(3);
JsonbValue *res = NULL;
Datum *path_elems;
bool *path_nulls;
@@ -3457,9 +3461,9 @@ jsonb_replace(PG_FUNCTION_ARGS)
if (JB_ROOT_IS_SCALAR(in))
ereport(ERROR,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
- errmsg("cannot replace path in scalar")));
+ errmsg("cannot set path in scalar")));
- if (JB_ROOT_COUNT(in) == 0)
+ if (JB_ROOT_COUNT(in) == 0 && !create)
PG_RETURN_JSONB(in);
deconstruct_array(path, TEXTOID, -1, false, 'i',
@@ -3470,7 +3474,8 @@ jsonb_replace(PG_FUNCTION_ARGS)
it = JsonbIteratorInit(&in->root);
- res = replacePath(&it, path_elems, path_nulls, path_len, &st, 0, newval);
+ res = setPath(&it, path_elems, path_nulls, path_len, &st,
+ 0, newval, create);
Assert(res != NULL);
@@ -3514,7 +3519,7 @@ jsonb_delete_path(PG_FUNCTION_ARGS)
it = JsonbIteratorInit(&in->root);
- res = replacePath(&it, path_elems, path_nulls, path_len, &st, 0, NULL);
+ res = setPath(&it, path_elems, path_nulls, path_len, &st, 0, NULL, false);
Assert(res != NULL);
@@ -3633,12 +3638,18 @@ IteratorConcat(JsonbIterator **it1, JsonbIterator **it2,
}
/*
- * do most of the heavy work for jsonb_replace
+ * Do most of the heavy work for jsonb_set
+ *
+ * If newval is null, the element is to be removed.
+ *
+ * If create is true, we create the new value if the key or array index
+ * does not exist. All path elemnts before the last must already exist
+ * whether or not create is true, or nothing is done.
*/
static JsonbValue *
-replacePath(JsonbIterator **it, Datum *path_elems,
+setPath(JsonbIterator **it, Datum *path_elems,
bool *path_nulls, int path_len,
- JsonbParseState **st, int level, Jsonb *newval)
+ JsonbParseState **st, int level, Jsonb *newval, bool create)
{
JsonbValue v;
JsonbValue *res = NULL;
@@ -3650,8 +3661,8 @@ replacePath(JsonbIterator **it, Datum *path_elems,
{
case WJB_BEGIN_ARRAY:
(void) pushJsonbValue(st, r, NULL);
- replacePathArray(it, path_elems, path_nulls, path_len, st, level,
- newval, v.val.array.nElems);
+ setPathArray(it, path_elems, path_nulls, path_len, st, level,
+ newval, v.val.array.nElems, create);
r = JsonbIteratorNext(it, &v, false);
Assert(r == WJB_END_ARRAY);
res = pushJsonbValue(st, r, NULL);
@@ -3659,8 +3670,8 @@ replacePath(JsonbIterator **it, Datum *path_elems,
break;
case WJB_BEGIN_OBJECT:
(void) pushJsonbValue(st, r, NULL);
- replacePathObject(it, path_elems, path_nulls, path_len, st, level,
- newval, v.val.object.nPairs);
+ setPathObject(it, path_elems, path_nulls, path_len, st, level,
+ newval, v.val.object.nPairs, create);
r = JsonbIteratorNext(it, &v, true);
Assert(r == WJB_END_OBJECT);
res = pushJsonbValue(st, r, NULL);
@@ -3678,12 +3689,12 @@ replacePath(JsonbIterator **it, Datum *path_elems,
}
/*
- * Object walker for replacePath
+ * Object walker for setPath
*/
static void
-replacePathObject(JsonbIterator **it, Datum *path_elems, bool *path_nulls,
+setPathObject(JsonbIterator **it, Datum *path_elems, bool *path_nulls,
int path_len, JsonbParseState **st, int level,
- Jsonb *newval, uint32 nelems)
+ Jsonb *newval, uint32 npairs, bool create)
{
JsonbValue v;
int i;
@@ -3693,7 +3704,18 @@ replacePathObject(JsonbIterator **it, Datum *path_elems, bool *path_nulls,
if (level >= path_len || path_nulls[level])
done = true;
- for (i = 0; i < nelems; i++)
+ /* empty object is a special case for create */
+ if ((npairs == 0) && create && (level == path_len - 1))
+ {
+ JsonbValue new = k;
+ new.val.string.len = VARSIZE_ANY_EXHDR(path_elems[level]);
+ new.val.string.val = VARDATA_ANY(path_elems[level]);
+
+ (void) pushJsonbValue(st, WJB_KEY, &new);
+ addJsonbToParseState(st, newval);
+ }
+
+ for (i = 0; i < npairs; i++)
{
int r = JsonbIteratorNext(it, &k, true);
@@ -3712,16 +3734,27 @@ replacePathObject(JsonbIterator **it, Datum *path_elems, bool *path_nulls,
(void) pushJsonbValue(st, WJB_KEY, &k);
addJsonbToParseState(st, newval);
}
+ done = true;
}
else
{
(void) pushJsonbValue(st, r, &k);
- replacePath(it, path_elems, path_nulls, path_len,
- st, level + 1, newval);
+ setPath(it, path_elems, path_nulls, path_len,
+ st, level + 1, newval, create);
}
}
else
{
+ if (create && !done && level == path_len - 1 && i == npairs - 1)
+ {
+ JsonbValue new = k;
+ new.val.string.len = VARSIZE_ANY_EXHDR(path_elems[level]);
+ new.val.string.val = VARDATA_ANY(path_elems[level]);
+
+ (void) pushJsonbValue(st, WJB_KEY, &new);
+ addJsonbToParseState(st, newval);
+ }
+
(void) pushJsonbValue(st, r, &k);
r = JsonbIteratorNext(it, &v, false);
(void) pushJsonbValue(st, r, r < WJB_BEGIN_ARRAY ? &v : NULL);
@@ -3746,17 +3779,18 @@ replacePathObject(JsonbIterator **it, Datum *path_elems, bool *path_nulls,
}
/*
- * Array walker for replacePath
+ * Array walker for setPath
*/
static void
-replacePathArray(JsonbIterator **it, Datum *path_elems, bool *path_nulls,
+setPathArray(JsonbIterator **it, Datum *path_elems, bool *path_nulls,
int path_len, JsonbParseState **st, int level,
- Jsonb *newval, uint32 npairs)
+ Jsonb *newval, uint32 nelems, bool create)
{
JsonbValue v;
int idx,
i;
char *badp;
+ bool done = false;
/* pick correct index */
if (level < path_len && !path_nulls[level])
@@ -3766,24 +3800,37 @@ replacePathArray(JsonbIterator **it, Datum *path_elems, bool *path_nulls,
errno = 0;
idx = (int) strtol(c, &badp, 10);
if (errno != 0 || badp == c)
- idx = npairs;
+ idx = nelems;
}
else
- idx = npairs;
+ idx = nelems;
if (idx < 0)
{
- if (-idx > npairs)
- idx = npairs;
+ if (-idx > nelems)
+ idx = -1;
else
- idx = npairs + idx;
+ idx = nelems + idx;
}
- if (idx > npairs)
- idx = npairs;
+ if (idx > 0 && idx > nelems)
+ idx = nelems;
+
+ /*
+ * if we're creating, and idx == -1, we prepend the new value to the array
+ * also if the array is empty - in which case we don't really care what the
+ * idx value is
+ */
+
+ if ((idx == -1 || nelems == 0) && create && (level == path_len - 1))
+ {
+ Assert(newval != NULL);
+ addJsonbToParseState(st, newval);
+ done = true;
+ }
/* iterate over the array elements */
- for (i = 0; i < npairs; i++)
+ for (i = 0; i < nelems; i++)
{
int r;
@@ -3794,10 +3841,12 @@ replacePathArray(JsonbIterator **it, Datum *path_elems, bool *path_nulls,
r = JsonbIteratorNext(it, &v, true); /* skip */
if (newval != NULL)
addJsonbToParseState(st, newval);
+
+ done = true;
}
else
- (void) replacePath(it, path_elems, path_nulls, path_len,
- st, level + 1, newval);
+ (void) setPath(it, path_elems, path_nulls, path_len,
+ st, level + 1, newval, create);
}
else
{
@@ -3821,6 +3870,12 @@ replacePathArray(JsonbIterator **it, Datum *path_elems, bool *path_nulls,
(void) pushJsonbValue(st, r, r < WJB_BEGIN_ARRAY ? &v : NULL);
}
}
+
+ if (create && !done && level == path_len - 1 && i == nelems - 1)
+ {
+ addJsonbToParseState(st, newval);
+ }
+
}
}
}
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index c0aab38..973eec6 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -4859,8 +4859,8 @@ DATA(insert OID = 3301 ( jsonb_concat PGNSP PGUID 12 1 0 0 0 f f f f t f i 2
DATA(insert OID = 3302 ( jsonb_delete PGNSP PGUID 12 1 0 0 0 f f f f t f i 2 0 3802 "3802 25" _null_ _null_ _null_ _null_ _null_ jsonb_delete _null_ _null_ _null_ ));
DATA(insert OID = 3303 ( jsonb_delete PGNSP PGUID 12 1 0 0 0 f f f f t f i 2 0 3802 "3802 23" _null_ _null_ _null_ _null_ _null_ jsonb_delete_idx _null_ _null_ _null_ ));
DATA(insert OID = 3304 ( jsonb_delete PGNSP PGUID 12 1 0 0 0 f f f f t f i 2 0 3802 "3802 1009" _null_ _null_ _null_ _null_ _null_ jsonb_delete_path _null_ _null_ _null_ ));
-DATA(insert OID = 3305 ( jsonb_replace PGNSP PGUID 12 1 0 0 0 f f f f t f i 3 0 3802 "3802 1009 3802" _null_ _null_ _null_ _null_ _null_ jsonb_replace _null_ _null_ _null_ ));
-DESCR("Replace part of a jsonb");
+DATA(insert OID = 3305 ( jsonb_set PGNSP PGUID 12 1 0 0 0 f f f f t f i 4 0 3802 "3802 1009 3802 16" _null_ _null_ _null_ _null_ _null_ jsonb_set _null_ _null_ _null_ ));
+DESCR("Set part of a jsonb");
DATA(insert OID = 3306 ( jsonb_pretty PGNSP PGUID 12 1 0 0 0 f f f f t f i 1 0 25 "3802" _null_ _null_ _null_ _null_ _null_ jsonb_pretty _null_ _null_ _null_ ));
DESCR("Indented text from jsonb");
/* txid */
diff --git a/src/include/utils/jsonb.h b/src/include/utils/jsonb.h
index 026ed55..3049a87 100644
--- a/src/include/utils/jsonb.h
+++ b/src/include/utils/jsonb.h
@@ -406,7 +406,7 @@ extern Datum jsonb_delete_idx(PG_FUNCTION_ARGS);
extern Datum jsonb_delete_path(PG_FUNCTION_ARGS);
/* replacement */
-extern Datum jsonb_replace(PG_FUNCTION_ARGS);
+extern Datum jsonb_set(PG_FUNCTION_ARGS);
/* Support functions */
extern uint32 getJsonbOffset(const JsonbContainer *jc, int index);
diff --git a/src/test/regress/expected/jsonb.out b/src/test/regress/expected/jsonb.out
index c589cd1..9274bbe 100644
--- a/src/test/regress/expected/jsonb.out
+++ b/src/test/regress/expected/jsonb.out
@@ -3079,62 +3079,62 @@ select '{"a":1, "b":2, "c":3}'::jsonb - -4;
{"a": 1, "b": 2, "c": 3}
(1 row)
-select jsonb_replace('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{n}', '[1,2,3]');
- jsonb_replace
+select jsonb_set('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{n}', '[1,2,3]');
+ jsonb_set
--------------------------------------------------------------------------
{"a": 1, "b": [1, 2], "c": {"1": 2}, "d": {"1": [2, 3]}, "n": [1, 2, 3]}
(1 row)
-select jsonb_replace('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{b,-1}', '[1,2,3]');
- jsonb_replace
+select jsonb_set('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{b,-1}', '[1,2,3]');
+ jsonb_set
-----------------------------------------------------------------------------
{"a": 1, "b": [1, [1, 2, 3]], "c": {"1": 2}, "d": {"1": [2, 3]}, "n": null}
(1 row)
-select jsonb_replace('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{d,1,0}', '[1,2,3]');
- jsonb_replace
+select jsonb_set('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{d,1,0}', '[1,2,3]');
+ jsonb_set
-----------------------------------------------------------------------------
{"a": 1, "b": [1, 2], "c": {"1": 2}, "d": {"1": [[1, 2, 3], 3]}, "n": null}
(1 row)
-select jsonb_replace('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{d,NULL,0}', '[1,2,3]');
- jsonb_replace
+select jsonb_set('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{d,NULL,0}', '[1,2,3]');
+ jsonb_set
---------------------------------------------------------------------
{"a": 1, "b": [1, 2], "c": {"1": 2}, "d": {"1": [2, 3]}, "n": null}
(1 row)
-select jsonb_replace('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{n}', '{"1": 2}');
- jsonb_replace
+select jsonb_set('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{n}', '{"1": 2}');
+ jsonb_set
-------------------------------------------------------------------------
{"a": 1, "b": [1, 2], "c": {"1": 2}, "d": {"1": [2, 3]}, "n": {"1": 2}}
(1 row)
-select jsonb_replace('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{b,-1}', '{"1": 2}');
- jsonb_replace
+select jsonb_set('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{b,-1}', '{"1": 2}');
+ jsonb_set
----------------------------------------------------------------------------
{"a": 1, "b": [1, {"1": 2}], "c": {"1": 2}, "d": {"1": [2, 3]}, "n": null}
(1 row)
-select jsonb_replace('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{d,1,0}', '{"1": 2}');
- jsonb_replace
+select jsonb_set('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{d,1,0}', '{"1": 2}');
+ jsonb_set
----------------------------------------------------------------------------
{"a": 1, "b": [1, 2], "c": {"1": 2}, "d": {"1": [{"1": 2}, 3]}, "n": null}
(1 row)
-select jsonb_replace('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{d,NULL,0}', '{"1": 2}');
- jsonb_replace
+select jsonb_set('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{d,NULL,0}', '{"1": 2}');
+ jsonb_set
---------------------------------------------------------------------
{"a": 1, "b": [1, 2], "c": {"1": 2}, "d": {"1": [2, 3]}, "n": null}
(1 row)
-select jsonb_replace('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{b,-1}', '"test"');
- jsonb_replace
+select jsonb_set('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{b,-1}', '"test"');
+ jsonb_set
--------------------------------------------------------------------------
{"a": 1, "b": [1, "test"], "c": {"1": 2}, "d": {"1": [2, 3]}, "n": null}
(1 row)
-select jsonb_replace('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{b,-1}', '{"f": "test"}');
- jsonb_replace
+select jsonb_set('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{b,-1}', '{"f": "test"}');
+ jsonb_set
---------------------------------------------------------------------------------
{"a": 1, "b": [1, {"f": "test"}], "c": {"1": 2}, "d": {"1": [2, 3]}, "n": null}
(1 row)
@@ -3218,17 +3218,59 @@ select '[]'::jsonb - '{a}'::text[];
[]
(1 row)
-select jsonb_replace('"a"','{a}','"b"'); --error
-ERROR: cannot replace path in scalar
-select jsonb_replace('{}','{a}','"b"');
- jsonb_replace
----------------
+select jsonb_set('"a"','{a}','"b"'); --error
+ERROR: cannot set path in scalar
+select jsonb_set('{}','{a}','"b"');
+ jsonb_set
+-----------
{}
(1 row)
-select jsonb_replace('[]','{1}','"b"');
- jsonb_replace
----------------
+select jsonb_set('[]','{1}','"b"');
+ jsonb_set
+-----------
[]
(1 row)
+-- jsonb_set adding instead of replacing
+-- prepend to array
+select jsonb_set('{"a":1,"b":[0,1,2],"c":{"d":4}}','{b,-33}','{"foo":123}', true);
+ jsonb_set
+-------------------------------------------------------
+ {"a": 1, "b": [{"foo": 123}, 0, 1, 2], "c": {"d": 4}}
+(1 row)
+
+-- append to array
+select jsonb_set('{"a":1,"b":[0,1,2],"c":{"d":4}}','{b,33}','{"foo":123}', true);
+ jsonb_set
+-------------------------------------------------------
+ {"a": 1, "b": [0, 1, 2, {"foo": 123}], "c": {"d": 4}}
+(1 row)
+
+-- check nesting levels addition
+select jsonb_set('{"a":1,"b":[4,5,[0,1,2],6,7],"c":{"d":4}}','{b,2,33}','{"foo":123}', true);
+ jsonb_set
+---------------------------------------------------------------------
+ {"a": 1, "b": [4, 5, [0, 1, 2, {"foo": 123}], 6, 7], "c": {"d": 4}}
+(1 row)
+
+-- add new key
+select jsonb_set('{"a":1,"b":[0,1,2],"c":{"d":4}}','{c,e}','{"foo":123}', true);
+ jsonb_set
+------------------------------------------------------------
+ {"a": 1, "b": [0, 1, 2], "c": {"d": 4, "e": {"foo": 123}}}
+(1 row)
+
+-- adding doesn't do anything if elements before last aren't present
+select jsonb_set('{"a":1,"b":[0,1,2],"c":{"d":4}}','{x,-33}','{"foo":123}', true);
+ jsonb_set
+-----------------------------------------
+ {"a": 1, "b": [0, 1, 2], "c": {"d": 4}}
+(1 row)
+
+select jsonb_set('{"a":1,"b":[0,1,2],"c":{"d":4}}','{x,y}','{"foo":123}', true);
+ jsonb_set
+-----------------------------------------
+ {"a": 1, "b": [0, 1, 2], "c": {"d": 4}}
+(1 row)
+
diff --git a/src/test/regress/expected/jsonb_1.out b/src/test/regress/expected/jsonb_1.out
index c4b51e5..2e70c69 100644
--- a/src/test/regress/expected/jsonb_1.out
+++ b/src/test/regress/expected/jsonb_1.out
@@ -3079,62 +3079,62 @@ select '{"a":1, "b":2, "c":3}'::jsonb - -4;
{"a": 1, "b": 2, "c": 3}
(1 row)
-select jsonb_replace('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{n}', '[1,2,3]');
- jsonb_replace
+select jsonb_set('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{n}', '[1,2,3]');
+ jsonb_set
--------------------------------------------------------------------------
{"a": 1, "b": [1, 2], "c": {"1": 2}, "d": {"1": [2, 3]}, "n": [1, 2, 3]}
(1 row)
-select jsonb_replace('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{b,-1}', '[1,2,3]');
- jsonb_replace
+select jsonb_set('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{b,-1}', '[1,2,3]');
+ jsonb_set
-----------------------------------------------------------------------------
{"a": 1, "b": [1, [1, 2, 3]], "c": {"1": 2}, "d": {"1": [2, 3]}, "n": null}
(1 row)
-select jsonb_replace('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{d,1,0}', '[1,2,3]');
- jsonb_replace
+select jsonb_set('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{d,1,0}', '[1,2,3]');
+ jsonb_set
-----------------------------------------------------------------------------
{"a": 1, "b": [1, 2], "c": {"1": 2}, "d": {"1": [[1, 2, 3], 3]}, "n": null}
(1 row)
-select jsonb_replace('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{d,NULL,0}', '[1,2,3]');
- jsonb_replace
+select jsonb_set('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{d,NULL,0}', '[1,2,3]');
+ jsonb_set
---------------------------------------------------------------------
{"a": 1, "b": [1, 2], "c": {"1": 2}, "d": {"1": [2, 3]}, "n": null}
(1 row)
-select jsonb_replace('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{n}', '{"1": 2}');
- jsonb_replace
+select jsonb_set('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{n}', '{"1": 2}');
+ jsonb_set
-------------------------------------------------------------------------
{"a": 1, "b": [1, 2], "c": {"1": 2}, "d": {"1": [2, 3]}, "n": {"1": 2}}
(1 row)
-select jsonb_replace('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{b,-1}', '{"1": 2}');
- jsonb_replace
+select jsonb_set('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{b,-1}', '{"1": 2}');
+ jsonb_set
----------------------------------------------------------------------------
{"a": 1, "b": [1, {"1": 2}], "c": {"1": 2}, "d": {"1": [2, 3]}, "n": null}
(1 row)
-select jsonb_replace('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{d,1,0}', '{"1": 2}');
- jsonb_replace
+select jsonb_set('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{d,1,0}', '{"1": 2}');
+ jsonb_set
----------------------------------------------------------------------------
{"a": 1, "b": [1, 2], "c": {"1": 2}, "d": {"1": [{"1": 2}, 3]}, "n": null}
(1 row)
-select jsonb_replace('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{d,NULL,0}', '{"1": 2}');
- jsonb_replace
+select jsonb_set('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{d,NULL,0}', '{"1": 2}');
+ jsonb_set
---------------------------------------------------------------------
{"a": 1, "b": [1, 2], "c": {"1": 2}, "d": {"1": [2, 3]}, "n": null}
(1 row)
-select jsonb_replace('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{b,-1}', '"test"');
- jsonb_replace
+select jsonb_set('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{b,-1}', '"test"');
+ jsonb_set
--------------------------------------------------------------------------
{"a": 1, "b": [1, "test"], "c": {"1": 2}, "d": {"1": [2, 3]}, "n": null}
(1 row)
-select jsonb_replace('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{b,-1}', '{"f": "test"}');
- jsonb_replace
+select jsonb_set('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{b,-1}', '{"f": "test"}');
+ jsonb_set
---------------------------------------------------------------------------------
{"a": 1, "b": [1, {"f": "test"}], "c": {"1": 2}, "d": {"1": [2, 3]}, "n": null}
(1 row)
@@ -3218,17 +3218,59 @@ select '[]'::jsonb - '{a}'::text[];
[]
(1 row)
-select jsonb_replace('"a"','{a}','"b"'); --error
-ERROR: cannot replace path in scalar
-select jsonb_replace('{}','{a}','"b"');
- jsonb_replace
----------------
+select jsonb_set('"a"','{a}','"b"'); --error
+ERROR: cannot set path in scalar
+select jsonb_set('{}','{a}','"b"');
+ jsonb_set
+-----------
{}
(1 row)
-select jsonb_replace('[]','{1}','"b"');
- jsonb_replace
----------------
+select jsonb_set('[]','{1}','"b"');
+ jsonb_set
+-----------
[]
(1 row)
+-- jsonb_set adding instead of replacing
+-- prepend to array
+select jsonb_set('{"a":1,"b":[0,1,2],"c":{"d":4}}','{b,-33}','{"foo":123}', true);
+ jsonb_set
+-------------------------------------------------------
+ {"a": 1, "b": [{"foo": 123}, 0, 1, 2], "c": {"d": 4}}
+(1 row)
+
+-- append to array
+select jsonb_set('{"a":1,"b":[0,1,2],"c":{"d":4}}','{b,33}','{"foo":123}', true);
+ jsonb_set
+-------------------------------------------------------
+ {"a": 1, "b": [0, 1, 2, {"foo": 123}], "c": {"d": 4}}
+(1 row)
+
+-- check nesting levels addition
+select jsonb_set('{"a":1,"b":[4,5,[0,1,2],6,7],"c":{"d":4}}','{b,2,33}','{"foo":123}', true);
+ jsonb_set
+---------------------------------------------------------------------
+ {"a": 1, "b": [4, 5, [0, 1, 2, {"foo": 123}], 6, 7], "c": {"d": 4}}
+(1 row)
+
+-- add new key
+select jsonb_set('{"a":1,"b":[0,1,2],"c":{"d":4}}','{c,e}','{"foo":123}', true);
+ jsonb_set
+------------------------------------------------------------
+ {"a": 1, "b": [0, 1, 2], "c": {"d": 4, "e": {"foo": 123}}}
+(1 row)
+
+-- adding doesn't do anything if elements before last aren't present
+select jsonb_set('{"a":1,"b":[0,1,2],"c":{"d":4}}','{x,-33}','{"foo":123}', true);
+ jsonb_set
+-----------------------------------------
+ {"a": 1, "b": [0, 1, 2], "c": {"d": 4}}
+(1 row)
+
+select jsonb_set('{"a":1,"b":[0,1,2],"c":{"d":4}}','{x,y}','{"foo":123}', true);
+ jsonb_set
+-----------------------------------------
+ {"a": 1, "b": [0, 1, 2], "c": {"d": 4}}
+(1 row)
+
diff --git a/src/test/regress/sql/jsonb.sql b/src/test/regress/sql/jsonb.sql
index 382a7fb..50cf925 100644
--- a/src/test/regress/sql/jsonb.sql
+++ b/src/test/regress/sql/jsonb.sql
@@ -747,18 +747,18 @@ select '{"a":1, "b":2, "c":3}'::jsonb - -2;
select '{"a":1, "b":2, "c":3}'::jsonb - -3;
select '{"a":1, "b":2, "c":3}'::jsonb - -4;
-select jsonb_replace('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{n}', '[1,2,3]');
-select jsonb_replace('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{b,-1}', '[1,2,3]');
-select jsonb_replace('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{d,1,0}', '[1,2,3]');
-select jsonb_replace('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{d,NULL,0}', '[1,2,3]');
+select jsonb_set('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{n}', '[1,2,3]');
+select jsonb_set('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{b,-1}', '[1,2,3]');
+select jsonb_set('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{d,1,0}', '[1,2,3]');
+select jsonb_set('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{d,NULL,0}', '[1,2,3]');
-select jsonb_replace('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{n}', '{"1": 2}');
-select jsonb_replace('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{b,-1}', '{"1": 2}');
-select jsonb_replace('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{d,1,0}', '{"1": 2}');
-select jsonb_replace('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{d,NULL,0}', '{"1": 2}');
+select jsonb_set('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{n}', '{"1": 2}');
+select jsonb_set('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{b,-1}', '{"1": 2}');
+select jsonb_set('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{d,1,0}', '{"1": 2}');
+select jsonb_set('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{d,NULL,0}', '{"1": 2}');
-select jsonb_replace('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{b,-1}', '"test"');
-select jsonb_replace('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{b,-1}', '{"f": "test"}');
+select jsonb_set('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{b,-1}', '"test"');
+select jsonb_set('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}'::jsonb, '{b,-1}', '{"f": "test"}');
select jsonb_delete('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}', '{n}'::text[]);
select jsonb_delete('{"n":null, "a":1, "b":[1,2], "c":{"1":2}, "d":{"1":[2,3]}}', '{b,-1}'::text[]);
@@ -780,6 +780,26 @@ select '[]'::jsonb - 1;
select '"a"'::jsonb - '{a}'::text[]; -- error
select '{}'::jsonb - '{a}'::text[];
select '[]'::jsonb - '{a}'::text[];
-select jsonb_replace('"a"','{a}','"b"'); --error
-select jsonb_replace('{}','{a}','"b"');
-select jsonb_replace('[]','{1}','"b"');
+select jsonb_set('"a"','{a}','"b"'); --error
+select jsonb_set('{}','{a}','"b"');
+select jsonb_set('[]','{1}','"b"');
+
+-- jsonb_set adding instead of replacing
+
+-- prepend to array
+select jsonb_set('{"a":1,"b":[0,1,2],"c":{"d":4}}','{b,-33}','{"foo":123}', true);
+-- append to array
+select jsonb_set('{"a":1,"b":[0,1,2],"c":{"d":4}}','{b,33}','{"foo":123}', true);
+-- check nesting levels addition
+select jsonb_set('{"a":1,"b":[4,5,[0,1,2],6,7],"c":{"d":4}}','{b,2,33}','{"foo":123}', true);
+-- add new key
+select jsonb_set('{"a":1,"b":[0,1,2],"c":{"d":4}}','{c,e}','{"foo":123}', true);
+-- adding doesn't do anything if elements before last aren't present
+select jsonb_set('{"a":1,"b":[0,1,2],"c":{"d":4}}','{x,-33}','{"foo":123}', true);
+select jsonb_set('{"a":1,"b":[0,1,2],"c":{"d":4}}','{x,y}','{"foo":123}', true);
+-- add to empty object
+select jsonb_set('{}','{x}','{"foo":123}', true);
+--add to empty array
+select jsonb_set('[]','{0}','{"foo":123}', true);
+select jsonb_set('[]','{99}','{"foo":123}', true);
+select jsonb_set('[]','{-99}','{"foo":123}', true);
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
