public inbox for [email protected]help / color / mirror / Atom feed
Re: jsonb subscripting vs SQL/JSON array accessor semantics (SQL:2023) 4+ messages / 4 participants [nested] [flat]
* Re: jsonb subscripting vs SQL/JSON array accessor semantics (SQL:2023) @ 2026-02-09 21:15 Alexandra Wang <[email protected]> 0 siblings, 3 replies; 4+ messages in thread From: Alexandra Wang @ 2026-02-09 21:15 UTC (permalink / raw) To: Andrew Dunstan <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>; Peter Eisentraut <[email protected]>; Jelte Fennema-Nio <[email protected]>; [email protected] Hi all, I've put together patches based on the discussion so far to help move things forward. The current vote seems to be 2:1 in favor of implementing lax mode and numeric truncation while keeping negative subscripts, but I’d like to hear more opinions before we settle on a direction. On Mon, Feb 2, 2026 at 7:32 AM Andrew Dunstan <[email protected]> wrote: > > On 2026-01-16 Fr 4:26 PM, Alexandra Wang wrote: >> In all cases above, json_query() follows the SQL/JSON standard >> specification. jsonb subscripting, which predates the standard, >> differs in several ways: >> >> 1. Array access on non-arrays (scalars or objects) does not use 'lax' >> mode wrapping. As a result, "[0]" does not return the original value. >> 2. Non-integer subscripts are not supported. >> 3. Negative subscripts use a PostgreSQL-specific extension. > > I'd be inclined to move to the standard for 1 and 2, and document that > people might need to reindex after an upgrade if they have expression > indexes, but keep supporting negative subscripts. Not sure how feasible > that is exactly. The attached patches implement exactly this: 0001: Add numeric type support with truncation 0002: Add lax mode support for non-array access On Sat, Jan 17, 2026 at 1:54 AM Jelte Fennema-Nio <[email protected]> wrote: >> 3. Negative subscripts use a PostgreSQL-specific extension. > I think there are probably people relying on it. And postgres > behaviour actually seems way more useful than the SQL Standard > behaviour. > > You said DuckDB does the same as Postgres. That doesn't surprise me > much, since DuckDB usually defaults to Postgres behaviour. They don't > care much about being strictly sql standard compliant, if that means > more sensible/useful SQL for their users. And since many of their > users are used to Postgres, they try to stay PostgreSQL compatible in > their SQL (unless they think the postgres behaviour is really > weird/confusing). > > I do wonder what other databases do though. Does Oracle, MySQL or > MSSQL actually follow the standard here? i.e how incompatible is this > behaviour in practice with other databases? I did more research on what other databases do: Lax mode (integer subscript on non-array): Oracle: lax - SELECT jcol[0] on '{"a":1}' returns {"a":1} MySQL: lax - SELECT data->'$[0]' on '{"a":1}' returns {"a":1}, although this is not exactly simplified accessor syntax. DuckDB, MSSQL: no lax (return NULL) Numeric truncation: DuckDB: supports rounding Oracle has odd behavior - jcol[3.0] on '{"a":123}' also returns {"a":123}, same as [0]. Not sure what's happening there. MySQL and MSSQL only accept integers in their JSON path syntax. Negative index support: PostgreSQL, DuckDB: supported (count from end) Oracle, MySQL, MSSQL: not supported On Sat, Jan 17, 2026 at 7:07 AM Pavel Stehule <[email protected]> wrote: > I see some JavaScript philosophy (or HTML) in design of SQL/JSON - > '{[1,2,3]}'[0,1] -> 1 (SQL/JSON) versus -> NULL (Postgres) I believe Pavel is referring to the lax mode behavior that the standard defines for the json simplified accessor, am I right? This is indeed a philosophical difference, and so far the exact syntax is only implemented in Oracle (MySQL also has lax mode behavior, but not exactly using the simplified accessor syntax). If others share this concern about lax mode being too lenient, I'd like to hear it. One thing we didn't discuss explicitly: implementing lax mode also affects assignment behavior. For put-get consistency, if val[0] reads the value, then val[0] = 'x' should replace it: UPDATE t SET val[0] = '"x"' WHERE val = '123'; -- was ERROR, now sets val to "x" UPDATE t SET val['a'][0] = '"x"' WHERE val = '{"a": "hello"}'; -- val becomes {"a": "x"} Direct subscript assignment is a PostgreSQL extension anyway - Oracle uses JSON_TRANSFORM, MySQL uses JSON_SET, MS SQL uses JSON_MODIFY. So we have some freedom here in defining the semantics. I'd appreciate more votes on whether this direction makes sense, or if there are concerns I've missed. Best, Alex -- Alexandra Wang EDB: https://www.enterprisedb.com Attachments: [application/octet-stream] v1-0001-Support-numeric-type-for-jsonb-subscripting.patch (9.3K, 3-v1-0001-Support-numeric-type-for-jsonb-subscripting.patch) download | inline diff: From 16d52d78c599847c93c685d6694157c5ebefffe5 Mon Sep 17 00:00:00 2001 From: Alexandra Wang <[email protected]> Date: Tue, 3 Feb 2026 12:58:07 -0800 Subject: [PATCH v1 1/2] Support numeric type for jsonb subscripting Previously, using a numeric value as a jsonb subscript would error. Now numeric subscripts are accepted and truncated toward zero to produce an integer index. This matches the semantics of array access in json_query() per the SQL/JSON standard. Examples: SELECT ('["a","b","c"]'::jsonb)[1.7]; -- returns "b" (truncates to 1) SELECT ('["a","b","c"]'::jsonb)[-1.7]; -- returns "c" (truncates to -1) --- doc/src/sgml/json.sgml | 7 ++- src/backend/utils/adt/jsonbsubs.c | 73 +++++++++++++++++------------ src/test/regress/expected/jsonb.out | 69 +++++++++++++++++++++++++-- src/test/regress/sql/jsonb.sql | 18 +++++++ 4 files changed, 132 insertions(+), 35 deletions(-) diff --git a/doc/src/sgml/json.sgml b/doc/src/sgml/json.sgml index 206eadb8f7b..a584aab6ac1 100644 --- a/doc/src/sgml/json.sgml +++ b/doc/src/sgml/json.sgml @@ -621,7 +621,8 @@ SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @@ '$.tags[*] == "qui"'; subscripting expressions, following the same rules as the <literal>path</literal> argument in the <literal>jsonb_set</literal> function. If a <type>jsonb</type> value is an array, numeric subscripts start at zero, and negative integers count - backwards from the last element of the array. Slice expressions are not supported. + backwards from the last element of the array. Non-integer numeric subscripts are + truncated toward zero. Slice expressions are not supported. The result of a subscripting expression is always of the jsonb data type. </para> @@ -667,6 +668,10 @@ UPDATE table_name SET jsonb_field['a']['b']['c'] = '1'; -- subscripting is jsonb, the value we compare it against must also be jsonb. -- The double quotes make "value" also a valid jsonb string. SELECT * FROM table_name WHERE jsonb_field['key'] = '"value"'; + +-- Numeric subscripts are truncated toward zero +SELECT ('[1, 2, 3]'::jsonb)[1.7]; -- returns 2 + </programlisting> <type>jsonb</type> assignment via subscripting handles a few edge cases diff --git a/src/backend/utils/adt/jsonbsubs.c b/src/backend/utils/adt/jsonbsubs.c index 6bf55f19d6a..b110d30298d 100644 --- a/src/backend/utils/adt/jsonbsubs.c +++ b/src/backend/utils/adt/jsonbsubs.c @@ -21,6 +21,7 @@ #include "parser/parse_expr.h" #include "utils/builtins.h" #include "utils/jsonb.h" +#include "utils/numeric.h" /* SubscriptingRefState.workspace for jsonb subscripting execution */ @@ -78,41 +79,34 @@ jsonb_subscript_transform(SubscriptingRef *sbsref, if (subExprType != UNKNOWNOID) { - Oid targets[2] = {INT4OID, TEXTOID}; + Oid int4Oid = INT4OID; + Oid numericOid = NUMERICOID; + Oid textOid = TEXTOID; + bool can_int; + bool can_numeric; + bool can_text; /* - * Jsonb can handle multiple subscript types, but cases when a - * subscript could be coerced to multiple target types must be - * avoided, similar to overloaded functions. It could be - * possibly extend with jsonpath in the future. + * Check which types the subscript can be coerced to. We + * prefer INT4 for integer types (fast path), NUMERIC for + * numeric types (allows truncation toward zero per SQL/JSON + * spec), and TEXT for text types (field access). */ - for (int i = 0; i < 2; i++) - { - if (can_coerce_type(1, &subExprType, &targets[i], COERCION_IMPLICIT)) - { - /* - * One type has already succeeded, it means there are - * two coercion targets possible, failure. - */ - if (targetType != UNKNOWNOID) - ereport(ERROR, - (errcode(ERRCODE_DATATYPE_MISMATCH), - errmsg("subscript type %s is not supported", format_type_be(subExprType)), - errhint("jsonb subscript must be coercible to only one type, integer or text."), - parser_errposition(pstate, exprLocation(subExpr)))); - - targetType = targets[i]; - } - } - - /* - * No suitable types were found, failure. - */ - if (targetType == UNKNOWNOID) + can_int = can_coerce_type(1, &subExprType, &int4Oid, COERCION_IMPLICIT); + can_numeric = can_coerce_type(1, &subExprType, &numericOid, COERCION_IMPLICIT); + can_text = can_coerce_type(1, &subExprType, &textOid, COERCION_IMPLICIT); + + if (can_int && !can_text) + targetType = INT4OID; + else if (can_text && !can_int) + targetType = TEXTOID; + else if (can_numeric) + targetType = NUMERICOID; + else ereport(ERROR, (errcode(ERRCODE_DATATYPE_MISMATCH), errmsg("subscript type %s is not supported", format_type_be(subExprType)), - errhint("jsonb subscript must be coercible to either integer or text."), + errhint("jsonb subscript must be coercible to either numeric or text."), parser_errposition(pstate, exprLocation(subExpr)))); } else @@ -187,7 +181,9 @@ jsonb_subscript_check_subscripts(ExprState *state, * an empty source. */ if (sbsrefstate->numupper > 0 && sbsrefstate->upperprovided[0] && - !sbsrefstate->upperindexnull[0] && workspace->indexOid[0] == INT4OID) + !sbsrefstate->upperindexnull[0] && + (workspace->indexOid[0] == INT4OID || + workspace->indexOid[0] == NUMERICOID)) workspace->expectArray = true; /* Process upper subscripts */ @@ -217,6 +213,23 @@ jsonb_subscript_check_subscripts(ExprState *state, workspace->index[i] = CStringGetTextDatum(cs); } + else if (workspace->indexOid[i] == NUMERICOID) + { + /* + * Truncate numeric toward zero per SQL/JSON spec, then + * convert to int4 text for element access. + */ + Datum truncated; + Datum int_val; + char *cs; + + truncated = DirectFunctionCall2(numeric_trunc, + sbsrefstate->upperindex[i], + Int32GetDatum(0)); + int_val = DirectFunctionCall1(numeric_int4, truncated); + cs = DatumGetCString(DirectFunctionCall1(int4out, int_val)); + workspace->index[i] = CStringGetTextDatum(cs); + } else workspace->index[i] = sbsrefstate->upperindex[i]; } diff --git a/src/test/regress/expected/jsonb.out b/src/test/regress/expected/jsonb.out index 4e2467852db..f0ea654434f 100644 --- a/src/test/regress/expected/jsonb.out +++ b/src/test/regress/expected/jsonb.out @@ -5077,10 +5077,11 @@ select ('[1, "2", null]'::jsonb)['1']; (1 row) select ('[1, "2", null]'::jsonb)[1.0]; -ERROR: subscript type numeric is not supported -LINE 1: select ('[1, "2", null]'::jsonb)[1.0]; - ^ -HINT: jsonb subscript must be coercible to either integer or text. + jsonb +------- + "2" +(1 row) + select ('[1, "2", null]'::jsonb)[2]; jsonb ------- @@ -5952,3 +5953,63 @@ select ('{"text": "hello"}'::jsonb).text; {"text": "hello"} (1 row) +-- non-integer numeric subscripts +select ('[1, "2", null]'::jsonb)[0.0]; + jsonb +------- + 1 +(1 row) + +select ('[1, "2", null]'::jsonb)[0.9]; + jsonb +------- + 1 +(1 row) + +select ('[1, "2", null]'::jsonb)[1.9]; + jsonb +------- + "2" +(1 row) + +select ('[1, "2", null]'::jsonb)[-1.0]; + jsonb +------- + null +(1 row) + +select ('[1, "2", null]'::jsonb)[-1.9]; + jsonb +------- + null +(1 row) + +select ('[1, "2", null]'::jsonb)[1::numeric]; + jsonb +------- + "2" +(1 row) + +select ('[1, "2", null]'::jsonb)[1.5::float8]; -- errors +ERROR: subscript type double precision is not supported +LINE 1: select ('[1, "2", null]'::jsonb)[1.5::float8]; + ^ +HINT: jsonb subscript must be coercible to either numeric or text. +-- non-integer numeric subscript assignment +create temp table test_jsonb_numeric_subscript(id int, val jsonb); +insert into test_jsonb_numeric_subscript values (1, '[1, 2, 3]'); +update test_jsonb_numeric_subscript set val[1.0] = '"x"'; +select * from test_jsonb_numeric_subscript; + id | val +----+------------- + 1 | [1, "x", 3] +(1 row) + +update test_jsonb_numeric_subscript set val[1.9] = '"y"'; +select * from test_jsonb_numeric_subscript; + id | val +----+------------- + 1 | [1, "y", 3] +(1 row) + +drop table test_jsonb_numeric_subscript; diff --git a/src/test/regress/sql/jsonb.sql b/src/test/regress/sql/jsonb.sql index d28ed1c1e85..d7d26205534 100644 --- a/src/test/regress/sql/jsonb.sql +++ b/src/test/regress/sql/jsonb.sql @@ -1615,3 +1615,21 @@ select ('true'::jsonb)::bool; select ('true'::jsonb).bool; select ('{"text": "hello"}'::jsonb)::text; select ('{"text": "hello"}'::jsonb).text; + +-- non-integer numeric subscripts +select ('[1, "2", null]'::jsonb)[0.0]; +select ('[1, "2", null]'::jsonb)[0.9]; +select ('[1, "2", null]'::jsonb)[1.9]; +select ('[1, "2", null]'::jsonb)[-1.0]; +select ('[1, "2", null]'::jsonb)[-1.9]; +select ('[1, "2", null]'::jsonb)[1::numeric]; +select ('[1, "2", null]'::jsonb)[1.5::float8]; -- errors + +-- non-integer numeric subscript assignment +create temp table test_jsonb_numeric_subscript(id int, val jsonb); +insert into test_jsonb_numeric_subscript values (1, '[1, 2, 3]'); +update test_jsonb_numeric_subscript set val[1.0] = '"x"'; +select * from test_jsonb_numeric_subscript; +update test_jsonb_numeric_subscript set val[1.9] = '"y"'; +select * from test_jsonb_numeric_subscript; +drop table test_jsonb_numeric_subscript; -- 2.50.1 (Apple Git-155) [application/octet-stream] v1-0002-Implement-lax-mode-for-jsonb-subscripting.patch (33.3K, 4-v1-0002-Implement-lax-mode-for-jsonb-subscripting.patch) download | inline diff: From 40848bbc9a45e171726a1b465a00a438b60d110e Mon Sep 17 00:00:00 2001 From: Alexandra Wang <[email protected]> Date: Tue, 3 Feb 2026 14:36:52 -0800 Subject: [PATCH v1 2/2] Implement lax mode for jsonb subscripting Jsonb subscripting predates the SQL/JSON standard and had custom behavior that diverges from the SQL/JSON simplified accessor, which has identical syntax. This commit aligns jsonb subscripting with the SQL/JSON standard by enabling lax mode for integer subscripts on non-array values. Per SQL/JSON lax mode, when an integer subscript is applied to a non-array value (object or scalar), the value is treated as if wrapped in a single-element array. Index 0 and -1 are in-bounds (first/last element of the wrapped array), while other indices are out-of-bounds. Read behavior: - In-bounds (0 or -1): returns the value itself - Out-of-bounds: returns NULL Examples: SELECT ('123'::jsonb)[0]; -- was NULL, now 123 SELECT ('{"a":1}'::jsonb)[-1]; -- was NULL, now {"a":1} SELECT ('123'::jsonb)[2]; -- still NULL (out-of-bounds) Write behavior is also changed to maintain put-get consistency (if reading val[0] returns the value, then writing val[0] should replace that value): - In-bounds (0 or -1): replaces the value - Out-of-bounds: wraps value in array, then inserts at the index Examples: -- In-bounds assignment (was ERROR, now replaces) UPDATE t SET val[0] = '"x"' WHERE val = '123'; -- result: val becomes "x" -- Out-of-bounds assignment (wraps and inserts with NULL gap, -- note that filling gaps is exsting behavior for array assignment) UPDATE t SET val[2] = '"x"' WHERE val = '123'; -- result: val becomes [123, null, "x"] --- doc/src/sgml/json.sgml | 25 ++- src/backend/utils/adt/jsonbsubs.c | 9 +- src/backend/utils/adt/jsonfuncs.c | 323 ++++++++++++++++++++++++++-- src/include/utils/jsonb.h | 6 +- src/test/regress/expected/jsonb.out | 229 ++++++++++++++++++-- src/test/regress/sql/jsonb.sql | 86 ++++++++ 6 files changed, 626 insertions(+), 52 deletions(-) diff --git a/doc/src/sgml/json.sgml b/doc/src/sgml/json.sgml index a584aab6ac1..6b175d8dc9a 100644 --- a/doc/src/sgml/json.sgml +++ b/doc/src/sgml/json.sgml @@ -626,6 +626,14 @@ SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @@ '$.tags[*] == "qui"'; The result of a subscripting expression is always of the jsonb data type. </para> + <para> + When an integer subscript is applied to a <type>jsonb</type> value that is not + an array (i.e., an object or a scalar), the value is treated as if it were a + single-element array per SQL/JSON lax mode. Thus, index 0 and -1 access the + value itself, while other indices are out-of-bounds and return + <literal>NULL</literal>. + </para> + <para> <command>UPDATE</command> statements may use subscripting in the <literal>SET</literal> clause to modify <type>jsonb</type> values. Subscript @@ -635,10 +643,10 @@ SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @@ '$.tags[*] == "qui"'; <literal>val['a']</literal>, and <literal>val['a']['b']</literal> is an object. If any <literal>val['a']</literal> or <literal>val['a']['b']</literal> is not defined, it will be created as an empty object and filled as - necessary. However, if any <literal>val</literal> itself or one of the - intermediary values is defined as a non-object such as a string, number, or - <literal>jsonb</literal> <literal>null</literal>, traversal cannot proceed so - an error is raised and the transaction aborted. + necessary. However, if a text subscript (key access) is applied to a non-object + <literal>val</literal>, traversal cannot proceed so an error is raised. Integer + subscripts on any non-array <literal>val</literal> use lax mode as described + above, treating the value as a single-element array. </para> <para> @@ -672,6 +680,15 @@ SELECT * FROM table_name WHERE jsonb_field['key'] = '"value"'; -- Numeric subscripts are truncated toward zero SELECT ('[1, 2, 3]'::jsonb)[1.7]; -- returns 2 +-- Integer subscript on a non-array value (lax mode) +SELECT ('{"a": 1}'::jsonb)[0]; -- returns {"a": 1} +SELECT ('"hello"'::jsonb)[-1]; -- returns "hello" +SELECT ('{"a": 1}'::jsonb)[1]; -- returns NULL (out-of-bounds) + +-- Lax mode also works for assignment +UPDATE table_name SET jsonb_field['a'][0] = '"x"'; +-- If jsonb_field['a'] is "hello", it becomes "x" + </programlisting> <type>jsonb</type> assignment via subscripting handles a few edge cases diff --git a/src/backend/utils/adt/jsonbsubs.c b/src/backend/utils/adt/jsonbsubs.c index b110d30298d..ef240ae7234 100644 --- a/src/backend/utils/adt/jsonbsubs.c +++ b/src/backend/utils/adt/jsonbsubs.c @@ -261,7 +261,8 @@ jsonb_subscript_fetch(ExprState *state, workspace->index, sbsrefstate->numupper, op->resnull, - false); + false, + workspace->indexOid); } /* @@ -320,7 +321,8 @@ jsonb_subscript_assign(ExprState *state, *op->resvalue = jsonb_set_element(jsonbSource, workspace->index, sbsrefstate->numupper, - &replacevalue); + &replacevalue, + workspace->indexOid); /* The result is never NULL, so no need to change *op->resnull */ } @@ -353,7 +355,8 @@ jsonb_subscript_fetch_old(ExprState *state, sbsrefstate->upperindex, sbsrefstate->numupper, &sbsrefstate->prevnull, - false); + false, + NULL); } } diff --git a/src/backend/utils/adt/jsonfuncs.c b/src/backend/utils/adt/jsonfuncs.c index d5b64d7fca5..ac9f4d57ace 100644 --- a/src/backend/utils/adt/jsonfuncs.c +++ b/src/backend/utils/adt/jsonfuncs.c @@ -480,15 +480,25 @@ static void IteratorConcat(JsonbIterator **it1, JsonbIterator **it2, static void setPath(JsonbIterator **it, const Datum *path_elems, const bool *path_nulls, int path_len, JsonbInState *st, int level, JsonbValue *newval, - int op_type); + int op_type, const Oid *index_oid); static void setPathObject(JsonbIterator **it, const Datum *path_elems, const bool *path_nulls, int path_len, JsonbInState *st, int level, - JsonbValue *newval, uint32 npairs, int op_type); + JsonbValue *newval, uint32 npairs, int op_type, + const Oid *index_oid); static void setPathArray(JsonbIterator **it, const Datum *path_elems, const bool *path_nulls, int path_len, JsonbInState *st, int level, - JsonbValue *newval, uint32 nelems, int op_type); + JsonbValue *newval, uint32 nelems, int op_type, + const Oid *index_oid); +static Jsonb *wrap_container_in_array(JsonbContainer *container); +static Jsonb *wrap_scalar_in_array(JsonbValue *scalar); +static bool is_lax_subscript_in_bounds(Datum subscript, long *index_out); +static void setPath_lax_assignment(Datum subscript, int level, int path_len, + JsonbInState *st, JsonbValue *newval, int op_type, + Jsonb *value, bool is_scalar, + const Datum *path_elems, const bool *path_nulls, + const Oid *index_oid); /* function supporting iterate_json_values */ static JsonParseErrorType iterate_values_scalar(void *state, char *token, JsonTokenType tokentype); @@ -1519,7 +1529,7 @@ get_jsonb_path_all(FunctionCallInfo fcinfo, bool as_text) deconstruct_array_builtin(path, TEXTOID, &pathtext, &pathnulls, &npath); - res = jsonb_get_element(jb, pathtext, npath, &isnull, as_text); + res = jsonb_get_element(jb, pathtext, npath, &isnull, as_text, NULL); if (isnull) PG_RETURN_NULL(); @@ -1527,8 +1537,142 @@ get_jsonb_path_all(FunctionCallInfo fcinfo, bool as_text) PG_RETURN_DATUM(res); } +/* + * Check whether the subscript at the given level is a lax array access + * (integer or numeric index, as opposed to a text key for field access). + */ +static inline bool +is_lax_array_access(const Oid *index_oid, int level) +{ + return index_oid != NULL && + (index_oid[level] == INT4OID || index_oid[level] == NUMERICOID); +} + +/* + * Wrap a jsonb object in a single-element array. + * Used for out-of-bounds lax-mode assignment (reads return NULL instead). + */ +static Jsonb * +wrap_container_in_array(JsonbContainer *container) +{ + JsonbInState wrapState = {0}; + JsonbIterator *it = JsonbIteratorInit(container); + JsonbIteratorToken tok; + JsonbValue v; + + pushJsonbValue(&wrapState, WJB_BEGIN_ARRAY, NULL); + while ((tok = JsonbIteratorNext(&it, &v, false)) != WJB_DONE) + pushJsonbValue(&wrapState, tok, tok < WJB_BEGIN_ARRAY ? &v : NULL); + pushJsonbValue(&wrapState, WJB_END_ARRAY, NULL); + + return JsonbValueToJsonb(wrapState.result); +} + +/* + * Wrap a scalar JsonbValue in a single-element, non-raw array. + * Used for out-of-bounds lax-mode assignment (reads return NULL instead). + */ +static Jsonb * +wrap_scalar_in_array(JsonbValue *scalar) +{ + JsonbValue wrapper; + + wrapper.type = jbvArray; + wrapper.val.array.nElems = 1; + wrapper.val.array.elems = scalar; + wrapper.val.array.rawScalar = false; + return JsonbValueToJsonb(&wrapper); +} + +/* + * Parse an integer subscript and check if it's in-bounds for a + * single-element array (index 0 or -1). + */ +static bool +is_lax_subscript_in_bounds(Datum subscript, long *index_out) +{ + char *indextext = TextDatumGetCString(subscript); + char *endptr; + long index; + + index = strtol(indextext, &endptr, 10); + if (index_out) + *index_out = index; + return (index == 0 || index == -1); +} + +/* + * Handle lax array access on a non-array value during assignment. + * The value must already be materialized as a Jsonb. + * + * For in-bounds (0/-1): virtual wrap - either push newval (terminal) or + * recurse at level+1 (intermediate). + * For out-of-bounds: actual wrap and recurse at same level. + */ +static void +setPath_lax_assignment(Datum subscript, int level, int path_len, + JsonbInState *st, JsonbValue *newval, int op_type, + Jsonb *value, bool is_scalar, + const Datum *path_elems, const bool *path_nulls, + const Oid *index_oid) +{ + long index; + JsonbIterator *newit; + + if (is_lax_subscript_in_bounds(subscript, &index)) + { + /* In-bounds: virtual wrap */ + if (level == path_len - 1) + { + /* Terminal: replace with newval directly */ + if (newval != NULL && !(op_type & JB_PATH_DELETE)) + pushJsonbValue(st, WJB_VALUE, newval); + } + else + { + /* Intermediate: skip this level, recurse at level+1 */ + newit = JsonbIteratorInit(&value->root); + setPath(&newit, path_elems, path_nulls, path_len, + st, level + 1, newval, op_type, index_oid); + } + } + else + { + /* Out-of-bounds: actual wrap */ + Jsonb *wrapped; + + if (is_scalar) + { + /* + * Scalar is stored as rawScalar array in Jsonb. Extract it by + * iterating: WJB_BEGIN_ARRAY, WJB_ELEM (the scalar), ... + */ + JsonbIterator *extract_it = JsonbIteratorInit(&value->root); + JsonbValue scalar; + + JsonbIteratorNext(&extract_it, &scalar, false); /* WJB_BEGIN_ARRAY */ + JsonbIteratorNext(&extract_it, &scalar, false); /* WJB_ELEM */ + wrapped = wrap_scalar_in_array(&scalar); + } + else + wrapped = wrap_container_in_array(&value->root); + + newit = JsonbIteratorInit(&wrapped->root); + setPath(&newit, path_elems, path_nulls, path_len, + st, level, newval, op_type, index_oid); + } +} + +/* + * Fetch an element from a jsonb value using a path of subscripts. + * + * If index_oid is non-NULL, it provides the type OID of each subscript + * to enable SQL/JSON lax mode: integer subscripts on non-array values + * treat the value as a single-element array. If NULL, lax mode is disabled. + */ Datum -jsonb_get_element(Jsonb *jb, const Datum *path, int npath, bool *isnull, bool as_text) +jsonb_get_element(Jsonb *jb, const Datum *path, int npath, bool *isnull, + bool as_text, const Oid *index_oid) { JsonbContainer *container = &jb->root; JsonbValue *jbvp = NULL; @@ -1546,9 +1690,13 @@ jsonb_get_element(Jsonb *jb, const Datum *path, int npath, bool *isnull, bool as else { Assert(JB_ROOT_IS_ARRAY(jb) && JB_ROOT_IS_SCALAR(jb)); - /* Extract the scalar value, if it is what we'll return */ - if (npath <= 0) - jbvp = getIthJsonbValueFromContainer(container, 0); + + /* + * Extract the scalar value. We need this both when returning the + * scalar directly (npath <= 0) and for lax mode in-bounds access + * which also returns the scalar (npath > 0, index 0 or -1). + */ + jbvp = getIthJsonbValueFromContainer(container, 0); } /* @@ -1576,6 +1724,48 @@ jsonb_get_element(Jsonb *jb, const Datum *path, int npath, bool *isnull, bool as for (i = 0; i < npath; i++) { + if (is_lax_array_access(index_oid, i) && !have_array) + { + /* + * SQL/JSON lax mode: integer subscript on a non-array value + * (scalar or object). Virtual wrap: treat as single-element + * array. Index 0 or -1 returns the value; other indices return + * NULL. + */ + if (is_lax_subscript_in_bounds(path[i], NULL)) + { + /* + * In-bounds for virtual single-element array. + */ + if (i == npath - 1) + { + /* + * Terminal: return current value. For nested objects, + * jbvp is already set from previous iteration. For root + * level object, create jbvp from jb. For scalars, jbvp + * is already set. + */ + if (have_object && jbvp == NULL) + { + /* Root level object */ + jbvp = palloc(sizeof(JsonbValue)); + jbvp->type = jbvBinary; + jbvp->val.binary.data = container; + jbvp->val.binary.len = VARSIZE(jb) - VARHDRSZ; + } + break; + } + /* Intermediate: continue to next path element */ + continue; + } + else + { + /* Out-of-bounds: return NULL */ + *isnull = true; + return PointerGetDatum(NULL); + } + } + if (have_object) { text *subscr = DatumGetTextPP(path[i]); @@ -1675,9 +1865,16 @@ jsonb_get_element(Jsonb *jb, const Datum *path, int npath, bool *isnull, bool as } } +/* + * Assign a value to a jsonb element at a path of subscripts. + * + * If index_oid is non-NULL, it provides the type OID of each subscript + * to enable SQL/JSON lax mode: integer subscripts on non-array values + * treat the value as a single-element array. If NULL, lax mode is disabled. + */ Datum jsonb_set_element(Jsonb *jb, const Datum *path, int path_len, - JsonbValue *newval) + JsonbValue *newval, const Oid *index_oid) { JsonbInState state = {0}; JsonbIterator *it; @@ -1690,7 +1887,7 @@ jsonb_set_element(Jsonb *jb, const Datum *path, int path_len, setPath(&it, path, path_nulls, path_len, &state, 0, newval, JB_PATH_CREATE | JB_PATH_FILL_GAPS | - JB_PATH_CONSISTENT_POSITION); + JB_PATH_CONSISTENT_POSITION, index_oid); pfree(path_nulls); @@ -4886,7 +5083,7 @@ jsonb_set(PG_FUNCTION_ARGS) it = JsonbIteratorInit(&in->root); setPath(&it, path_elems, path_nulls, path_len, &st, - 0, &newval, create ? JB_PATH_CREATE : JB_PATH_REPLACE); + 0, &newval, create ? JB_PATH_CREATE : JB_PATH_REPLACE, NULL); PG_RETURN_JSONB_P(JsonbValueToJsonb(st.result)); } @@ -4990,7 +5187,7 @@ jsonb_delete_path(PG_FUNCTION_ARGS) it = JsonbIteratorInit(&in->root); setPath(&it, path_elems, path_nulls, path_len, &st, - 0, NULL, JB_PATH_DELETE); + 0, NULL, JB_PATH_DELETE, NULL); PG_RETURN_JSONB_P(JsonbValueToJsonb(st.result)); } @@ -5032,7 +5229,7 @@ jsonb_insert(PG_FUNCTION_ARGS) it = JsonbIteratorInit(&in->root); setPath(&it, path_elems, path_nulls, path_len, &st, 0, &newval, - after ? JB_PATH_INSERT_AFTER : JB_PATH_INSERT_BEFORE); + after ? JB_PATH_INSERT_AFTER : JB_PATH_INSERT_BEFORE, NULL); PG_RETURN_JSONB_P(JsonbValueToJsonb(st.result)); } @@ -5166,13 +5363,18 @@ IteratorConcat(JsonbIterator **it1, JsonbIterator **it2, * and a negative index out of the range, so this behavior will be prevented * and return an error. * + * If index_oid is non-NULL, it provides the type OID of each subscript + * to enable SQL/JSON lax mode: integer subscripts on non-array values + * treat the value as a single-element array. If NULL, lax mode is disabled. + * * All path elements before the last must already exist * whatever bits in op_type are set, or nothing is done. */ static void setPath(JsonbIterator **it, const Datum *path_elems, const bool *path_nulls, int path_len, - JsonbInState *st, int level, JsonbValue *newval, int op_type) + JsonbInState *st, int level, JsonbValue *newval, int op_type, + const Oid *index_oid) { JsonbValue v; JsonbIteratorToken r; @@ -5191,6 +5393,26 @@ setPath(JsonbIterator **it, const Datum *path_elems, { case WJB_BEGIN_ARRAY: + /* + * SQL/JSON lax mode: if this subscript is an element access + * (integer) and the value is a raw scalar, handle lax assignment. + */ + if (is_lax_array_access(index_oid, level) && v.val.array.rawScalar) + { + JsonbValue scalar_v; + Jsonb *scalar_jb; + + /* Consume scalar + end_array from original iterator */ + JsonbIteratorNext(it, &scalar_v, false); /* WJB_ELEM */ + JsonbIteratorNext(it, &v, false); /* WJB_END_ARRAY */ + + scalar_jb = JsonbValueToJsonb(&scalar_v); + setPath_lax_assignment(path_elems[level], level, path_len, + st, newval, op_type, scalar_jb, true, + path_elems, path_nulls, index_oid); + break; + } + /* * If instructed complain about attempts to replace within a raw * scalar value. This happens even when current level is equal to @@ -5207,15 +5429,57 @@ setPath(JsonbIterator **it, const Datum *path_elems, pushJsonbValue(st, r, NULL); setPathArray(it, path_elems, path_nulls, path_len, st, level, - newval, v.val.array.nElems, op_type); + newval, v.val.array.nElems, op_type, + index_oid); r = JsonbIteratorNext(it, &v, false); Assert(r == WJB_END_ARRAY); pushJsonbValue(st, r, NULL); break; case WJB_BEGIN_OBJECT: + + /* + * SQL/JSON lax mode: if this subscript is an element access + * (integer) on an object, handle according to the index value and + * whether this is an intermediate or terminal subscript. + */ + if (is_lax_array_access(index_oid, level)) + { + JsonbInState objState = {0}; + JsonbIteratorToken tok; + JsonbValue objv; + Jsonb *obj; + int depth = 1; + + /* + * Materialize the object by consuming from the iterator + * (tracking depth to handle nested objects/arrays). + */ + pushJsonbValue(&objState, WJB_BEGIN_OBJECT, NULL); + while ((tok = JsonbIteratorNext(it, &objv, false)) != WJB_DONE) + { + if (tok == WJB_BEGIN_OBJECT || tok == WJB_BEGIN_ARRAY) + depth++; + else if (tok == WJB_END_OBJECT || tok == WJB_END_ARRAY) + depth--; + + pushJsonbValue(&objState, tok, + tok < WJB_BEGIN_ARRAY ? &objv : NULL); + + if (depth == 0) + break; + } + obj = JsonbValueToJsonb(objState.result); + + setPath_lax_assignment(path_elems[level], level, path_len, + st, newval, op_type, obj, false, + path_elems, path_nulls, index_oid); + break; + } + pushJsonbValue(st, r, NULL); setPathObject(it, path_elems, path_nulls, path_len, st, level, - newval, v.val.object.nPairs, op_type); + newval, v.val.object.nPairs, op_type, + index_oid); r = JsonbIteratorNext(it, &v, true); Assert(r == WJB_END_OBJECT); pushJsonbValue(st, r, NULL); @@ -5223,6 +5487,21 @@ setPath(JsonbIterator **it, const Datum *path_elems, case WJB_ELEM: case WJB_VALUE: + /* + * SQL/JSON lax mode: if this subscript is an element access + * (integer) on a scalar value, handle lax assignment. + */ + if (is_lax_array_access(index_oid, level) && + (level <= path_len - 1)) + { + Jsonb *scalar_jb = JsonbValueToJsonb(&v); + + setPath_lax_assignment(path_elems[level], level, path_len, + st, newval, op_type, scalar_jb, true, + path_elems, path_nulls, index_oid); + break; + } + /* * If instructed complain about attempts to replace within a * scalar value. This happens even when current level is equal to @@ -5250,7 +5529,8 @@ setPath(JsonbIterator **it, const Datum *path_elems, static void setPathObject(JsonbIterator **it, const Datum *path_elems, const bool *path_nulls, int path_len, JsonbInState *st, int level, - JsonbValue *newval, uint32 npairs, int op_type) + JsonbValue *newval, uint32 npairs, int op_type, + const Oid *index_oid) { text *pathelem = NULL; int i; @@ -5317,7 +5597,8 @@ setPathObject(JsonbIterator **it, const Datum *path_elems, const bool *path_null { pushJsonbValue(st, r, &k); setPath(it, path_elems, path_nulls, path_len, - st, level + 1, newval, op_type); + st, level + 1, newval, op_type, + index_oid); } } else @@ -5388,7 +5669,8 @@ setPathObject(JsonbIterator **it, const Datum *path_elems, const bool *path_null static void setPathArray(JsonbIterator **it, const Datum *path_elems, const bool *path_nulls, int path_len, JsonbInState *st, int level, - JsonbValue *newval, uint32 nelems, int op_type) + JsonbValue *newval, uint32 nelems, int op_type, + const Oid *index_oid) { JsonbValue v; int idx, @@ -5489,7 +5771,8 @@ setPathArray(JsonbIterator **it, const Datum *path_elems, const bool *path_nulls } else setPath(it, path_elems, path_nulls, path_len, - st, level + 1, newval, op_type); + st, level + 1, newval, op_type, + index_oid); } else { diff --git a/src/include/utils/jsonb.h b/src/include/utils/jsonb.h index ca13efba0fb..48c8a3ceca6 100644 --- a/src/include/utils/jsonb.h +++ b/src/include/utils/jsonb.h @@ -454,9 +454,11 @@ extern bool JsonbExtractScalar(JsonbContainer *jbc, JsonbValue *res); extern const char *JsonbTypeName(JsonbValue *val); extern Datum jsonb_set_element(Jsonb *jb, const Datum *path, int path_len, - JsonbValue *newval); + JsonbValue *newval, + const Oid *index_oid); extern Datum jsonb_get_element(Jsonb *jb, const Datum *path, int npath, - bool *isnull, bool as_text); + bool *isnull, bool as_text, + const Oid *index_oid); extern bool to_jsonb_is_immutable(Oid typoid); extern Datum jsonb_build_object_worker(int nargs, const Datum *args, const bool *nulls, const Oid *types, bool absent_on_null, diff --git a/src/test/regress/expected/jsonb.out b/src/test/regress/expected/jsonb.out index f0ea654434f..71fdee8e759 100644 --- a/src/test/regress/expected/jsonb.out +++ b/src/test/regress/expected/jsonb.out @@ -5025,7 +5025,7 @@ select ('123'::jsonb)['a']; select ('123'::jsonb)[0]; jsonb ------- - + 123 (1 row) select ('123'::jsonb)[NULL]; @@ -5041,9 +5041,9 @@ select ('{"a": 1}'::jsonb)['a']; (1 row) select ('{"a": 1}'::jsonb)[0]; - jsonb -------- - + jsonb +---------- + {"a": 1} (1 row) select ('{"a": 1}'::jsonb)['not_exist']; @@ -5109,7 +5109,7 @@ select ('[1, "2", null]'::jsonb)[1]['a']; select ('[1, "2", null]'::jsonb)[1][0]; jsonb ------- - + "2" (1 row) select ('{"a": 1, "b": "c", "d": [1, 2, 3]}'::jsonb)['b']; @@ -5279,10 +5279,10 @@ select * from test_jsonb_subscript; update test_jsonb_subscript set test_json = NULL where id = 3; update test_jsonb_subscript set test_json[0] = '1'; select * from test_jsonb_subscript; - id | test_json -----+--------------------------------------------------------------- - 1 | {"0": 1, "a": [1, 2, 3], "another_key": null} - 2 | {"0": 1, "a": [1, 2, 3], "key": "value", "another_key": null} + id | test_json +----+----------- + 1 | 1 + 2 | 1 3 | [1] (3 rows) @@ -5355,9 +5355,9 @@ delete from test_jsonb_subscript; insert into test_jsonb_subscript values (1, '{}'); update test_jsonb_subscript set test_json[0]['a'] = '1'; select * from test_jsonb_subscript; - id | test_json -----+----------------- - 1 | {"0": {"a": 1}} + id | test_json +----+----------- + 1 | {"a": 1} (1 row) -- the start jsonb is an array @@ -5397,9 +5397,9 @@ insert into test_jsonb_subscript values (1, '{}'); update test_jsonb_subscript set test_json['a']['b'][10] = '1'; update test_jsonb_subscript set test_json['a'][10][10] = '1'; select * from test_jsonb_subscript; - id | test_json -----+------------------------------------------------------------------------------------------------------------------------------------------------------ - 1 | {"a": {"b": [null, null, null, null, null, null, null, null, null, null, 1], "10": [null, null, null, null, null, null, null, null, null, null, 1]}} + id | test_json +----+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- + 1 | {"a": [{"b": [null, null, null, null, null, null, null, null, null, null, 1]}, null, null, null, null, null, null, null, null, null, [null, null, null, null, null, null, null, null, null, null, 1]]} (1 row) -- an empty sub element @@ -5431,23 +5431,15 @@ update test_jsonb_subscript set test_json['a']['b']['c'] = '1'; ERROR: cannot replace existing key DETAIL: The path assumes key is a composite object, but it is a scalar value. update test_jsonb_subscript set test_json['a'][0] = '1'; -ERROR: cannot replace existing key -DETAIL: The path assumes key is a composite object, but it is a scalar value. update test_jsonb_subscript set test_json['a'][0]['c'] = '1'; ERROR: cannot replace existing key DETAIL: The path assumes key is a composite object, but it is a scalar value. update test_jsonb_subscript set test_json['a'][0][0] = '1'; -ERROR: cannot replace existing key -DETAIL: The path assumes key is a composite object, but it is a scalar value. -- trying replace assuming a composite object, but it's a raw scalar delete from test_jsonb_subscript; insert into test_jsonb_subscript values (1, 'null'); update test_jsonb_subscript set test_json[0] = '1'; -ERROR: cannot replace existing key -DETAIL: The path assumes key is a composite object, but it is a scalar value. update test_jsonb_subscript set test_json[0][0] = '1'; -ERROR: cannot replace existing key -DETAIL: The path assumes key is a composite object, but it is a scalar value. -- try some things with short-header and toasted subscript values drop table test_jsonb_subscript; create temp table test_jsonb_subscript ( @@ -6013,3 +6005,194 @@ select * from test_jsonb_numeric_subscript; (1 row) drop table test_jsonb_numeric_subscript; +-- integer subscript on non-array values +select ('123'::jsonb)[0]; + jsonb +------- + 123 +(1 row) + +select ('123'::jsonb)[-1]; + jsonb +------- + 123 +(1 row) + +select ('123'::jsonb)[1]; + jsonb +------- + +(1 row) + +select ('{"a": 1}'::jsonb)[0]; + jsonb +---------- + {"a": 1} +(1 row) + +select ('{"a": 1}'::jsonb)[-1]; + jsonb +---------- + {"a": 1} +(1 row) + +select ('{"a": 1}'::jsonb)[1]; + jsonb +------- + +(1 row) + +-- chained subscript with integer index +select ('{"a": 1}'::jsonb)['a'][0]; + jsonb +------- + 1 +(1 row) + +select ('{"a": 1}'::jsonb)['a'][1]; -- out-of-bounds + jsonb +------- + +(1 row) + +select ('{"a": {"b": 2}}'::jsonb)['a'][0]; + jsonb +---------- + {"b": 2} +(1 row) + +select ('{"a": {"b": 2}}'::jsonb)['a'][0]['b']; + jsonb +------- + 2 +(1 row) + +-- integer vs text subscript on object with numeric key +select ('{"0": "value"}'::jsonb)[0]; + jsonb +---------------- + {"0": "value"} +(1 row) + +select ('{"0": "value"}'::jsonb)['0']; + jsonb +--------- + "value" +(1 row) + +select ('{"0": "value"}'::jsonb)[0.0]; + jsonb +---------------- + {"0": "value"} +(1 row) + +-- Write tests: integer subscript assignment on non-array values +create temp table test_jsonb_lax(val jsonb); +-- in-bounds on scalar +insert into test_jsonb_lax values ('123'); +update test_jsonb_lax set val[0] = '"x"'; +select val from test_jsonb_lax; + val +----- + "x" +(1 row) + +-- in-bounds on object +truncate test_jsonb_lax; +insert into test_jsonb_lax values ('{"a": 1}'); +update test_jsonb_lax set val[-1] = '"x"'; +select val from test_jsonb_lax; + val +----- + "x" +(1 row) + +-- in-bounds on nested object +truncate test_jsonb_lax; +insert into test_jsonb_lax values ('{"a": {"b": 1}}'); +update test_jsonb_lax set val[0] = '"x"'; +select val from test_jsonb_lax; + val +----- + "x" +(1 row) + +-- out-of-bounds wraps in array +truncate test_jsonb_lax; +insert into test_jsonb_lax values ('{"a": 1}'); +update test_jsonb_lax set val[1] = '"x"'; +select val from test_jsonb_lax; + val +----------------- + [{"a": 1}, "x"] +(1 row) + +-- out-of-bounds negative is error +truncate test_jsonb_lax; +insert into test_jsonb_lax values ('{"a": 1}'); +update test_jsonb_lax set val[-2] = '"x"'; +ERROR: path element at position 1 is out of range: -2 +select val from test_jsonb_lax; + val +---------- + {"a": 1} +(1 row) + +-- integer subscript vs text subscript on object with numeric key +truncate test_jsonb_lax; +insert into test_jsonb_lax values ('{"0": "value"}'); +update test_jsonb_lax set val[0] = '"x"'; +select val from test_jsonb_lax; + val +----- + "x" +(1 row) + +truncate test_jsonb_lax; +insert into test_jsonb_lax values ('{"0": "value"}'); +update test_jsonb_lax set val['0'] = '"x"'; +select val from test_jsonb_lax; + val +------------ + {"0": "x"} +(1 row) + +truncate test_jsonb_lax; +insert into test_jsonb_lax values ('{"0": "value"}'); +update test_jsonb_lax set val[0.0] = '"x"'; +select val from test_jsonb_lax; + val +----- + "x" +(1 row) + +-- numeric subscript truncates toward zero +truncate test_jsonb_lax; +insert into test_jsonb_lax values ('{"a": 1}'); +update test_jsonb_lax set val[0.7] = '"x"'; +select val from test_jsonb_lax; + val +----- + "x" +(1 row) + +truncate test_jsonb_lax; +insert into test_jsonb_lax values ('{"a": 1}'); +update test_jsonb_lax set val[-1.7] = '"x"'; +select val from test_jsonb_lax; + val +----- + "x" +(1 row) + +-- chained subscript on intermediate non-array +truncate test_jsonb_lax; +insert into test_jsonb_lax values ('{"a": "hello"}'); +update test_jsonb_lax set val['a'][0] = '"x"'; +select val from test_jsonb_lax; + val +------------ + {"a": "x"} +(1 row) + +drop table test_jsonb_lax; diff --git a/src/test/regress/sql/jsonb.sql b/src/test/regress/sql/jsonb.sql index d7d26205534..84860f6912b 100644 --- a/src/test/regress/sql/jsonb.sql +++ b/src/test/regress/sql/jsonb.sql @@ -1633,3 +1633,89 @@ select * from test_jsonb_numeric_subscript; update test_jsonb_numeric_subscript set val[1.9] = '"y"'; select * from test_jsonb_numeric_subscript; drop table test_jsonb_numeric_subscript; + +-- integer subscript on non-array values +select ('123'::jsonb)[0]; +select ('123'::jsonb)[-1]; +select ('123'::jsonb)[1]; +select ('{"a": 1}'::jsonb)[0]; +select ('{"a": 1}'::jsonb)[-1]; +select ('{"a": 1}'::jsonb)[1]; + +-- chained subscript with integer index +select ('{"a": 1}'::jsonb)['a'][0]; +select ('{"a": 1}'::jsonb)['a'][1]; -- out-of-bounds +select ('{"a": {"b": 2}}'::jsonb)['a'][0]; +select ('{"a": {"b": 2}}'::jsonb)['a'][0]['b']; + +-- integer vs text subscript on object with numeric key +select ('{"0": "value"}'::jsonb)[0]; +select ('{"0": "value"}'::jsonb)['0']; +select ('{"0": "value"}'::jsonb)[0.0]; + +-- Write tests: integer subscript assignment on non-array values +create temp table test_jsonb_lax(val jsonb); + +-- in-bounds on scalar +insert into test_jsonb_lax values ('123'); +update test_jsonb_lax set val[0] = '"x"'; +select val from test_jsonb_lax; + +-- in-bounds on object +truncate test_jsonb_lax; +insert into test_jsonb_lax values ('{"a": 1}'); +update test_jsonb_lax set val[-1] = '"x"'; +select val from test_jsonb_lax; + +-- in-bounds on nested object +truncate test_jsonb_lax; +insert into test_jsonb_lax values ('{"a": {"b": 1}}'); +update test_jsonb_lax set val[0] = '"x"'; +select val from test_jsonb_lax; + +-- out-of-bounds wraps in array +truncate test_jsonb_lax; +insert into test_jsonb_lax values ('{"a": 1}'); +update test_jsonb_lax set val[1] = '"x"'; +select val from test_jsonb_lax; + +-- out-of-bounds negative is error +truncate test_jsonb_lax; +insert into test_jsonb_lax values ('{"a": 1}'); +update test_jsonb_lax set val[-2] = '"x"'; +select val from test_jsonb_lax; + +-- integer subscript vs text subscript on object with numeric key +truncate test_jsonb_lax; +insert into test_jsonb_lax values ('{"0": "value"}'); +update test_jsonb_lax set val[0] = '"x"'; +select val from test_jsonb_lax; + +truncate test_jsonb_lax; +insert into test_jsonb_lax values ('{"0": "value"}'); +update test_jsonb_lax set val['0'] = '"x"'; +select val from test_jsonb_lax; + +truncate test_jsonb_lax; +insert into test_jsonb_lax values ('{"0": "value"}'); +update test_jsonb_lax set val[0.0] = '"x"'; +select val from test_jsonb_lax; + +-- numeric subscript truncates toward zero +truncate test_jsonb_lax; +insert into test_jsonb_lax values ('{"a": 1}'); +update test_jsonb_lax set val[0.7] = '"x"'; +select val from test_jsonb_lax; + +truncate test_jsonb_lax; +insert into test_jsonb_lax values ('{"a": 1}'); +update test_jsonb_lax set val[-1.7] = '"x"'; +select val from test_jsonb_lax; + +-- chained subscript on intermediate non-array +truncate test_jsonb_lax; +insert into test_jsonb_lax values ('{"a": "hello"}'); +update test_jsonb_lax set val['a'][0] = '"x"'; +select val from test_jsonb_lax; + +drop table test_jsonb_lax; -- 2.50.1 (Apple Git-155) ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: jsonb subscripting vs SQL/JSON array accessor semantics (SQL:2023) @ 2026-02-10 03:05 jian he <[email protected]> parent: Alexandra Wang <[email protected]> 2 siblings, 0 replies; 4+ messages in thread From: jian he @ 2026-02-10 03:05 UTC (permalink / raw) To: Alexandra Wang <[email protected]>; +Cc: Andrew Dunstan <[email protected]>; PostgreSQL Hackers <[email protected]>; Peter Eisentraut <[email protected]>; Jelte Fennema-Nio <[email protected]>; [email protected] On Tue, Feb 10, 2026 at 5:16 AM Alexandra Wang <[email protected]> wrote: > > The attached patches implement exactly this: > > 0001: Add numeric type support with truncation Subject: [PATCH v1 1/2] Support numeric type for jsonb subscripting Previously, using a numeric value as a jsonb subscript would error. Now numeric subscripts are accepted and truncated toward zero to produce an integer index. This matches the semantics of array access in json_query() per the SQL/JSON standard. Examples: SELECT ('["a","b","c"]'::jsonb)[1.7]; -- returns "b" (truncates to 1) SELECT ('["a","b","c"]'::jsonb)[-1.7]; -- returns "c" (truncates to first thing come to my mind would be special numeric value +inf, -inf, NaN SELECT ('{"NaN":"b"}'::jsonb)['inf'::numeric]; ERROR: cannot convert infinity to integer SELECT ('{"NaN":"b"}'::jsonb)['NaN'::numeric]; ERROR: cannot convert NaN to integer Is the above what we expected, or should just return NULL? Anyway, obviously we need to test these special numeric values. +select ('[1, "2", null]'::jsonb)[1.5::float8]; -- errors +ERROR: subscript type double precision is not supported +LINE 1: select ('[1, "2", null]'::jsonb)[1.5::float8]; + ^ +HINT: jsonb subscript must be coercible to either numeric or text. This errhint message appears to be incorrect? given that 1.5::float8 is coercible to numeric. -- jian https://www.enterprisedb.com/ ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: jsonb subscripting vs SQL/JSON array accessor semantics (SQL:2023) @ 2026-02-10 10:34 Pavel Stehule <[email protected]> parent: Alexandra Wang <[email protected]> 2 siblings, 0 replies; 4+ messages in thread From: Pavel Stehule @ 2026-02-10 10:34 UTC (permalink / raw) To: Alexandra Wang <[email protected]>; +Cc: Andrew Dunstan <[email protected]>; PostgreSQL Hackers <[email protected]>; Peter Eisentraut <[email protected]>; Jelte Fennema-Nio <[email protected]> po 9. 2. 2026 v 22:16 odesílatel Alexandra Wang < [email protected]> napsal: > Hi all, > > I've put together patches based on the discussion so far to help move > things forward. The current vote seems to be 2:1 in favor of > implementing lax mode and numeric truncation while keeping negative > subscripts, but I’d like to hear more opinions before we settle on a > direction. > > On Mon, Feb 2, 2026 at 7:32 AM Andrew Dunstan <[email protected]> wrote: > > > > On 2026-01-16 Fr 4:26 PM, Alexandra Wang wrote: > >> In all cases above, json_query() follows the SQL/JSON standard > >> specification. jsonb subscripting, which predates the standard, > >> differs in several ways: > >> > >> 1. Array access on non-arrays (scalars or objects) does not use 'lax' > >> mode wrapping. As a result, "[0]" does not return the original value. > >> 2. Non-integer subscripts are not supported. > >> 3. Negative subscripts use a PostgreSQL-specific extension. > > > > I'd be inclined to move to the standard for 1 and 2, and document that > > people might need to reindex after an upgrade if they have expression > > indexes, but keep supporting negative subscripts. Not sure how feasible > > that is exactly. > > The attached patches implement exactly this: > > 0001: Add numeric type support with truncation > 0002: Add lax mode support for non-array access > > On Sat, Jan 17, 2026 at 1:54 AM Jelte Fennema-Nio <[email protected]> > wrote: > >> 3. Negative subscripts use a PostgreSQL-specific extension. > > I think there are probably people relying on it. And postgres > > behaviour actually seems way more useful than the SQL Standard > > behaviour. > > > > You said DuckDB does the same as Postgres. That doesn't surprise me > > much, since DuckDB usually defaults to Postgres behaviour. They don't > > care much about being strictly sql standard compliant, if that means > > more sensible/useful SQL for their users. And since many of their > > users are used to Postgres, they try to stay PostgreSQL compatible in > > their SQL (unless they think the postgres behaviour is really > > weird/confusing). > > > > I do wonder what other databases do though. Does Oracle, MySQL or > > MSSQL actually follow the standard here? i.e how incompatible is this > > behaviour in practice with other databases? > > I did more research on what other databases do: > > Lax mode (integer subscript on non-array): > Oracle: lax - SELECT jcol[0] on '{"a":1}' returns {"a":1} > MySQL: lax - SELECT data->'$[0]' on '{"a":1}' returns {"a":1}, > although this is not exactly simplified accessor syntax. > DuckDB, MSSQL: no lax (return NULL) > > Numeric truncation: > DuckDB: supports rounding > Oracle has odd behavior - jcol[3.0] on '{"a":123}' also returns > {"a":123}, same as [0]. Not sure what's happening there. > MySQL and MSSQL only accept integers in their JSON path syntax. > > Negative index support: > PostgreSQL, DuckDB: supported (count from end) > Oracle, MySQL, MSSQL: not supported > > On Sat, Jan 17, 2026 at 7:07 AM Pavel Stehule <[email protected]> > wrote: > > I see some JavaScript philosophy (or HTML) in design of SQL/JSON - > > '{[1,2,3]}'[0,1] -> 1 (SQL/JSON) versus -> NULL (Postgres) > > I believe Pavel is referring to the lax mode behavior that the > standard defines for the json simplified accessor, am I right? This is > indeed a philosophical difference, and so far the exact syntax is only > implemented in Oracle (MySQL also has lax mode behavior, but not > exactly using the simplified accessor syntax). If others share this > concern about lax mode being too lenient, I'd like to hear it. > > One thing we didn't discuss explicitly: implementing lax mode also > affects assignment behavior. For put-get consistency, if val[0] reads > the value, then val[0] = 'x' should replace it: > > UPDATE t SET val[0] = '"x"' WHERE val = '123'; > -- was ERROR, now sets val to "x" > > UPDATE t SET val['a'][0] = '"x"' WHERE val = '{"a": "hello"}'; > -- val becomes {"a": "x"} > When I see this, then I prefer a strict SQL/JSON behaviour and well and strong documentation. Introducing one's own behaviour is way to hell. This is too complex, and maintaining one's own behavior is against users. I dislike it, but the best we can do is respect the standard. The problem that should be well documented is fact, so we can have a json with an array inside, but we can have a postgresql's array of json values too - and can be pretty messy, because the behaviour can be different. I am not sure if ANSI/SQL knows arrays (there are only sets if I remember it correctly). Regards Pavel > Direct subscript assignment is a PostgreSQL extension anyway - Oracle > uses JSON_TRANSFORM, MySQL uses JSON_SET, MS SQL uses JSON_MODIFY. So > we have some freedom here in defining the semantics. > > I'd appreciate more votes on whether this direction makes sense, or if > there are concerns I've missed. > > Best, > Alex > > -- > Alexandra Wang > EDB: https://www.enterprisedb.com > ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: jsonb subscripting vs SQL/JSON array accessor semantics (SQL:2023) @ 2026-03-31 14:43 Peter Eisentraut <[email protected]> parent: Alexandra Wang <[email protected]> 2 siblings, 0 replies; 4+ messages in thread From: Peter Eisentraut @ 2026-03-31 14:43 UTC (permalink / raw) To: Alexandra Wang <[email protected]>; Andrew Dunstan <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>; Jelte Fennema-Nio <[email protected]>; [email protected] On 09.02.26 22:15, Alexandra Wang wrote: > I'd appreciate more votes on whether this direction makes sense, or if > there are concerns I've missed. At the moment, I see no consensus here, in fact, it seems there are more opinions than options. ;-) I think this is an important topic to resolve one way or another, and there also appear to be at least some documentation gaps if not bugs in the existing code. I created a commitfest entry for this for PG20-1, so we can continue tracking this. ^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2026-03-31 14:43 UTC | newest] Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2026-02-09 21:15 Re: jsonb subscripting vs SQL/JSON array accessor semantics (SQL:2023) Alexandra Wang <[email protected]> 2026-02-10 03:05 ` jian he <[email protected]> 2026-02-10 10:34 ` Pavel Stehule <[email protected]> 2026-03-31 14:43 ` Peter Eisentraut <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox