Add jsonb_insert

It inserts a new value into an jsonb array at arbitrary position or
a new key to jsonb object.

Author: Dmitry Dolgov
Reviewers: Petr Jelinek, Vitaly Burovoy, Andrew Dunstan

2 files changed, 159 insertions, 0 deletions

diff --git a/src/test/regress/expected/jsonb.out b/src/test/regress/expected/jsonb.out
index 497b0d93ef0..a6d25defb0d 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 c6716841344..b84bd70a299 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);