Feedback-ID: i76984098:Fastmail
Message-ID: <0d47dfca-297f-4add-a395-bc5997ce47a9@aklaver.com>
Date: Wed, 8 Oct 2025 07:58:43 -0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: JSONB subscripting initializes numeric keys as arrays instead of
 objects
To: Krrish Malhotra <malekm09122003@gmail.com>,
 pgsql-general@lists.postgresql.org
References: <CA+8JitKMRuPU4iFwSGPG9w4R1LVqk1FjDqkS4nW-FwHXRv+V1Q@mail.gmail.com>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <CA+8JitKMRuPU4iFwSGPG9w4R1LVqk1FjDqkS4nW-FwHXRv+V1Q@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/0d47dfca-297f-4add-a395-bc5997ce47a9%40aklaver.com>
Precedence: bulk

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