Subject: [PATCH v30 6/6] Filling gaps in jsonb arrays

From: Dmitrii Dolgov <[email-masked]>
Date: Thu, 19 Dec 2019 14:13:35 +0100

Appending or prepending array elements on the specified position, gaps
filled with nulls (JavaScript has similar behavior)

Author: Nikita Glukhov
---
 src/backend/utils/adt/jsonfuncs.c   | 26 +++++++++++-
 src/test/regress/expected/jsonb.out | 62 ++++++++++++++---------------
 2 files changed, 55 insertions(+), 33 deletions(-)

diff --git a/src/backend/utils/adt/jsonfuncs.c b/src/backend/utils/adt/jsonfuncs.c
index 82d4a98e7e..e86d3918dd 100644
--- a/src/backend/utils/adt/jsonfuncs.c
+++ b/src/backend/utils/adt/jsonfuncs.c
@@ -1753,6 +1753,17 @@ jsonb_subscript_apply(JsonbValue *jbv, Datum subscriptVal, Oid subscriptTypid,
 	}
 }
 
+static void
+push_null_elements(JsonbParseState **ps, int num)
+{
+	JsonbValue	null;
+
+	null.type = jbvNull;
+
+	while (num-- > 0)
+		pushJsonbValue(ps, WJB_ELEM, &null);
+}
+
 /* Perfrom one subscript assignment step */
 static void
 jsonb_subscript_step_assignment(SubscriptingRefState *sbstate, Datum value,
@@ -1809,6 +1820,10 @@ jsonb_subscript_step_assignment(SubscriptingRefState *sbstate, Datum value,
 				JsonbIteratorNext(&astate->iter, &jbv, last) != WJB_END_ARRAY)
 				subscript[1].exists = true;
 		}
+
+		/* Fill the gap before the new element with nulls */
+		if (i < index)
+			push_null_elements(&astate->ps, index - i);
 	}
 	else
 	{
@@ -5517,8 +5532,15 @@ jsonb_subscript_assign(Datum containerSource, SubscriptingRefState *sbstate)
 		if (subscript[1].exists)
 			break;
 
-		if (subscript->is_array && subscript->array_index < 0 && subscript->exists)
-			break;	/* original elements are copied from the iterator */
+		if (subscript->is_array && subscript->array_index < 0)
+		{
+			/* Fill the gap between prepended element and 0th element */
+			if (subscript->array_index < -1)
+				push_null_elements(&astate->ps, -1 - subscript->array_index);
+
+			if (subscript->exists)
+				break;	/* original elements are copied from the iterator */
+		}
 
 		tok = subscript->is_array ? WJB_END_ARRAY : WJB_END_OBJECT;
 		res = pushJsonbValue(&astate->ps, tok, NULL);
diff --git a/src/test/regress/expected/jsonb.out b/src/test/regress/expected/jsonb.out
index 16ffdcecf9..89b1452a75 100644
--- a/src/test/regress/expected/jsonb.out
+++ b/src/test/regress/expected/jsonb.out
@@ -5024,35 +5024,35 @@ select * from test_jsonb_subscript;
 -- append element to array with a gap filled with nulls
 update test_jsonb_subscript set test_json['a'][7] = '8'::jsonb;
 select * from test_jsonb_subscript;
- id |                test_json                 
-----+------------------------------------------
-  1 | {"a": [1, 2, 3, "4", 8]}
-  2 | {"a": [1, 2, 3, "4", 8], "key": "value"}
+ id |                         test_json                          
+----+------------------------------------------------------------
+  1 | {"a": [1, 2, 3, "4", null, null, null, 8]}
+  2 | {"a": [1, 2, 3, "4", null, null, null, 8], "key": "value"}
 (2 rows)
 
 -- replace element in array using negative subscript
 update test_jsonb_subscript set test_json['a'][-4] = '5'::jsonb;
 select * from test_jsonb_subscript;
- id |                test_json                 
-----+------------------------------------------
-  1 | {"a": [1, 5, 3, "4", 8]}
-  2 | {"a": [1, 5, 3, "4", 8], "key": "value"}
+ id |                        test_json                        
+----+---------------------------------------------------------
+  1 | {"a": [1, 2, 3, "4", 5, null, null, 8]}
+  2 | {"a": [1, 2, 3, "4", 5, null, null, 8], "key": "value"}
 (2 rows)
 
 -- prepend element to array using negative subscript with a gap filled with nulls
 update test_jsonb_subscript set test_json['a'][-10] = '6'::jsonb;
 select * from test_jsonb_subscript;
- id |                  test_json                  
-----+---------------------------------------------
-  1 | {"a": [6, 1, 5, 3, "4", 8]}
-  2 | {"a": [6, 1, 5, 3, "4", 8], "key": "value"}
+ id |                            test_json                             
+----+------------------------------------------------------------------
+  1 | {"a": [6, null, 1, 2, 3, "4", 5, null, null, 8]}
+  2 | {"a": [6, null, 1, 2, 3, "4", 5, null, null, 8], "key": "value"}
 (2 rows)
 
 -- use jsonb subscription in where clause
 select * from test_jsonb_subscript where test_json['key'] = '"value"';
- id |                  test_json                  
-----+---------------------------------------------
-  2 | {"a": [6, 1, 5, 3, "4", 8], "key": "value"}
+ id |                            test_json                             
+----+------------------------------------------------------------------
+  2 | {"a": [6, null, 1, 2, 3, "4", 5, null, null, 8], "key": "value"}
 (1 row)
 
 select * from test_jsonb_subscript where test_json['key_doesnt_exists'] = '"value"';
@@ -5070,10 +5070,10 @@ update test_jsonb_subscript set test_json[NULL] = 1;
 ERROR:  subscript in assignment must not be null
 update test_jsonb_subscript set test_json['another_key'] = NULL;
 select * from test_jsonb_subscript;
- id |                            test_json                             
-----+------------------------------------------------------------------
-  1 | {"a": [6, 1, 5, 3, "4", 8], "another_key": null}
-  2 | {"a": [6, 1, 5, 3, "4", 8], "key": "value", "another_key": null}
+ id |                                       test_json                                       
+----+---------------------------------------------------------------------------------------
+  1 | {"a": [6, null, 1, 2, 3, "4", 5, null, null, 8], "another_key": null}
+  2 | {"a": [6, null, 1, 2, 3, "4", 5, null, null, 8], "key": "value", "another_key": null}
 (2 rows)
 
 -- create a path
@@ -5102,30 +5102,30 @@ select * from test_jsonb_subscript;
 
 update test_jsonb_subscript set test_json['d'][0]['a'][3] = '4'::jsonb;
 select * from test_jsonb_subscript;
- id |                       test_json                        
-----+--------------------------------------------------------
-  0 | {"a": 1, "b": [2], "c": [{"a": 3}], "d": [{"a": [4]}]}
+ id |                                test_json                                 
+----+--------------------------------------------------------------------------
+  0 | {"a": 1, "b": [2], "c": [{"a": 3}], "d": [{"a": [null, null, null, 4]}]}
 (1 row)
 
 update test_jsonb_subscript set test_json['d'][0]['c'][-3] = '5'::jsonb;
 select * from test_jsonb_subscript;
- id |                            test_json                             
-----+------------------------------------------------------------------
-  0 | {"a": 1, "b": [2], "c": [{"a": 3}], "d": [{"a": [4], "c": [5]}]}
+ id |                                           test_json                                            
+----+------------------------------------------------------------------------------------------------
+  0 | {"a": 1, "b": [2], "c": [{"a": 3}], "d": [{"a": [null, null, null, 4], "c": [5, null, null]}]}
 (1 row)
 
 update test_jsonb_subscript set test_json['d'][0]['b']['x'] = '6'::jsonb;
 select * from test_jsonb_subscript;
- id |                                    test_json                                    
-----+---------------------------------------------------------------------------------
-  0 | {"a": 1, "b": [2], "c": [{"a": 3}], "d": [{"a": [4], "b": {"x": 6}, "c": [5]}]}
+ id |                                                   test_json                                                   
+----+---------------------------------------------------------------------------------------------------------------
+  0 | {"a": 1, "b": [2], "c": [{"a": 3}], "d": [{"a": [null, null, null, 4], "b": {"x": 6}, "c": [5, null, null]}]}
 (1 row)
 
 update test_jsonb_subscript set test_json['e']['y'] = '7'::jsonb;
 select * from test_jsonb_subscript;
- id |                                           test_json                                            
-----+------------------------------------------------------------------------------------------------
-  0 | {"a": 1, "b": [2], "c": [{"a": 3}], "d": [{"a": [4], "b": {"x": 6}, "c": [5]}], "e": {"y": 7}}
+ id |                                                          test_json                                                           
+----+------------------------------------------------------------------------------------------------------------------------------
+  0 | {"a": 1, "b": [2], "c": [{"a": 3}], "d": [{"a": [null, null, null, 4], "b": {"x": 6}, "c": [5, null, null]}], "e": {"y": 7}}
 (1 row)
 
 -- updating of scalar's subscripts
-- 
2.21.0

--g5st7yssq7xavhfb--