[PATH] Jsonb, insert a new value into an array at arbitrary position

Started by Dmitry Dolgovalmost 10 years ago25 messages

9erthalion6@gmail.com

almost 10 years ago

1 attachment(s)

As far as I see there is one basic update function for jsonb, that can't be
covered by `jsonb_set` - insert a new value into an array at arbitrary
position.
Using `jsonb_set` function we can only append into array at the end/at the
beginning, and it looks more like a hack:

```
=# select jsonb_set('{"a": [1, 2, 3]}', '{a, 100}', '4');
jsonb_set
---------------------
{"a": [1, 2, 3, 4]}
(1 row)

=# select jsonb_set('{"a": [1, 2, 3]}', '{a, -100}', '4');
jsonb_set
---------------------
{"a": [4, 1, 2, 3]}
(1 row)
```

I think the possibility to insert into arbitrary position will be quite
useful,
something like `json_array_insert` in MySql:

```
mysql> set @j = '["a", {"b": [1, 2]}, [3, 4]]';
mysql> select json_array_insert(@j, '$[1].b[0]', 'x');

json_array_insert(@j, '$[1].b[0]', 'x')
+-----------------------------------------+
["a", {"b": ["x", 1, 2]}, [3, 4]]
```

It can look like `jsonb_insert` function in our case:

```
=# select jsonb_insert('{"a": [0,1,2]}', '{a, 1}', '"new_value"');
jsonb_insert
-------------------------------
{"a": [0, "new_value", 1, 2]}
(1 row)
```

I attached possible implementation, which is basically quite small (all
logic-related
modifications is only about 4 lines in `setPath` function). This
implementation
assumes a flag to separate "insert before"/"insert after" operations, and an
alias to `jsonb_set` in case if we working with a jsonb object, not an
array.

What do you think about this?

Attachments:

jsonb_insert.patchapplication/octet-stream; name=jsonb_insert.patchDownload

diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 923fe58..bd3ca7d 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -965,3 +965,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..896a329 100644
--- a/src/backend/utils/adt/jsonfuncs.c
+++ b/src/backend/utils/adt/jsonfuncs.c
@@ -33,6 +33,12 @@
 #include "utils/memutils.h"
 #include "utils/typcache.h"
 
+/* Operations available for setPath */
+#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 +136,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 flag);
 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 flag);
 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 flag);
 static void addJsonbToParseState(JsonbParseState **jbps, Jsonb *jb);
 
 /* state for json_object_keys */
@@ -3544,7 +3550,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 : 0x0);
 
 	Assert(res != NULL);
 
@@ -3588,7 +3594,51 @@ 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 +3768,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 flag)
 {
 	JsonbValue	v;
 	JsonbIteratorToken r;
@@ -3736,7 +3786,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, flag);
 			r = JsonbIteratorNext(it, &v, false);
 			Assert(r == WJB_END_ARRAY);
 			res = pushJsonbValue(st, r, NULL);
@@ -3745,7 +3795,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, flag);
 			r = JsonbIteratorNext(it, &v, true);
 			Assert(r == WJB_END_OBJECT);
 			res = pushJsonbValue(st, r, NULL);
@@ -3768,7 +3818,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 flag)
 {
 	JsonbValue	v;
 	int			i;
@@ -3778,8 +3828,14 @@ setPathObject(JsonbIterator **it, Datum *path_elems, bool *path_nulls,
 	if (level >= path_len || path_nulls[level])
 		done = true;
 
+	/* Both insert flags are equal to a create flag for jsonb objects */
+	if (level == path_len - 1 && (flag == JB_PATH_INSERT_BEFORE ||
+								  flag == JB_PATH_INSERT_AFTER))
+		flag = JB_PATH_CREATE;
+
+
 	/* empty object is a special case for create */
-	if ((npairs == 0) && create && (level == path_len - 1))
+	if ((npairs == 0) && flag == JB_PATH_CREATE && (level == path_len - 1))
 	{
 		JsonbValue	newkey;
 
@@ -3816,12 +3872,12 @@ 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, flag);
 			}
 		}
 		else
 		{
-			if (create && !done && level == path_len - 1 && i == npairs - 1)
+			if (flag == JB_PATH_CREATE && !done && level == path_len - 1 && i == npairs - 1)
 			{
 				JsonbValue	newkey;
 
@@ -3862,7 +3918,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 flag)
 {
 	JsonbValue	v;
 	int			idx,
@@ -3904,7 +3960,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) &&
+		(flag == JB_PATH_CREATE || flag == JB_PATH_INSERT_BEFORE || flag == JB_PATH_INSERT_AFTER))
 	{
 		Assert(newval != NULL);
 		addJsonbToParseState(st, newval);
@@ -3920,15 +3977,22 @@ setPathArray(JsonbIterator **it, Datum *path_elems, bool *path_nulls,
 		{
 			if (level == path_len - 1)
 			{
-				r = JsonbIteratorNext(it, &v, true);	/* skip */
+				r = JsonbIteratorNext(it, &v, true);
+
+				if (flag == JB_PATH_INSERT_BEFORE)
+					(void) pushJsonbValue(st, r, &v);
+
 				if (newval != NULL)
 					addJsonbToParseState(st, newval);
 
+				if (flag == 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, flag);
 		}
 		else
 		{
@@ -3953,7 +4017,7 @@ setPathArray(JsonbIterator **it, Datum *path_elems, bool *path_nulls,
 				}
 			}
 
-			if (create && !done && level == path_len - 1 && i == nelems - 1)
+			if (flag == JB_PATH_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 244aa4d..277743b 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -4776,6 +4776,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 72fe808..01b0d1e 100644
--- a/src/test/regress/expected/jsonb.out
+++ b/src/test/regress/expected/jsonb.out
@@ -3282,3 +3282,114 @@ 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)
+
+-- 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 b724f0a..9282acb 100644
--- a/src/test/regress/sql/jsonb.sql
+++ b/src/test/regress/sql/jsonb.sql
@@ -823,3 +823,28 @@ 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);
+
+-- 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);

Petr Jelinek

petr@2ndquadrant.com

almost 10 years ago

In reply to: Dmitry Dolgov (#1)

Re: [PATH] Jsonb, insert a new value into an array at arbitrary position

On 18/02/16 15:38, Dmitry Dolgov wrote:

Hi

As far as I see there is one basic update function for jsonb, that can't be
covered by `jsonb_set` - insert a new value into an array at arbitrary
position.
Using `jsonb_set` function we can only append into array at the end/at the
beginning, and it looks more like a hack:

```
=# select jsonb_set('{"a": [1, 2, 3]}', '{a, 100}', '4');
jsonb_set
---------------------
{"a": [1, 2, 3, 4]}
(1 row)

=# select jsonb_set('{"a": [1, 2, 3]}', '{a, -100}', '4');
jsonb_set
---------------------
{"a": [4, 1, 2, 3]}
(1 row)
```

I think the possibility to insert into arbitrary position will be quite
useful,
something like `json_array_insert` in MySql:

```
mysql> set @j = '["a", {"b": [1, 2]}, [3, 4]]';
mysql> select json_array_insert(@j, '$[1].b[0]', 'x');

json_array_insert(@j, '$[1].b[0]', 'x')
+-----------------------------------------+
["a", {"b": ["x", 1, 2]}, [3, 4]]
```

It can look like `jsonb_insert` function in our case:

```
=# select jsonb_insert('{"a": [0,1,2]}', '{a, 1}', '"new_value"');
jsonb_insert
-------------------------------
{"a": [0, "new_value", 1, 2]}
(1 row)
```

I think it makes sense to have interface like this, I'd strongly prefer
the jsonb_array_insert naming though.

I attached possible implementation, which is basically quite small (all
logic-related
modifications is only about 4 lines in `setPath` function). This
implementation
assumes a flag to separate "insert before"/"insert after" operations, and an
alias to `jsonb_set` in case if we working with a jsonb object, not an
array.

I don't think it's a good idea to use set when this is used on object, I
think that we should throw error in that case.

Also this patch needs documentation.

--
Petr Jelinek http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Dmitry Dolgov

9erthalion6@gmail.com

almost 10 years ago

In reply to: Petr Jelinek (#2)

1 attachment(s)

Re: [PATH] Jsonb, insert a new value into an array at arbitrary position

I'd strongly prefer the jsonb_array_insert naming though
I don't think it's a good idea to use set when this is used on object, I

think that we should throw error in that case.

Well, I thought it's wrong to introduce different functions and behaviour
patterns for objects and arrays, because it's the one data type after all.
But it's just my opinion of course.

Also this patch needs documentation.

I've added new version in attachments, thanks.

Attachments:

jsonb_insert_v2.patchapplication/octet-stream; name=jsonb_insert_v2.patchDownload

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index c0b94bc..ceaf88d 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,29 @@ 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..896a329 100644
--- a/src/backend/utils/adt/jsonfuncs.c
+++ b/src/backend/utils/adt/jsonfuncs.c
@@ -33,6 +33,12 @@
 #include "utils/memutils.h"
 #include "utils/typcache.h"
 
+/* Operations available for setPath */
+#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 +136,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 flag);
 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 flag);
 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 flag);
 static void addJsonbToParseState(JsonbParseState **jbps, Jsonb *jb);
 
 /* state for json_object_keys */
@@ -3544,7 +3550,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 : 0x0);
 
 	Assert(res != NULL);
 
