public inbox for [email protected]  

help / color / mirror / Atom feed

From: Krrish Malhotra <[email protected]>
To: [email protected]
Subject: JSONB subscripting initializes numeric keys as arrays instead of objects
Date: Wed, 8 Oct 2025 14:41:33 +0530
Message-ID: <CA+8JitKMRuPU4iFwSGPG9w4R1LVqk1FjDqkS4nW-FwHXRv+V1Q@mail.gmail.com> (raw)

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.1...;).
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?

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]
  Subject: Re: JSONB subscripting initializes numeric keys as arrays instead of objects
  In-Reply-To: <CA+8JitKMRuPU4iFwSGPG9w4R1LVqk1FjDqkS4nW-FwHXRv+V1Q@mail.gmail.com>

* 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