diff --git a/doc/src/sgml/json.sgml b/doc/src/sgml/json.sgml index 4405570d66e..5e8eb3c3541 100644 --- a/doc/src/sgml/json.sgml +++ b/doc/src/sgml/json.sgml @@ -715,12 +715,12 @@ UPDATE table_name SET jsonb_field[1]['a'] = '1'; JSON Simplified Accessor - PostgreSQL implements the JSON simplified accessor as specified in SQL:2023. + PostgreSQL implements the JSON simplified accessor as specified in SQL:2023. The SQL standard defines the simplified accessor as a chain of operations that can include JSON member accessors (dot notation for object fields) and JSON array accessors (integer subscripts for array elements). This provides a standardized way to access JSON data that complements - PostgreSQL's pre-standard subscripting and operator-based access methods. + PostgreSQL's pre-standard subscripting and operator-based access methods. @@ -758,7 +758,6 @@ UPDATE table_name SET jsonb_field[1]['a'] = '1'; Examples of JSON simplified accessor syntax: - -- Basic field access SELECT ('{"color": "red", "rgb": [255, 0, 0]}'::jsonb).color; @@ -769,7 +768,7 @@ SELECT ('{"user": {"profile": {"settings": {"theme": "dark"}}}}'::jsonb).user.pr SELECT ('{"repertoire": [{"title": "Swan Lake"}, {"title": "The Nutcracker"}]}'::jsonb).repertoire[1].title; -- In WHERE clauses -SELECT * FROM users WHERE profile.preferences.theme = '"dark"'; +SELECT * FROM users WHERE (profile).preferences.theme = '"dark"'; -- Comparison with other access methods (NOT equivalent - different semantics): SELECT json_col['address']['city']; -- Subscripting @@ -781,7 +780,7 @@ SELECT json_col.address.city; -- Simplified accessor (different Comparison of JSON Access Methods - PostgreSQL provides three different approaches for accessing JSON data, each with + PostgreSQL provides three different approaches for accessing JSON data, each with distinct semantics and behaviors: @@ -834,7 +833,8 @@ SELECT json_col.address.city; -- Simplified accessor (different - PostgreSQL's original JSONB subscripting behavior (available since version 14) + PostgreSQL's original JSONB subscripting + behavior (available since version 14) @@ -860,7 +860,8 @@ SELECT json_col.address.city; -- Simplified accessor (different - PostgreSQL's JSON operators that work with both json and jsonb types + PostgreSQL's JSON operators that work with + both json and jsonb types @@ -870,7 +871,7 @@ SELECT json_col.address.city; -- Simplified accessor (different - -> returns jsonb, ->> returns text + -> returns jsonb, ->> returns text @@ -890,19 +891,37 @@ SELECT json_col.address.city; -- Simplified accessor (different Member Access from Arrays: -- Setup data +CREATE TABLE test_table(data jsonb); INSERT INTO test_table VALUES - ('{"brightness": 80}'), -- Object case - ('[{"brightness": 45}, {"brightness": 90}]'); -- Array case + ('{"brightness": 80}'), ('[{"brightness": 45}, {"brightness": 90}]'); -- Different behaviors: -SELECT data.brightness FROM test_table; -- Simplified accessor --- Results: 80, [45, 90] (array elements unwrapped, results wrapped) - -SELECT data['brightness'] FROM test_table; -- Pre-standard subscripting --- Results: 80, NULL (no array handling) - -SELECT data->'brightness' FROM test_table; -- Arrow operator --- Results: 80, NULL (no array handling) +-- Simplified accessor +--(array elements unwrapped, results wrapped) +SELECT (data).brightness FROM test_table; + brightness +------------ + 80 + [45, 90] +(2 rows) + +-- Pre-standard subscripting +--(no array handling) +SELECT data['brightness'], data['brightness'] IS NULL as is_null FROM test_table; + data | is_null +------+--------- + 80 | f + | t +(2 rows) + +-- Arrow operator +--(no array handling) +SELECT data->'brightness', data->'brightness' IS NULL as isnull FROM test_table; + ?column? | isnull +----------+-------- + 80 | f + | t +(2 rows) In the array case, the simplified accessor applies the field access to each array element @@ -915,6 +934,7 @@ SELECT data->'brightness' FROM test_table; -- Arrow operator Array Access from Objects (Lax Mode Behavior): -- Setup data +TRUNCATE test_table; INSERT INTO test_table VALUES ('{"weather": "sunny", "temperature": "72F"}'); -- Different behaviors when accessing [0] on a non-array value: @@ -924,8 +944,13 @@ SELECT data[0] FROM test_table; -- Simplified accessor (lax mode SELECT data[0] FROM test_table; -- Pre-standard subscripting (strict mode, no dots) -- Result: NULL (no wrapping, direct array access on object fails) -SELECT data->0 FROM test_table; -- Arrow operator (strict mode) --- Result: NULL (no wrapping, direct array access on object fails) +-- Arrow operator (strict mode) +--(no wrapping, direct array access on object fails) +SELECT data->0, data->0 IS NULL as isnull FROM test_table; + ?column? | isnull +----------+-------- + | t +(1 row) In lax mode (simplified accessor), when an array operation is performed on a non-array value, @@ -938,17 +963,20 @@ SELECT data->0 FROM test_table; -- Arrow operator (strict mode) - Use SQL:2023 simplified accessor for standard compliance and when you want lax mode and conditional array wrapper + Use SQL:2023 simplified accessor for standard + compliance and when you want lax mode and conditional array wrapper - Use pre-standard subscripting for write operations or when you need direct field access without array processing + Use pre-standard subscripting for write operations + or when you need direct field access without array processing - Use arrow operators when you need text output (->>) or when working with both json and jsonb types + Use arrow operators when you need text output (->>) + or when working with both json and jsonb types