@@ -3588,7 +3594,51 @@ 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 +3768,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 flag)
 {
 	JsonbValue	v;
 	JsonbIteratorToken r;
@@ -3736,7 +3786,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, flag);
 			r = JsonbIteratorNext(it, &v, false);
 			Assert(r == WJB_END_ARRAY);
 			res = pushJsonbValue(st, r, NULL);
@@ -3745,7 +3795,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, flag);
 			r = JsonbIteratorNext(it, &v, true);
 			Assert(r == WJB_END_OBJECT);
 			res = pushJsonbValue(st, r, NULL);
@@ -3768,7 +3818,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 flag)
 {
 	JsonbValue	v;
 	int			i;
@@ -3778,8 +3828,14 @@ setPathObject(JsonbIterator **it, Datum *path_elems, bool *path_nulls,
 	if (level >= path_len || path_nulls[level])
 		done = true;
 
+	/* Both insert flags are equal to a create flag for jsonb objects */
+	if (level == path_len - 1 && (flag == JB_PATH_INSERT_BEFORE ||
+								  flag == JB_PATH_INSERT_AFTER))
+		flag = JB_PATH_CREATE;
+
+
 	/* empty object is a special case for create */
-	if ((npairs == 0) && create && (level == path_len - 1))
+	if ((npairs == 0) && flag == JB_PATH_CREATE && (level == path_len - 1))
 	{
 		JsonbValue	newkey;
 
@@ -3816,12 +3872,12 @@ 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, flag);
 			}
 		}
 		else
 		{
-			if (create && !done && level == path_len - 1 && i == npairs - 1)
+			if (flag == JB_PATH_CREATE && !done && level == path_len - 1 && i == npairs - 1)
 			{
 				JsonbValue	newkey;
 
@@ -3862,7 +3918,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 flag)
 {
 	JsonbValue	v;
 	int			idx,
@@ -3904,7 +3960,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) &&
+		(flag == JB_PATH_CREATE || flag == JB_PATH_INSERT_BEFORE || flag == JB_PATH_INSERT_AFTER))
 	{
 		Assert(newval != NULL);
 		addJsonbToParseState(st, newval);
@@ -3920,15 +3977,22 @@ setPathArray(JsonbIterator **it, Datum *path_elems, bool *path_nulls,
 		{
 			if (level == path_len - 1)
 			{
-				r = JsonbIteratorNext(it, &v, true);	/* skip */
+				r = JsonbIteratorNext(it, &v, true);
+
+				if (flag == JB_PATH_INSERT_BEFORE)
+					(void) pushJsonbValue(st, r, &v);
+
 				if (newval != NULL)
 					addJsonbToParseState(st, newval);
 
+				if (flag == 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, flag);
 		}
 		else
 		{
@@ -3953,7 +4017,7 @@ setPathArray(JsonbIterator **it, Datum *path_elems, bool *path_nulls,
 				}
 			}
 
-			if (create && !done && level == path_len - 1 && i == nelems - 1)
+			if (flag == JB_PATH_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 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..b9caa59 100644
--- a/src/test/regress/expected/jsonb.out
+++ b/src/test/regress/expected/jsonb.out
@@ -3296,3 +3296,114 @@ 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)
+
+-- 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..a9c4d67 100644
--- a/src/test/regress/sql/jsonb.sql
+++ b/src/test/regress/sql/jsonb.sql
@@ -829,3 +829,28 @@ 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);
+
+-- 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);

Petr Jelinek

petr@2ndquadrant.com

almost 10 years ago

In reply to: Dmitry Dolgov (#3)

Re: [PATH] Jsonb, insert a new value into an array at arbitrary position

On 29/02/16 18:19, Dmitry Dolgov wrote:

I'd strongly prefer the jsonb_array_insert naming though
I don't think it's a good idea to use set when this is used on

object, I think that we should throw error in that case.

Well, I thought it's wrong to introduce different functions and
behaviour patterns for objects and arrays, because it's the one data
type after all. But it's just my opinion of course.

The problem I see with that logic is because we don't keep order of keys
in the jsonb object the "insert" in the name will have misleading
implications from the point of the user. Also it does not do anything
for object that "set" doesn't do already, so why have two interfaces for
doing same thing.

--
Petr Jelinek http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Vitaly Burovoy

vitaly.burovoy@gmail.com

almost 10 years ago

In reply to: Dmitry Dolgov (#3)

Re: [PATH] Jsonb, insert a new value into an array at arbitrary position

On 2016-02-29 17:19+00, Dmitry Dolgov <9erthalion6@gmail.com> wrote:
On 2016-02-24 19:37+00, Petr Jelinek <petr@2ndquadrant.com> wrote:

Also this patch needs documentation.

I've added new version in attachments, thanks.

Hello! The first pass of a review is below.

1. Rename the "flag" variable to something more meaningful. (e.g.
"op_type" - "operation_type")

2. There is two extra spaces after the "_DELETE" word
+#define JB_PATH_DELETE 0x0002

3.
 	res = setPath(&it, path_elems, path_nulls, path_len, &st,
-				  0, newval, create);
+				  0, newval, create ? JB_PATH_CREATE : 0x0);

What is the magic constant "0x0"? If not "create", then what?
Introduce something like JB_PATH_NOOP = 0x0...

4. In the "setPathArray" the block:
if (newval != NULL)
"newval == NULL" is considered as "to be deleted" from the previous
convention and from the comments for the "setPath" function.
I think since you've introduced special constants one of which is
JB_PATH_DELETE (which is not used now) you should replace convention
from checking for a value to checking for a constant:
if (flag != JB_PATH_DELETE)
or even better:
if (!flag & JB_PATH_DELETE)

5. Change checking for equality (to bit constants) to bit operations:
(flag == JB_PATH_INSERT_BEFORE || flag == JB_PATH_INSERT_AFTER)
which can be replaced to:
(flag & (JB_PATH_INSERT_BEFORE | JB_PATH_INSERT_AFTER))

also here:
(flag == JB_PATH_CREATE || flag == JB_PATH_INSERT_BEFORE || flag
== JB_PATH_INSERT_AFTER))
can be:
(flag & (JB_PATH_CREATE | JB_PATH_INSERT_BEFORE | JB_PATH_INSERT_AFTER))

6. Pay attention to parenthesises to make the code looks like similar
one around:
+	if ((npairs == 0) && flag == JB_PATH_CREATE && (level == path_len - 1))
should be:
+	if ((npairs == 0) && (flag == JB_PATH_CREATE) && (level == path_len - 1))

7. Why did you remove "skip"? It is a comment what "true" means...
-				r = JsonbIteratorNext(it, &v, true);	/* skip */
+				r = JsonbIteratorNext(it, &v, true);

8. After your changes some statements exceed 80-column threshold...
The same rules for the documentation.

9. And finally... it does not work as expected in case of:
postgres=# select jsonb_insert('{"a":[0,1,2,3]}', '{"a", 10}', '"4"');
jsonb_insert
---------------------
{"a": [0, 1, 2, 3]}
(1 row)

wheras jsonb_set works:
postgres=# select jsonb_set('{"a":[0,1,2,3]}', '{"a", 10}', '"4"');
jsonb_set
--------------------------
{"a": [0, 1, 2, 3, "4"]}
(1 row)

--
Best regards,
Vitaly Burovoy

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

David Steele

david@pgmasters.net

almost 10 years ago

In reply to: Vitaly Burovoy (#5)

Re: [PATH] Jsonb, insert a new value into an array at arbitrary position

Hi Dmitry,

On 3/7/16 8:50 PM, Vitaly Burovoy wrote:

On 2016-02-29 17:19+00, Dmitry Dolgov <9erthalion6@gmail.com> wrote:

Hello! The first pass of a review is below.

It's be over a week since Vitaly posted this review. Please respond
and/or provide a new patch as soon as you can.

Thanks,
--
-David
david@pgmasters.net

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Dmitry Dolgov

9erthalion6@gmail.com

almost 10 years ago

In reply to: David Steele (#6)

1 attachment(s)

Re: [PATH] Jsonb, insert a new value into an array at arbitrary position

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.

Attachments:

jsonb_insert_v3.patchtext/x-patch; charset=US-ASCII; name=jsonb_insert_v3.patchDownload

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);

Petr Jelinek

petr@2ndquadrant.com

almost 10 years ago

In reply to: Dmitry Dolgov (#7)

Re: [PATH] Jsonb, insert a new value into an array at arbitrary position

I still don't like that this works on path leading to an object given
that we can't fulfill the promise of inserting to an arbitrary position
there.

--
Petr Jelinek http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Dmitry Dolgov

9erthalion6@gmail.com

almost 10 years ago

In reply to: Petr Jelinek (#8)

Re: [PATH] Jsonb, insert a new value into an array at arbitrary position

I still don't like that this works on path leading to an object given

that we can't fulfill the promise of inserting to an arbitrary position
there.

I'm thinking about this following way - `jsonb_insert` is just a function
to insert a new value, which has specific options to enforce arbitrary
position in case of jsonb array. I don't think this can confuse someone
since it's not something like `jsonb_insert_at_position` function. But of
course, if I'm wrong we can easy change the function name and make it
available only for arrays.

#10

Vitaly Burovoy

vitaly.burovoy@gmail.com

almost 10 years ago

In reply to: Dmitry Dolgov (#7)

Re: [PATH] Jsonb, insert a new value into an array at arbitrary position

On 2016-03-16, Dmitry Dolgov <9erthalion6@gmail.com> wrote:

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,

As I got it, it is "skipNested", skipping iterating over nested
containers, not skipping an element.

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.

I think it is not a reason for deleting this 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);
```

The comment in your example is for the extra "JsonbIteratorNext" call
(the first one is in a "while" statement outside your example, but
over it in the code), not for the "skip" parameter in the
"JsonbIteratorNext" call here.
===

Notes for the jsonb_insert_v3.patch applied on the f6bd0da:

1a. Please, rebase your patch to the current master (it is easy to
resolve conflict, but still necessary).

1b. Now OID=3318 is "pg_stat_get_progress_info" (Hint: 3324 is free now).

2.
The documentation, func.sgml:

If<replaceable>target</replaceable>

Here must be a space after the "If" word.

3.
There is a little odd formulating me in the documentation part
(without formatting for better readability):

If target section designated by path is a JSONB array, new_value will be inserted before it, or after if after is true (defailt is false).

a. "new_value" is not inserted before "it" (a JSONB array), it is
inserted before target;
b. s/or after if/or after target if/;
c. s/defailt/default/

If ... is a JSONB object, new_value will be added just like a regular key.

d. "new_value" is not a regular key: key is the final part of the "target";
e. "new_value" replaces target if it exists;
f. "new_value" is added if target is not exists as if jsonb_set is
called with create_missing=true.
g. "will" is about possibility. "be" is about an action.

4.
function setPathObject, block with "op_type = JB_PATH_CREATE"
(jsonfuncs.c, lines 3833..3840).
It seems it is not necessary now since you can easily check for all
three options like:
op_type & (JB_PATH_INSERT_BEFORE | JB_PATH_INSERT_AFTER | JB_PATH_CREATE)

or even create a new constant (there can be better name for it):
#define JB_PATH_CREATE_OR_INSERT (JB_PATH_INSERT_BEFORE |
JB_PATH_INSERT_AFTER | JB_PATH_CREATE)

and use it like:
op_type & JB_PATH_CREATE_OR_INSERT

all occurrences of JB_PATH_CREATE in the function are already with the
"(level == path_len - 1)" condition, there is no additional check
needed.

also the new constant JB_PATH_CREATE_OR_INSERT can be used at lines 3969, 4025.

if (op_type != JB_PATH_DELETE)

It seems strange direct comparison among bitwise operators (lines 3987..3994)

You can leave it as is, but I'd change it (and similar one at the line
3868) to a bitwise operator:
if (!op_type & JB_PATH_DELETE)

6.
I'd like to see a test with big negative index as a reverse for big
positive index:
select jsonb_insert('{"a": [0,1,2]}', '{a, -10}', '"new_value"');

I know now it works as expected, it is just as a defense against
further changes that can be destructive for this special case.

7.
Please, return the "skip" comment.

8.
The documentation: add "jsonb_insert" to the note about importance of
existing intermediate keys. Try to reword it since the function
doesn't have a "create_missing" parameter support.

All the items of the path parameter of jsonb_set must be present in the target,
... in which case all but the last item must be present.

Currently I can't break the code, so I think it is close to the final state. ;-)

--
Best regards,
Vitaly Burovoy

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

#11

Dmitry Dolgov

9erthalion6@gmail.com

almost 10 years ago

In reply to: Vitaly Burovoy (#10)

1 attachment(s)

Re: [PATH] Jsonb, insert a new value into an array at arbitrary position

Here is a new version of path, I hope I didn't miss anything. Few notes:

4.
or even create a new constant (there can be better name for it):
#define JB_PATH_CREATE_OR_INSERT (JB_PATH_INSERT_BEFORE |
JB_PATH_INSERT_AFTER | JB_PATH_CREATE)

Good idea, thanks.

5.

if (op_type != JB_PATH_DELETE)

Yes, I just missed that in previous patch.

7.
Please, return the "skip" comment.

Well, I'm still not so sure about that, but if you're so confident then ok
=)

Attachments:

jsonb_insert_v4.patchtext/x-patch; charset=US-ASCII; name=jsonb_insert_v4.patchDownload

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index ae93e69..dfed589 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -10904,6 +10904,9 @@ table2-mapping
    <primary>jsonb_set</primary>
   </indexterm>
   <indexterm>
+   <primary>jsonb_insert</primary>
+  </indexterm>
+  <indexterm>
    <primary>jsonb_pretty</primary>
   </indexterm>
 
@@ -11185,6 +11188,40 @@ 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 target or
+         after if <replaceable>after</replaceable> is true (default is
+         <literal>false</>). If <replaceable>target</replaceable> section
+         designated by <replaceable>path</replaceable> is a JSONB object,
+         <function>jsonb_insert</> will act like
+         <function>jsonb_set</> function where
+         <replaceable>create_missing</replaceable> is true. 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>
@@ -11236,10 +11273,11 @@ table2-mapping
   <note>
     <para>
       All the items of the <literal>path</> parameter of <literal>jsonb_set</>
-      must be present in the <literal>target</>, unless
-      <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.
+      as well as <literal>jsonb_insert</> except the last item must be present
+      in the <literal>target</>. If <literal>create_missing</> is false, all
+      items of the <literal>path</> parameter of <literal>jsonb_set</> 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
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 9ae1ef4..58ed3c3 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -997,3 +997,11 @@ RETURNS text[]
 LANGUAGE INTERNAL
 STRICT IMMUTABLE
 AS 'parse_ident';
+
+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 97e0e8e..5bd2165 100644
--- a/src/backend/utils/adt/jsonfuncs.c
+++ b/src/backend/utils/adt/jsonfuncs.c
@@ -33,6 +33,15 @@
 #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
+#define JB_PATH_CREATE_OR_INSERT (JB_PATH_INSERT_BEFORE | JB_PATH_INSERT_AFTER \
+		| JB_PATH_CREATE)
+
 /* 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 +139,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 +3553,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 +3597,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 +3772,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;
@@ -3739,7 +3793,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);
@@ -3747,7 +3801,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);
@@ -3771,7 +3825,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;
@@ -3782,7 +3836,7 @@ setPathObject(JsonbIterator **it, Datum *path_elems, bool *path_nulls,
 		done = true;
 
 	/* empty object is a special case for create */
-	if ((npairs == 0) && create && (level == path_len - 1))
+	if ((npairs == 0) && (op_type & JB_PATH_CREATE_OR_INSERT) && (level == path_len - 1))
 	{
 		JsonbValue	newkey;
 
@@ -3808,7 +3862,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);
@@ -3819,12 +3873,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_OR_INSERT) && !done &&
+				level == path_len - 1 && i == npairs - 1)
 			{
 				JsonbValue	newkey;
 
@@ -3865,7 +3920,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,
@@ -3909,7 +3964,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_OR_INSERT))
 	{
 		Assert(newval != NULL);
 		addJsonbToParseState(st, newval);
@@ -3926,14 +3982,21 @@ setPathArray(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_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
 		{
@@ -3958,7 +4021,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_OR_INSERT && !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 a595327..b0412fd 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -4839,6 +4839,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 = 3324 (  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 497b0d9..6b1b2df 100644
--- a/src/test/regress/expected/jsonb.out
+++ b/src/test/regress/expected/jsonb.out
@@ -3312,3 +3312,126 @@ select jsonb_set('{"a": {"b": [1, 2, 3]}}', '{a, b, non_integer}', '"new_value"'
 ERROR:  path element at position 3 is not an integer: "non_integer"
 select jsonb_set('{"a": {"b": [1, 2, 3]}}', '{a, b, NULL}', '"new_value"');
 ERROR:  path element at 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)
+
+select jsonb_insert('{"a": [0,1,2]}', '{a, -10}', '"new_value"');
+         jsonb_insert          
+-------------------------------
+ {"a": ["new_value", 0, 1, 2]}
+(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 c671684..4c51e80 100644
--- a/src/test/regress/sql/jsonb.sql
+++ b/src/test/regress/sql/jsonb.sql
@@ -837,3 +837,30 @@ 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"');
+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);

#12

Vitaly Burovoy

vitaly.burovoy@gmail.com

almost 10 years ago

In reply to: Dmitry Dolgov (#11)

Re: [PATH] Jsonb, insert a new value into an array at arbitrary position

On 3/25/16, Dmitry Dolgov <9erthalion6@gmail.com> wrote:

Here is a new version of path, I hope I didn't miss anything. Few notes:

4.
or even create a new constant (there can be better name for it):
#define JB_PATH_CREATE_OR_INSERT (JB_PATH_INSERT_BEFORE |
JB_PATH_INSERT_AFTER | JB_PATH_CREATE)

Good idea, thanks.

You're welcome.
===

I'm sorry for the late letter.

Unfortunately, it seems one more round is necessary.
The documentation changes still has to be fixed.

The main description points to a "target section designated by path is
a JSONB array". It is a copy-paste from jsonb_set, but here it has
wrong context.
The main idea in jsonb_set is replacing any object which is pointed
(designated) by "path" no matter where (array or object) it is
located.
But in the phrase for jsonb_insert above you want to point to an upper
object _where_ "section designated by path" is located.

I'd write something like "If target section designated by path is _IN_
a JSONB array, ...". The same thing with "a JSONB object".

Also I'd rewrite "will act like" as "behaves as".

Last time I missed the argument's name "insert_before_after". It is
better to replace it as just "insert_after". Also reflect that name in
the documentation properly.

To be consistent change the constant "JB_PATH_NOOP" as "0x0000"
instead of just "0x0".

Also the variable's name "bool before" is incorrect because it
contradicts with the SQL function's argument "after" (from the
documentation) or "insert_after" (proposed above) or tests (de-facto
behavior). Moreover it seems the logic in the code is correct, so I
have no idea why "before ? JB_PATH_INSERT_BEFORE :
JB_PATH_INSERT_AFTER" works.
I think either proper comment should be added or lack in the code must be found.
Anyway the variable's name must reflect the SQL argument's name.

--
Best regards,
Vitaly Burovoy

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

#13

Dmitry Dolgov

9erthalion6@gmail.com

almost 10 years ago

In reply to: Vitaly Burovoy (#12)

1 attachment(s)

Re: [PATH] Jsonb, insert a new value into an array at arbitrary position

On 31 March 2016 at 05:04, Vitaly Burovoy <vitaly.burovoy@gmail.com> wrote:

The documentation changes still has to be fixed.

Thanks for help. Looks like I'm not so good at text formulation. Fixed.

Moreover it seems the logic in the code is correct

No - I see now, that I made the same mistake in two places (e.g. in case of
`setPathArray` a current item was pushed to parse state after
`JB_PATH_INSERT_BEFORE`, not a new one), so they were annihilated. Fixed.

Attachments:

jsonb_insert_v5.patchtext/x-patch; charset=US-ASCII; name=jsonb_insert_v5.patchDownload

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index ae93e69..3c3ff7a 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -10904,6 +10904,9 @@ table2-mapping
    <primary>jsonb_set</primary>
   </indexterm>
   <indexterm>
+   <primary>jsonb_insert</primary>
+  </indexterm>
+  <indexterm>
    <primary>jsonb_pretty</primary>
   </indexterm>
 
@@ -11185,6 +11188,40 @@ table2-mapping
         </para></entry>
        </row>
       <row>
+       <entry>
+           <para><literal>
+           jsonb_insert(target jsonb, path text[], new_value jsonb, <optional><parameter>insert_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 in a JSONB array,
+         <replaceable>new_value</replaceable> will be inserted before target or
+         after if <replaceable>insert_after</replaceable> is true (default is
+         <literal>false</>). If <replaceable>target</replaceable> section
+         designated by <replaceable>path</replaceable> is in JSONB object,
+         <function>jsonb_insert</> behaves as
+         <function>jsonb_set</> function where
+         <replaceable>create_missing</replaceable> is true. 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>
@@ -11236,10 +11273,11 @@ table2-mapping
   <note>
     <para>
       All the items of the <literal>path</> parameter of <literal>jsonb_set</>
-      must be present in the <literal>target</>, unless
-      <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.
+      as well as <literal>jsonb_insert</> except the last item must be present
+      in the <literal>target</>. If <literal>create_missing</> is false, all
+      items of the <literal>path</> parameter of <literal>jsonb_set</> 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
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 9ae1ef4..a6e661c 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -997,3 +997,11 @@ RETURNS text[]
 LANGUAGE INTERNAL
 STRICT IMMUTABLE
 AS 'parse_ident';
+
+CREATE OR REPLACE FUNCTION
+  jsonb_insert(jsonb_in jsonb, path text[] , replacement jsonb,
+            insert_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 97e0e8e..df9fa5d 100644
--- a/src/backend/utils/adt/jsonfuncs.c
+++ b/src/backend/utils/adt/jsonfuncs.c
@@ -33,6 +33,15 @@
 #include "utils/memutils.h"
 #include "utils/typcache.h"
 
+/* Operations available for setPath */
+#define JB_PATH_NOOP					0x0000
+#define JB_PATH_CREATE					0x0001
+#define JB_PATH_DELETE					0x0002
+#define JB_PATH_INSERT_BEFORE			0x0004
+#define JB_PATH_INSERT_AFTER			0x0008
+#define JB_PATH_CREATE_OR_INSERT (JB_PATH_INSERT_BEFORE | JB_PATH_INSERT_AFTER \
+		| JB_PATH_CREATE)
+
 /* 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 +139,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 +3553,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 +3597,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		after = 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,
+				  after ? JB_PATH_INSERT_AFTER : JB_PATH_INSERT_BEFORE);
 
 	Assert(res != NULL);
 
@@ -3718,7 +3772,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;
@@ -3739,7 +3793,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);
@@ -3747,7 +3801,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);
@@ -3771,7 +3825,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;
@@ -3782,7 +3836,7 @@ setPathObject(JsonbIterator **it, Datum *path_elems, bool *path_nulls,
 		done = true;
 
 	/* empty object is a special case for create */
-	if ((npairs == 0) && create && (level == path_len - 1))
+	if ((npairs == 0) && (op_type & JB_PATH_CREATE_OR_INSERT) && (level == path_len - 1))
 	{
 		JsonbValue	newkey;
 
@@ -3808,7 +3862,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);
@@ -3819,12 +3873,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_OR_INSERT) && !done &&
+				level == path_len - 1 && i == npairs - 1)
 			{
 				JsonbValue	newkey;
 
@@ -3865,7 +3920,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,
@@ -3909,7 +3964,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_OR_INSERT))
 	{
 		Assert(newval != NULL);
 		addJsonbToParseState(st, newval);
@@ -3926,14 +3982,21 @@ setPathArray(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_INSERT_AFTER)
+					(void) pushJsonbValue(st, r, &v);
+
+				if (!(op_type & JB_PATH_DELETE))
 					addJsonbToParseState(st, newval);
 
+				if (op_type & JB_PATH_INSERT_BEFORE)
+					(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
 		{
@@ -3958,7 +4021,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_OR_INSERT && !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 a595327..b0412fd 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -4839,6 +4839,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 = 3324 (  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 497b0d9..6b1b2df 100644
--- a/src/test/regress/expected/jsonb.out
+++ b/src/test/regress/expected/jsonb.out
@@ -3312,3 +3312,126 @@ select jsonb_set('{"a": {"b": [1, 2, 3]}}', '{a, b, non_integer}', '"new_value"'
 ERROR:  path element at position 3 is not an integer: "non_integer"
 select jsonb_set('{"a": {"b": [1, 2, 3]}}', '{a, b, NULL}', '"new_value"');
 ERROR:  path element at 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)
+
+select jsonb_insert('{"a": [0,1,2]}', '{a, -10}', '"new_value"');
+         jsonb_insert          
+-------------------------------
+ {"a": ["new_value", 0, 1, 2]}
+(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 c671684..4c51e80 100644
--- a/src/test/regress/sql/jsonb.sql
+++ b/src/test/regress/sql/jsonb.sql
@@ -837,3 +837,30 @@ 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"');
+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);

#14

Vitaly Burovoy

vitaly.burovoy@gmail.com

almost 10 years ago

In reply to: Dmitry Dolgov (#13)

Re: [PATH] Jsonb, insert a new value into an array at arbitrary position

On 3/30/16, Dmitry Dolgov <9erthalion6@gmail.com> wrote:

On 31 March 2016 at 05:04, Vitaly Burovoy <vitaly.burovoy@gmail.com> wrote:

The documentation changes still has to be fixed.

Thanks for help. Looks like I'm not so good at text formulation. Fixed.

Never mind. I'm also not so good at it. It is still should be
rewritten by a native English speaker, but it is better to give him
good source for it.

===
I'm almost ready to mark it as "Ready for committer".

Few notes again.
1.

a current item was pushed to parse state after JB_PATH_INSERT_BEFORE

I paid attention that the function's name 'pushJsonbValue' looks as
working with stack (push/pop), but there is no function "pop*" neither
in jsonfuncs.c nor jsonb_util.c.
That's why I wrote "it seems the logic in the code is correct" - it is
logical to insert new value if "before", then current value, then new
value if "after".
But yes, following by executing path to the "pushState" function
anyone understands "JsonbParseState" is constructed as a stack, not a
queue. So additional comment is needed why "JB_PATH_INSERT_AFTER"
check is placed before inserting curent value and
JB_PATH_INSERT_BEFORE check.

The comment can be located just before "if (op_type &
JB_PATH_INSERT_AFTER)" (line 3986) and look similar to:

/*
* JsonbParseState struct behaves as a stack -- see the "pushState" function,
* so check for JB_PATH_INSERT_BEFORE/JB_PATH_INSERT_AFTER in a reverse order.
*/

2.
A comment for the function "setPath" is obsolete: "If newval is null"
check and "If create is true" name do not longer exist.
Since I answered so late last time I propose the next formulating to
avoid one (possible) round:

/*
* Do most of the heavy work for jsonb_set/jsonb_insert
*
* If JB_PATH_DELETE bit is set in op_type, the element is to be removed.
*
* If any bit mentioned in JB_PATH_CREATE_OR_INSERT is set in op_type,
* we create the new value if the key or array index does not exist.
*
* Bits JB_PATH_INSERT_BEFORE and JB_PATH_INSERT_AFTER in op_type
* behave as JB_PATH_CREATE if new value is inserted in JsonbObject.
*
* All path elements before the last must already exist
* whatever bits in op_type are set, or nothing is done.
*/

===
I hope that notes are final (additional check in formulating is appreciated).

P.S.: if it is not hard for you, please rebase the patch to the
current master to avoid offset in func.sgml.

--
Best regards,
Vitaly Burovoy

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

#15

Dmitry Dolgov

9erthalion6@gmail.com

almost 10 years ago

In reply to: Vitaly Burovoy (#14)

1 attachment(s)

Re: [PATH] Jsonb, insert a new value into an array at arbitrary position

On 31 March 2016 at 17:31, Vitaly Burovoy <vitaly.burovoy@gmail.com> wrote:

it is logical to insert new value if "before", then current value, then new
value if "after".

Oh, I see now. There is a slightly different logic: `v` is a current value
and `newval` is a new value.
So basically we insert a current item in case of "after", then a new value
(if it's not a delete operation),
then a current item in case of "before". But I agree, this code can be more
straightforward. I've attached
a new version, pls take a look (it contains the same logic that you've
mentioned).

Attachments:

jsonb_insert_v6.patchtext/x-patch; charset=US-ASCII; name=jsonb_insert_v6.patchDownload

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 1bc9fbc..8fb2624 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -10903,6 +10903,9 @@ table2-mapping
    <primary>jsonb_set</primary>
   </indexterm>
   <indexterm>
+   <primary>jsonb_insert</primary>
+  </indexterm>
+  <indexterm>
    <primary>jsonb_pretty</primary>
   </indexterm>
 
@@ -11184,6 +11187,40 @@ table2-mapping
         </para></entry>
        </row>
       <row>
+       <entry>
+           <para><literal>
+           jsonb_insert(target jsonb, path text[], new_value jsonb, <optional><parameter>insert_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 in a JSONB array,
+         <replaceable>new_value</replaceable> will be inserted before target or
+         after if <replaceable>insert_after</replaceable> is true (default is
+         <literal>false</>). If <replaceable>target</replaceable> section
+         designated by <replaceable>path</replaceable> is in JSONB object,
+         <function>jsonb_insert</> behaves as
+         <function>jsonb_set</> function where
+         <replaceable>create_missing</replaceable> is true. 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>
@@ -11235,10 +11272,11 @@ table2-mapping
   <note>
     <para>
       All the items of the <literal>path</> parameter of <literal>jsonb_set</>
-      must be present in the <literal>target</>, unless
-      <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.
+      as well as <literal>jsonb_insert</> except the last item must be present
+      in the <literal>target</>. If <literal>create_missing</> is false, all
+      items of the <literal>path</> parameter of <literal>jsonb_set</> 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
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 9ae1ef4..a6e661c 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -997,3 +997,11 @@ RETURNS text[]
 LANGUAGE INTERNAL
 STRICT IMMUTABLE
 AS 'parse_ident';
+
+CREATE OR REPLACE FUNCTION
+  jsonb_insert(jsonb_in jsonb, path text[] , replacement jsonb,
+            insert_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 97e0e8e..97bb08e 100644
--- a/src/backend/utils/adt/jsonfuncs.c
+++ b/src/backend/utils/adt/jsonfuncs.c
@@ -33,6 +33,15 @@
 #include "utils/memutils.h"
 #include "utils/typcache.h"
 
+/* Operations available for setPath */
+#define JB_PATH_NOOP					0x0000
+#define JB_PATH_CREATE					0x0001
+#define JB_PATH_DELETE					0x0002
+#define JB_PATH_INSERT_BEFORE			0x0004
+#define JB_PATH_INSERT_AFTER			0x0008
+#define JB_PATH_CREATE_OR_INSERT (JB_PATH_INSERT_BEFORE | JB_PATH_INSERT_AFTER \
+		| JB_PATH_CREATE)
+
 /* 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 +139,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 +3553,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 +3597,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		after = 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,
+				  after ? JB_PATH_INSERT_AFTER : JB_PATH_INSERT_BEFORE);
 
 	Assert(res != NULL);
 
@@ -3707,18 +3761,23 @@ IteratorConcat(JsonbIterator **it1, JsonbIterator **it2,
 }
 
 /*
- * Do most of the heavy work for jsonb_set
+ * Do most of the heavy work for jsonb_set/jsonb_insert
+ *
+ * If JB_PATH_DELETE bit is set in op_type, the element is to be removed.
  *
- * If newval is null, the element is to be removed.
+ * If any bit mentioned in JB_PATH_CREATE_OR_INSERT is set in op_type,
+ * we create the new value if the key or array index does not exist.
  *
- * If create is true, we create the new value if the key or array index
- * does not exist. All path elements before the last must already exist
- * whether or not create is true, or nothing is done.
+ * Bits JB_PATH_INSERT_BEFORE and JB_PATH_INSERT_AFTER in op_type
+ * behave as JB_PATH_CREATE if new value is inserted in JsonbObject.
+ *
+ * All path elements before the last must already exist
+ * whatever bits in op_type are set, or nothing is done.
  */
 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;
@@ -3739,7 +3798,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);
@@ -3747,7 +3806,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);
@@ -3771,7 +3830,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;
@@ -3782,7 +3841,7 @@ setPathObject(JsonbIterator **it, Datum *path_elems, bool *path_nulls,
 		done = true;
 
 	/* empty object is a special case for create */
-	if ((npairs == 0) && create && (level == path_len - 1))
+	if ((npairs == 0) && (op_type & JB_PATH_CREATE_OR_INSERT) && (level == path_len - 1))
 	{
 		JsonbValue	newkey;
 
@@ -3808,7 +3867,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);
@@ -3819,12 +3878,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_OR_INSERT) && !done &&
+				level == path_len - 1 && i == npairs - 1)
 			{
 				JsonbValue	newkey;
 
@@ -3865,7 +3925,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,
@@ -3909,7 +3969,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_OR_INSERT))
 	{
 		Assert(newval != NULL);
 		addJsonbToParseState(st, newval);
@@ -3926,14 +3987,26 @@ setPathArray(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_INSERT_BEFORE | JB_PATH_CREATE))
+					addJsonbToParseState(st, newval);
+
+				/*
+				 * We should keep current value only in case of
+				 * JB_PATH_INSERT_BEFORE or JB_PATH_INSERT_AFTER
+				 * because otherwise it should be deleted or replaced
+				 */
+				if (op_type & (JB_PATH_INSERT_AFTER | JB_PATH_INSERT_BEFORE))
+					(void) pushJsonbValue(st, r, &v);
+
+				if (op_type & JB_PATH_INSERT_AFTER)
 					addJsonbToParseState(st, newval);
 
 				done = true;
 			}
 			else
 				(void) setPath(it, path_elems, path_nulls, path_len,
-							   st, level + 1, newval, create);
+							   st, level + 1, newval, op_type);
 		}
 		else
 		{
@@ -3958,7 +4031,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_OR_INSERT && !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 c86b920..cfc9dd8 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -4839,6 +4839,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 = 3324 (  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 497b0d9..6b1b2df 100644
--- a/src/test/regress/expected/jsonb.out
+++ b/src/test/regress/expected/jsonb.out
@@ -3312,3 +3312,126 @@ select jsonb_set('{"a": {"b": [1, 2, 3]}}', '{a, b, non_integer}', '"new_value"'
 ERROR:  path element at position 3 is not an integer: "non_integer"
 select jsonb_set('{"a": {"b": [1, 2, 3]}}', '{a, b, NULL}', '"new_value"');
 ERROR:  path element at 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)
+
+select jsonb_insert('{"a": [0,1,2]}', '{a, -10}', '"new_value"');
+         jsonb_insert          
+-------------------------------
+ {"a": ["new_value", 0, 1, 2]}
+(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 c671684..4c51e80 100644
--- a/src/test/regress/sql/jsonb.sql
+++ b/src/test/regress/sql/jsonb.sql
@@ -837,3 +837,30 @@ 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"');
+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);

#16

Vitaly Burovoy

vitaly.burovoy@gmail.com

almost 10 years ago

In reply to: Dmitry Dolgov (#15)

Re: [PATH] Jsonb, insert a new value into an array at arbitrary position

The following review has been posted through the commitfest application:
make installcheck-world: tested, passed
Implements feature: tested, passed
Spec compliant: not tested
Documentation: tested, passed

I have reviewed this patch.
It applies cleanly at the top of current master (3501f71), compiles silently and implements declared feature.

The documentation describes behavior of the function with pointing to a difference between inserting into JsonbObject and JsonbArray.

The code is clean and commented. Linked comment is changed too.

Regression tests cover possible use cases and edge cases.

Notes for a committer:
1. pg_proc.h has changed, so the CATALOG_VERSION_NO must also be changed.
2. Code comments and changes in the documentation need proof-reading by a native
English speaker, which the Author and I are not.

The new status of this patch is: Ready for Committer

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

#17

Andrew Dunstan

andrew@dunslane.net

almost 10 years ago

In reply to: Dmitry Dolgov (#15)

Re: [PATH] Jsonb, insert a new value into an array at arbitrary position

On 03/31/2016 09:00 AM, Dmitry Dolgov wrote:

On 31 March 2016 at 17:31, Vitaly Burovoy <vitaly.burovoy@gmail.com
<mailto:vitaly.burovoy@gmail.com>> wrote:

it is logical to insert new value if "before", then current value,
then new
value if "after".

Oh, I see now. There is a slightly different logic: `v` is a current
value and `newval` is a new value.
So basically we insert a current item in case of "after", then a new
value (if it's not a delete operation),
then a current item in case of "before". But I agree, this code can be
more straightforward. I've attached
a new version, pls take a look (it contains the same logic that you've
mentioned).

I haven't been following this thread due to pressure of time, so my
apologies in advance if these comments have already been covered.

I've been asked to look at and comment on the SQL API of the feature. I
think it's basically sound, although there is one thing that's not clear
from the regression tests: what happens if we're inserting into an
object and the key already exists? e.g.:

select jsonb_insert('{"a": {"b": "value"}}', '{a, b}', '"new_value"');

I think this should be forbidden, i.e. the function shouldn't ever
overwrite an existing value. If that's not handled it should be, and
either way there should be a regression test for it.

cheers

andrew

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

#18

Teodor Sigaev

teodor@sigaev.ru

almost 10 years ago

In reply to: Andrew Dunstan (#17)

Re: [PATH] Jsonb, insert a new value into an array at arbitrary position

I've been asked to look at and comment on the SQL API of the feature. I think
it's basically sound, although there is one thing that's not clear from the
regression tests: what happens if we're inserting into an object and the key
already exists? e.g.:

select jsonb_insert('{"a": {"b": "value"}}', '{a, b}', '"new_value"');

I think this should be forbidden, i.e. the function shouldn't ever overwrite an
existing value. If that's not handled it should be, and either way there should
be a regression test for it.

I'm agree about covering this case by tests, but I think it should be allowed.
In this case it will work exactly as jsbonb_set
--
Teodor Sigaev E-mail: teodor@sigaev.ru
WWW: http://www.sigaev.ru/

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

#19

Alvaro Herrera

alvherre@2ndquadrant.com

almost 10 years ago

In reply to: Andrew Dunstan (#17)

Re: [PATH] Jsonb, insert a new value into an array at arbitrary position

Andrew Dunstan wrote:

I haven't been following this thread due to pressure of time, so my
apologies in advance if these comments have already been covered.

I've been asked to look at and comment on the SQL API of the feature. I
think it's basically sound, although there is one thing that's not clear
from the regression tests: what happens if we're inserting into an object
and the key already exists? e.g.:

select jsonb_insert('{"a": {"b": "value"}}', '{a, b}', '"new_value"');

It has been covered: Petr said, and I agree with him, that this new
interface is for arrays, not objects, and so the above example should be
rejected altogether. For objects we already have jsonb_set(), so what
is the point of having this work there? It feels too much like
TIMTOWTDI, which isn't a principle we adhere to.

I think it'd be much more sensible if we were just to make this function
work on arrays only. There, the solution to Andrew's question is
trivial: if you insert a value in a position that's already occupied,
the elements to its right are "shifted" one position upwards in the
array (this is why this is an "insert" operation and not "replace" or
"set").

--
ï¿½lvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

#20

Andrew Dunstan

andrew@dunslane.net

almost 10 years ago

In reply to: Teodor Sigaev (#18)

Re: [PATH] Jsonb, insert a new value into an array at arbitrary position

On 04/05/2016 12:42 PM, Teodor Sigaev wrote:

I've been asked to look at and comment on the SQL API of the feature.
I think
it's basically sound, although there is one thing that's not clear
from the
regression tests: what happens if we're inserting into an object and
the key
already exists? e.g.:

select jsonb_insert('{"a": {"b": "value"}}', '{a, b}', '"new_value"');

I think this should be forbidden, i.e. the function shouldn't ever
overwrite an
existing value. If that's not handled it should be, and either way
there should
be a regression test for it.

I'm agree about covering this case by tests, but I think it should be
allowed.
In this case it will work exactly as jsbonb_set

It seems to me a violation of POLA to allow something called "insert" to
do a "replace" instead.

cheers

andre

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

#21

Tom Lane

tgl@sss.pgh.pa.us

almost 10 years ago

In reply to: Andrew Dunstan (#20)

Re: [PATH] Jsonb, insert a new value into an array at arbitrary position

Andrew Dunstan <andrew@dunslane.net> writes:

On 04/05/2016 12:42 PM, Teodor Sigaev wrote:

I'm agree about covering this case by tests, but I think it should be
allowed.
In this case it will work exactly as jsbonb_set

It seems to me a violation of POLA to allow something called "insert" to
do a "replace" instead.

I agree with Andrew's point here. If the target is an array it would
never replace an entry, so why would it do so for an object?

I think there is potentially some use-case for insert-only semantics
for an object target, if you want to be sure you're not overwriting
data. So I think "throw an error on duplicate key" is marginally better
than "reject object target altogether". But I could live with either.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

#22

Andrew Dunstan

andrew@dunslane.net

almost 10 years ago

In reply to: Tom Lane (#21)

Re: [PATH] Jsonb, insert a new value into an array at arbitrary position

On 04/05/2016 03:08 PM, Tom Lane wrote:

I think there is potentially some use-case for insert-only semantics
for an object target, if you want to be sure you're not overwriting
data. So I think "throw an error on duplicate key" is marginally better
than "reject object target altogether". But I could live with either.

Yeah, keeping it but rejecting update of an existing key is my
preference too.

cheers

andrew

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

#23

Dmitry Dolgov

9erthalion6@gmail.com

almost 10 years ago

In reply to: Andrew Dunstan (#22)

1 attachment(s)

Re: [PATH] Jsonb, insert a new value into an array at arbitrary position

On 6 April 2016 at 03:29, Andrew Dunstan <andrew@dunslane.net> wrote:

Yeah, keeping it but rejecting update of an existing key is my preference
too.

cheers

andrew

Yes, it sounds quite reasonable. Here is a new version of patch (it will
throw
an error for an existing key). Is it better now?

Attachments:

jsonb_insert_v7.patchtext/x-patch; charset=US-ASCII; name=jsonb_insert_v7.patchDownload

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 1bc9fbc..0d4b3a1 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -10903,6 +10903,9 @@ table2-mapping
    <primary>jsonb_set</primary>
   </indexterm>
   <indexterm>
+   <primary>jsonb_insert</primary>
+  </indexterm>
+  <indexterm>
    <primary>jsonb_pretty</primary>
   </indexterm>
 
@@ -11184,6 +11187,39 @@ table2-mapping
         </para></entry>
        </row>
       <row>
+       <entry>
+           <para><literal>
+           jsonb_insert(target jsonb, path text[], new_value jsonb, <optional><parameter>insert_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 in a JSONB array,
+         <replaceable>new_value</replaceable> will be inserted before target or
+         after if <replaceable>insert_after</replaceable> is true (default is
+         <literal>false</>). If <replaceable>target</replaceable> section
+         designated by <replaceable>path</replaceable> is in JSONB object,
+         <replaceable>new_value</replaceable> will be inserted only if
+         <replaceable>target</replaceable> does not exist. 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>
@@ -11235,10 +11271,11 @@ table2-mapping
   <note>
     <para>
       All the items of the <literal>path</> parameter of <literal>jsonb_set</>
-      must be present in the <literal>target</>, unless
-      <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.
+      as well as <literal>jsonb_insert</> except the last item must be present
+      in the <literal>target</>. If <literal>create_missing</> is false, all
+      items of the <literal>path</> parameter of <literal>jsonb_set</> 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
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 9ae1ef4..a6e661c 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -997,3 +997,11 @@ RETURNS text[]
 LANGUAGE INTERNAL
 STRICT IMMUTABLE
 AS 'parse_ident';
+
+CREATE OR REPLACE FUNCTION
+  jsonb_insert(jsonb_in jsonb, path text[] , replacement jsonb,
+            insert_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 97e0e8e..c1b8041 100644
--- a/src/backend/utils/adt/jsonfuncs.c
+++ b/src/backend/utils/adt/jsonfuncs.c
@@ -33,6 +33,15 @@
 #include "utils/memutils.h"
 #include "utils/typcache.h"
 
+/* Operations available for setPath */
+#define JB_PATH_NOOP					0x0000
+#define JB_PATH_CREATE					0x0001
+#define JB_PATH_DELETE					0x0002
+#define JB_PATH_INSERT_BEFORE			0x0004
+#define JB_PATH_INSERT_AFTER			0x0008
+#define JB_PATH_CREATE_OR_INSERT (JB_PATH_INSERT_BEFORE | JB_PATH_INSERT_AFTER \
+		| JB_PATH_CREATE)
+
 /* 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 +139,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 +3553,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 +3597,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		after = 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,
+				  after ? JB_PATH_INSERT_AFTER : JB_PATH_INSERT_BEFORE);
 
 	Assert(res != NULL);
 
@@ -3707,18 +3761,23 @@ IteratorConcat(JsonbIterator **it1, JsonbIterator **it2,
 }
 
 /*
- * Do most of the heavy work for jsonb_set
+ * Do most of the heavy work for jsonb_set/jsonb_insert
+ *
+ * If JB_PATH_DELETE bit is set in op_type, the element is to be removed.
+ *
+ * If any bit mentioned in JB_PATH_CREATE_OR_INSERT is set in op_type,
+ * we create the new value if the key or array index does not exist.
  *
- * If newval is null, the element is to be removed.
+ * Bits JB_PATH_INSERT_BEFORE and JB_PATH_INSERT_AFTER in op_type
+ * behave as JB_PATH_CREATE if new value is inserted in JsonbObject.
  *
- * If create is true, we create the new value if the key or array index
- * does not exist. All path elements before the last must already exist
- * whether or not create is true, or nothing is done.
+ * All path elements before the last must already exist
+ * whatever bits in op_type are set, or nothing is done.
  */
 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;
@@ -3739,7 +3798,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);
@@ -3747,7 +3806,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);
@@ -3771,7 +3830,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;
@@ -3782,7 +3841,7 @@ setPathObject(JsonbIterator **it, Datum *path_elems, bool *path_nulls,
 		done = true;
 
 	/* empty object is a special case for create */
-	if ((npairs == 0) && create && (level == path_len - 1))
+	if ((npairs == 0) && (op_type & JB_PATH_CREATE_OR_INSERT) && (level == path_len - 1))
 	{
 		JsonbValue	newkey;
 
@@ -3807,8 +3866,15 @@ setPathObject(JsonbIterator **it, Datum *path_elems, bool *path_nulls,
 		{
 			if (level == path_len - 1)
 			{
+				if (op_type & (JB_PATH_INSERT_BEFORE | JB_PATH_INSERT_AFTER))
+					ereport(ERROR,
+							(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+							 errmsg("cannot replace existing key"),
+							 errhint("Try using the function jsonb_set "
+									 "to replace key value.")));
+
 				r = JsonbIteratorNext(it, &v, true);	/* skip */
-				if (newval != NULL)
+				if (!(op_type & JB_PATH_DELETE))
 				{
 					(void) pushJsonbValue(st, WJB_KEY, &k);
 					addJsonbToParseState(st, newval);
@@ -3819,12 +3885,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_OR_INSERT) && !done &&
+				level == path_len - 1 && i == npairs - 1)
 			{
 				JsonbValue	newkey;
 
@@ -3865,7 +3932,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,
@@ -3909,7 +3976,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_OR_INSERT))
 	{
 		Assert(newval != NULL);
 		addJsonbToParseState(st, newval);
@@ -3926,14 +3994,26 @@ setPathArray(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_INSERT_BEFORE | JB_PATH_CREATE))
+					addJsonbToParseState(st, newval);
+
+				/*
+				 * We should keep current value only in case of
+				 * JB_PATH_INSERT_BEFORE or JB_PATH_INSERT_AFTER
+				 * because otherwise it should be deleted or replaced
+				 */
+				if (op_type & (JB_PATH_INSERT_AFTER | JB_PATH_INSERT_BEFORE))
+					(void) pushJsonbValue(st, r, &v);
+
+				if (op_type & JB_PATH_INSERT_AFTER)
 					addJsonbToParseState(st, newval);
 
 				done = true;
 			}
 			else
 				(void) setPath(it, path_elems, path_nulls, path_len,
-							   st, level + 1, newval, create);
+							   st, level + 1, newval, op_type);
 		}
 		else
 		{
@@ -3958,7 +4038,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_OR_INSERT && !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 c86b920..cfc9dd8 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -4839,6 +4839,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 = 3324 (  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 497b0d9..a6d25de 100644
--- a/src/test/regress/expected/jsonb.out
+++ b/src/test/regress/expected/jsonb.out
@@ -3312,3 +3312,132 @@ select jsonb_set('{"a": {"b": [1, 2, 3]}}', '{a, b, non_integer}', '"new_value"'
 ERROR:  path element at position 3 is not an integer: "non_integer"
 select jsonb_set('{"a": {"b": [1, 2, 3]}}', '{a, b, NULL}', '"new_value"');
 ERROR:  path element at 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)
+
+select jsonb_insert('{"a": [0,1,2]}', '{a, -10}', '"new_value"');
+         jsonb_insert          
+-------------------------------
+ {"a": ["new_value", 0, 1, 2]}
+(1 row)
+
+-- jsonb_insert should be able to insert new value for objects, but not to replace
+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)
+
+select jsonb_insert('{"a": {"b": "value"}}', '{a, b}', '"new_value"');
+ERROR:  cannot replace existing key
+HINT:  Try using the function jsonb_set to replace key value.
+select jsonb_insert('{"a": {"b": "value"}}', '{a, b}', '"new_value"', true);
+ERROR:  cannot replace existing key
+HINT:  Try using the function jsonb_set to replace key value.
diff --git a/src/test/regress/sql/jsonb.sql b/src/test/regress/sql/jsonb.sql
index c671684..b84bd70 100644
--- a/src/test/regress/sql/jsonb.sql
+++ b/src/test/regress/sql/jsonb.sql
@@ -837,3 +837,33 @@ 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"');
+select jsonb_insert('{"a": [0,1,2]}', '{a, -10}', '"new_value"');
+
+-- jsonb_insert should be able to insert new value for objects, but not to replace
+select jsonb_insert('{"a": {"b": "value"}}', '{a, c}', '"new_value"');
+select jsonb_insert('{"a": {"b": "value"}}', '{a, c}', '"new_value"', true);
+
+select jsonb_insert('{"a": {"b": "value"}}', '{a, b}', '"new_value"');
+select jsonb_insert('{"a": {"b": "value"}}', '{a, b}', '"new_value"', true);

#24

Petr Jelinek

petr@2ndquadrant.com

almost 10 years ago

In reply to: Dmitry Dolgov (#23)

Re: [PATH] Jsonb, insert a new value into an array at arbitrary position

On 06/04/16 06:13, Dmitry Dolgov wrote:

On 6 April 2016 at 03:29, Andrew Dunstan <andrew@dunslane.net
<mailto:andrew@dunslane.net>> wrote:

Yeah, keeping it but rejecting update of an existing key is my
preference too.

cheers

andrew

Yes, it sounds quite reasonable. Here is a new version of patch (it will
throw
an error for an existing key). Is it better now?

This seems like reasonable compromise to me. I wonder if the errcode
should be ERRCODE_INVALID_PARAMETER_VALUE but don't feel too strongly
about that.

--
Petr Jelinek http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

#25

Teodor Sigaev

teodor@sigaev.ru

almost 10 years ago

In reply to: Dmitry Dolgov (#23)

Re: [PATH] Jsonb, insert a new value into an array at arbitrary position

Thank you, pushed with Petr's notice

Dmitry Dolgov wrote:

On 6 April 2016 at 03:29, Andrew Dunstan <andrew@dunslane.net
<mailto:andrew@dunslane.net>> wrote:

Yeah, keeping it but rejecting update of an existing key is my preference too.

cheers

andrew

Yes, it sounds quite reasonable. Here is a new version of patch (it will throw
an error for an existing key). Is it better now?

--
Teodor Sigaev E-mail: teodor@sigaev.ru
WWW: http://www.sigaev.ru/

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers