agora inbox for [email protected]help / color / mirror / Atom feed
[PATCH v29 6/6] Filling gaps in jsonb arrays 9+ messages / 2 participants [nested] [flat]
* [PATCH v29 6/6] Filling gaps in jsonb arrays @ 2019-12-19 13:13 Dmitrii Dolgov <[email protected]> 0 siblings, 0 replies; 9+ messages in thread From: Dmitrii Dolgov @ 2019-12-19 13:13 UTC (permalink / raw) 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 a5120d005e..2bc13f9e5d 100644 --- a/src/backend/utils/adt/jsonfuncs.c +++ b/src/backend/utils/adt/jsonfuncs.c @@ -1620,6 +1620,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, @@ -1676,6 +1687,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 { @@ -5318,8 +5333,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 8b403aa2a2..526fb7574b 100644 --- a/src/test/regress/expected/jsonb.out +++ b/src/test/regress/expected/jsonb.out @@ -4967,35 +4967,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"'; @@ -5013,10 +5013,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 @@ -5045,30 +5045,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 --adquyx6kg5n26q6j-- ^ permalink raw reply [nested|flat] 9+ messages in thread
* [PATCH v30 6/6] Filling gaps in jsonb arrays @ 2019-12-19 13:13 Dmitrii Dolgov <[email protected]> 0 siblings, 0 replies; 9+ messages in thread From: Dmitrii Dolgov @ 2019-12-19 13:13 UTC (permalink / raw) 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-- ^ permalink raw reply [nested|flat] 9+ messages in thread
* [PATCH v31 6/6] Filling gaps in jsonb arrays @ 2019-12-19 13:13 Dmitrii Dolgov <[email protected]> 0 siblings, 0 replies; 9+ messages in thread From: Dmitrii Dolgov @ 2019-12-19 13:13 UTC (permalink / raw) 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 f1f72dd6b1..77687e7f71 100644 --- a/src/backend/utils/adt/jsonfuncs.c +++ b/src/backend/utils/adt/jsonfuncs.c @@ -1739,6 +1739,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, @@ -1795,6 +1806,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 { @@ -5504,8 +5519,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 --bvn7grlx53cj2mie-- ^ permalink raw reply [nested|flat] 9+ messages in thread
* [PATCH v32 6/6] Filling gaps in jsonb arrays @ 2019-12-19 13:13 Dmitrii Dolgov <[email protected]> 0 siblings, 0 replies; 9+ messages in thread From: Dmitrii Dolgov @ 2019-12-19 13:13 UTC (permalink / raw) 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 afc45b9f50..04d3078f68 100644 --- a/src/backend/utils/adt/jsonfuncs.c +++ b/src/backend/utils/adt/jsonfuncs.c @@ -1739,6 +1739,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); +} + /* Perform one subscript assignment step */ static void jsonb_subscript_step_assignment(SubscriptingRefState *sbstate, Datum value, @@ -1795,6 +1806,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 { @@ -5504,8 +5519,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 f7bcadda6b..9b97114946 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 --ques2rolqe435p6o-- ^ permalink raw reply [nested|flat] 9+ messages in thread
* [PATCH v32 6/6] Filling gaps in jsonb arrays @ 2019-12-19 13:13 Dmitrii Dolgov <[email protected]> 0 siblings, 0 replies; 9+ messages in thread From: Dmitrii Dolgov @ 2019-12-19 13:13 UTC (permalink / raw) 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 afc45b9f50..04d3078f68 100644 --- a/src/backend/utils/adt/jsonfuncs.c +++ b/src/backend/utils/adt/jsonfuncs.c @@ -1739,6 +1739,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); +} + /* Perform one subscript assignment step */ static void jsonb_subscript_step_assignment(SubscriptingRefState *sbstate, Datum value, @@ -1795,6 +1806,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 { @@ -5504,8 +5519,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 --xuxpg5p32zizz4zy-- ^ permalink raw reply [nested|flat] 9+ messages in thread
* [PATCH v33 5/5] Filling gaps in jsonb arrays @ 2020-08-04 15:41 Dmitrii Dolgov <[email protected]> 0 siblings, 0 replies; 9+ messages in thread From: Dmitrii Dolgov @ 2020-08-04 15:41 UTC (permalink / raw) Appending or prepending array elements on the specified position, gaps filled with nulls (similar to JavaScript behavior). Originally proposed by Nikita Glukhov based on polymorphic subscripting patch, but transformed into an independent change. --- src/backend/utils/adt/jsonfuncs.c | 43 +++++++++++++++++++++++++---- src/test/regress/expected/jsonb.out | 24 ++++++++++++++++ src/test/regress/sql/jsonb.sql | 13 +++++++++ 3 files changed, 74 insertions(+), 6 deletions(-) diff --git a/src/backend/utils/adt/jsonfuncs.c b/src/backend/utils/adt/jsonfuncs.c index f46a2828b3..6bb81f4c40 100644 --- a/src/backend/utils/adt/jsonfuncs.c +++ b/src/backend/utils/adt/jsonfuncs.c @@ -47,6 +47,7 @@ #define JB_PATH_INSERT_AFTER 0x0010 #define JB_PATH_CREATE_OR_INSERT \ (JB_PATH_INSERT_BEFORE | JB_PATH_INSERT_AFTER | JB_PATH_CREATE) +#define JB_PATH_FILL_GAPS 0x0020 /* state for json_object_keys */ typedef struct OkeysState @@ -1492,10 +1493,8 @@ get_jsonb_path_all(FunctionCallInfo fcinfo, bool as_text) static Datum jsonb_get_element(Jsonb *jb, Datum *path, int npath, bool *isnull, bool as_text) { - Jsonb *res; JsonbContainer *container = &jb->root; JsonbValue *jbvp = NULL; - JsonbValue tv; int i; bool have_object = false, have_array = false; @@ -1657,13 +1656,24 @@ jsonb_set_element(Datum jsonbdatum, Datum *path, int path_len, it = JsonbIteratorInit(&jb->root); res = setPath(&it, path, path_nulls, path_len, &state, 0, - newval, JB_PATH_CREATE); + newval, JB_PATH_CREATE | JB_PATH_FILL_GAPS); pfree(path_nulls); PG_RETURN_JSONB_P(JsonbValueToJsonb(res)); } +static void +push_null_elements(JsonbParseState **ps, int num) +{ + JsonbValue null; + + null.type = jbvNull; + + while (num-- > 0) + pushJsonbValue(ps, WJB_ELEM, &null); +} + /* * Return the text representation of the given JsonbValue. */ @@ -4811,6 +4821,13 @@ IteratorConcat(JsonbIterator **it1, JsonbIterator **it2, * 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 JB_PATH_FILL_GAPS bit is set, this will change an assignment logic in + * case if target is an array. The assignment index will not be restricted by + * number of elements in the array, and if there are any empty slots between + * last element of the array and a new one they will be filled with nulls. If + * the index is negative, it still will be considered an an index from the end + * of the array. + * * All path elements before the last must already exist * whatever bits in op_type are set, or nothing is done. */ @@ -5012,15 +5029,21 @@ setPathArray(JsonbIterator **it, Datum *path_elems, bool *path_nulls, idx = nelems + idx; } - if (idx > 0 && idx > nelems) - idx = nelems; + /* + * Filling the gaps means there are no limits on the positive index are + * imposed, we can set any element. Otherwise limit the index by nelems. + */ + if (!(op_type & JB_PATH_FILL_GAPS)) + { + if (idx > 0 && idx > nelems) + idx = nelems; + } /* * if we're creating, and idx == INT_MIN, we prepend the new value to the * array also if the array is empty - in which case we don't really care * what the idx value is */ - if ((idx == INT_MIN || nelems == 0) && (level == path_len - 1) && (op_type & JB_PATH_CREATE_OR_INSERT)) { @@ -5086,10 +5109,18 @@ setPathArray(JsonbIterator **it, Datum *path_elems, bool *path_nulls, if ((op_type & JB_PATH_CREATE_OR_INSERT) && !done && level == path_len - 1 && i == nelems - 1) { + /* + * If asked to fill the gaps, idx could be bigger than nelems, + * so prepend the new element with nulls if that's the case. + */ + if (op_type & JB_PATH_FILL_GAPS && idx > nelems) + push_null_elements(st, idx - nelems); + (void) pushJsonbValue(st, WJB_ELEM, newval); } } } + } /* diff --git a/src/test/regress/expected/jsonb.out b/src/test/regress/expected/jsonb.out index 04a146a7d0..4d52652688 100644 --- a/src/test/regress/expected/jsonb.out +++ b/src/test/regress/expected/jsonb.out @@ -4928,6 +4928,30 @@ select * from test_jsonb_subscript; 2 | {"a": [1, 2, 3], "key": "value", "another_key": null} (2 rows) +-- Fill the gaps logic +delete from test_jsonb_subscript; +insert into test_jsonb_subscript values (1, '[0]'); +update test_jsonb_subscript set test_json[5] = 1; +select * from test_jsonb_subscript; + id | test_json +----+-------------------------------- + 1 | [0, null, null, null, null, 1] +(1 row) + +update test_jsonb_subscript set test_json[-4] = 1; +select * from test_jsonb_subscript; + id | test_json +----+----------------------------- + 1 | [0, null, 1, null, null, 1] +(1 row) + +update test_jsonb_subscript set test_json[-8] = 1; +select * from test_jsonb_subscript; + id | test_json +----+-------------------------------- + 1 | [1, 0, null, 1, null, null, 1] +(1 row) + -- jsonb to tsvector select to_tsvector('{"a": "aaa bbb ddd ccc", "b": ["eee fff ggg"], "c": {"d": "hhh iii"}}'::jsonb); to_tsvector diff --git a/src/test/regress/sql/jsonb.sql b/src/test/regress/sql/jsonb.sql index 12541e7e50..584f145bab 100644 --- a/src/test/regress/sql/jsonb.sql +++ b/src/test/regress/sql/jsonb.sql @@ -1269,6 +1269,19 @@ update test_jsonb_subscript set test_json[NULL] = 1; update test_jsonb_subscript set test_json['another_key'] = NULL; select * from test_jsonb_subscript; +-- Fill the gaps logic +delete from test_jsonb_subscript; +insert into test_jsonb_subscript values (1, '[0]'); + +update test_jsonb_subscript set test_json[5] = 1; +select * from test_jsonb_subscript; + +update test_jsonb_subscript set test_json[-4] = 1; +select * from test_jsonb_subscript; + +update test_jsonb_subscript set test_json[-8] = 1; +select * from test_jsonb_subscript; + -- jsonb to tsvector select to_tsvector('{"a": "aaa bbb ddd ccc", "b": ["eee fff ggg"], "c": {"d": "hhh iii"}}'::jsonb); -- 2.21.0 --u66dlpg6gaevsofr-- ^ permalink raw reply [nested|flat] 9+ messages in thread
* [PATCH v35 5/5] Filling gaps in jsonb arrays @ 2020-08-04 15:41 Dmitrii Dolgov <[email protected]> 0 siblings, 0 replies; 9+ messages in thread From: Dmitrii Dolgov @ 2020-08-04 15:41 UTC (permalink / raw) Introduces two new modes for jsonb assignment: * Appending array elements on the specified position, gaps filled with nulls (similar to JavaScript behavior). * Assigning keeps array positions consistent by prevent prepending of elements. Originally proposed by Nikita Glukhov based on polymorphic subscripting patch, but transformed into an independent change. --- src/backend/utils/adt/jsonfuncs.c | 72 +++++++++++++++++++++++++---- src/test/regress/expected/jsonb.out | 35 ++++++++++++++ src/test/regress/sql/jsonb.sql | 20 ++++++++ 3 files changed, 119 insertions(+), 8 deletions(-) diff --git a/src/backend/utils/adt/jsonfuncs.c b/src/backend/utils/adt/jsonfuncs.c index 37508061a1..5fe234b88a 100644 --- a/src/backend/utils/adt/jsonfuncs.c +++ b/src/backend/utils/adt/jsonfuncs.c @@ -47,6 +47,8 @@ #define JB_PATH_INSERT_AFTER 0x0010 #define JB_PATH_CREATE_OR_INSERT \ (JB_PATH_INSERT_BEFORE | JB_PATH_INSERT_AFTER | JB_PATH_CREATE) +#define JB_PATH_FILL_GAPS 0x0020 +#define JB_PATH_CONSISTENT_POSITION 0x0040 /* state for json_object_keys */ typedef struct OkeysState @@ -1492,10 +1494,8 @@ get_jsonb_path_all(FunctionCallInfo fcinfo, bool as_text) static Datum jsonb_get_element(Jsonb *jb, Datum *path, int npath, bool *isnull, bool as_text) { - Jsonb *res; JsonbContainer *container = &jb->root; JsonbValue *jbvp = NULL; - JsonbValue tv; int i; bool have_object = false, have_array = false; @@ -1656,14 +1656,26 @@ jsonb_set_element(Datum jsonbdatum, Datum *path, int path_len, it = JsonbIteratorInit(&jb->root); - res = setPath(&it, path, path_nulls, path_len, &state, 0, - newval, JB_PATH_CREATE); + res = setPath(&it, path, path_nulls, path_len, &state, 0, newval, + JB_PATH_CREATE | JB_PATH_FILL_GAPS | + JB_PATH_CONSISTENT_POSITION); pfree(path_nulls); PG_RETURN_JSONB_P(JsonbValueToJsonb(res)); } +static void +push_null_elements(JsonbParseState **ps, int num) +{ + JsonbValue null; + + null.type = jbvNull; + + while (num-- > 0) + pushJsonbValue(ps, WJB_ELEM, &null); +} + /* * Return the text representation of the given JsonbValue. */ @@ -4809,6 +4821,19 @@ IteratorConcat(JsonbIterator **it1, JsonbIterator **it2, * 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 JB_PATH_FILL_GAPS bit is set, this will change an assignment logic in + * case if target is an array. The assignment index will not be restricted by + * number of elements in the array, and if there are any empty slots between + * last element of the array and a new one they will be filled with nulls. If + * the index is negative, it still will be considered an an index from the end + * of the array. + * + * JB_PATH_CONSISTENT_POSITION for an array indicates that the called wants to + * keep values with fixed indices. Indices for existing elements could be + * changed (shifted forward) in case if the array is prepended with a new value + * and a negative index out of the range, so this behavior will be prevented + * and return an error. + * * All path elements before the last must already exist * whatever bits in op_type are set, or nothing is done. */ @@ -5005,25 +5030,48 @@ setPathArray(JsonbIterator **it, Datum *path_elems, bool *path_nulls, if (idx < 0) { if (-idx > nelems) - idx = INT_MIN; + { + /* + * If asked to keep elements position consistent, it's not allowed + * to prepend the array. + */ + if (op_type & JB_PATH_CONSISTENT_POSITION) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("path element at position %d is out of range: %d", + level + 1, idx))); + else + idx = INT_MIN; + } else idx = nelems + idx; } - if (idx > 0 && idx > nelems) - idx = nelems; + /* + * Filling the gaps means there are no limits on the positive index are + * imposed, we can set any element. Otherwise limit the index by nelems. + */ + if (!(op_type & JB_PATH_FILL_GAPS)) + { + if (idx > 0 && idx > nelems) + idx = nelems; + } /* * if we're creating, and idx == INT_MIN, we prepend the new value to the * array also if the array is empty - in which case we don't really care * what the idx value is */ - if ((idx == INT_MIN || nelems == 0) && (level == path_len - 1) && (op_type & JB_PATH_CREATE_OR_INSERT)) { Assert(newval != NULL); + + if (op_type & JB_PATH_FILL_GAPS && nelems == 0 && idx > 0) + push_null_elements(st, idx); + (void) pushJsonbValue(st, WJB_ELEM, newval); + done = true; } @@ -5084,10 +5132,18 @@ setPathArray(JsonbIterator **it, Datum *path_elems, bool *path_nulls, if ((op_type & JB_PATH_CREATE_OR_INSERT) && !done && level == path_len - 1 && i == nelems - 1) { + /* + * If asked to fill the gaps, idx could be bigger than nelems, + * so prepend the new element with nulls if that's the case. + */ + if (op_type & JB_PATH_FILL_GAPS && idx > nelems) + push_null_elements(st, idx - nelems); + (void) pushJsonbValue(st, WJB_ELEM, newval); } } } + } /* diff --git a/src/test/regress/expected/jsonb.out b/src/test/regress/expected/jsonb.out index 04a146a7d0..b294a56461 100644 --- a/src/test/regress/expected/jsonb.out +++ b/src/test/regress/expected/jsonb.out @@ -4928,6 +4928,41 @@ select * from test_jsonb_subscript; 2 | {"a": [1, 2, 3], "key": "value", "another_key": null} (2 rows) +-- Fill the gaps logic +delete from test_jsonb_subscript; +insert into test_jsonb_subscript values (1, '[0]'); +update test_jsonb_subscript set test_json[5] = 1; +select * from test_jsonb_subscript; + id | test_json +----+-------------------------------- + 1 | [0, null, null, null, null, 1] +(1 row) + +update test_jsonb_subscript set test_json[-4] = 1; +select * from test_jsonb_subscript; + id | test_json +----+----------------------------- + 1 | [0, null, 1, null, null, 1] +(1 row) + +update test_jsonb_subscript set test_json[-8] = 1; +ERROR: path element at position 1 is out of range: -8 +select * from test_jsonb_subscript; + id | test_json +----+----------------------------- + 1 | [0, null, 1, null, null, 1] +(1 row) + +-- keep consistent values position +delete from test_jsonb_subscript; +insert into test_jsonb_subscript values (1, '[]'); +update test_jsonb_subscript set test_json[5] = 1; +select * from test_jsonb_subscript; + id | test_json +----+----------------------------------- + 1 | [null, null, null, null, null, 1] +(1 row) + -- jsonb to tsvector select to_tsvector('{"a": "aaa bbb ddd ccc", "b": ["eee fff ggg"], "c": {"d": "hhh iii"}}'::jsonb); to_tsvector diff --git a/src/test/regress/sql/jsonb.sql b/src/test/regress/sql/jsonb.sql index 12541e7e50..468a9138dc 100644 --- a/src/test/regress/sql/jsonb.sql +++ b/src/test/regress/sql/jsonb.sql @@ -1269,6 +1269,26 @@ update test_jsonb_subscript set test_json[NULL] = 1; update test_jsonb_subscript set test_json['another_key'] = NULL; select * from test_jsonb_subscript; +-- Fill the gaps logic +delete from test_jsonb_subscript; +insert into test_jsonb_subscript values (1, '[0]'); + +update test_jsonb_subscript set test_json[5] = 1; +select * from test_jsonb_subscript; + +update test_jsonb_subscript set test_json[-4] = 1; +select * from test_jsonb_subscript; + +update test_jsonb_subscript set test_json[-8] = 1; +select * from test_jsonb_subscript; + +-- keep consistent values position +delete from test_jsonb_subscript; +insert into test_jsonb_subscript values (1, '[]'); + +update test_jsonb_subscript set test_json[5] = 1; +select * from test_jsonb_subscript; + -- jsonb to tsvector select to_tsvector('{"a": "aaa bbb ddd ccc", "b": ["eee fff ggg"], "c": {"d": "hhh iii"}}'::jsonb); -- 2.21.0 --evj76j7ekwacmcsz-- ^ permalink raw reply [nested|flat] 9+ messages in thread
* [PATCH v34 5/5] Filling gaps in jsonb arrays @ 2020-08-04 15:41 Dmitrii Dolgov <[email protected]> 0 siblings, 0 replies; 9+ messages in thread From: Dmitrii Dolgov @ 2020-08-04 15:41 UTC (permalink / raw) Introduces two new modes for jsonb assignment: * Appending array elements on the specified position, gaps filled with nulls (similar to JavaScript behavior). * Assigning keeps array positions consistent by prevent prepending of elements. Originally proposed by Nikita Glukhov based on polymorphic subscripting patch, but transformed into an independent change. --- src/backend/utils/adt/jsonfuncs.c | 72 +++++++++++++++++++++++++---- src/test/regress/expected/jsonb.out | 35 ++++++++++++++ src/test/regress/sql/jsonb.sql | 20 ++++++++ 3 files changed, 119 insertions(+), 8 deletions(-) diff --git a/src/backend/utils/adt/jsonfuncs.c b/src/backend/utils/adt/jsonfuncs.c index 37508061a1..5fe234b88a 100644 --- a/src/backend/utils/adt/jsonfuncs.c +++ b/src/backend/utils/adt/jsonfuncs.c @@ -47,6 +47,8 @@ #define JB_PATH_INSERT_AFTER 0x0010 #define JB_PATH_CREATE_OR_INSERT \ (JB_PATH_INSERT_BEFORE | JB_PATH_INSERT_AFTER | JB_PATH_CREATE) +#define JB_PATH_FILL_GAPS 0x0020 +#define JB_PATH_CONSISTENT_POSITION 0x0040 /* state for json_object_keys */ typedef struct OkeysState @@ -1492,10 +1494,8 @@ get_jsonb_path_all(FunctionCallInfo fcinfo, bool as_text) static Datum jsonb_get_element(Jsonb *jb, Datum *path, int npath, bool *isnull, bool as_text) { - Jsonb *res; JsonbContainer *container = &jb->root; JsonbValue *jbvp = NULL; - JsonbValue tv; int i; bool have_object = false, have_array = false; @@ -1656,14 +1656,26 @@ jsonb_set_element(Datum jsonbdatum, Datum *path, int path_len, it = JsonbIteratorInit(&jb->root); - res = setPath(&it, path, path_nulls, path_len, &state, 0, - newval, JB_PATH_CREATE); + res = setPath(&it, path, path_nulls, path_len, &state, 0, newval, + JB_PATH_CREATE | JB_PATH_FILL_GAPS | + JB_PATH_CONSISTENT_POSITION); pfree(path_nulls); PG_RETURN_JSONB_P(JsonbValueToJsonb(res)); } +static void +push_null_elements(JsonbParseState **ps, int num) +{ + JsonbValue null; + + null.type = jbvNull; + + while (num-- > 0) + pushJsonbValue(ps, WJB_ELEM, &null); +} + /* * Return the text representation of the given JsonbValue. */ @@ -4809,6 +4821,19 @@ IteratorConcat(JsonbIterator **it1, JsonbIterator **it2, * 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 JB_PATH_FILL_GAPS bit is set, this will change an assignment logic in + * case if target is an array. The assignment index will not be restricted by + * number of elements in the array, and if there are any empty slots between + * last element of the array and a new one they will be filled with nulls. If + * the index is negative, it still will be considered an an index from the end + * of the array. + * + * JB_PATH_CONSISTENT_POSITION for an array indicates that the called wants to + * keep values with fixed indices. Indices for existing elements could be + * changed (shifted forward) in case if the array is prepended with a new value + * and a negative index out of the range, so this behavior will be prevented + * and return an error. + * * All path elements before the last must already exist * whatever bits in op_type are set, or nothing is done. */ @@ -5005,25 +5030,48 @@ setPathArray(JsonbIterator **it, Datum *path_elems, bool *path_nulls, if (idx < 0) { if (-idx > nelems) - idx = INT_MIN; + { + /* + * If asked to keep elements position consistent, it's not allowed + * to prepend the array. + */ + if (op_type & JB_PATH_CONSISTENT_POSITION) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("path element at position %d is out of range: %d", + level + 1, idx))); + else + idx = INT_MIN; + } else idx = nelems + idx; } - if (idx > 0 && idx > nelems) - idx = nelems; + /* + * Filling the gaps means there are no limits on the positive index are + * imposed, we can set any element. Otherwise limit the index by nelems. + */ + if (!(op_type & JB_PATH_FILL_GAPS)) + { + if (idx > 0 && idx > nelems) + idx = nelems; + } /* * if we're creating, and idx == INT_MIN, we prepend the new value to the * array also if the array is empty - in which case we don't really care * what the idx value is */ - if ((idx == INT_MIN || nelems == 0) && (level == path_len - 1) && (op_type & JB_PATH_CREATE_OR_INSERT)) { Assert(newval != NULL); + + if (op_type & JB_PATH_FILL_GAPS && nelems == 0 && idx > 0) + push_null_elements(st, idx); + (void) pushJsonbValue(st, WJB_ELEM, newval); + done = true; } @@ -5084,10 +5132,18 @@ setPathArray(JsonbIterator **it, Datum *path_elems, bool *path_nulls, if ((op_type & JB_PATH_CREATE_OR_INSERT) && !done && level == path_len - 1 && i == nelems - 1) { + /* + * If asked to fill the gaps, idx could be bigger than nelems, + * so prepend the new element with nulls if that's the case. + */ + if (op_type & JB_PATH_FILL_GAPS && idx > nelems) + push_null_elements(st, idx - nelems); + (void) pushJsonbValue(st, WJB_ELEM, newval); } } } + } /* diff --git a/src/test/regress/expected/jsonb.out b/src/test/regress/expected/jsonb.out index 04a146a7d0..b294a56461 100644 --- a/src/test/regress/expected/jsonb.out +++ b/src/test/regress/expected/jsonb.out @@ -4928,6 +4928,41 @@ select * from test_jsonb_subscript; 2 | {"a": [1, 2, 3], "key": "value", "another_key": null} (2 rows) +-- Fill the gaps logic +delete from test_jsonb_subscript; +insert into test_jsonb_subscript values (1, '[0]'); +update test_jsonb_subscript set test_json[5] = 1; +select * from test_jsonb_subscript; + id | test_json +----+-------------------------------- + 1 | [0, null, null, null, null, 1] +(1 row) + +update test_jsonb_subscript set test_json[-4] = 1; +select * from test_jsonb_subscript; + id | test_json +----+----------------------------- + 1 | [0, null, 1, null, null, 1] +(1 row) + +update test_jsonb_subscript set test_json[-8] = 1; +ERROR: path element at position 1 is out of range: -8 +select * from test_jsonb_subscript; + id | test_json +----+----------------------------- + 1 | [0, null, 1, null, null, 1] +(1 row) + +-- keep consistent values position +delete from test_jsonb_subscript; +insert into test_jsonb_subscript values (1, '[]'); +update test_jsonb_subscript set test_json[5] = 1; +select * from test_jsonb_subscript; + id | test_json +----+----------------------------------- + 1 | [null, null, null, null, null, 1] +(1 row) + -- jsonb to tsvector select to_tsvector('{"a": "aaa bbb ddd ccc", "b": ["eee fff ggg"], "c": {"d": "hhh iii"}}'::jsonb); to_tsvector diff --git a/src/test/regress/sql/jsonb.sql b/src/test/regress/sql/jsonb.sql index 12541e7e50..468a9138dc 100644 --- a/src/test/regress/sql/jsonb.sql +++ b/src/test/regress/sql/jsonb.sql @@ -1269,6 +1269,26 @@ update test_jsonb_subscript set test_json[NULL] = 1; update test_jsonb_subscript set test_json['another_key'] = NULL; select * from test_jsonb_subscript; +-- Fill the gaps logic +delete from test_jsonb_subscript; +insert into test_jsonb_subscript values (1, '[0]'); + +update test_jsonb_subscript set test_json[5] = 1; +select * from test_jsonb_subscript; + +update test_jsonb_subscript set test_json[-4] = 1; +select * from test_jsonb_subscript; + +update test_jsonb_subscript set test_json[-8] = 1; +select * from test_jsonb_subscript; + +-- keep consistent values position +delete from test_jsonb_subscript; +insert into test_jsonb_subscript values (1, '[]'); + +update test_jsonb_subscript set test_json[5] = 1; +select * from test_jsonb_subscript; + -- jsonb to tsvector select to_tsvector('{"a": "aaa bbb ddd ccc", "b": ["eee fff ggg"], "c": {"d": "hhh iii"}}'::jsonb); -- 2.21.0 --j77abfbqdsj2lq6d-- ^ permalink raw reply [nested|flat] 9+ messages in thread
* [PATCH v44 02/10] Do not dereference varattrib_4b in VARSIZE_4B. @ 2026-03-11 13:53 Antonin Houska <[email protected]> 0 siblings, 0 replies; 9+ messages in thread From: Antonin Houska @ 2026-03-11 13:53 UTC (permalink / raw) Since VARSIZE_ANY() may call VARSIZE_4B(), it's possible that the compiler (when invoked with -Warray-bounds) complains if the argument of VARSIZE_ANY() is actually smaller than what VARSIZE_4B() expects. This patch adjusts the VARSIZE_4B() macro so that it does not have to dereference the varattrib_4b structure. We assume that varlena value always starts with the length word. The problem does not exist in the tree at the moment since the current users of VARSIZE_ANY() pass a pointer to a dynamically allocated memory, so the compiler has no idea about the memory available. However, in an upcoming patch, it makes sense to pass a pointer to a local variable of "varlena" type. In such a case, the compiler warning might appear because sizeof(varlena) is lower than sizeof(varattrib_4b). --- src/include/varatt.h | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) diff --git a/src/include/varatt.h b/src/include/varatt.h index 000bdf33b92..31063e5d4f1 100644 --- a/src/include/varatt.h +++ b/src/include/varatt.h @@ -207,7 +207,7 @@ typedef struct /* VARSIZE_4B() should only be used on known-aligned data */ #define VARSIZE_4B(PTR) \ - (((const varattrib_4b *) (PTR))->va_4byte.va_header & 0x3FFFFFFF) + (*((const uint32 *) (PTR)) & 0x3FFFFFFF) #define VARSIZE_1B(PTR) \ (((const varattrib_1b *) (PTR))->va_header & 0x7F) #define VARTAG_1B_E(PTR) \ @@ -240,7 +240,7 @@ typedef struct /* VARSIZE_4B() should only be used on known-aligned data */ #define VARSIZE_4B(PTR) \ - ((((const varattrib_4b *) (PTR))->va_4byte.va_header >> 2) & 0x3FFFFFFF) + ((*((const uint32 *) (PTR)) >> 2) & 0x3FFFFFFF) #define VARSIZE_1B(PTR) \ ((((const varattrib_1b *) (PTR))->va_header >> 1) & 0x7F) #define VARTAG_1B_E(PTR) \ -- 2.47.3 --gwom7bl7ogtszo4k Content-Type: text/x-diff; charset=utf-8 Content-Disposition: attachment; filename="v44-0003-Add-CONCURRENTLY-option-to-REPACK-command.patch" ^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2026-03-11 13:53 UTC | newest] Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2019-12-19 13:13 [PATCH v29 6/6] Filling gaps in jsonb arrays Dmitrii Dolgov <[email protected]> 2019-12-19 13:13 [PATCH v30 6/6] Filling gaps in jsonb arrays Dmitrii Dolgov <[email protected]> 2019-12-19 13:13 [PATCH v31 6/6] Filling gaps in jsonb arrays Dmitrii Dolgov <[email protected]> 2019-12-19 13:13 [PATCH v32 6/6] Filling gaps in jsonb arrays Dmitrii Dolgov <[email protected]> 2019-12-19 13:13 [PATCH v32 6/6] Filling gaps in jsonb arrays Dmitrii Dolgov <[email protected]> 2020-08-04 15:41 [PATCH v33 5/5] Filling gaps in jsonb arrays Dmitrii Dolgov <[email protected]> 2020-08-04 15:41 [PATCH v34 5/5] Filling gaps in jsonb arrays Dmitrii Dolgov <[email protected]> 2020-08-04 15:41 [PATCH v35 5/5] Filling gaps in jsonb arrays Dmitrii Dolgov <[email protected]> 2026-03-11 13:53 [PATCH v44 02/10] Do not dereference varattrib_4b in VARSIZE_4B. Antonin Houska <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox