🌐 AI搜索 & 代理 主页
Skip to content

Commit 81fcc72

Browse files
akorotkovakorotkov
committed
Filling array gaps during jsonb subscripting
This commit introduces two new flags for jsonb assignment: * JB_PATH_FILL_GAPS: Appending array elements on the specified position, gaps are filled with nulls (similar to the JavaScript behavior). This mode also instructs to create the whole path in a jsonb object if some part of the path (more than just the last element) is not present. * JB_PATH_CONSISTENT_POSITION: Assigning keeps array positions consistent by preventing prepending of elements. Both flags are used only in jsonb subscripting assignment. Initially proposed by Nikita Glukhov based on polymorphic subscripting patch, but transformed into an independent change. Discussion: https://postgr.es/m/CA%2Bq6zcV8qvGcDXurwwgUbwACV86Th7G80pnubg42e-p9gsSf%3Dg%40mail.gmail.com Discussion: https://postgr.es/m/CA%2Bq6zcX3mdxGCgdThzuySwH-ApyHHM-G4oB1R0fn0j2hZqqkLQ%40mail.gmail.com Discussion: https://postgr.es/m/CA%2Bq6zcVDuGBv%3DM0FqBYX8DPebS3F_0KQ6OVFobGJPM507_SZ_w%40mail.gmail.com Discussion: https://postgr.es/m/CA%2Bq6zcVovR%2BXY4mfk-7oNk-rF91gH0PebnNfuUjuuDsyHjOcVA%40mail.gmail.com Author: Dmitry Dolgov Reviewed-by: Tom Lane, Arthur Zakirov, Pavel Stehule, Dian M Fay Reviewed-by: Andrew Dunstan, Chapman Flack, Merlin Moncure, Peter Geoghegan Reviewed-by: Alvaro Herrera, Jim Nasby, Josh Berkus, Victor Wagner Reviewed-by: Aleksander Alekseev, Robert Haas, Oleg Bartunov
1 parent 676887a commit 81fcc72

File tree

4 files changed

+452
-15
lines changed

4 files changed

+452
-15
lines changed

‎doc/src/sgml/json.sgml‎

Lines changed: 24 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -648,6 +648,30 @@ UPDATE table_name SET jsonb_field['a'] = '1';
648648

649649
-- Where jsonb_field was NULL, it is now [1]
650650
UPDATE table_name SET jsonb_field[0] = '1';
651+
</programlisting>
652+
653+
If an index is specified for an array containing too few elements,
654+
<literal>NULL</literal> elements will be appended until the index is reachable
655+
and the value can be set.
656+
657+
<programlisting>
658+
-- Where jsonb_field was [], it is now [null, null, 2];
659+
-- where jsonb_field was [0], it is now [0, null, 2]
660+
UPDATE table_name SET jsonb_field[2] = '2';
661+
</programlisting>
662+
663+
A <type>jsonb</type> value will accept assignments to nonexistent subscript
664+
paths as long as the last existing path key is an object or an array. Since
665+
the final subscript is not traversed, it may be an object key. Nested arrays
666+
will be created and <literal>NULL</literal>-padded according to the path until
667+
the value can be placed appropriately.
668+
669+
<programlisting>
670+
-- Where jsonb_field was {}, it is now {'a': [{'b': 1}]}
671+
UPDATE table_name SET jsonb_field['a'][0]['b'] = '1';
672+
673+
-- Where jsonb_field was [], it is now [{'a': 1}]
674+
UPDATE table_name SET jsonb_field[0]['a'] = '1';
651675
</programlisting>
652676

653677
</para>

‎src/backend/utils/adt/jsonfuncs.c‎

Lines changed: 212 additions & 15 deletions
Original file line numberDiff line numberDiff line change
@@ -44,6 +44,8 @@
4444
#define JB_PATH_INSERT_AFTER 0x0010
4545
#define JB_PATH_CREATE_OR_INSERT \
4646
(JB_PATH_INSERT_BEFORE | JB_PATH_INSERT_AFTER | JB_PATH_CREATE)
47+
#define JB_PATH_FILL_GAPS 0x0020
48+
#define JB_PATH_CONSISTENT_POSITION 0x0040
4749

