Hi Vitaly, thanks for the review. I've attached a new version of path with
improvements. Few notes:
> 7. Why did you remove "skip"? It is a comment what "true" means...
Actually, I thought that this comment was about skipping an element from
jsonb in order to change/delete it,
not about the last argument. E.g. you can find several occurrences of
`JsonbIteratorNext` with `true` as the last
argument but without a "last argument is about skip" comment.
And there is a piece of code in the function `jsonb_delete` with a "skip
element" commentary:
```
/* skip corresponding value as well */
if (r == WJB_KEY)
JsonbIteratorNext(&it, &v, true);
```
So since in this patch it's not a simple skipping for setPathArray, I
removed that commentary. Am I wrong?
> 9. And finally... it does not work as expected in case of:
Yes, good catch, thanks.
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index c0b94bc..158e7fb 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -10791,6 +10791,9 @@ table2-mapping
<primary>jsonb_set</primary>
</indexterm>
<indexterm>
+ <primary>jsonb_insert</primary>
+ </indexterm>
+ <indexterm>
<primary>jsonb_pretty</primary>
</indexterm>
@@ -11072,6 +11075,39 @@ table2-mapping
</para></entry>
</row>
<row>
+ <entry>
+ <para><literal>
+ jsonb_insert(target jsonb, path text[], new_value jsonb, <optional><parameter>after</parameter> <type>boolean</type></optional>)
+ </literal></para>
+ </entry>
+ <entry><para><type>jsonb</type></para></entry>
+ <entry>
+ Returns <replaceable>target</replaceable> with
+ <replaceable>new_value</replaceable> inserted.
+ If<replaceable>target</replaceable> section designated by
+ <replaceable>path</replaceable> is a JSONB array,
+ <replaceable>new_value</replaceable> will be inserted before it, or
+ after if <replaceable>after</replaceable> is true (defailt is
+ <literal>false</>). If <replaceable>target</replaceable> section
+ designated by <replaceable>path</replaceable> is a JSONB object,
+ <replaceable>new_value</replaceable> will be added just like a regular
+ key. As with the path orientated operators, negative integers that
+ appear in <replaceable>path</replaceable> count from the end of JSON
+ arrays.
+ </entry>
+ <entry>
+ <para><literal>
+ jsonb_insert('{"a": [0,1,2]}', '{a, 1}', '"new_value"')
+ </literal></para>
+ <para><literal>
+ jsonb_insert('{"a": [0,1,2]}', '{a, 1}', '"new_value"', true)
+ </literal></para>
+ </entry>
+ <entry><para><literal>{"a": [0, "new_value", 1, 2]}</literal>
+ </para><para><literal>{"a": [0, 1, "new_value", 2]}</literal>
+ </para></entry>
+ </row>
+ <row>
<entry><para><literal>jsonb_pretty(from_json jsonb)</literal>
</para></entry>
<entry><para><type>text</type></para></entry>
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index abf9a70..b1281e7 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -971,3 +971,11 @@ RETURNS jsonb
LANGUAGE INTERNAL
STRICT IMMUTABLE
AS 'jsonb_set';
+
+CREATE OR REPLACE FUNCTION
+ jsonb_insert(jsonb_in jsonb, path text[] , replacement jsonb,
+ insert_before_after boolean DEFAULT false)
+RETURNS jsonb
+LANGUAGE INTERNAL
+STRICT IMMUTABLE
+AS 'jsonb_insert';
diff --git a/src/backend/utils/adt/jsonfuncs.c b/src/backend/utils/adt/jsonfuncs.c
index 88225aa..1c1da7c 100644
--- a/src/backend/utils/adt/jsonfuncs.c
+++ b/src/backend/utils/adt/jsonfuncs.c
@@ -33,6 +33,13 @@
#include "utils/memutils.h"
#include "utils/typcache.h"
+/* Operations available for setPath */
+#define JB_PATH_NOOP 0x0
+#define JB_PATH_CREATE 0x0001
+#define JB_PATH_DELETE 0x0002
+#define JB_PATH_INSERT_BEFORE 0x0004
+#define JB_PATH_INSERT_AFTER 0x0008
+
/* semantic action functions for json_object_keys */
static void okeys_object_field_start(void *state, char *fname, bool isnull);
static void okeys_array_start(void *state);
@@ -130,14 +137,14 @@ static JsonbValue *IteratorConcat(JsonbIterator **it1, JsonbIterator **it2,
static JsonbValue *setPath(JsonbIterator **it, Datum *path_elems,
bool *path_nulls, int path_len,
JsonbParseState **st, int level, Jsonb *newval,
- bool create);
+ int op_type);
static void setPathObject(JsonbIterator **it, Datum *path_elems,
bool *path_nulls, int path_len, JsonbParseState **st,
int level,
- Jsonb *newval, uint32 npairs, bool create);
+ Jsonb *newval, uint32 npairs, int op_type);
static void setPathArray(JsonbIterator **it, Datum *path_elems,
bool *path_nulls, int path_len, JsonbParseState **st,
- int level, Jsonb *newval, uint32 nelems, bool create);
+ int level, Jsonb *newval, uint32 nelems, int op_type);
static void addJsonbToParseState(JsonbParseState **jbps, Jsonb *jb);
/* state for json_object_keys */
@@ -3544,7 +3551,7 @@ jsonb_set(PG_FUNCTION_ARGS)
it = JsonbIteratorInit(&in->root);
res = setPath(&it, path_elems, path_nulls, path_len, &st,
- 0, newval, create);
+ 0, newval, create ? JB_PATH_CREATE : JB_PATH_NOOP);
Assert(res != NULL);
@@ -3588,7 +3595,52 @@ jsonb_delete_path(PG_FUNCTION_ARGS)
it = JsonbIteratorInit(&in->root);
- res = setPath(&it, path_elems, path_nulls, path_len, &st, 0, NULL, false);
+ res = setPath(&it, path_elems, path_nulls, path_len, &st,
+ 0, NULL, JB_PATH_DELETE);
+
+ Assert(res != NULL);
+
+ PG_RETURN_JSONB(JsonbValueToJsonb(res));
+}
+
+/*
+ * SQL function jsonb_insert(jsonb, text[], jsonb, boolean)
+ *
+ */
+Datum
+jsonb_insert(PG_FUNCTION_ARGS)
+{
+ Jsonb *in = PG_GETARG_JSONB(0);
+ ArrayType *path = PG_GETARG_ARRAYTYPE_P(1);
+ Jsonb *newval = PG_GETARG_JSONB(2);
+ bool before = PG_GETARG_BOOL(3);
+ JsonbValue *res = NULL;
+ Datum *path_elems;
+ bool *path_nulls;
+ int path_len;
+ JsonbIterator *it;
+ JsonbParseState *st = NULL;
+
+ if (ARR_NDIM(path) > 1)
+ ereport(ERROR,
+ (errcode(ERRCODE_ARRAY_SUBSCRIPT_ERROR),
+ errmsg("wrong number of array subscripts")));
+
+ if (JB_ROOT_IS_SCALAR(in))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("cannot set path in scalar")));
+
+ deconstruct_array(path, TEXTOID, -1, false, 'i',
+ &path_elems, &path_nulls, &path_len);
+
+ if (path_len == 0)
+ PG_RETURN_JSONB(in);
+
+ it = JsonbIteratorInit(&in->root);
+
+ res = setPath(&it, path_elems, path_nulls, path_len, &st, 0, newval,
+ before ? JB_PATH_INSERT_BEFORE : JB_PATH_INSERT_AFTER);
Assert(res != NULL);
@@ -3718,7 +3770,7 @@ IteratorConcat(JsonbIterator **it1, JsonbIterator **it2,
static JsonbValue *
setPath(JsonbIterator **it, Datum *path_elems,
bool *path_nulls, int path_len,
- JsonbParseState **st, int level, Jsonb *newval, bool create)
+ JsonbParseState **st, int level, Jsonb *newval, int op_type)
{
JsonbValue v;
JsonbIteratorToken r;
@@ -3736,7 +3788,7 @@ setPath(JsonbIterator **it, Datum *path_elems,
case WJB_BEGIN_ARRAY:
(void) pushJsonbValue(st, r, NULL);
setPathArray(it, path_elems, path_nulls, path_len, st, level,
- newval, v.val.array.nElems, create);
+ newval, v.val.array.nElems, op_type);
r = JsonbIteratorNext(it, &v, false);
Assert(r == WJB_END_ARRAY);
res = pushJsonbValue(st, r, NULL);
@@ -3745,7 +3797,7 @@ setPath(JsonbIterator **it, Datum *path_elems,
case WJB_BEGIN_OBJECT:
(void) pushJsonbValue(st, r, NULL);
setPathObject(it, path_elems, path_nulls, path_len, st, level,
- newval, v.val.object.nPairs, create);
+ newval, v.val.object.nPairs, op_type);
r = JsonbIteratorNext(it, &v, true);
Assert(r == WJB_END_OBJECT);
res = pushJsonbValue(st, r, NULL);
@@ -3768,7 +3820,7 @@ setPath(JsonbIterator **it, Datum *path_elems,
static void
setPathObject(JsonbIterator **it, Datum *path_elems, bool *path_nulls,
int path_len, JsonbParseState **st, int level,
- Jsonb *newval, uint32 npairs, bool create)
+ Jsonb *newval, uint32 npairs, int op_type)
{
JsonbValue v;
int i;
@@ -3778,8 +3830,16 @@ setPathObject(JsonbIterator **it, Datum *path_elems, bool *path_nulls,
if (level >= path_len || path_nulls[level])
done = true;
+ /* Both insert types are equal to a create type for jsonb objects */
+ if (level == path_len - 1 &&
+ op_type & (JB_PATH_INSERT_BEFORE | JB_PATH_INSERT_AFTER))
+ {
+ op_type = JB_PATH_CREATE;
+ }
+
+
/* empty object is a special case for create */
- if ((npairs == 0) && create && (level == path_len - 1))
+ if ((npairs == 0) && (op_type & JB_PATH_CREATE) && (level == path_len - 1))
{
JsonbValue newkey;
@@ -3805,7 +3865,7 @@ setPathObject(JsonbIterator **it, Datum *path_elems, bool *path_nulls,
if (level == path_len - 1)
{
r = JsonbIteratorNext(it, &v, true); /* skip */
- if (newval != NULL)
+ if (op_type != JB_PATH_DELETE)
{
(void) pushJsonbValue(st, WJB_KEY, &k);
addJsonbToParseState(st, newval);
@@ -3816,12 +3876,13 @@ setPathObject(JsonbIterator **it, Datum *path_elems, bool *path_nulls,
{
(void) pushJsonbValue(st, r, &k);
setPath(it, path_elems, path_nulls, path_len,
- st, level + 1, newval, create);
+ st, level + 1, newval, op_type);
}
}
else
{
- if (create && !done && level == path_len - 1 && i == npairs - 1)
+ if ((op_type & JB_PATH_CREATE) && !done &&
+ level == path_len - 1 && i == npairs - 1)
{
JsonbValue newkey;
@@ -3862,7 +3923,7 @@ setPathObject(JsonbIterator **it, Datum *path_elems, bool *path_nulls,
static void
setPathArray(JsonbIterator **it, Datum *path_elems, bool *path_nulls,
int path_len, JsonbParseState **st, int level,
- Jsonb *newval, uint32 nelems, bool create)
+ Jsonb *newval, uint32 nelems, int op_type)
{
JsonbValue v;
int idx,
@@ -3904,7 +3965,8 @@ setPathArray(JsonbIterator **it, Datum *path_elems, bool *path_nulls,
* what the idx value is
*/
- if ((idx == INT_MIN || nelems == 0) && create && (level == path_len - 1))
+ if ((idx == INT_MIN || nelems == 0) && (level == path_len - 1) &&
+ (op_type & (JB_PATH_CREATE | JB_PATH_INSERT_BEFORE | JB_PATH_INSERT_AFTER)))
{
Assert(newval != NULL);
addJsonbToParseState(st, newval);
@@ -3920,15 +3982,22 @@ setPathArray(JsonbIterator **it, Datum *path_elems, bool *path_nulls,
{
if (level == path_len - 1)
{
- r = JsonbIteratorNext(it, &v, true); /* skip */
- if (newval != NULL)
+ r = JsonbIteratorNext(it, &v, true);
+
+ if (op_type & JB_PATH_INSERT_BEFORE)
+ (void) pushJsonbValue(st, r, &v);
+
+ if (op_type != JB_PATH_DELETE)
addJsonbToParseState(st, newval);
+ if (op_type & JB_PATH_INSERT_AFTER)
+ (void) pushJsonbValue(st, r, &v);
+
done = true;
}
else
(void) setPath(it, path_elems, path_nulls, path_len,
- st, level + 1, newval, create);
+ st, level + 1, newval, op_type);
}
else
{
@@ -3953,7 +4022,8 @@ setPathArray(JsonbIterator **it, Datum *path_elems, bool *path_nulls,
}
}
- if (create && !done && level == path_len - 1 && i == nelems - 1)
+ if (op_type & (JB_PATH_CREATE | JB_PATH_INSERT_BEFORE | JB_PATH_INSERT_AFTER) &&
+ !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 62b9125..103396f 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -4809,6 +4809,8 @@ DATA(insert OID = 3305 ( jsonb_set PGNSP PGUID 12 1 0 0 0 f f f f t f i s 4
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 s 1 0 25 "3802" _null_ _null_ _null_ _null_ _null_ jsonb_pretty _null_ _null_ _null_ ));
DESCR("Indented text from jsonb");
+DATA(insert OID = 3318 ( jsonb_insert PGNSP PGUID 12 1 0 0 0 f f f f t f i s 4 0 3802 "3802 1009 3802 16" _null_ _null_ _null_ _null_ _null_ jsonb_insert _null_ _null_ _null_ ));
+DESCR("Insert value into a jsonb");
/* txid */
DATA(insert OID = 2939 ( txid_snapshot_in PGNSP PGUID 12 1 0 0 0 f f f f t f i s 1 0 2970 "2275" _null_ _null_ _null_ _null_ _null_ txid_snapshot_in _null_ _null_ _null_ ));
DESCR("I/O");
diff --git a/src/include/utils/jsonb.h b/src/include/utils/jsonb.h
index 5f49d8d..5d8e4a9 100644
--- a/src/include/utils/jsonb.h
+++ b/src/include/utils/jsonb.h
@@ -408,6 +408,9 @@ extern Datum jsonb_delete_path(PG_FUNCTION_ARGS);
/* replacement */
extern Datum jsonb_set(PG_FUNCTION_ARGS);
+/* insert after or before (for arrays) */
+extern Datum jsonb_insert(PG_FUNCTION_ARGS);
+
/* Support functions */
extern uint32 getJsonbOffset(const JsonbContainer *jc, int index);
extern uint32 getJsonbLength(const JsonbContainer *jc, int index);
diff --git a/src/test/regress/expected/jsonb.out b/src/test/regress/expected/jsonb.out
index 4789e4e..4a1c1b8 100644
--- a/src/test/regress/expected/jsonb.out
+++ b/src/test/regress/expected/jsonb.out
@@ -3296,3 +3296,120 @@ select jsonb_set('{"a": {"b": [1, 2, 3]}}', '{a, b, non_integer}', '"new_value"'
ERROR: path element at the position 3 is not an integer
select jsonb_set('{"a": {"b": [1, 2, 3]}}', '{a, b, NULL}', '"new_value"');
ERROR: path element at the position 3 is NULL
+-- jsonb_insert
+select jsonb_insert('{"a": [0,1,2]}', '{a, 1}', '"new_value"');
+ jsonb_insert
+-------------------------------
+ {"a": [0, "new_value", 1, 2]}
+(1 row)
+
+select jsonb_insert('{"a": [0,1,2]}', '{a, 1}', '"new_value"', true);
+ jsonb_insert
+-------------------------------
+ {"a": [0, 1, "new_value", 2]}
+(1 row)
+
+select jsonb_insert('{"a": {"b": {"c": [0, 1, "test1", "test2"]}}}', '{a, b, c, 2}', '"new_value"');
+ jsonb_insert
+------------------------------------------------------------
+ {"a": {"b": {"c": [0, 1, "new_value", "test1", "test2"]}}}
+(1 row)
+
+select jsonb_insert('{"a": {"b": {"c": [0, 1, "test1", "test2"]}}}', '{a, b, c, 2}', '"new_value"', true);
+ jsonb_insert
+------------------------------------------------------------
+ {"a": {"b": {"c": [0, 1, "test1", "new_value", "test2"]}}}
+(1 row)
+
+select jsonb_insert('{"a": [0,1,2]}', '{a, 1}', '{"b": "value"}');
+ jsonb_insert
+----------------------------------
+ {"a": [0, {"b": "value"}, 1, 2]}
+(1 row)
+
+select jsonb_insert('{"a": [0,1,2]}', '{a, 1}', '["value1", "value2"]');
+ jsonb_insert
+----------------------------------------
+ {"a": [0, ["value1", "value2"], 1, 2]}
+(1 row)
+
+-- edge cases
+select jsonb_insert('{"a": [0,1,2]}', '{a, 0}', '"new_value"');
+ jsonb_insert
+-------------------------------
+ {"a": ["new_value", 0, 1, 2]}
+(1 row)
+
+select jsonb_insert('{"a": [0,1,2]}', '{a, 0}', '"new_value"', true);
+ jsonb_insert
+-------------------------------
+ {"a": [0, "new_value", 1, 2]}
+(1 row)
+
+select jsonb_insert('{"a": [0,1,2]}', '{a, 2}', '"new_value"');
+ jsonb_insert
+-------------------------------
+ {"a": [0, 1, "new_value", 2]}
+(1 row)
+
+select jsonb_insert('{"a": [0,1,2]}', '{a, 2}', '"new_value"', true);
+ jsonb_insert
+-------------------------------
+ {"a": [0, 1, 2, "new_value"]}
+(1 row)
+
+select jsonb_insert('{"a": [0,1,2]}', '{a, -1}', '"new_value"');
+ jsonb_insert
+-------------------------------
+ {"a": [0, 1, "new_value", 2]}
+(1 row)
+
+select jsonb_insert('{"a": [0,1,2]}', '{a, -1}', '"new_value"', true);
+ jsonb_insert
+-------------------------------
+ {"a": [0, 1, 2, "new_value"]}
+(1 row)
+
+select jsonb_insert('[]', '{1}', '"new_value"');
+ jsonb_insert
+---------------
+ ["new_value"]
+(1 row)
+
+select jsonb_insert('[]', '{1}', '"new_value"', true);
+ jsonb_insert
+---------------
+ ["new_value"]
+(1 row)
+
+select jsonb_insert('{"a": []}', '{a, 1}', '"new_value"');
+ jsonb_insert
+----------------------
+ {"a": ["new_value"]}
+(1 row)
+
+select jsonb_insert('{"a": []}', '{a, 1}', '"new_value"', true);
+ jsonb_insert
+----------------------
+ {"a": ["new_value"]}
+(1 row)
+
+select jsonb_insert('{"a": [0,1,2]}', '{a, 10}', '"new_value"');
+ jsonb_insert
+-------------------------------
+ {"a": [0, 1, 2, "new_value"]}
+(1 row)
+
+-- jsonb_insert should be just a jsonb_set alias for objects
+select jsonb_insert('{"a": {"b": "value"}}', '{a, c}', '"new_value"');
+ jsonb_insert
+-----------------------------------------
+ {"a": {"b": "value", "c": "new_value"}}
+(1 row)
+
+select jsonb_insert('{"a": {"b": "value"}}', '{a, c}', '"new_value"', true);
+ jsonb_insert
+-----------------------------------------
+ {"a": {"b": "value", "c": "new_value"}}
+(1 row)
+
diff --git a/src/test/regress/sql/jsonb.sql b/src/test/regress/sql/jsonb.sql
index 4b24477..2ce7963 100644
--- a/src/test/regress/sql/jsonb.sql
+++ b/src/test/regress/sql/jsonb.sql
@@ -829,3 +829,29 @@ select jsonb_set('[]','{-99}','{"foo":123}');
select jsonb_set('{"a": [1, 2, 3]}', '{a, non_integer}', '"new_value"');
select jsonb_set('{"a": {"b": [1, 2, 3]}}', '{a, b, non_integer}', '"new_value"');
select jsonb_set('{"a": {"b": [1, 2, 3]}}', '{a, b, NULL}', '"new_value"');
+
+
+-- jsonb_insert
+select jsonb_insert('{"a": [0,1,2]}', '{a, 1}', '"new_value"');
+select jsonb_insert('{"a": [0,1,2]}', '{a, 1}', '"new_value"', true);
+select jsonb_insert('{"a": {"b": {"c": [0, 1, "test1", "test2"]}}}', '{a, b, c, 2}', '"new_value"');
+select jsonb_insert('{"a": {"b": {"c": [0, 1, "test1", "test2"]}}}', '{a, b, c, 2}', '"new_value"', true);
+select jsonb_insert('{"a": [0,1,2]}', '{a, 1}', '{"b": "value"}');
+select jsonb_insert('{"a": [0,1,2]}', '{a, 1}', '["value1", "value2"]');
+
+-- edge cases
+select jsonb_insert('{"a": [0,1,2]}', '{a, 0}', '"new_value"');
+select jsonb_insert('{"a": [0,1,2]}', '{a, 0}', '"new_value"', true);
+select jsonb_insert('{"a": [0,1,2]}', '{a, 2}', '"new_value"');
+select jsonb_insert('{"a": [0,1,2]}', '{a, 2}', '"new_value"', true);
+select jsonb_insert('{"a": [0,1,2]}', '{a, -1}', '"new_value"');
+select jsonb_insert('{"a": [0,1,2]}', '{a, -1}', '"new_value"', true);
+select jsonb_insert('[]', '{1}', '"new_value"');
+select jsonb_insert('[]', '{1}', '"new_value"', true);
+select jsonb_insert('{"a": []}', '{a, 1}', '"new_value"');
+select jsonb_insert('{"a": []}', '{a, 1}', '"new_value"', true);
+select jsonb_insert('{"a": [0,1,2]}', '{a, 10}', '"new_value"');
+
+-- jsonb_insert should be just a jsonb_set alias for objects
+select jsonb_insert('{"a": {"b": "value"}}', '{a, c}', '"new_value"');
+select jsonb_insert('{"a": {"b": "value"}}', '{a, c}', '"new_value"', true);
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
