public inbox for [email protected]  

help / color / mirror / Atom feed

From: Adrian Klaver <[email protected]>
To: Krrish Malhotra <[email protected]>
To: [email protected]
Subject: Re: JSONB subscripting initializes numeric keys as arrays instead of objects
Date: Wed, 8 Oct 2025 07:58:43 -0700
Message-ID: <[email protected]> (raw)
In-Reply-To: <CA+8JitKMRuPU4iFwSGPG9w4R1LVqk1FjDqkS4nW-FwHXRv+V1Q@mail.gmail.com>
References: <CA+8JitKMRuPU4iFwSGPG9w4R1LVqk1FjDqkS4nW-FwHXRv+V1Q@mail.gmail.com>

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
[email protected]

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: JSONB subscripting initializes numeric keys as arrays instead of objects
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox