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
