On 05/21/2015 10:15 AM, Robert Haas wrote:
On Wed, May 20, 2015 at 3:42 PM, Andres Freund <and...@anarazel.de> wrote:
On 2015-05-20 15:37:15 -0400, Tom Lane wrote:
Josh Berkus <j...@agliodbs.com> writes:
That does cover all bases, and users would be able to create the
operator which suits their particular use case easily. It's also fairly
similar to how jsquery works, although the syntax is completely different.
But ... it's after feature freeze. So, thoughts?
I think this could be seen as a correction/bug fix for a pre-freeze
feature. We should not be too resistant to filing down rough edges
on new features, even if that involves a spec change.
+1
+1, emphatically.
I appreciate your enthusiasm :-)
Here is a patch that renames jsonb_replace to jsonb_set with a boolean
create_missing flag that defaults to false (should we default it to
true?). With the flag set it's more or less upsert for jsonb. Without,
it's just update.
While the patch is sizable, it's mostly a) docs, b) regression changes,
and c) code changes from changing jsonb_replace to jsonb_set (and
replacePath to setPath in jsonfuncs.c). The actual significant code
changes are still quite small, around 200 lines.
I added a note in the docs to make the semantics of jsonb || jsonb much
more explicit.
This change really makes this set of jsonb features quite a bit more
compelling. I'm glad I thought of it - wish I had done so earlier. So
notwithstanding the controversy upthread, I think this is a good result.
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 9987c73..65f5b0e 100644
--- a/src/backend/utils/adt/jsonfuncs.c
+++ b/src/backend/utils/adt/jsonfuncs.c
@@ -124,19 +124,19 @@ 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 *walkJsonb(JsonbIterator **it, JsonbParseState **state, bool stop_at_level_zero);
-static JsonbValue *replacePath(JsonbIterator **it, Datum *path_elems,
+static JsonbValue *setPath(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,
+ 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 nelems);
-static void replacePathArray(JsonbIterator **it, Datum *path_elems, bool *path_nulls,
+ Jsonb *newval, uint32 nelems, bool create);
+static void setPathArray(JsonbIterator **it, Datum *path_elems, bool *path_nulls,
int path_len, JsonbParseState **st, int level,
- Jsonb *newval, uint32 npairs);
+ Jsonb *newval, uint32 npairs, bool create);
static void addJsonbToParseState(JsonbParseState **jbps, Jsonb * jb);
/* state for json_object_keys */
@@ -3459,14 +3459,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;
@@ -3482,7 +3484,7 @@ 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)
PG_RETURN_JSONB(in);
@@ -3495,7 +3497,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);
@@ -3539,7 +3542,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);
@@ -3708,12 +3711,18 @@ walkJsonb(JsonbIterator **it, JsonbParseState **state, bool stop_at_level_zero)
/*
- * 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;
@@ -3725,8 +3734,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);
@@ -3734,8 +3743,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);
@@ -3753,12 +3762,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 nelems, bool create)
{
JsonbValue v;
int i;
@@ -3786,16 +3795,26 @@ 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 == nelems - 1)
+ {
+ JsonbValue new = k;
+ 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);
@@ -3820,17 +3839,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 npairs, bool create)
{
JsonbValue v;
int idx,
i;
char *badp;
+ bool done = false;
/* pick correct index */
if (level < path_len && !path_nulls[level])
@@ -3848,14 +3868,23 @@ replacePathArray(JsonbIterator **it, Datum *path_elems, bool *path_nulls,
if (idx < 0)
{
if (-idx > npairs)
- idx = npairs;
+ idx = -1;
else
idx = npairs + idx;
}
- if (idx > npairs)
+ if (idx > 0 && idx > npairs)
idx = npairs;
+ /* if we're creating, and idx == -1, we prepend the new value to the array */
+
+ if (idx == -1 && create && level == path_len - 1)
+ {
+ Assert(newval != NULL);
+ addJsonbToParseState(st, newval);
+ done = true;
+ }
+
/* iterate over the array elements */
for (i = 0; i < npairs; i++)
{
@@ -3868,10 +3897,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
{
@@ -3895,6 +3926,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 == npairs - 1)
+ {
+ addJsonbToParseState(st, newval);
+ }
+
}
}
}
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index 0405027..3a0ebbd 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -4859,7 +4859,7 @@ 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_ ));
+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("Replace 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");
diff --git a/src/include/utils/jsonb.h b/src/include/utils/jsonb.h
index 7b56175..6093651 100644
--- a/src/include/utils/jsonb.h
+++ b/src/include/utils/jsonb.h
@@ -406,7 +406,7 @@ Datum jsonb_delete_idx(PG_FUNCTION_ARGS);
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..6a78bbd 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,20 @@ 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);
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
