JSONB subscripting initializes numeric keys as arrays instead of objects

From: Krrish Malhotra <malekm09122003(at)gmail(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: JSONB subscripting initializes numeric keys as arrays instead of objects
Date: 2025-10-08 09:11:33
Message-ID: CA+8JitKMRuPU4iFwSGPG9w4R1LVqk1FjDqkS4nW-FwHXRv+V1Q@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-general

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?

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Klaver 2025-10-08 14:58:43 Re: JSONB subscripting initializes numeric keys as arrays instead of objects
Previous Message Merlin Moncure 2025-10-07 20:51:12 Re: Are SQL-language function lock ordering deadlocks solved in PostgreSQL 18?