4850
/* state for json_object_keys */
4951
typedef struct OkeysState
@@ -1634,14 +1636,117 @@ jsonb_set_element(Jsonb *jb, Datum *path, int path_len,
16341636

16351637
it = JsonbIteratorInit(&jb->root);
16361638

1637-
res = setPath(&it, path, path_nulls, path_len, &state, 0,
1638-
newval, JB_PATH_CREATE);
1639+
res = setPath(&it, path, path_nulls, path_len, &state, 0, newval,
1640+
JB_PATH_CREATE | JB_PATH_FILL_GAPS |
1641+
JB_PATH_CONSISTENT_POSITION);
16391642

16401643
pfree(path_nulls);
16411644

16421645
PG_RETURN_JSONB_P(JsonbValueToJsonb(res));
16431646
}
16441647

1648+
static void
1649+
push_null_elements(JsonbParseState **ps, int num)
1650+
{
1651+
JsonbValue null;
1652+
1653+
null.type = jbvNull;
1654+
1655+
while (num-- > 0)
1656+
pushJsonbValue(ps, WJB_ELEM, &null);
1657+
}
1658+
1659+
/*
1660+
* Prepare a new structure containing nested empty objects and arrays
1661+
* corresponding to the specified path, and assign a new value at the end of
1662+
* this path. E.g. the path [a][0][b] with the new value 1 will produce the
1663+
* structure {a: [{b: 1}]}.
1664+
*
1665+
* Called is responsible to make sure such path does not exist yet.
1666+
*/
1667+
static void
1668+
push_path(JsonbParseState **st, int level, Datum *path_elems,
1669+
bool *path_nulls, int path_len, JsonbValue *newval)
1670+
{
1671+
/*
1672+
* tpath contains expected type of an empty jsonb created at each level
1673+
* higher or equal than the current one, either jbvObject or jbvArray.
1674+
* Since it contains only information about path slice from level to the
1675+
* end, the access index must be normalized by level.
1676+
*/
1677+
enum jbvType *tpath = palloc0((path_len - level) * sizeof(enum jbvType));
1678+
long lindex;
1679+
JsonbValue newkey;
1680+
1681+
/*
1682+
* Create first part of the chain with beginning tokens. For the current
1683+
* level WJB_BEGIN_OBJECT/WJB_BEGIN_ARRAY was already created, so start
1684+
* with the next one.
1685+
*/
1686+
for (int i = level + 1; i < path_len; i++)
1687+
{
1688+
char *c,
1689+
*badp;
1690+
1691+
if (path_nulls[i])
1692+
break;
1693+
1694+
/*
1695+
* Try to convert to an integer to find out the expected type, object
1696+
* or array.
1697+
*/
1698+
c = TextDatumGetCString(path_elems[i]);
1699+
errno = 0;
1700+
lindex = strtol(c, &badp, 10);
1701+
if (errno != 0 || badp == c || *badp != '\0' || lindex > INT_MAX ||
1702+
lindex < INT_MIN)
1703+
{
1704+
/* text, an object is expected */
1705+
newkey.type = jbvString;
1706+
newkey.val.string.len = VARSIZE_ANY_EXHDR(path_elems[i]);
1707+
newkey.val.string.val = VARDATA_ANY(path_elems[i]);
1708+
1709+
(void) pushJsonbValue(st, WJB_BEGIN_OBJECT, NULL);
1710+
(void) pushJsonbValue(st, WJB_KEY, &newkey);
1711+
1712+
tpath[i - level] = jbvObject;
1713+
}
1714+
else
1715+
{
1716+
/* integer, an array is expected */
1717+
(void) pushJsonbValue(st, WJB_BEGIN_ARRAY, NULL);
1718+
1719+
push_null_elements(st, lindex);
1720+
1721+
tpath[i - level] = jbvArray;
1722+
}
1723+
1724+
}
1725+
1726+
/* Insert an actual value for either an object or array */
1727+
if (tpath[(path_len - level) - 1] == jbvArray)
1728+
{
1729+
(void) pushJsonbValue(st, WJB_ELEM, newval);
1730+
}
1731+
else
1732+
(void) pushJsonbValue(st, WJB_VALUE, newval);
1733+
1734+
/*
1735+
* Close everything up to the last but one level. The last one will be
1736+
* closed outside of this function.
1737+
*/
1738+
for (int i = path_len - 1; i > level; i--)
1739+
{
1740+
if (path_nulls[i])
1741+
break;
1742+
1743+
if (tpath[i - level] == jbvObject)
1744+
(void) pushJsonbValue(st, WJB_END_OBJECT, NULL);
1745+
else
1746+
(void) pushJsonbValue(st, WJB_END_ARRAY, NULL);
1747+
}
1748+
}
1749+
16451750
/*
16461751
* Return the text representation of the given JsonbValue.
16471752
*/
@@ -4786,6 +4891,21 @@ IteratorConcat(JsonbIterator **it1, JsonbIterator **it2,
47864891
* Bits JB_PATH_INSERT_BEFORE and JB_PATH_INSERT_AFTER in op_type
47874892
* behave as JB_PATH_CREATE if new value is inserted in JsonbObject.
47884893
*
4894+
* If JB_PATH_FILL_GAPS bit is set, this will change an assignment logic in
4895+
* case if target is an array. The assignment index will not be restricted by
4896+
* number of elements in the array, and if there are any empty slots between
4897+
* last element of the array and a new one they will be filled with nulls. If
4898+
* the index is negative, it still will be considered an an index from the end
4899+
* of the array. Of a part of the path is not present and this part is more
4900+
* than just one last element, this flag will instruct to create the whole
4901+
* chain of corresponding objects and insert the value.
4902+
*
4903+
* JB_PATH_CONSISTENT_POSITION for an array indicates that the called wants to
4904+
* keep values with fixed indices. Indices for existing elements could be
4905+
* changed (shifted forward) in case if the array is prepended with a new value
4906+
* and a negative index out of the range, so this behavior will be prevented
4907+
* and return an error.
4908+
*
47894909
* All path elements before the last must already exist
47904910
* whatever bits in op_type are set, or nothing is done.
47914911
*/
@@ -4880,6 +5000,8 @@ setPathObject(JsonbIterator **it, Datum *path_elems, bool *path_nulls,
48805000
memcmp(k.val.string.val, VARDATA_ANY(path_elems[level]),
48815001
k.val.string.len) == 0)
48825002
{
5003+
done = true;
5004+
48835005
if (level == path_len - 1)
48845006
{
48855007
/*
@@ -4899,7 +5021,6 @@ setPathObject(JsonbIterator **it, Datum *path_elems, bool *path_nulls,
48995021
(void) pushJsonbValue(st, WJB_KEY, &k);
49005022
(void) pushJsonbValue(st, WJB_VALUE, newval);
49015023
}
4902-
done = true;
49035024
}
49045025
else
49055026
{
@@ -4944,6 +5065,31 @@ setPathObject(JsonbIterator **it, Datum *path_elems, bool *path_nulls,
49445065
}
49455066
}
49465067
}
5068+
5069+
/*--
5070+
* If we got here there are only few possibilities:
5071+
* - no target path was found, and an open object with some keys/values was
5072+
* pushed into the state
5073+
* - an object is empty, only WJB_BEGIN_OBJECT is pushed
5074+
*
5075+
* In both cases if instructed to create the path when not present,
5076+
* generate the whole chain of empty objects and insert the new value
5077+
* there.
5078+
*/
5079+
if (!done && (op_type & JB_PATH_FILL_GAPS) && (level < path_len - 1))
5080+
{
5081+
JsonbValue newkey;
5082+
5083+
newkey.type = jbvString;
5084+
newkey.val.string.len = VARSIZE_ANY_EXHDR(path_elems[level]);
5085+
newkey.val.string.val = VARDATA_ANY(path_elems[level]);
5086+
5087+
(void) pushJsonbValue(st, WJB_KEY, &newkey);
5088+
(void) push_path(st, level, path_elems, path_nulls,
5089+
path_len, newval);
5090+
5091+
/* Result is closed with WJB_END_OBJECT outside of this function */
5092+
}
49475093
}
49485094

49495095
/*
@@ -4982,25 +5128,48 @@ setPathArray(JsonbIterator **it, Datum *path_elems, bool *path_nulls,
49825128
if (idx < 0)
49835129
{
49845130
if (-idx > nelems)
4985-
idx = INT_MIN;
5131+
{
5132+
/*
5133+
* If asked to keep elements position consistent, it's not allowed
5134+
* to prepend the array.
5135+
*/
5136+
if (op_type & JB_PATH_CONSISTENT_POSITION)
5137+
ereport(ERROR,
5138+
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
5139+
errmsg("path element at position %d is out of range: %d",
5140+
level + 1, idx)));
5141+
else
5142+
idx = INT_MIN;
5143+
}
49865144
else
49875145
idx = nelems + idx;
49885146
}
49895147

