Обсуждение: JSONB subscripting initializes numeric keys as arrays instead of objects
I'm using PostgreSQL 16+ and working extensively with jsonb columns using JSON subscripting paths (as described here: PostgreSQL docs – jsonb subscripting). I've run into an issue when updating nested paths where intermediate keys might not exist. For example:
UPDATE test SET data['A']['B']['C'] = '{"a": "b"}' WHERE data->>'ID' = 'abcde';
If A.B doesn’t exist, PostgreSQL automatically initializes it as an empty JSON object ({}), and then correctly sets the key C. However, if the last key is numeric, for example:
UPDATE test SET data['A']['B']['3'] = '{"a": "b"}' WHERE data->>'ID' = 'abcde';
PostgreSQL initializes A.B as an empty array instead of an object, and sets the value at index 3. This behavior isn’t what I want, I’d like numeric keys to be treated as JSON object keys (e.g., {"3": {...}}) rather than as array indices. I know I can pre-initialize A.B like this:
data['A']['B'] = coalesce(data->'A'->'B', '{}')
But that causes problems when multiple JSON path updates happen in the same query, since it can overwrite or reset other keys.
Additionally, in my use case, I don’t always know in advance whether a given path exists at the time of the update, so I’d like a solution that won’t break or conflict with existing data.
Is there any way to force PostgreSQL to treat numeric subscripts as object keys instead of array indices, or otherwise control this initialization behavior?
On 10/8/25 02:11, Krrish Malhotra wrote: > I'm using PostgreSQL 16+ and working extensively with jsonb columns > using JSON subscripting paths (as described here: PostgreSQL docs – > jsonb subscripting <https://www.postgresql.org/docs/14/datatype- > json.html#:%7E:text=default%20database%20collation.-,8.14.5. > %C2%A0jsonb%20Subscripting,-The%20jsonb%20data>). I've run into an issue > when updating nested paths where intermediate keys might not exist. For > example: > > |UPDATE test SET data['A']['B']['C'] = '{"a": "b"}' WHERE data->>'ID' = > 'abcde'; | > > If A.B doesn’t exist, PostgreSQL automatically initializes it as an > empty JSON object ({}), and then correctly sets the key C. However, if > the last key is numeric, for example: > > |UPDATE test SET data['A']['B']['3'] = '{"a": "b"}' WHERE data->>'ID' = > 'abcde'; | > > PostgreSQL initializes A.B as an empty array instead of an object, and > sets the value at index 3. This behavior isn’t what I want, I’d like > numeric keys to be treated as JSON object keys (e.g., {"3": {...}}) > rather than as array indices. I know I can pre-initialize A.B like this: > > |data['A']['B'] = coalesce(data->'A'->'B', '{}') | > > But that causes problems when multiple JSON path updates happen in the > same query, since it can overwrite or reset other keys. > > Additionally, in my use case, I don’t always know in advance whether a > given path exists at the time of the update, so I’d like a solution that > won’t break or conflict with existing data. > > Is there any way to force PostgreSQL to treat numeric subscripts as > object keys instead of array indices, or otherwise control this > initialization behavior? > The docs are pretty straight forward on this: " Subscript paths must be traversable for all affected values insofar as they exist. For instance, the path val['a']['b']['c'] can be traversed all the way to c if every val, val['a'], and val['a']['b'] is an object. If any val['a'] or val['a']['b'] is not defined, it will be created as an empty object and filled as necessary. However, if any val itself or one of the intermediary values is defined as a non-object such as a string, number, or jsonb null, traversal cannot proceed so an error is raised and the transaction aborted. " You might want to look at jsonb_set from here: https://www.postgresql.org/docs/current/functions-json.html -- Adrian Klaver adrian.klaver@aklaver.com