Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1v6QUB-004Qqw-Ge for pgsql-general@arkaria.postgresql.org; Wed, 08 Oct 2025 09:29:27 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1v6QU8-008vNv-7C for pgsql-general@arkaria.postgresql.org; Wed, 08 Oct 2025 09:29:25 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1v6QD6-008opm-0Q for pgsql-general@lists.postgresql.org; Wed, 08 Oct 2025 09:11:48 +0000 Received: from mail-ej1-x630.google.com ([2a00:1450:4864:20::630]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1v6QD4-0010ch-0m for pgsql-general@lists.postgresql.org; Wed, 08 Oct 2025 09:11:48 +0000 Received: by mail-ej1-x630.google.com with SMTP id a640c23a62f3a-afcb7a16441so1160925266b.2 for ; Wed, 08 Oct 2025 02:11:46 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1759914705; x=1760519505; darn=lists.postgresql.org; h=to:subject:message-id:date:from:mime-version:from:to:cc:subject :date:message-id:reply-to; bh=JRE7qgGoh1mNg6+jPhoG9hirnRl0NfpmYSdO4ojF1M4=; b=F8//PSV8kClsctdVt4c0mqcgWbv+JAH3BDD8uuzLddvdvF4C7WcC6iBeUfDohCi7cv Rj9pBJKUmIOMnxJDyD3n9G3SeGACxJ/zYb82wbSRprRL9ZLGjgwC/PkZhN8d66mNcOuh uI6odj0N+RQ5esiJD3mtTeqBoOg1iiCUBgeDGGsLQvE3BA5It3GTleY2MUp5t6DYvs9B E2m2RcDMfQPWgm4XgCZAz8BjwDWEO5XyYm1Dr9sFf6jtO1+vFgYOvfkwGu9Z6y7kexF2 xI/UuiGgfgIYsL5F+ZUdyjdP/BrOCojrG++Gt1wAfYe3cQuGuYy6YPNV8MeIkxY4aobF 6yoQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1759914705; x=1760519505; h=to:subject:message-id:date:from:mime-version:x-gm-message-state :from:to:cc:subject:date:message-id:reply-to; bh=JRE7qgGoh1mNg6+jPhoG9hirnRl0NfpmYSdO4ojF1M4=; b=l+/cJ3DcHX+rAe8S1OuXp3QOvENb/rqS5pAt6xYYxrlUNRkUwRqRADCu1zzou5t6bf UtL5Hqe9YIJ0oBugL9uzXTQ1zmh+UNwJJN2dBbg228wpPg9lFeY9hOfXV5iGUW/iYaS9 uLlRdATzJBTBdHG7zVloFztOBiyK2R6+3GtqI88nn2bmB5S3oNUAoYqkXt9RoxZsVa8Z o7FH2hL+oRID8IsCs+8QXMd84UXOM+LKJv4M6ARMTjiXllo+VQRoJSO0SJfU9QdXAr3t 80fdFHf4FmplQsAigH794VgdTVCbtJcHjPJVkoiyuKT3MeZp1duoiR9g0UWXgamFGdNb VW/w== X-Gm-Message-State: AOJu0YxlC7CAk1nf0YHM5aR3JS+7l5u496wX/N6kzZufOAatyjqS07yP J8ansHHvvNee5I8mnIbPaGx/ue6jzYwYMidjxDRS2y+oCCIz4/0lPheew64eeocSyQF70EJPNhW hZm9GQL8yI3Lm01jxViomCWrfxEXgksJRm953 X-Gm-Gg: ASbGncvefVeYngWMDc7lFC7pf0BhH35XGnXdpWziL/lC8IbDYW74/HNcpGP7Y1lZtmR NmA4/l/G45oAh7fvUXwjnsnFc7YhQHvtHW81b4B8tccXn+5YAleY5pVahLtwLw9ny32JWT23dIL Z8visvIOud4CB681P7+Yu1VQK0WQC+3gXc91y7u2HtEfMgZM36ftUKlECYhWi3jBzmX+C1aDlk1 b6dJUARSSFoXyqvcdm+jh9+Db733lyu0w== X-Google-Smtp-Source: AGHT+IEGF3bBrauU3biT6d2O3lbFkoEua/fE7xy4xwXYRvp/D3qpb9BW/DYLEdTstJfzltAqwIOZneLmqjFjDQsNyfE= X-Received: by 2002:a17:907:7b97:b0:b46:abad:430e with SMTP id a640c23a62f3a-b50ac1c4e88mr267660466b.37.1759914704909; Wed, 08 Oct 2025 02:11:44 -0700 (PDT) MIME-Version: 1.0 From: Krrish Malhotra Date: Wed, 8 Oct 2025 14:41:33 +0530 X-Gm-Features: AS18NWDKIJcwR6PedZYMSRIFPbGXP2tSjysRa7lfuMtsNjodfTGwk5VZ3VDutF8 Message-ID: Subject: JSONB subscripting initializes numeric keys as arrays instead of objects To: pgsql-general@lists.postgresql.org Content-Type: multipart/alternative; boundary="000000000000d23b640640a21278" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000d23b640640a21278 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable I'm using PostgreSQL 16+ and working extensively with jsonb columns using JSON subscripting paths (as described here: PostgreSQL docs =E2=80=93 jsonb subscripting ). 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'] =3D '{"a": "b"}' WHERE data->>'ID' =3D = 'abcde'; If A.B doesn=E2=80=99t 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'] =3D '{"a": "b"}' WHERE data->>'ID' =3D = 'abcde'; PostgreSQL initializes A.B as an empty array instead of an object, and sets the value at index 3. This behavior isn=E2=80=99t what I want, I=E2=80=99d = 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'] =3D 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=E2=80=99t always know in advance whethe= r a given path exists at the time of the update, so I=E2=80=99d like a solution= that won=E2=80=99t 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? --000000000000d23b640640a21278 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

I'm using Pos= tgreSQL 16+ and working extensively with jsonb columns using JSON subscript= ing paths (as described here:=C2=A0PostgreSQL docs =E2=80=93 jsonb subscripting). I've run int= o an issue when updating nested paths where intermediate keys might not exi= st. For example:

=
UPDATE test SET data['B']['C'] =3D '{"a&q=
uot;: "b"}' W=
HERE data->>'ID' =3D 'abcde'<=
/span>;

If A.B doesn=E2=80=99= 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'] =3D '{"a&qu=
ot;: "b"}' WHER=
E data->>'ID' =3D 'abcde';

PostgreSQL initialize= s A.B as an empty array instead of an object, and sets the value at index 3= . This behavior isn=E2=80=99t what I want, I=E2=80=99d 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'][=
9;B'] =3D coalesce(data->'A'->'B', '{}')

But that causes probl= ems 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=E2=80=99t always know in advance whethe= r a given path exists at the time of the update, so I=E2=80=99d like a solu= tion that won=E2=80=99t break or conflict with existing data.

Is there any way to force PostgreSQL to treat numeri= c subscripts as object keys instead of array indices, or otherwise control = this initialization behavior?

--000000000000d23b640640a21278--