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?