4990-
if (idx > 0 && idx > nelems)
4991-
idx = nelems;
5148+
/*
5149+
* Filling the gaps means there are no limits on the positive index are
5150+
* imposed, we can set any element. Otherwise limit the index by nelems.
5151+
*/
5152+
if (!(op_type & JB_PATH_FILL_GAPS))
5153+
{
5154+
if (idx > 0 && idx > nelems)
5155+
idx = nelems;
5156+
}
49925157

49935158
/*
49945159
* if we're creating, and idx == INT_MIN, we prepend the new value to the
49955160
* array also if the array is empty - in which case we don't really care
49965161
* what the idx value is
49975162
*/
4998-
49995163
if ((idx == INT_MIN || nelems == 0) && (level == path_len - 1) &&
50005164
(op_type & JB_PATH_CREATE_OR_INSERT))
50015165
{
50025166
Assert(newval != NULL);
5167+
5168+
if (op_type & JB_PATH_FILL_GAPS && nelems == 0 && idx > 0)
5169+
push_null_elements(st, idx);
5170+
50035171
(void) pushJsonbValue(st, WJB_ELEM, newval);
5172+
50045173
done = true;
50055174
}
50065175

@@ -5011,6 +5180,8 @@ setPathArray(JsonbIterator **it, Datum *path_elems, bool *path_nulls,
50115180

50125181
if (i == idx && level < path_len)
50135182
{
5183+
done = true;
5184+
50145185
if (level == path_len - 1)
50155186
{
50165187
r = JsonbIteratorNext(it, &v, true); /* skip */
@@ -5028,8 +5199,6 @@ setPathArray(JsonbIterator **it, Datum *path_elems, bool *path_nulls,
50285199

50295200
if (op_type & (JB_PATH_INSERT_AFTER | JB_PATH_REPLACE))
50305201
(void) pushJsonbValue(st, WJB_ELEM, newval);
5031-
5032-
done = true;
50335202
}
50345203
else
50355204
(void) setPath(it, path_elems, path_nulls, path_len,
@@ -5057,14 +5226,42 @@ setPathArray(JsonbIterator **it, Datum *path_elems, bool *path_nulls,
50575226
(void) pushJsonbValue(st, r, r < WJB_BEGIN_ARRAY ? &v : NULL);
50585227
}
50595228
}
5060-
5061-
if ((op_type & JB_PATH_CREATE_OR_INSERT) && !done &&
5062-
level == path_len - 1 && i == nelems - 1)
5063-
{
5064-
(void) pushJsonbValue(st, WJB_ELEM, newval);
5065-
}
50665229
}
50675230
}
5231+
5232+
if ((op_type & JB_PATH_CREATE_OR_INSERT) && !done && level == path_len - 1)
5233+
{
5234+
/*
5235+
* If asked to fill the gaps, idx could be bigger than nelems, so
5236+
* prepend the new element with nulls if that's the case.
5237+
*/
5238+
if (op_type & JB_PATH_FILL_GAPS && idx > nelems)
5239+
push_null_elements(st, idx - nelems);
5240+
5241+
(void) pushJsonbValue(st, WJB_ELEM, newval);
5242+
done = true;
5243+
}
5244+
5245+
/*--
5246+
* If we got here there are only few possibilities:
5247+
* - no target path was found, and an open array with some keys/values was
5248+
* pushed into the state
5249+
* - an array is empty, only WJB_BEGIN_ARRAY is pushed
5250+
*
5251+
* In both cases if instructed to create the path when not present,
5252+
* generate the whole chain of empty objects and insert the new value
5253+
* there.
5254+
*/
5255+
if (!done && (op_type & JB_PATH_FILL_GAPS) && (level < path_len - 1))
5256+
{
5257+
if (idx > 0)
5258+
push_null_elements(st, idx - nelems);
5259+
5260+
(void) push_path(st, level, path_elems, path_nulls,
5261+
path_len, newval);
5262+
5263+
/* Result is closed with WJB_END_OBJECT outside of this function */
5264+
}
50685265
}
50695266

50705267
/*

0 commit comments

Comments
 (0)