public inbox for [email protected]
help / color / mirror / Atom feedFrom: Alexandra Wang <[email protected]>
To: PostgreSQL Hackers <[email protected]>
Cc: Nikita Glukhov <[email protected]>
Cc: jian he <[email protected]>
Cc: Nikita Malakhov <[email protected]>
Cc: Vik Fearing <[email protected]>
Cc: Mark Dilger <[email protected]>
Cc: Matheus Alcantara <[email protected]>
Cc: Peter Eisentraut <[email protected]>
Cc: Andrew Dunstan <[email protected]>
Cc: David E. Wheeler <[email protected]>
Cc: Chao Li <[email protected]>
Subject: Re: SQL:2023 JSON simplified accessor support
Date: Fri, 19 Sep 2025 13:40:32 -0700
Message-ID: <CAK98qZ2EhKC=Z23jNbMX=aGPGi9+n42a8Eiz1rKYmF388UCHUQ@mail.gmail.com> (raw)
In-Reply-To: <CAK98qZ2nezKeQFqPV9GX80mDiLqS2Fh6MRBGz6ox_HkQF86asA@mail.gmail.com>
References: <[email protected]>
<[email protected]>
<CAK98qZ2QGcyJrJAFv9wjY6S8yP9dUVnmG9Gb4OXuzuMMuM1Z5Q@mail.gmail.com>
<[email protected]>
<[email protected]>
<CAK98qZ3Ly6PhRwCVmMKJBba5oHVF9k370MMT2b_gep-SuQfRtg@mail.gmail.com>
<CAK98qZ1za8XgOLY+2hQMPGoxVYFyh=dDkM2dZPVeJK4J6poyvA@mail.gmail.com>
<CAK98qZ0EfrPcv3ZwGdeDiLPEpXYfHOEc8S2D=OCPJGcyWy5d-g@mail.gmail.com>
<CAK98qZ1rZaVNy6ViangQom4iinZVH7=ebqhTsPxMQbN5ZtE9XQ@mail.gmail.com>
<CAFY6G8cY5SUG5L-0ryVpom6HynE49p8-XQ59qkaEgnZZJ-c4Rg@mail.gmail.com>
<CAFY6G8eUGcU3A4AHprgYbSOAOj7+WGhGxS_YP0wd2+aCpZTiNg@mail.gmail.com>
<CAK98qZ14uKKRVFg4ibzMfReYaZD6Byxq8nYnvNNtXNjCfcd8kQ@mail.gmail.com>
<CAHgHdKtL9nNaKXGCLt9gWugVzYWKhoBDQ7NESUwdCBty8kFK-A@mail.gmail.com>
<CAK98qZ1nz6ZZhQqTOCNwRguZE5GsBLW5BQT_k=s7AA6gc2CN_g@mail.gmail.com>
<[email protected]>
<CAN-LCVM6A9z6AzxsEX-vx9=3XgEUU6+zjUqejQw8LfjaRY5P1A@mail.gmail.com>
<CAK98qZ2Pmf6ZSChLq+CEPKJ_8jSa0gFTNJWJTcWbCziDpqa=CA@mail.gmail.com>
<CACJufxEt5bL-xYojenA7x1Fq=-DfhW3KGH=3Zz9TjJ1k95=uuQ@mail.gmail.com>
<CAK98qZ0whQ=c+JGXbGSEBxCtLgy6sf-YGYqsKTAGsS-wt0wj+A@mail.gmail.com>
<CACJufxHqiKbh1RN4-rquYdnS8qK9kEQq=bpt6ED_yo1+OkU8jg@mail.gmail.com>
<CAK98qZ19bC=Qw9rWGOFKyX4B-fg1XQWEbV2OWAawqWC62fx79A@mail.gmail.com>
<CACJufxGW0Uq2Xx2NLexYR410pvLx2+QKBvBjgM+5qHsqvT5BFQ@mail.gmail.com>
<CAK98qZ3CvSfKS5yV3FAtOpWhbWHkB5aFWrngV_wYnwUbmHF4SQ@mail.gmail.com>
<CACJufxG34m9BGnfD9RD5OEohkV3Oh-+7Xf=3epXyHfQj4DPiOw@mail.gmail.com>
<CAK98qZ35eF+9MZuqR4HNrmebyBFdNiNLiLZHpQPB7S7OUk-DDQ@mail.gmail.com>
<[email protected]>
<[email protected]>
<[email protected]>
<CAK98qZ0SUBjETvUT31U01pSKYCuQPznCtb2kf3-jB5+Dh2V03w@mail.gmail.com>
<[email protected]>
<CAK98qZ1GwW_dCHSi7wMV_H+eQ9qdWajUNXmntufHF4Md=xRSsw@mail.gmail.com>
<[email protected]>
<CAK98qZ0v5dkgMZNK-ogC8Cio-6OPSnNVM-4BWa=Jh=JnvZpB-Q@mail.gmail.com>
<CAK98qZ2nezKeQFqPV9GX80mDiLqS2Fh6MRBGz6ox_HkQF86asA@mail.gmail.com>
Hi there,
I've attached v20. It has the following changes:
1. New 0001: It adds test coverage for single-argument functions and
casting for jsonb expressions. This ensures that the relevant behavior
changes become visible in 0005 when field access via dot-notation is
introduced.
Specifically, once member access through dot-notation is enabled for
jsonb, we can no longer write single-argument functions (including
casts) in dot form for jsonb. For example:
Before 0005:
select ('{"a":1}'::jsonb).jsonb_typeof;
jsonb_typeof
--------------
object
(1 row)
select ('[{"name": "alice"}, {"name": "bob"}]'::jsonb).name;
name
--------------------------------------
[{"name": "alice"}, {"name": "bob"}]
(1 row)
After 0005:
select ('{"a":1}'::jsonb).jsonb_typeof;
jsonb_typeof
--------------
(1 row)
select ('[{"name": "alice"}, {"name": "bob"}]'::jsonb).name;
name
------------------
["alice", "bob"]
(1 row)
In the meanwhile, these functions still return correct results through
standard syntax:
test=# select jsonb_typeof(('{"a":1}'::jsonb));
jsonb_typeof
--------------
object
(1 row)
test=# select ('[{"name": "alice"}, {"name": "bob"}]'::jsonb)::name;
name
--------------------------------------
[{"name": "alice"}, {"name": "bob"}]
(1 row)
I don't consider this behavior change a major issue, because the
dot-form for single-argument functions is not standard SQL and seems
to be PostgreSQL-specific. Still, it's worth highlighting here so
users aren't surprised.
2. Refactored 0002: It combines and refactors v19-0001 and v19-0002.
Instead of changing the existing transform() callback in
SubscriptRoutines, it now introduces an additional callback,
transform_partial(). This alternative transform method, used by jsonb,
is more flexible: it accepts a wider range of indirection node types
and can transform only a prefix of the indirection list. This avoids
breaking compatibility for arrays, hstore, and external data types
that supports subscripting.
3. 0003 and 0004 stay unchanged. They are both small and can be squashed
into 0005. I leave them as-is for now for easier review.
4. Added two additional tests in 0005 for assignments using jsonb
dot-notation, showing explicitly that assignment is not yet supported.
5. Removed 0006 (array slicing) and 0007 (wildcard) from the previous
versions, as they need additional work. My immediate goal is to first
reach consensus on the dot-notation implementation.
Best,
Alex
Attachments:
[application/octet-stream] v20-0005-Implement-read-only-dot-notation-for-jsonb.patch (74.4K, ../CAK98qZ2EhKC=Z23jNbMX=aGPGi9+n42a8Eiz1rKYmF388UCHUQ@mail.gmail.com/3-v20-0005-Implement-read-only-dot-notation-for-jsonb.patch)
download | inline diff:
From 089670877f762c140d24af27be27bd7dbccbe883 Mon Sep 17 00:00:00 2001
From: Alexandra Wang <[email protected]>
Date: Tue, 8 Jul 2025 22:18:07 -0700
Subject: [PATCH v20 5/5] Implement read-only dot notation for jsonb
This patch introduces JSONB member access using dot notation that
aligns with the JSON simplified accessor specified in SQL:2023.
Examples:
-- Setup
create table t(x int, y jsonb);
insert into t select 1, '{"a": 1, "b": 42}'::jsonb;
insert into t select 1, '{"a": 2, "b": {"c": 42}}'::jsonb;
insert into t select 1, '{"a": 3, "b": {"c": "42"}, "d":[11, 12]}'::jsonb;
-- Existing syntax in PostgreSQL that predates the SQL standard:
select (t.y)->'b' from t;
select (t.y)->'b'->'c' from t;
select (t.y)->'d'->0 from t;
-- JSON simplified accessor specified by the SQL standard:
select (t.y).b from t;
select (t.y).b.c from t;
select (t.y).d[0] from t;
The SQL standard states that simplified access is equivalent to:
JSON_QUERY (VEP, 'lax $.JC' WITH CONDITIONAL ARRAY WRAPPER NULL ON EMPTY NULL ON ERROR)
where:
VEP = <value expression primary>
JC = <JSON simplified accessor op chain>
For example, the JSON_QUERY equivalents of the above queries are:
select json_query(y, 'lax $.b' WITH CONDITIONAL ARRAY WRAPPER NULL ON EMPTY NULL ON ERROR) from t;
select json_query(y, 'lax $.b.c' WITH CONDITIONAL ARRAY WRAPPER NULL ON EMPTY NULL ON ERROR) from t;
select json_query(y, 'lax $.d[0]' WITH CONDITIONAL ARRAY WRAPPER NULL ON EMPTY NULL ON ERROR) from t;
Implementation details:
This patch extends the existing container subscripting interface to
support container-specific information, namely a JSONPath expression
for jsonb.
During query transformation, if dot-notation is present, a JSONPath
expression is constructed to represent the access chain.
Then during execution, if a JSONPath expression is present in
JsonbSubWorkspace, executes it via JsonPathQuery().
Note that we cannot simply rewrite the accessors into JSON_QUERY()
during transformation, because the original query structure must be
preserved for EXPLAIN and CREATE VIEW.
Co-authored-by: Nikita Glukhov <[email protected]>
Co-authored-by: Alexandra Wang <[email protected]>
Reviewed-by: Andrew Dunstan <[email protected]>
Reviewed-by: Matheus Alcantara <[email protected]>
Reviewed-by: Mark Dilger <[email protected]>
Reviewed-by: Jian He <[email protected]>
Reviewed-by: Vik Fearing <[email protected]>
Reviewed-by: Nikita Malakhov <[email protected]>
Reviewed-by: Peter Eisentraut <[email protected]>
Reviewed-by: Chao Li <[email protected]>
Tested-by: Jelte Fennema-Nio <[email protected]>
---
doc/src/sgml/json.sgml | 301 ++++++++++++
src/backend/catalog/sql_features.txt | 4 +-
src/backend/executor/execExpr.c | 81 ++--
src/backend/nodes/nodeFuncs.c | 12 +
src/backend/utils/adt/jsonbsubs.c | 302 +++++++++++-
src/backend/utils/adt/ruleutils.c | 43 +-
src/include/nodes/primnodes.h | 54 ++-
.../ecpg/test/expected/sql-sqljson.c | 112 ++++-
.../ecpg/test/expected/sql-sqljson.stderr | 100 ++++
.../ecpg/test/expected/sql-sqljson.stdout | 7 +
src/interfaces/ecpg/test/sql/sqljson.pgc | 33 ++
src/test/regress/expected/jsonb.out | 444 +++++++++++++++++-
src/test/regress/sql/jsonb.sql | 114 +++++
src/tools/pgindent/typedefs.list | 1 +
14 files changed, 1526 insertions(+), 82 deletions(-)
diff --git a/doc/src/sgml/json.sgml b/doc/src/sgml/json.sgml
index 206eadb8f7b..4e77e9e9512 100644
--- a/doc/src/sgml/json.sgml
+++ b/doc/src/sgml/json.sgml
@@ -712,6 +712,307 @@ UPDATE table_name SET jsonb_field[1]['a'] = '1';
</para>
</sect2>
+ <sect2 id="jsonb-simplified-accessor">
+ <title>JSON Simplified Accessor</title>
+ <para>
+ 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.
+ </para>
+
+ <para>
+ The SQL:2023 simplified accessor syntax includes:
+ <itemizedlist>
+ <listitem>
+ <para>
+ <emphasis>JSON member accessor:</emphasis> <literal>jsonb_column.field_name</literal>
+ for accessing object fields
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ <emphasis>JSON array accessor:</emphasis> <literal>jsonb_column[index]</literal>
+ for accessing array elements by integer index
+ </para>
+ </listitem>
+ </itemizedlist>
+ These can be chained together: <literal>jsonb_column.field_name[0].nested_field</literal>.
+ When dot notation is present in the accessor chain, the entire chain follows
+ SQL:2023 semantics with lax mode behavior and conditional array wrapper.
+ </para>
+
+ <para>
+ The JSON simplified accessor is semantically equivalent to using
+ <function>JSON_QUERY</function> with the <literal>lax</literal> mode and
+ <literal>WITH CONDITIONAL ARRAY WRAPPER NULL ON EMPTY NULL ON ERROR</literal>
+ options. For example, <literal>json_col.field</literal> is equivalent to
+ <literal>JSON_QUERY(json_col, 'lax $.field' WITH CONDITIONAL ARRAY WRAPPER NULL ON EMPTY NULL ON ERROR)</literal>.
+ The conditional array wrapper wraps multiple results in an array
+ but leaves a single result as-is without wrapping.
+ </para>
+
+ <para>
+ Examples of JSON simplified accessor syntax:
+
+<programlisting>
+
+-- Basic field access
+SELECT ('{"color": "red", "rgb": [255, 0, 0]}'::jsonb).color;
+
+-- Nested field access
+SELECT ('{"user": {"profile": {"settings": {"theme": "dark"}}}}'::jsonb).user.profile.settings.theme;
+
+-- JSON member accessor followed by JSON array accessor (both part of simplified accessor)
+SELECT ('{"repertoire": [{"title": "Swan Lake"}, {"title": "The Nutcracker"}]}'::jsonb).repertoire[1].title;
+
+-- In WHERE clauses
+SELECT * FROM users WHERE profile.preferences.theme = '"dark"';
+
+-- Comparison with other access methods (NOT equivalent - different semantics):
+SELECT json_col['address']['city']; -- Subscripting
+SELECT json_col->'address'->'city'; -- Operator
+SELECT json_col.address.city; -- Simplified accessor (different behavior)
+</programlisting>
+ </para>
+
+ <sect3 id="jsonb-access-method-comparison">
+ <title>Comparison of JSON Access Methods</title>
+ <para>
+ PostgreSQL provides three different approaches for accessing JSON data, each with
+ distinct semantics and behaviors:
+ </para>
+
+ <para>
+ <emphasis>SQL:2023 JSON Simplified Accessor:</emphasis>
+ <itemizedlist>
+ <listitem>
+ <para>
+ Syntax: <literal>json_col.field_name</literal> (member accessor) and
+ <literal>json_col[index]</literal> (array accessor when used with dot notation)
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ Standard SQL:2023 behavior with <literal>lax</literal> mode semantics
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ Automatic array wrapping/unwrapping as specified in the SQL standard
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ When accessing a field from an array, operates on each array element and wraps results in an array;
+ when accessing an array index from a non-array, wraps the value as an array first
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ Triggered when dot notation is present anywhere in the accessor chain
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ Read-only access
+ </para>
+ </listitem>
+ </itemizedlist>
+ </para>
+
+ <para>
+ <emphasis>Pre-standard JSONB Subscripting:</emphasis>
+ <itemizedlist>
+ <listitem>
+ <para>
+ Syntax: <literal>json_col['field_name']</literal> (text-based) and
+ <literal>json_col[index]</literal> (integer-based when no dot notation present)
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ PostgreSQL's original JSONB subscripting behavior (available since version 14)
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ Direct object field and array element access without array wrapping/unwrapping
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ Supports both read and write operations
+ </para>
+ </listitem>
+ </itemizedlist>
+ </para>
+
+ <para>
+ <emphasis>Arrow Operators:</emphasis>
+ <itemizedlist>
+ <listitem>
+ <para>
+ Syntax: <literal>json_col->'field_name'</literal> and <literal>json_col->>'field_name'</literal>
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ PostgreSQL's JSON operators that work with both <literal>json</literal> and <literal>jsonb</literal> types
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ Direct object field and array element access without array wrapping/unwrapping
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ <literal>-></literal> returns jsonb, <literal>->></literal> returns text
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ Read-only access
+ </para>
+ </listitem>
+ </itemizedlist>
+ </para>
+
+ <para>
+ <emphasis>Key Semantic Differences:</emphasis> The most important distinctions are
+ how these methods handle member access from arrays and array access from non-array values.
+ </para>
+
+ <para>
+ <emphasis>Member Access from Arrays:</emphasis>
+<programlisting>
+-- Setup data
+INSERT INTO test_table VALUES
+ ('{"brightness": 80}'), -- Object case
+ ('[{"brightness": 45}, {"brightness": 90}]'); -- Array case
+
+-- 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)
+</programlisting>
+
+ In the array case, the simplified accessor applies the field access to each array element
+ (unwrapping) and conditionally wraps the results in an array, while subscripting and arrow operators
+ attempt direct field access on the array itself (which returns NULL since
+ arrays don't have named fields).
+ </para>
+
+ <para>
+ <emphasis>Array Access from Objects (Lax Mode Behavior):</emphasis>
+<programlisting>
+-- Setup data
+INSERT INTO test_table VALUES ('{"weather": "sunny", "temperature": "72F"}');
+
+-- Different behaviors when accessing [0] on a non-array value:
+SELECT data[0] FROM test_table; -- Simplified accessor (lax mode, if dots present elsewhere)
+-- Result: {"weather": "sunny", "temperature": "72F"} (object wrapped as array, [0] returns entire object)
+
+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)
+</programlisting>
+
+ In lax mode (simplified accessor), when an array operation is performed on a non-array value,
+ the value is first wrapped in an array, then the operation proceeds. In strict mode
+ (pre-standard methods), the operation fails and returns NULL.
+ </para>
+
+ <para>
+ <emphasis>When to Use Each Method:</emphasis>
+ <itemizedlist>
+ <listitem>
+ <para>
+ Use <emphasis>SQL:2023 simplified accessor</emphasis> for standard compliance and when you want lax mode and conditional array wrapper
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ Use <emphasis>pre-standard subscripting</emphasis> for write operations or when you need direct field access without array processing
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ Use <emphasis>arrow operators</emphasis> when you need text output (<literal>->></literal>) or when working with both <literal>json</literal> and <literal>jsonb</literal> types
+ </para>
+ </listitem>
+ </itemizedlist>
+ </para>
+ </sect3>
+
+ <sect3 id="jsonb-accessor-best-practices">
+ <title>Best Practices: Avoid Mixing Access Methods</title>
+ <para>
+ <emphasis>Important:</emphasis> Do not mix SQL:2023 simplified accessor syntax
+ with pre-standard subscripting syntax in the same accessor chain. These
+ methods have subtly different semantics and are not interchangeable aliases.
+ Mixing them can lead to confusion and code that is difficult to understand.
+ </para>
+
+ <para>
+ <emphasis>Recommended - Consistent simplified accessor:</emphasis>
+<programlisting>
+-- All parts use simplified accessor (standard behavior)
+SELECT data.location.coordinates.latitude FROM table; -- Good
+SELECT data.repertoire[0].title FROM table; -- Good
+SELECT data.users[1].profile.email FROM table; -- Good
+</programlisting>
+ </para>
+
+ <para>
+ <emphasis>Recommended - Consistent pre-standard subscripting:</emphasis>
+<programlisting>
+-- All parts use pre-standard subscripting
+SELECT data['location']['coordinates']['latitude'] FROM table; -- Good
+SELECT data[0]['title'] FROM table; -- Good (when no dots present)
+SELECT data['users'][1]['profile']['email'] FROM table; -- Good
+</programlisting>
+ </para>
+
+ <para>
+ <emphasis>Not recommended - Mixed syntax:</emphasis>
+<programlisting>
+-- Mixing simplified accessor with pre-standard subscripting
+SELECT data.location['latitude'] FROM table; -- Avoid
+SELECT data['repertoire'][0].title FROM table; -- Avoid
+</programlisting>
+ While these mixed forms work as designed, they can be very confusing
+ because the simplified accessor cannot handle text-based subscripts like `['field']`.
+ This forces a fallback to pre-standard semantics for those specific parts,
+ creating a chain that switches between lax mode (for dot notation) and
+ strict mode (for text subscripts) within the same accessor expression.
+ </para>
+
+ <para>
+ Choose one approach consistently throughout your accessor chain to ensure
+ predictable and maintainable code.
+ </para>
+
+ <para>
+ <emphasis>Current Implementation:</emphasis> The current implementation supports
+ JSON member accessors (dot notation) and JSON array accessors (integer subscripts)
+ as defined in the SQL:2023 simplified accessor specification.
+ Advanced features from the SQL:2023 specification, such as wildcard member
+ accessors and item method accessors, are not yet implemented.
+ </para>
+ </sect3>
+ </sect2>
+
<sect2 id="datatype-json-transforms">
<title>Transforms</title>
diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt
index ebe85337c28..457e993305e 100644
--- a/src/backend/catalog/sql_features.txt
+++ b/src/backend/catalog/sql_features.txt
@@ -568,8 +568,8 @@ T838 JSON_TABLE: PLAN DEFAULT clause NO
T839 Formatted cast of datetimes to/from character strings NO
T840 Hex integer literals in SQL/JSON path language YES
T851 SQL/JSON: optional keywords for default syntax YES
-T860 SQL/JSON simplified accessor: column reference only NO
-T861 SQL/JSON simplified accessor: case-sensitive JSON member accessor NO
+T860 SQL/JSON simplified accessor: column reference only YES
+T861 SQL/JSON simplified accessor: case-sensitive JSON member accessor YES
T862 SQL/JSON simplified accessor: wildcard member accessor NO
T863 SQL/JSON simplified accessor: single-quoted string literal as member accessor NO
T864 SQL/JSON simplified accessor NO
diff --git a/src/backend/executor/execExpr.c b/src/backend/executor/execExpr.c
index f1569879b52..385c8d0cefe 100644
--- a/src/backend/executor/execExpr.c
+++ b/src/backend/executor/execExpr.c
@@ -3320,50 +3320,59 @@ ExecInitSubscriptingRef(ExprEvalStep *scratch, SubscriptingRef *sbsref,
state->steps_len - 1);
}
- /* Evaluate upper subscripts */
- i = 0;
- foreach(lc, sbsref->refupperindexpr)
+ /* Evaluate upper subscripts, unless refjsonbpath is used for execution */
+ if (!sbsref->refjsonbpath)
{
- Expr *e = (Expr *) lfirst(lc);
-
- /* When slicing, individual subscript bounds can be omitted */
- if (!e)
- {
- sbsrefstate->upperprovided[i] = false;
- sbsrefstate->upperindexnull[i] = true;
- }
- else
+ i = 0;
+ foreach(lc, sbsref->refupperindexpr)
{
- sbsrefstate->upperprovided[i] = true;
- /* Each subscript is evaluated into appropriate array entry */
- ExecInitExprRec(e, state,
- &sbsrefstate->upperindex[i],
- &sbsrefstate->upperindexnull[i]);
+ Expr *e = (Expr *) lfirst(lc);
+
+ /* When slicing, individual subscript bounds can be omitted */
+ if (!e)
+ {
+ sbsrefstate->upperprovided[i] = false;
+ sbsrefstate->upperindexnull[i] = true;
+ }
+ else
+ {
+ sbsrefstate->upperprovided[i] = true;
+ /* Each subscript is evaluated into appropriate array entry */
+ ExecInitExprRec(e, state,
+ &sbsrefstate->upperindex[i],
+ &sbsrefstate->upperindexnull[i]);
+ }
+ i++;
}
- i++;
}
- /* Evaluate lower subscripts similarly */
- i = 0;
- foreach(lc, sbsref->reflowerindexpr)
+ /*
+ * Evaluate lower subscripts similarly, unless refjsonbpath is used for
+ * execution
+ */
+ if (!sbsref->refjsonbpath)
{
- Expr *e = (Expr *) lfirst(lc);
-
- /* When slicing, individual subscript bounds can be omitted */
- if (!e)
- {
- sbsrefstate->lowerprovided[i] = false;
- sbsrefstate->lowerindexnull[i] = true;
- }
- else
+ i = 0;
+ foreach(lc, sbsref->reflowerindexpr)
{
- sbsrefstate->lowerprovided[i] = true;
- /* Each subscript is evaluated into appropriate array entry */
- ExecInitExprRec(e, state,
- &sbsrefstate->lowerindex[i],
- &sbsrefstate->lowerindexnull[i]);
+ Expr *e = (Expr *) lfirst(lc);
+
+ /* When slicing, individual subscript bounds can be omitted */
+ if (!e)
+ {
+ sbsrefstate->lowerprovided[i] = false;
+ sbsrefstate->lowerindexnull[i] = true;
+ }
+ else
+ {
+ sbsrefstate->lowerprovided[i] = true;
+ /* Each subscript is evaluated into appropriate array entry */
+ ExecInitExprRec(e, state,
+ &sbsrefstate->lowerindex[i],
+ &sbsrefstate->lowerindexnull[i]);
+ }
+ i++;
}
- i++;
}
/* SBSREF_SUBSCRIPTS checks and converts all the subscripts at once */
diff --git a/src/backend/nodes/nodeFuncs.c b/src/backend/nodes/nodeFuncs.c
index 7bc823507f1..d1bd575d9bd 100644
--- a/src/backend/nodes/nodeFuncs.c
+++ b/src/backend/nodes/nodeFuncs.c
@@ -284,6 +284,13 @@ exprType(const Node *expr)
case T_PlaceHolderVar:
type = exprType((Node *) ((const PlaceHolderVar *) expr)->phexpr);
break;
+ case T_FieldAccessorExpr:
+ /*
+ * FieldAccessorExpr is not evaluable. Treat it as TEXT for collation,
+ * deparsing, and similar purposes, since it represents a JSON field name.
+ */
+ type = TEXTOID;
+ break;
default:
elog(ERROR, "unrecognized node type: %d", (int) nodeTag(expr));
type = InvalidOid; /* keep compiler quiet */
@@ -1146,6 +1153,9 @@ exprSetCollation(Node *expr, Oid collation)
case T_MergeSupportFunc:
((MergeSupportFunc *) expr)->msfcollid = collation;
break;
+ case T_FieldAccessorExpr:
+ ((FieldAccessorExpr *) expr)->faecollid = collation;
+ break;
case T_SubscriptingRef:
((SubscriptingRef *) expr)->refcollid = collation;
break;
@@ -2129,6 +2139,7 @@ expression_tree_walker_impl(Node *node,
case T_SortGroupClause:
case T_CTESearchClause:
case T_MergeSupportFunc:
+ case T_FieldAccessorExpr:
/* primitive node types with no expression subnodes */
break;
case T_WithCheckOption:
@@ -3008,6 +3019,7 @@ expression_tree_mutator_impl(Node *node,
case T_SortGroupClause:
case T_CTESearchClause:
case T_MergeSupportFunc:
+ case T_FieldAccessorExpr:
return copyObject(node);
case T_WithCheckOption:
{
diff --git a/src/backend/utils/adt/jsonbsubs.c b/src/backend/utils/adt/jsonbsubs.c
index 516146d1146..bdfe6a310de 100644
--- a/src/backend/utils/adt/jsonbsubs.c
+++ b/src/backend/utils/adt/jsonbsubs.c
@@ -15,21 +15,30 @@
#include "postgres.h"
#include "executor/execExpr.h"
+#include "nodes/makefuncs.h"
#include "nodes/nodeFuncs.h"
#include "nodes/subscripting.h"
#include "parser/parse_coerce.h"
#include "parser/parse_expr.h"
#include "utils/builtins.h"
#include "utils/jsonb.h"
+#include "utils/jsonpath.h"
-/* SubscriptingRefState.workspace for jsonb subscripting execution */
+/*
+ * SubscriptingRefState.workspace for generic jsonb subscripting execution.
+ *
+ * Stores state for both jsonb simple subscripting and dot notation access.
+ * Dot notation additionally uses `jsonpath` for JsonPath evaluation.
+ */
typedef struct JsonbSubWorkspace
{
bool expectArray; /* jsonb root is expected to be an array */
Oid *indexOid; /* OID of coerced subscript expression, could
* be only integer or text */
Datum *index; /* Subscript values in Datum format */
+ JsonPath *jsonpath; /* JsonPath for dot notation execution via
+ * JsonPathQuery() */
} JsonbSubWorkspace;
static Node *
@@ -96,6 +105,233 @@ coerce_jsonpath_subscript_to_int4_or_text(ParseState *pstate, Node *subExpr)
return subExpr;
}
+/*
+ * During transformation, determine whether to build a JsonPath
+ * for JsonPathQuery() execution.
+ *
+ * JsonPath is needed if the indirection list includes:
+ * - String-based access (dot notation)
+ * - Slice-based subscripting (when isSlice is true)
+ *
+ * Otherwise, simple jsonb subscripting is enough.
+ */
+static bool
+jsonb_check_jsonpath_needed(List *indirection)
+{
+ ListCell *lc;
+
+ foreach(lc, indirection)
+ {
+ Node *accessor = lfirst(lc);
+
+ if (IsA(accessor, String))
+ return true;
+ else
+ Assert(IsA(accessor, A_Indices));
+ }
+
+ return false;
+}
+
+/*
+ * Helper functions for constructing JsonPath expressions.
+ *
+ * The make_jsonpath_item_* functions create various types of JsonPathParseItem
+ * nodes, which are used to build JsonPath expressions for jsonb simplified
+ * accessor.
+ */
+
+static JsonPathParseItem *
+make_jsonpath_item(JsonPathItemType type)
+{
+ JsonPathParseItem *v = palloc(sizeof(*v));
+
+ v->type = type;
+ v->next = NULL;
+
+ return v;
+}
+
+/*
+ * Convert a constant integer expression into a JsonPathParseItem.
+ *
+ * The input expression must be a non-null constant of type INT4. Returns NULL otherwise.
+ * This function constructs a jpiNumeric item for use in JsonPath and appends a matching
+ * Const(INT4) node to the given expression list for use in EXPLAIN, views, etc.
+ *
+ * Parameters:
+ * - pstate: parse state context
+ * - expr: input expression node
+ * - exprs: list of expression nodes (updated in place)
+ */
+static JsonPathParseItem *
+make_jsonpath_item_expr(ParseState *pstate, Node *expr, List **exprs)
+{
+ Const *cnst;
+
+ expr = transformExpr(pstate, expr, pstate->p_expr_kind);
+
+ if (IsA(expr, Const))
+ {
+ cnst = (Const *) expr;
+ if (cnst->consttype == INT4OID && !(cnst->constisnull))
+ {
+ JsonPathParseItem *jpi = make_jsonpath_item(jpiNumeric);
+
+ jpi->value.numeric =
+ DatumGetNumeric(DirectFunctionCall1(int4_numeric, Int32GetDatum(cnst->constvalue)));
+
+ *exprs = lappend(*exprs, makeConst(INT4OID, -1, InvalidOid, 4,
+ Int32GetDatum(cnst->constvalue), false, true));
+
+ return jpi;
+ }
+ }
+
+ return NULL;
+}
+
+/*
+ * Constructs a JsonPath expression from a list of indirections.
+ * This function is used when jsonb subscripting involves dot notation,
+ * requiring JsonPath-based evaluation.
+ *
+ * The function modifies the indirection list in place, removing processed
+ * elements as it converts them into JsonPath components, as follows:
+ * - String keys (dot notation) -> jpiKey items.
+ * - Array indices -> jpiIndexArray items.
+ *
+ * In addition to building the JsonPath expression, this function populates
+ * the following fields of the given SubscriptingRef:
+ * - refjsonbpath: the generated JsonPath
+ * - refupperindexpr: upper index expressions (object keys or array indexes)
+ * - reflowerindexpr: lower index expressions, remains NIL as slices are not supported.
+ *
+ * Parameters:
+ * - pstate: Parse state context.
+ * - indirection: List of subscripting expressions (modified in-place).
+ * - sbsref: SubscriptingRef node to update
+ */
+static int
+jsonb_subscript_make_jsonpath(ParseState *pstate, List *indirection, SubscriptingRef *sbsref)
+{
+ JsonPathParseResult jpres;
+ JsonPathParseItem *path = make_jsonpath_item(jpiRoot);
+ ListCell *lc;
+ Datum jsp;
+ int pathlen = 0;
+
+ sbsref->refupperindexpr = NIL;
+ sbsref->reflowerindexpr = NIL;
+ sbsref->refjsonbpath = NULL;
+
+ jpres.expr = path;
+ jpres.lax = true;
+
+ foreach(lc, indirection)
+ {
+ Node *accessor = lfirst(lc);
+ JsonPathParseItem *jpi;
+
+ if (IsA(accessor, String))
+ {
+ char *field = strVal(accessor);
+ FieldAccessorExpr *accessor_expr;
+
+ jpi = make_jsonpath_item(jpiKey);
+ jpi->value.string.val = field;
+ jpi->value.string.len = strlen(field);
+
+ accessor_expr = makeNode(FieldAccessorExpr);
+ accessor_expr->type = T_FieldAccessorExpr;
+ accessor_expr->fieldname = field;
+
+ sbsref->refupperindexpr = lappend(sbsref->refupperindexpr, accessor_expr);
+ }
+ else if (IsA(accessor, A_Indices))
+ {
+ A_Indices *ai = castNode(A_Indices, accessor);
+
+ if (!ai->is_slice)
+ {
+ JsonPathParseItem *jpi_from = NULL;
+
+ Assert(ai->uidx && !ai->lidx);
+ jpi_from = make_jsonpath_item_expr(pstate, ai->uidx, &sbsref->refupperindexpr);
+ if (jpi_from == NULL)
+ {
+ /*
+ * Break out of the loop if the subscript is not a
+ * non-null integer constant, so that we can fall back to
+ * jsonb subscripting logic.
+ *
+ * This is needed to handle cases with mixed usage of SQL
+ * standard json simplified accessor syntax and PostgreSQL
+ * jsonb subscripting syntax, e.g:
+ *
+ * select (jb).a['b'].c from jsonb_table;
+ *
+ * where dot-notation (.a and .c) is the SQL standard json
+ * simplified accessor syntax, and the ['b'] subscript is
+ * the PostgreSQL jsonb subscripting syntax, because 'b'
+ * is not a non-null constant integer and cannot be used
+ * for json array access.
+ *
+ * In this case, we cannot create a JsonPath item, so we
+ * break out of the loop and let
+ * jsonb_subscript_transform() handle this indirection as
+ * a PostgreSQL jsonb subscript.
+ */
+ break;
+ }
+
+ jpi = make_jsonpath_item(jpiIndexArray);
+ jpi->value.array.nelems = 1;
+ jpi->value.array.elems = palloc(sizeof(jpi->value.array.elems[0]));
+
+ jpi->value.array.elems[0].from = jpi_from;
+ jpi->value.array.elems[0].to = NULL;
+ }
+ else
+ {
+ Node *expr = ai->uidx ? ai->uidx : ai->lidx;
+
+ ereport(ERROR,
+ (errcode(ERRCODE_DATATYPE_MISMATCH),
+ errmsg("jsonb subscript does not support slices"),
+ parser_errposition(pstate, exprLocation(expr))));
+ }
+ }
+ else
+ {
+ /*
+ * Unexpected node type in indirection list. This should not
+ * happen with current grammar, but we handle it defensively by
+ * breaking out of the loop rather than crashing. In case of
+ * future grammar changes that might introduce new node types,
+ * this allows us to create a jsonpath from as many indirection
+ * elements as we can and let transformIndirection() fallback to
+ * alternative logic to handle the remaining indirection elements.
+ */
+ Assert(false); /* not reachable */
+ break;
+ }
+
+ /* append path item */
+ path->next = jpi;
+ path = jpi;
+ pathlen++;
+ }
+
+ if (pathlen != 0)
+ {
+ jsp = jsonPathFromParseResult(&jpres, 0, NULL);
+ sbsref->refjsonbpath = (Node *) makeConst(JSONPATHOID, -1, InvalidOid, -1, jsp, false, false);
+ }
+
+ return pathlen;
+}
+
/*
* Finish parse analysis of a SubscriptingRef expression for a jsonb.
*
@@ -111,9 +347,29 @@ jsonb_subscript_transform(SubscriptingRef *sbsref,
List *upperIndexpr = NIL;
ListCell *idx;
+ /* Determine the result type of the subscripting operation; always jsonb */
+ sbsref->refrestype = JSONBOID;
+ sbsref->reftypmod = -1;
+
+ if (jsonb_check_jsonpath_needed(indirection))
+ {
+ int pathlen;
+
+ pathlen = jsonb_subscript_make_jsonpath(pstate, indirection, sbsref);
+ if (sbsref->refjsonbpath)
+ return pathlen;
+ }
+
/*
- * Transform and convert the subscript expressions. Jsonb subscripting
- * does not support slices, look only at the upper index.
+ * We reach here only in two cases: (a) the JSON simplified accessor is
+ * not needed at all (for example, a plain array subscript like [1] or
+ * object key access like ['a']), or (b) jsonb_subscript_make_jsonpath()
+ * was called but could not complete the JsonPath construction (for
+ * example, when mixing dot notation with non-integer subscripts like
+ * (jb)['a'].b where 'a' is not a constant integer).
+ *
+ * In both cases we fall back to pre-standard jsonb subscripting, coercing
+ * each subscript to array index or object key as needed.
*/
foreach(idx, indirection)
{
@@ -160,10 +416,6 @@ jsonb_subscript_transform(SubscriptingRef *sbsref,
sbsref->refupperindexpr = upperIndexpr;
sbsref->reflowerindexpr = NIL;
- /* Determine the result type of the subscripting operation; always jsonb */
- sbsref->refrestype = JSONBOID;
- sbsref->reftypmod = -1;
-
return list_length(upperIndexpr);
}
@@ -216,7 +468,7 @@ jsonb_subscript_check_subscripts(ExprState *state,
* For jsonb fetch and assign functions we need to provide path in
* text format. Convert if it's not already text.
*/
- if (workspace->indexOid[i] == INT4OID)
+ if (!workspace->jsonpath && workspace->indexOid[i] == INT4OID)
{
Datum datum = sbsrefstate->upperindex[i];
char *cs = DatumGetCString(DirectFunctionCall1(int4out, datum));
@@ -244,17 +496,32 @@ jsonb_subscript_fetch(ExprState *state,
{
SubscriptingRefState *sbsrefstate = op->d.sbsref.state;
JsonbSubWorkspace *workspace = (JsonbSubWorkspace *) sbsrefstate->workspace;
- Jsonb *jsonbSource;
/* Should not get here if source jsonb (or any subscript) is null */
Assert(!(*op->resnull));
- jsonbSource = DatumGetJsonbP(*op->resvalue);
- *op->resvalue = jsonb_get_element(jsonbSource,
- workspace->index,
- sbsrefstate->numupper,
- op->resnull,
- false);
+ if (workspace->jsonpath)
+ {
+ bool empty = false;
+ bool error = false;
+
+ *op->resvalue = JsonPathQuery(*op->resvalue, workspace->jsonpath,
+ JSW_CONDITIONAL,
+ &empty, &error, NULL,
+ NULL);
+
+ *op->resnull = empty || error;
+ }
+ else
+ {
+ Jsonb *jsonbSource = DatumGetJsonbP(*op->resvalue);
+
+ *op->resvalue = jsonb_get_element(jsonbSource,
+ workspace->index,
+ sbsrefstate->numupper,
+ op->resnull,
+ false);
+ }
}
/*
@@ -362,7 +629,7 @@ jsonb_exec_setup(const SubscriptingRef *sbsref,
{
JsonbSubWorkspace *workspace;
ListCell *lc;
- int nupper = sbsref->refupperindexpr->length;
+ int nupper = list_length(sbsref->refupperindexpr);
char *ptr;
/* Allocate type-specific workspace with space for per-subscript data */
@@ -371,6 +638,9 @@ jsonb_exec_setup(const SubscriptingRef *sbsref,
workspace->expectArray = false;
ptr = ((char *) workspace) + MAXALIGN(sizeof(JsonbSubWorkspace));
+ if (sbsref->refjsonbpath)
+ workspace->jsonpath = DatumGetJsonPathP(castNode(Const, sbsref->refjsonbpath)->constvalue);
+
/*
* This coding assumes sizeof(Datum) >= sizeof(Oid), else we might
* misalign the indexOid pointer
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 0408a95941d..c899734c2e3 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -9329,10 +9329,13 @@ get_rule_expr(Node *node, deparse_context *context,
* Parenthesize the argument unless it's a simple Var or a
* FieldSelect. (In particular, if it's another
* SubscriptingRef, we *must* parenthesize to avoid
- * confusion.)
+ * confusion.) Always add parenthesis if JSON simplified
+ * accessor is used, for now.
*/
- need_parens = !IsA(sbsref->refexpr, Var) &&
- !IsA(sbsref->refexpr, FieldSelect);
+ need_parens = (!IsA(sbsref->refexpr, Var) &&
+ !IsA(sbsref->refexpr, FieldSelect)) ||
+ sbsref->refjsonbpath;
+
if (need_parens)
appendStringInfoChar(buf, '(');
get_rule_expr((Node *) sbsref->refexpr, context, showimplicit);
@@ -13005,17 +13008,35 @@ printSubscripts(SubscriptingRef *sbsref, deparse_context *context)
lowlist_item = list_head(sbsref->reflowerindexpr); /* could be NULL */
foreach(uplist_item, sbsref->refupperindexpr)
{
- appendStringInfoChar(buf, '[');
- if (lowlist_item)
+ Node *upper = (Node *) lfirst(uplist_item);
+
+ if (upper && IsA(upper, FieldAccessorExpr))
{
+ FieldAccessorExpr *fae = (FieldAccessorExpr *) upper;
+
+ /* Use dot-notation for field access */
+ appendStringInfoChar(buf, '.');
+ appendStringInfoString(buf, quote_identifier(fae->fieldname));
+
+ /* Skip matching low index — field access doesn't use slices */
+ if (lowlist_item)
+ lowlist_item = lnext(sbsref->reflowerindexpr, lowlist_item);
+ }
+ else
+ {
+ /* Use JSONB array subscripting */
+ appendStringInfoChar(buf, '[');
+ if (lowlist_item)
+ {
+ /* If subexpression is NULL, get_rule_expr prints nothing */
+ get_rule_expr((Node *) lfirst(lowlist_item), context, false);
+ appendStringInfoChar(buf, ':');
+ lowlist_item = lnext(sbsref->reflowerindexpr, lowlist_item);
+ }
/* If subexpression is NULL, get_rule_expr prints nothing */
- get_rule_expr((Node *) lfirst(lowlist_item), context, false);
- appendStringInfoChar(buf, ':');
- lowlist_item = lnext(sbsref->reflowerindexpr, lowlist_item);
+ get_rule_expr(upper, context, false);
+ appendStringInfoChar(buf, ']');
}
- /* If subexpression is NULL, get_rule_expr prints nothing */
- get_rule_expr((Node *) lfirst(uplist_item), context, false);
- appendStringInfoChar(buf, ']');
}
}
diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h
index 6dfca3cb35b..7e89621bd65 100644
--- a/src/include/nodes/primnodes.h
+++ b/src/include/nodes/primnodes.h
@@ -708,18 +708,30 @@ typedef struct SubscriptingRef
int32 reftypmod pg_node_attr(query_jumble_ignore);
/* collation of result, or InvalidOid if none */
Oid refcollid pg_node_attr(query_jumble_ignore);
- /* expressions that evaluate to upper container indexes */
+
+ /*
+ * expressions that evaluate to upper container indexes or expressions
+ * that are collected but not evaluated when refjsonbpath is set.
+ */
List *refupperindexpr;
/*
- * expressions that evaluate to lower container indexes, or NIL for single
- * container element.
+ * expressions that evaluate to lower container indexes, or NIL for a
+ * single container element, or expressions that are collected but not
+ * evaluated when refjsonbpath is set.
*/
List *reflowerindexpr;
/* the expression that evaluates to a container value */
Expr *refexpr;
/* expression for the source value, or NULL if fetch */
Expr *refassgnexpr;
+
+ /*
+ * container-specific extra information, currently used only by jsonb.
+ * stores a JsonPath expression when jsonb dot notation is used. NULL for
+ * simple subscripting.
+ */
+ Node *refjsonbpath;
} SubscriptingRef;
/*
@@ -2371,4 +2383,40 @@ typedef struct OnConflictExpr
List *exclRelTlist; /* tlist of the EXCLUDED pseudo relation */
} OnConflictExpr;
+/*
+ * FieldAccessorExpr - represents a single object member access using dot-notation
+ * in JSON simplified accessor syntax (e.g., jsonb_col.a).
+ *
+ * These nodes appear as list elements in SubscriptingRef.refupperindexpr to
+ * indicate JSON object key access. They are not evaluable expressions by
+ * themselves but serve as placeholders to preserve source-level syntax for
+ * rule rewriting and deparsing (e.g., in EXPLAIN and view definitions).
+ * Execution is handled by the enclosing SubscriptingRef.
+ *
+ * If dot-notation is used in a SubscriptingRef, the JSON path is represented
+ * as a flat list of FieldAccessorExpr nodes (for object field access), Const
+ * nodes (for array indexes), and NULLs (for omitted slice bounds), rather than
+ * through nested expression trees.
+ *
+ * Note: The flat representation avoids nested FieldAccessorExpr chains,
+ * simplifying evaluation and enabling standard-compliant behavior such as
+ * conditional array wrapping. This avoids the need for position-aware
+ * wrapping/unwrapping logic during execution.
+ *
+ * For example, in the expression:
+ * ('{"a": [{"b": 1}]}'::jsonb).a[0].b
+ * the SubscriptingRef will contain:
+ * - refexpr: the base expression (the jsonb value)
+ * - refupperindexpr: [FieldAccessorExpr("a"), Const(0),
+ * FieldAccessorExpr("b")]
+ * - reflowerindexpr: [NULL, NULL, NULL] (slice lower bounds not used here)
+ */
+typedef struct FieldAccessorExpr
+{
+ NodeTag type;
+ char *fieldname; /* name of the JSONB object field accessed via
+ * dot notation */
+ Oid faecollid pg_node_attr(query_jumble_ignore);
+} FieldAccessorExpr;
+
#endif /* PRIMNODES_H */
diff --git a/src/interfaces/ecpg/test/expected/sql-sqljson.c b/src/interfaces/ecpg/test/expected/sql-sqljson.c
index 39221f9ea5d..e6a7ece6dab 100644
--- a/src/interfaces/ecpg/test/expected/sql-sqljson.c
+++ b/src/interfaces/ecpg/test/expected/sql-sqljson.c
@@ -417,12 +417,122 @@ if (sqlca.sqlcode < 0) sqlprint();}
for (int i = 0; i < sizeof(is_json); i++)
printf("Found is_json[%d]: %s\n", i, is_json[i] ? "true" : "false");
- { ECPGdisconnect(__LINE__, "CURRENT");
+ { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "select json ( ( '{\"a\": {\"b\": 1, \"c\": 2}}' :: jsonb ) . \"a\" )", ECPGt_EOIT,
+ ECPGt_char,(json),(long)1024,(long)1,(1024)*sizeof(char),
+ ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT);
#line 118 "sqljson.pgc"
if (sqlca.sqlcode < 0) sqlprint();}
#line 118 "sqljson.pgc"
+ printf("Found json=%s\n", json);
+
+ { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "select json ( '{\"a\": {\"b\": 1, \"c\": 2}}' :: jsonb . \"a\" )", ECPGt_EOIT,
+ ECPGt_char,(json),(long)1024,(long)1,(1024)*sizeof(char),
+ ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT);
+#line 121 "sqljson.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 121 "sqljson.pgc"
+
+ // error
+
+ { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "select json ( ( '{\"a\": {\"b\": 1, \"c\": 2}}' :: jsonb ) . a )", ECPGt_EOIT,
+ ECPGt_char,(json),(long)1024,(long)1,(1024)*sizeof(char),
+ ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT);
+#line 124 "sqljson.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 124 "sqljson.pgc"
+
+ printf("Found json=%s\n", json);
+
+ { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "select json ( ( '{\"a\": {\"b\": 1, \"c\": 2}}' :: jsonb ) . a . b )", ECPGt_EOIT,
+ ECPGt_char,(json),(long)1024,(long)1,(1024)*sizeof(char),
+ ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT);
+#line 127 "sqljson.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 127 "sqljson.pgc"
+
+ printf("Found json=%s\n", json);
+
+ { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "select json ( coalesce ( json ( ( '{\"a\": {\"b\": 1, \"c\": 2}}' :: jsonb ) . c ) , 'null' ) )", ECPGt_EOIT,
+ ECPGt_char,(json),(long)1024,(long)1,(1024)*sizeof(char),
+ ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT);
+#line 130 "sqljson.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 130 "sqljson.pgc"
+
+ printf("Found json=%s\n", json);
+
+ { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "select json ( ( '{\"a\": {\"b\": 1, \"c\": 2}, \"b\": [{\"x\": 1}, {\"x\": [12, {\"y\":1}]}]}' :: jsonb ) . b [ 0 ] )", ECPGt_EOIT,
+ ECPGt_char,(json),(long)1024,(long)1,(1024)*sizeof(char),
+ ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT);
+#line 133 "sqljson.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 133 "sqljson.pgc"
+
+ printf("Found json=%s\n", json);
+
+ { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "select json ( ( '{\"a\": {\"b\": 1, \"c\": 2}, \"b\": [{\"x\": 1}, {\"x\": [12, {\"y\":1}]}]}' :: jsonb ) . b . x )", ECPGt_EOIT,
+ ECPGt_char,(json),(long)1024,(long)1,(1024)*sizeof(char),
+ ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT);
+#line 136 "sqljson.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 136 "sqljson.pgc"
+
+ printf("Found json=%s\n", json);
+
+ { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "select json ( ( '{\"a\": {\"b\": 1, \"c\": 2}, \"b\": [{\"x\": 1}, {\"x\": [12, {\"y\":1}]}]}' :: jsonb ) [ 'b' ] [ 0 ] )", ECPGt_EOIT,
+ ECPGt_char,(json),(long)1024,(long)1,(1024)*sizeof(char),
+ ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT);
+#line 139 "sqljson.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 139 "sqljson.pgc"
+
+ printf("Found json=%s\n", json);
+
+ { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "select json ( ( '{\"a\": {\"b\": 1, \"c\": 2}, \"b\": [{\"x\": 1}, {\"x\": [12, {\"y\":1}]}]}' :: jsonb ) . b [ 1 ] . x [ 0 : ] )", ECPGt_EOIT,
+ ECPGt_char,(json),(long)1024,(long)1,(1024)*sizeof(char),
+ ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT);
+#line 142 "sqljson.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 142 "sqljson.pgc"
+
+ // error
+
+ { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "select json ( ( '{\"a\": {\"b\": 1, \"c\": 2}, \"b\": [{\"x\": 1}, {\"x\": [12, {\"y\":1}]}]}' :: jsonb ) . * )", ECPGt_EOIT,
+ ECPGt_char,(json),(long)1024,(long)1,(1024)*sizeof(char),
+ ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT);
+#line 145 "sqljson.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 145 "sqljson.pgc"
+
+ // error
+
+ { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "select json ( ( '{\"a\": {\"b\": 1, \"c\": 2}, \"b\": [{\"x\": 1}, {\"x\": [12, {\"y\":1}]}]}' :: jsonb ) [ 'b' ] [ 0 : ] )", ECPGt_EOIT,
+ ECPGt_char,(json),(long)1024,(long)1,(1024)*sizeof(char),
+ ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT);
+#line 148 "sqljson.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 148 "sqljson.pgc"
+
+ // error
+
+ { ECPGdisconnect(__LINE__, "CURRENT");
+#line 151 "sqljson.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 151 "sqljson.pgc"
+
return 0;
}
diff --git a/src/interfaces/ecpg/test/expected/sql-sqljson.stderr b/src/interfaces/ecpg/test/expected/sql-sqljson.stderr
index e55a95dd711..19f8c58af06 100644
--- a/src/interfaces/ecpg/test/expected/sql-sqljson.stderr
+++ b/src/interfaces/ecpg/test/expected/sql-sqljson.stderr
@@ -268,5 +268,105 @@ SQL error: cannot use type jsonb in RETURNING clause of JSON_SERIALIZE() on line
[NO_PID]: sqlca: code: 0, state: 00000
[NO_PID]: ecpg_get_data on line 102: RESULT: f offset: -1; array: no
[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 118: query: select json ( ( '{"a": {"b": 1, "c": 2}}' :: jsonb ) . "a" ); with 0 parameter(s) on connection ecpg1_regression
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 118: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 118: correctly got 1 tuples with 1 fields
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_get_data on line 118: RESULT: {"b": 1, "c": 2} offset: -1; array: no
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 121: query: select json ( '{"a": {"b": 1, "c": 2}}' :: jsonb . "a" ); with 0 parameter(s) on connection ecpg1_regression
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 121: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_check_PQresult on line 121: bad response - ERROR: schema "jsonb" does not exist
+LINE 1: select json ( '{"a": {"b": 1, "c": 2}}' :: jsonb . "a" )
+ ^
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: raising sqlstate 3F000 (sqlcode -400): schema "jsonb" does not exist on line 121
+[NO_PID]: sqlca: code: -400, state: 3F000
+SQL error: schema "jsonb" does not exist on line 121
+[NO_PID]: ecpg_execute on line 124: query: select json ( ( '{"a": {"b": 1, "c": 2}}' :: jsonb ) . a ); with 0 parameter(s) on connection ecpg1_regression
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 124: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 124: correctly got 1 tuples with 1 fields
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_get_data on line 124: RESULT: {"b": 1, "c": 2} offset: -1; array: no
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 127: query: select json ( ( '{"a": {"b": 1, "c": 2}}' :: jsonb ) . a . b ); with 0 parameter(s) on connection ecpg1_regression
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 127: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 127: correctly got 1 tuples with 1 fields
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_get_data on line 127: RESULT: 1 offset: -1; array: no
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 130: query: select json ( coalesce ( json ( ( '{"a": {"b": 1, "c": 2}}' :: jsonb ) . c ) , 'null' ) ); with 0 parameter(s) on connection ecpg1_regression
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 130: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 130: correctly got 1 tuples with 1 fields
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_get_data on line 130: RESULT: null offset: -1; array: no
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 133: query: select json ( ( '{"a": {"b": 1, "c": 2}, "b": [{"x": 1}, {"x": [12, {"y":1}]}]}' :: jsonb ) . b [ 0 ] ); with 0 parameter(s) on connection ecpg1_regression
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 133: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 133: correctly got 1 tuples with 1 fields
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_get_data on line 133: RESULT: {"x": 1} offset: -1; array: no
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 136: query: select json ( ( '{"a": {"b": 1, "c": 2}, "b": [{"x": 1}, {"x": [12, {"y":1}]}]}' :: jsonb ) . b . x ); with 0 parameter(s) on connection ecpg1_regression
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 136: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 136: correctly got 1 tuples with 1 fields
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_get_data on line 136: RESULT: [1, [12, {"y": 1}]] offset: -1; array: no
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 139: query: select json ( ( '{"a": {"b": 1, "c": 2}, "b": [{"x": 1}, {"x": [12, {"y":1}]}]}' :: jsonb ) [ 'b' ] [ 0 ] ); with 0 parameter(s) on connection ecpg1_regression
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 139: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 139: correctly got 1 tuples with 1 fields
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_get_data on line 139: RESULT: {"x": 1} offset: -1; array: no
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 142: query: select json ( ( '{"a": {"b": 1, "c": 2}, "b": [{"x": 1}, {"x": [12, {"y":1}]}]}' :: jsonb ) . b [ 1 ] . x [ 0 : ] ); with 0 parameter(s) on connection ecpg1_regression
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 142: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_check_PQresult on line 142: bad response - ERROR: jsonb subscript does not support slices
+LINE 1: ..., {"x": [12, {"y":1}]}]}' :: jsonb ) . b [ 1 ] . x [ 0 : ] )
+ ^
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: raising sqlstate 42804 (sqlcode -400): jsonb subscript does not support slices on line 142
+[NO_PID]: sqlca: code: -400, state: 42804
+SQL error: jsonb subscript does not support slices on line 142
+[NO_PID]: ecpg_execute on line 145: query: select json ( ( '{"a": {"b": 1, "c": 2}, "b": [{"x": 1}, {"x": [12, {"y":1}]}]}' :: jsonb ) . * ); with 0 parameter(s) on connection ecpg1_regression
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 145: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_check_PQresult on line 145: bad response - ERROR: row expansion via "*" is not supported here
+LINE 1: select json ( ( '{"a": {"b": 1, "c": 2}, "b": [{"x": 1}, {"x...
+ ^
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: raising sqlstate 0A000 (sqlcode -400): row expansion via "*" is not supported here on line 145
+[NO_PID]: sqlca: code: -400, state: 0A000
+SQL error: row expansion via "*" is not supported here on line 145
+[NO_PID]: ecpg_execute on line 148: query: select json ( ( '{"a": {"b": 1, "c": 2}, "b": [{"x": 1}, {"x": [12, {"y":1}]}]}' :: jsonb ) [ 'b' ] [ 0 : ] ); with 0 parameter(s) on connection ecpg1_regression
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 148: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_check_PQresult on line 148: bad response - ERROR: jsonb subscript does not support slices
+LINE 1: ...x": 1}, {"x": [12, {"y":1}]}]}' :: jsonb ) [ 'b' ] [ 0 : ] )
+ ^
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: raising sqlstate 42804 (sqlcode -400): jsonb subscript does not support slices on line 148
+[NO_PID]: sqlca: code: -400, state: 42804
+SQL error: jsonb subscript does not support slices on line 148
[NO_PID]: ecpg_finish: connection ecpg1_regression closed
[NO_PID]: sqlca: code: 0, state: 00000
diff --git a/src/interfaces/ecpg/test/expected/sql-sqljson.stdout b/src/interfaces/ecpg/test/expected/sql-sqljson.stdout
index 83f8df13e5a..442d36931f1 100644
--- a/src/interfaces/ecpg/test/expected/sql-sqljson.stdout
+++ b/src/interfaces/ecpg/test/expected/sql-sqljson.stdout
@@ -28,3 +28,10 @@ Found is_json[4]: false
Found is_json[5]: false
Found is_json[6]: true
Found is_json[7]: false
+Found json={"b": 1, "c": 2}
+Found json={"b": 1, "c": 2}
+Found json=1
+Found json=null
+Found json={"x": 1}
+Found json=[1, [12, {"y": 1}]]
+Found json={"x": 1}
diff --git a/src/interfaces/ecpg/test/sql/sqljson.pgc b/src/interfaces/ecpg/test/sql/sqljson.pgc
index ddcbcc3b3cb..57a9bff424d 100644
--- a/src/interfaces/ecpg/test/sql/sqljson.pgc
+++ b/src/interfaces/ecpg/test/sql/sqljson.pgc
@@ -115,6 +115,39 @@ EXEC SQL END DECLARE SECTION;
for (int i = 0; i < sizeof(is_json); i++)
printf("Found is_json[%d]: %s\n", i, is_json[i] ? "true" : "false");
+ EXEC SQL SELECT JSON(('{"a": {"b": 1, "c": 2}}'::jsonb)."a") INTO :json;
+ printf("Found json=%s\n", json);
+
+ EXEC SQL SELECT JSON('{"a": {"b": 1, "c": 2}}'::jsonb."a") INTO :json;
+ // error
+
+ EXEC SQL SELECT JSON(('{"a": {"b": 1, "c": 2}}'::jsonb).a) INTO :json;
+ printf("Found json=%s\n", json);
+
+ EXEC SQL SELECT JSON(('{"a": {"b": 1, "c": 2}}'::jsonb).a.b) INTO :json;
+ printf("Found json=%s\n", json);
+
+ EXEC SQL SELECT JSON(COALESCE(JSON(('{"a": {"b": 1, "c": 2}}'::jsonb).c), 'null')) INTO :json;
+ printf("Found json=%s\n", json);
+
+ EXEC SQL SELECT JSON(('{"a": {"b": 1, "c": 2}, "b": [{"x": 1}, {"x": [12, {"y":1}]}]}'::jsonb).b[0]) INTO :json;
+ printf("Found json=%s\n", json);
+
+ EXEC SQL SELECT JSON(('{"a": {"b": 1, "c": 2}, "b": [{"x": 1}, {"x": [12, {"y":1}]}]}'::jsonb).b.x) INTO :json;
+ printf("Found json=%s\n", json);
+
+ EXEC SQL SELECT JSON(('{"a": {"b": 1, "c": 2}, "b": [{"x": 1}, {"x": [12, {"y":1}]}]}'::jsonb)['b'][0]) INTO :json;
+ printf("Found json=%s\n", json);
+
+ EXEC SQL SELECT JSON(('{"a": {"b": 1, "c": 2}, "b": [{"x": 1}, {"x": [12, {"y":1}]}]}'::jsonb).b[1].x[0:]) INTO :json;
+ // error
+
+ EXEC SQL SELECT JSON(('{"a": {"b": 1, "c": 2}, "b": [{"x": 1}, {"x": [12, {"y":1}]}]}'::jsonb).*) INTO :json;
+ // error
+
+ EXEC SQL SELECT JSON(('{"a": {"b": 1, "c": 2}, "b": [{"x": 1}, {"x": [12, {"y":1}]}]}'::jsonb)['b'][0:]) INTO :json;
+ // error
+
EXEC SQL DISCONNECT;
return 0;
diff --git a/src/test/regress/expected/jsonb.out b/src/test/regress/expected/jsonb.out
index fb47c8a893a..685f229e682 100644
--- a/src/test/regress/expected/jsonb.out
+++ b/src/test/regress/expected/jsonb.out
@@ -4989,6 +4989,12 @@ select ('123'::jsonb)['a'];
(1 row)
+select ('123'::jsonb).a;
+ a
+---
+
+(1 row)
+
select ('123'::jsonb)[0];
jsonb
-------
@@ -5001,12 +5007,24 @@ select ('123'::jsonb)[NULL];
(1 row)
+select ('123'::jsonb).NULL;
+ null
+------
+
+(1 row)
+
select ('{"a": 1}'::jsonb)['a'];
jsonb
-------
1
(1 row)
+select ('{"a": 1}'::jsonb).a;
+ a
+---
+ 1
+(1 row)
+
select ('{"a": 1}'::jsonb)[0];
jsonb
-------
@@ -5019,6 +5037,12 @@ select ('{"a": 1}'::jsonb)['not_exist'];
(1 row)
+select ('{"a": 1}'::jsonb)."not_exist";
+ not_exist
+-----------
+
+(1 row)
+
select ('{"a": 1}'::jsonb)[NULL];
jsonb
-------
@@ -5031,6 +5055,12 @@ select ('[1, "2", null]'::jsonb)['a'];
(1 row)
+select ('[1, "2", null]'::jsonb).a;
+ a
+---
+
+(1 row)
+
select ('[1, "2", null]'::jsonb)[0];
jsonb
-------
@@ -5043,6 +5073,12 @@ select ('[1, "2", null]'::jsonb)['1'];
"2"
(1 row)
+select ('[1, "2", null]'::jsonb)."1";
+ 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];
@@ -5072,6 +5108,12 @@ select ('[1, "2", null]'::jsonb)[1]['a'];
(1 row)
+select ('[1, "2", null]'::jsonb)[1].a;
+ a
+---
+
+(1 row)
+
select ('[1, "2", null]'::jsonb)[1][0];
jsonb
-------
@@ -5084,54 +5126,126 @@ select ('{"a": 1, "b": "c", "d": [1, 2, 3]}'::jsonb)['b'];
"c"
(1 row)
+select ('{"a": 1, "b": "c", "d": [1, 2, 3]}'::jsonb).b;
+ b
+-----
+ "c"
+(1 row)
+
select ('{"a": 1, "b": "c", "d": [1, 2, 3]}'::jsonb)['d'];
jsonb
-----------
[1, 2, 3]
(1 row)
+select ('{"a": 1, "b": "c", "d": [1, 2, 3]}'::jsonb).d;
+ d
+-----------
+ [1, 2, 3]
+(1 row)
+
select ('{"a": 1, "b": "c", "d": [1, 2, 3]}'::jsonb)['d'][1];
jsonb
-------
2
(1 row)
+select ('{"a": 1, "b": "c", "d": [1, 2, 3]}'::jsonb).d[1];
+ d
+---
+ 2
+(1 row)
+
select ('{"a": 1, "b": "c", "d": [1, 2, 3]}'::jsonb)['d']['a'];
jsonb
-------
(1 row)
+select ('{"a": 1, "b": "c", "d": [1, 2, 3]}'::jsonb).d['a'];
+ d
+---
+
+(1 row)
+
+select ('{"a": 1, "b": "c", "d": [1, 2, 3]}'::jsonb).d.a;
+ a
+---
+
+(1 row)
+
+select ('{"a": 1, "b": "c", "d": {"a": [1, 2, 3]}}'::jsonb).d['a'][0];
+ d
+---
+ 1
+(1 row)
+
+select ('{"a": 1, "b": "c", "d": {"a": [1, 2, 3]}}'::jsonb).d.a[0];
+ a
+---
+ 1
+(1 row)
+
select ('{"a": {"a1": {"a2": "aaa"}}, "b": "bbb", "c": "ccc"}'::jsonb)['a']['a1'];
jsonb
---------------
{"a2": "aaa"}
(1 row)
+select ('{"a": {"a1": {"a2": "aaa"}}, "b": "bbb", "c": "ccc"}'::jsonb).a.a1;
+ a1
+---------------
+ {"a2": "aaa"}
+(1 row)
+
select ('{"a": {"a1": {"a2": "aaa"}}, "b": "bbb", "c": "ccc"}'::jsonb)['a']['a1']['a2'];
jsonb
-------
"aaa"
(1 row)
+select ('{"a": {"a1": {"a2": "aaa"}}, "b": "bbb", "c": "ccc"}'::jsonb).a.a1.a2;
+ a2
+-------
+ "aaa"
+(1 row)
+
select ('{"a": {"a1": {"a2": "aaa"}}, "b": "bbb", "c": "ccc"}'::jsonb)['a']['a1']['a2']['a3'];
jsonb
-------
(1 row)
+select ('{"a": {"a1": {"a2": "aaa"}}, "b": "bbb", "c": "ccc"}'::jsonb).a.a1.a2.a3;
+ a3
+----
+
+(1 row)
+
select ('{"a": ["a1", {"b1": ["aaa", "bbb", "ccc"]}], "b": "bb"}'::jsonb)['a'][1]['b1'];
jsonb
-----------------------
["aaa", "bbb", "ccc"]
(1 row)
+select ('{"a": ["a1", {"b1": ["aaa", "bbb", "ccc"]}], "b": "bb"}'::jsonb).a[1].b1;
+ b1
+-----------------------
+ ["aaa", "bbb", "ccc"]
+(1 row)
+
select ('{"a": ["a1", {"b1": ["aaa", "bbb", "ccc"]}], "b": "bb"}'::jsonb)['a'][1]['b1'][2];
jsonb
-------
"ccc"
(1 row)
+select ('{"a": ["a1", {"b1": ["aaa", "bbb", "ccc"]}], "b": "bb"}'::jsonb).a[1].b1[2];
+ b1
+-------
+ "ccc"
+(1 row)
+
-- slices are not supported
select ('{"a": 1}'::jsonb)['a':'b'];
ERROR: jsonb subscript does not support slices
@@ -5841,7 +5955,7 @@ select jsonb_typeof('{"a":1}'::jsonb);
select ('{"a":1}'::jsonb).jsonb_typeof;
jsonb_typeof
--------------
- object
+
(1 row)
select jsonb_array_length('["a", "b", "c"]'::jsonb);
@@ -5853,7 +5967,7 @@ select jsonb_array_length('["a", "b", "c"]'::jsonb);
select ('["a", "b", "c"]'::jsonb).jsonb_array_length;
jsonb_array_length
--------------------
- 3
+
(1 row)
select jsonb_object_keys('{"a":1, "b":2}'::jsonb);
@@ -5866,9 +5980,8 @@ select jsonb_object_keys('{"a":1, "b":2}'::jsonb);
select ('{"a":1, "b":2}'::jsonb).jsonb_object_keys;
jsonb_object_keys
-------------------
- a
- b
-(2 rows)
+
+(1 row)
-- cast jsonb to other types as (jsonb)::type and (jsonb).type
select ('123.45'::jsonb)::numeric;
@@ -5880,7 +5993,7 @@ select ('123.45'::jsonb)::numeric;
select ('123.45'::jsonb).numeric;
numeric
---------
- 123.45
+
(1 row)
select ('[{"name": "alice"}, {"name": "bob"}]'::jsonb)::name;
@@ -5890,9 +6003,9 @@ select ('[{"name": "alice"}, {"name": "bob"}]'::jsonb)::name;
(1 row)
select ('[{"name": "alice"}, {"name": "bob"}]'::jsonb).name;
- name
---------------------------------------
- [{"name": "alice"}, {"name": "bob"}]
+ name
+------------------
+ ["alice", "bob"]
(1 row)
select ('true'::jsonb)::bool;
@@ -5904,7 +6017,7 @@ select ('true'::jsonb)::bool;
select ('true'::jsonb).bool;
bool
------
- t
+
(1 row)
select ('{"text": "hello"}'::jsonb)::text;
@@ -5914,8 +6027,313 @@ select ('{"text": "hello"}'::jsonb)::text;
(1 row)
select ('{"text": "hello"}'::jsonb).text;
- text
--------------------
- {"text": "hello"}
+ text
+---------
+ "hello"
+(1 row)
+
+-- dot notation
+CREATE TABLE test_jsonb_dot_notation AS
+SELECT '{"a": [1, 2, {"b": "c"}, {"b": "d", "e": "f", "x": {"y": "yyy", "z": "zzz"}}], "b": [3, 4, {"b": "g", "x": {"y": "YYY", "z": "ZZZ"}}]}'::jsonb jb;
+SELECT (jb).a FROM test_jsonb_dot_notation;
+ a
+-------------------------------------------------------------------------
+ [1, 2, {"b": "c"}, {"b": "d", "e": "f", "x": {"y": "yyy", "z": "zzz"}}]
+(1 row)
+
+SELECT (jb)."a" FROM test_jsonb_dot_notation; -- double quote should work
+ a
+-------------------------------------------------------------------------
+ [1, 2, {"b": "c"}, {"b": "d", "e": "f", "x": {"y": "yyy", "z": "zzz"}}]
+(1 row)
+
+SELECT (jb).'a' FROM test_jsonb_dot_notation; -- single quote should not work
+ERROR: syntax error at or near "'a'"
+LINE 1: SELECT (jb).'a' FROM test_jsonb_dot_notation;
+ ^
+select (jb)[0].a from test_jsonb_dot_notation; -- returns same result as (jb).a
+ a
+-------------------------------------------------------------------------
+ [1, 2, {"b": "c"}, {"b": "d", "e": "f", "x": {"y": "yyy", "z": "zzz"}}]
+(1 row)
+
+select (jb)[1].a from test_jsonb_dot_notation; -- returns NULL
+ a
+---
+
+(1 row)
+
+SELECT (jb).b FROM test_jsonb_dot_notation;
+ b
+---------------------------------------------------
+ [3, 4, {"b": "g", "x": {"y": "YYY", "z": "ZZZ"}}]
+(1 row)
+
+SELECT (jb).c FROM test_jsonb_dot_notation;
+ c
+---
+
+(1 row)
+
+SELECT (jb).a.b FROM test_jsonb_dot_notation;
+ b
+------------
+ ["c", "d"]
+(1 row)
+
+SELECT (jb).a[2].b FROM test_jsonb_dot_notation;
+ b
+-----
+ "c"
+(1 row)
+
+SELECT (jb).a.x.y FROM test_jsonb_dot_notation;
+ y
+-------
+ "yyy"
+(1 row)
+
+SELECT (jb).b.x.z FROM test_jsonb_dot_notation;
+ z
+-------
+ "ZZZ"
+(1 row)
+
+SELECT (jb).a.b.c FROM test_jsonb_dot_notation;
+ c
+---
+
+(1 row)
+
+SELECT ((jb).b)[:].x FROM test_jsonb_dot_notation t; -- fails
+ERROR: jsonb subscript does not support slices
+SELECT (jb).a.* FROM test_jsonb_dot_notation; -- fails
+ERROR: type jsonb is not composite
+-- assignment is not supported
+UPDATE test_jsonb_dot_notation SET jb.a = '1';
+ERROR: cannot assign to field "a" of column "jb" because its type jsonb is not a composite type
+LINE 1: UPDATE test_jsonb_dot_notation SET jb.a = '1';
+ ^
+UPDATE test_jsonb_dot_notation SET (jb).a = '1';
+ERROR: syntax error at or near "."
+LINE 1: UPDATE test_jsonb_dot_notation SET (jb).a = '1';
+ ^
+-- explains should work
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (t.jb).a FROM test_jsonb_dot_notation t;
+ QUERY PLAN
+----------------------------------------------
+ Seq Scan on public.test_jsonb_dot_notation t
+ Output: (jb).a
+(2 rows)
+
+SELECT (t.jb).a FROM test_jsonb_dot_notation t;
+ a
+-------------------------------------------------------------------------
+ [1, 2, {"b": "c"}, {"b": "d", "e": "f", "x": {"y": "yyy", "z": "zzz"}}]
+(1 row)
+
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb).a[1] FROM test_jsonb_dot_notation;
+ QUERY PLAN
+--------------------------------------------
+ Seq Scan on public.test_jsonb_dot_notation
+ Output: (jb).a[1]
+(2 rows)
+
+SELECT (jb).a[1] FROM test_jsonb_dot_notation;
+ a
+---
+ 2
+(1 row)
+
+-- views should work
+CREATE VIEW test_jsonb_dot_notation_v1 AS SELECT (jb).a[3].x.y FROM test_jsonb_dot_notation;
+\sv test_jsonb_dot_notation_v1
+CREATE OR REPLACE VIEW public.test_jsonb_dot_notation_v1 AS
+ SELECT (jb).a[3].x.y AS y
+ FROM test_jsonb_dot_notation
+-- mixed syntax
+DROP VIEW test_jsonb_dot_notation_v1;
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb)['a'].b FROM test_jsonb_dot_notation;
+ QUERY PLAN
+--------------------------------------------
+ Seq Scan on public.test_jsonb_dot_notation
+ Output: (jb['a'::text]).b
+(2 rows)
+
+SELECT (jb)['a'].b FROM test_jsonb_dot_notation; -- returns an array due to lax mode
+ b
+------------
+ ["c", "d"]
+(1 row)
+
+CREATE VIEW public.test_jsonb_dot_notation_v1 AS
+SELECT (jb)['a'].b FROM test_jsonb_dot_notation;
+\sv test_jsonb_dot_notation_v1
+CREATE OR REPLACE VIEW public.test_jsonb_dot_notation_v1 AS
+ SELECT (jb['a'::text]).b AS b
+ FROM test_jsonb_dot_notation
+SELECT * from test_jsonb_dot_notation_v1;
+ b
+------------
+ ["c", "d"]
+(1 row)
+
+DROP VIEW public.test_jsonb_dot_notation_v1;
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb).a['b'] FROM test_jsonb_dot_notation;
+ QUERY PLAN
+--------------------------------------------
+ Seq Scan on public.test_jsonb_dot_notation
+ Output: ((jb).a)['b'::text]
+(2 rows)
+
+SELECT (jb).a['b'] FROM test_jsonb_dot_notation; -- returns NULL because ['b'] looks for strict match in an object
+ a
+---
+
+(1 row)
+
+CREATE VIEW public.test_jsonb_dot_notation_v1 AS
+SELECT (jb).a['b'] FROM test_jsonb_dot_notation;
+\sv test_jsonb_dot_notation_v1
+CREATE OR REPLACE VIEW public.test_jsonb_dot_notation_v1 AS
+ SELECT ((jb).a)['b'::text] AS a
+ FROM test_jsonb_dot_notation
+SELECT * from test_jsonb_dot_notation_v1;
+ a
+---
+
+(1 row)
+
+DROP VIEW public.test_jsonb_dot_notation_v1;
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb).b.x['z'] FROM test_jsonb_dot_notation;
+ QUERY PLAN
+--------------------------------------------
+ Seq Scan on public.test_jsonb_dot_notation
+ Output: ((jb).b.x)['z'::text]
+(2 rows)
+
+SELECT (jb).b.x['z'] FROM test_jsonb_dot_notation; -- warnings
+ x
+-------
+ "ZZZ"
+(1 row)
+
+CREATE VIEW public.test_jsonb_dot_notation_v1 AS
+SELECT (jb).b.x['z'] FROM test_jsonb_dot_notation;
+\sv test_jsonb_dot_notation_v1
+CREATE OR REPLACE VIEW public.test_jsonb_dot_notation_v1 AS
+ SELECT ((jb).b.x)['z'::text] AS x
+ FROM test_jsonb_dot_notation
+SELECT * from test_jsonb_dot_notation_v1;
+ x
+-------
+ "ZZZ"
+(1 row)
+
+DROP VIEW public.test_jsonb_dot_notation_v1;
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb)['b'].x['z'] FROM test_jsonb_dot_notation;
+ QUERY PLAN
+--------------------------------------------
+ Seq Scan on public.test_jsonb_dot_notation
+ Output: ((jb['b'::text]).x)['z'::text]
+(2 rows)
+
+SELECT (jb)['b'].x['z'] FROM test_jsonb_dot_notation; -- warnings
+ x
+-------
+ "ZZZ"
+(1 row)
+
+CREATE VIEW public.test_jsonb_dot_notation_v1 AS
+SELECT (jb)['b'].x['z'] FROM test_jsonb_dot_notation;
+\sv test_jsonb_dot_notation_v1
+CREATE OR REPLACE VIEW public.test_jsonb_dot_notation_v1 AS
+ SELECT ((jb['b'::text]).x)['z'::text] AS x
+ FROM test_jsonb_dot_notation
+SELECT * from test_jsonb_dot_notation_v1;
+ x
+-------
+ "ZZZ"
+(1 row)
+
+DROP VIEW public.test_jsonb_dot_notation_v1;
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb).b['x'].z FROM test_jsonb_dot_notation;
+ QUERY PLAN
+--------------------------------------------
+ Seq Scan on public.test_jsonb_dot_notation
+ Output: (((jb).b)['x'::text]).z
+(2 rows)
+
+SELECT (jb).b['x'].z FROM test_jsonb_dot_notation; -- returns NULL
+ z
+---
+
+(1 row)
+
+CREATE VIEW public.test_jsonb_dot_notation_v1 AS
+SELECT (jb).b['x'].z FROM test_jsonb_dot_notation;
+\sv test_jsonb_dot_notation_v1
+CREATE OR REPLACE VIEW public.test_jsonb_dot_notation_v1 AS
+ SELECT (((jb).b)['x'::text]).z AS z
+ FROM test_jsonb_dot_notation
+SELECT * from test_jsonb_dot_notation_v1;
+ z
+---
+
+(1 row)
+
+DROP VIEW public.test_jsonb_dot_notation_v1;
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb).b['x']['z'] FROM test_jsonb_dot_notation;
+ QUERY PLAN
+--------------------------------------------
+ Seq Scan on public.test_jsonb_dot_notation
+ Output: ((jb).b)['x'::text]['z'::text]
+(2 rows)
+
+SELECT (jb).b['x']['z'] FROM test_jsonb_dot_notation; -- returns NULL
+ b
+---
+
+(1 row)
+
+CREATE VIEW public.test_jsonb_dot_notation_v1 AS
+SELECT (jb).b['x']['z'] FROM test_jsonb_dot_notation;
+\sv test_jsonb_dot_notation_v1
+CREATE OR REPLACE VIEW public.test_jsonb_dot_notation_v1 AS
+ SELECT ((jb).b)['x'::text]['z'::text] AS b
+ FROM test_jsonb_dot_notation
+SELECT * from test_jsonb_dot_notation_v1;
+ b
+---
+
+(1 row)
+
+DROP VIEW public.test_jsonb_dot_notation_v1;
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb)['b']['x'].z FROM test_jsonb_dot_notation;
+ QUERY PLAN
+--------------------------------------------
+ Seq Scan on public.test_jsonb_dot_notation
+ Output: (jb['b'::text]['x'::text]).z
+(2 rows)
+
+SELECT (jb)['b']['x'].z FROM test_jsonb_dot_notation; -- returns NULL
+ z
+---
+
+(1 row)
+
+CREATE VIEW public.test_jsonb_dot_notation_v1 AS
+SELECT (jb)['b']['x'].z FROM test_jsonb_dot_notation;
+\sv test_jsonb_dot_notation_v1
+CREATE OR REPLACE VIEW public.test_jsonb_dot_notation_v1 AS
+ SELECT (jb['b'::text]['x'::text]).z AS z
+ FROM test_jsonb_dot_notation
+SELECT * from test_jsonb_dot_notation_v1;
+ z
+---
+
(1 row)
+DROP VIEW public.test_jsonb_dot_notation_v1;
+-- clean up
+DROP TABLE test_jsonb_dot_notation;
diff --git a/src/test/regress/sql/jsonb.sql b/src/test/regress/sql/jsonb.sql
index 970ed1cffca..cb0c398d65b 100644
--- a/src/test/regress/sql/jsonb.sql
+++ b/src/test/regress/sql/jsonb.sql
@@ -1304,30 +1304,49 @@ select jsonb_insert('{"a": {"b": "value"}}', '{a, b}', '"new_value"', true);
-- jsonb subscript
select ('123'::jsonb)['a'];
+select ('123'::jsonb).a;
select ('123'::jsonb)[0];
select ('123'::jsonb)[NULL];
+select ('123'::jsonb).NULL;
select ('{"a": 1}'::jsonb)['a'];
+select ('{"a": 1}'::jsonb).a;
select ('{"a": 1}'::jsonb)[0];
select ('{"a": 1}'::jsonb)['not_exist'];
+select ('{"a": 1}'::jsonb)."not_exist";
select ('{"a": 1}'::jsonb)[NULL];
select ('[1, "2", null]'::jsonb)['a'];
+select ('[1, "2", null]'::jsonb).a;
select ('[1, "2", null]'::jsonb)[0];
select ('[1, "2", null]'::jsonb)['1'];
+select ('[1, "2", null]'::jsonb)."1";
select ('[1, "2", null]'::jsonb)[1.0];
select ('[1, "2", null]'::jsonb)[2];
select ('[1, "2", null]'::jsonb)[3];
select ('[1, "2", null]'::jsonb)[-2];
select ('[1, "2", null]'::jsonb)[1]['a'];
+select ('[1, "2", null]'::jsonb)[1].a;
select ('[1, "2", null]'::jsonb)[1][0];
select ('{"a": 1, "b": "c", "d": [1, 2, 3]}'::jsonb)['b'];
+select ('{"a": 1, "b": "c", "d": [1, 2, 3]}'::jsonb).b;
select ('{"a": 1, "b": "c", "d": [1, 2, 3]}'::jsonb)['d'];
+select ('{"a": 1, "b": "c", "d": [1, 2, 3]}'::jsonb).d;
select ('{"a": 1, "b": "c", "d": [1, 2, 3]}'::jsonb)['d'][1];
+select ('{"a": 1, "b": "c", "d": [1, 2, 3]}'::jsonb).d[1];
select ('{"a": 1, "b": "c", "d": [1, 2, 3]}'::jsonb)['d']['a'];
+select ('{"a": 1, "b": "c", "d": [1, 2, 3]}'::jsonb).d['a'];
+select ('{"a": 1, "b": "c", "d": [1, 2, 3]}'::jsonb).d.a;
+select ('{"a": 1, "b": "c", "d": {"a": [1, 2, 3]}}'::jsonb).d['a'][0];
+select ('{"a": 1, "b": "c", "d": {"a": [1, 2, 3]}}'::jsonb).d.a[0];
select ('{"a": {"a1": {"a2": "aaa"}}, "b": "bbb", "c": "ccc"}'::jsonb)['a']['a1'];
+select ('{"a": {"a1": {"a2": "aaa"}}, "b": "bbb", "c": "ccc"}'::jsonb).a.a1;
select ('{"a": {"a1": {"a2": "aaa"}}, "b": "bbb", "c": "ccc"}'::jsonb)['a']['a1']['a2'];
+select ('{"a": {"a1": {"a2": "aaa"}}, "b": "bbb", "c": "ccc"}'::jsonb).a.a1.a2;
select ('{"a": {"a1": {"a2": "aaa"}}, "b": "bbb", "c": "ccc"}'::jsonb)['a']['a1']['a2']['a3'];
+select ('{"a": {"a1": {"a2": "aaa"}}, "b": "bbb", "c": "ccc"}'::jsonb).a.a1.a2.a3;
select ('{"a": ["a1", {"b1": ["aaa", "bbb", "ccc"]}], "b": "bb"}'::jsonb)['a'][1]['b1'];
+select ('{"a": ["a1", {"b1": ["aaa", "bbb", "ccc"]}], "b": "bb"}'::jsonb).a[1].b1;
select ('{"a": ["a1", {"b1": ["aaa", "bbb", "ccc"]}], "b": "bb"}'::jsonb)['a'][1]['b1'][2];
+select ('{"a": ["a1", {"b1": ["aaa", "bbb", "ccc"]}], "b": "bb"}'::jsonb).a[1].b1[2];
-- slices are not supported
select ('{"a": 1}'::jsonb)['a':'b'];
@@ -1608,3 +1627,98 @@ select ('true'::jsonb)::bool;
select ('true'::jsonb).bool;
select ('{"text": "hello"}'::jsonb)::text;
select ('{"text": "hello"}'::jsonb).text;
+
+-- dot notation
+CREATE TABLE test_jsonb_dot_notation AS
+SELECT '{"a": [1, 2, {"b": "c"}, {"b": "d", "e": "f", "x": {"y": "yyy", "z": "zzz"}}], "b": [3, 4, {"b": "g", "x": {"y": "YYY", "z": "ZZZ"}}]}'::jsonb jb;
+
+SELECT (jb).a FROM test_jsonb_dot_notation;
+SELECT (jb)."a" FROM test_jsonb_dot_notation; -- double quote should work
+SELECT (jb).'a' FROM test_jsonb_dot_notation; -- single quote should not work
+select (jb)[0].a from test_jsonb_dot_notation; -- returns same result as (jb).a
+select (jb)[1].a from test_jsonb_dot_notation; -- returns NULL
+SELECT (jb).b FROM test_jsonb_dot_notation;
+SELECT (jb).c FROM test_jsonb_dot_notation;
+SELECT (jb).a.b FROM test_jsonb_dot_notation;
+SELECT (jb).a[2].b FROM test_jsonb_dot_notation;
+SELECT (jb).a.x.y FROM test_jsonb_dot_notation;
+SELECT (jb).b.x.z FROM test_jsonb_dot_notation;
+SELECT (jb).a.b.c FROM test_jsonb_dot_notation;
+SELECT ((jb).b)[:].x FROM test_jsonb_dot_notation t; -- fails
+SELECT (jb).a.* FROM test_jsonb_dot_notation; -- fails
+
+-- assignment is not supported
+UPDATE test_jsonb_dot_notation SET jb.a = '1';
+UPDATE test_jsonb_dot_notation SET (jb).a = '1';
+
+-- explains should work
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (t.jb).a FROM test_jsonb_dot_notation t;
+SELECT (t.jb).a FROM test_jsonb_dot_notation t;
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb).a[1] FROM test_jsonb_dot_notation;
+SELECT (jb).a[1] FROM test_jsonb_dot_notation;
+
+-- views should work
+CREATE VIEW test_jsonb_dot_notation_v1 AS SELECT (jb).a[3].x.y FROM test_jsonb_dot_notation;
+\sv test_jsonb_dot_notation_v1
+
+-- mixed syntax
+DROP VIEW test_jsonb_dot_notation_v1;
+
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb)['a'].b FROM test_jsonb_dot_notation;
+SELECT (jb)['a'].b FROM test_jsonb_dot_notation; -- returns an array due to lax mode
+CREATE VIEW public.test_jsonb_dot_notation_v1 AS
+SELECT (jb)['a'].b FROM test_jsonb_dot_notation;
+\sv test_jsonb_dot_notation_v1
+SELECT * from test_jsonb_dot_notation_v1;
+DROP VIEW public.test_jsonb_dot_notation_v1;
+
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb).a['b'] FROM test_jsonb_dot_notation;
+SELECT (jb).a['b'] FROM test_jsonb_dot_notation; -- returns NULL because ['b'] looks for strict match in an object
+CREATE VIEW public.test_jsonb_dot_notation_v1 AS
+SELECT (jb).a['b'] FROM test_jsonb_dot_notation;
+\sv test_jsonb_dot_notation_v1
+SELECT * from test_jsonb_dot_notation_v1;
+DROP VIEW public.test_jsonb_dot_notation_v1;
+
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb).b.x['z'] FROM test_jsonb_dot_notation;
+SELECT (jb).b.x['z'] FROM test_jsonb_dot_notation; -- warnings
+CREATE VIEW public.test_jsonb_dot_notation_v1 AS
+SELECT (jb).b.x['z'] FROM test_jsonb_dot_notation;
+\sv test_jsonb_dot_notation_v1
+SELECT * from test_jsonb_dot_notation_v1;
+DROP VIEW public.test_jsonb_dot_notation_v1;
+
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb)['b'].x['z'] FROM test_jsonb_dot_notation;
+SELECT (jb)['b'].x['z'] FROM test_jsonb_dot_notation; -- warnings
+CREATE VIEW public.test_jsonb_dot_notation_v1 AS
+SELECT (jb)['b'].x['z'] FROM test_jsonb_dot_notation;
+\sv test_jsonb_dot_notation_v1
+SELECT * from test_jsonb_dot_notation_v1;
+DROP VIEW public.test_jsonb_dot_notation_v1;
+
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb).b['x'].z FROM test_jsonb_dot_notation;
+SELECT (jb).b['x'].z FROM test_jsonb_dot_notation; -- returns NULL
+CREATE VIEW public.test_jsonb_dot_notation_v1 AS
+SELECT (jb).b['x'].z FROM test_jsonb_dot_notation;
+\sv test_jsonb_dot_notation_v1
+SELECT * from test_jsonb_dot_notation_v1;
+DROP VIEW public.test_jsonb_dot_notation_v1;
+
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb).b['x']['z'] FROM test_jsonb_dot_notation;
+SELECT (jb).b['x']['z'] FROM test_jsonb_dot_notation; -- returns NULL
+CREATE VIEW public.test_jsonb_dot_notation_v1 AS
+SELECT (jb).b['x']['z'] FROM test_jsonb_dot_notation;
+\sv test_jsonb_dot_notation_v1
+SELECT * from test_jsonb_dot_notation_v1;
+DROP VIEW public.test_jsonb_dot_notation_v1;
+
+EXPLAIN (VERBOSE, COSTS OFF) SELECT (jb)['b']['x'].z FROM test_jsonb_dot_notation;
+SELECT (jb)['b']['x'].z FROM test_jsonb_dot_notation; -- returns NULL
+CREATE VIEW public.test_jsonb_dot_notation_v1 AS
+SELECT (jb)['b']['x'].z FROM test_jsonb_dot_notation;
+\sv test_jsonb_dot_notation_v1
+SELECT * from test_jsonb_dot_notation_v1;
+DROP VIEW public.test_jsonb_dot_notation_v1;
+
+-- clean up
+DROP TABLE test_jsonb_dot_notation;
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index e90af5b2ad3..23e38c163c5 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -805,6 +805,7 @@ FdwRoutine
FetchDirection
FetchDirectionKeywords
FetchStmt
+FieldAccessorExpr
FieldSelect
FieldStore
File
--
2.39.5 (Apple Git-154)
[application/octet-stream] v20-0004-Extract-coerce_jsonpath_subscript.patch (5.5K, ../CAK98qZ2EhKC=Z23jNbMX=aGPGi9+n42a8Eiz1rKYmF388UCHUQ@mail.gmail.com/4-v20-0004-Extract-coerce_jsonpath_subscript.patch)
download | inline diff:
From 8128920e7fe6972fba41856e118ad1a8059b8435 Mon Sep 17 00:00:00 2001
From: Alexandra Wang <[email protected]>
Date: Tue, 8 Jul 2025 22:18:07 -0700
Subject: [PATCH v20 4/5] Extract coerce_jsonpath_subscript()
This is a preparation step for a future commit that will reuse the
aforementioned function.
Co-authored-by: Nikita Glukhov <[email protected]>
Co-authored-by: Alexandra Wang <[email protected]>
Reviewed-by: Andrew Dunstan <[email protected]>
Reviewed-by: Matheus Alcantara <[email protected]>
Reviewed-by: Jian He <[email protected]>
Reviewed-by: Chao Li <[email protected]>
---
src/backend/utils/adt/jsonbsubs.c | 128 +++++++++++++++---------------
1 file changed, 64 insertions(+), 64 deletions(-)
diff --git a/src/backend/utils/adt/jsonbsubs.c b/src/backend/utils/adt/jsonbsubs.c
index dfe7da55e8e..516146d1146 100644
--- a/src/backend/utils/adt/jsonbsubs.c
+++ b/src/backend/utils/adt/jsonbsubs.c
@@ -32,6 +32,69 @@ typedef struct JsonbSubWorkspace
Datum *index; /* Subscript values in Datum format */
} JsonbSubWorkspace;
+static Node *
+coerce_jsonpath_subscript_to_int4_or_text(ParseState *pstate, Node *subExpr)
+{
+ Oid subExprType = exprType(subExpr);
+ Oid targetType = InvalidOid;
+
+ if (subExprType != UNKNOWNOID)
+ {
+ const Oid targets[] = {INT4OID, TEXTOID};
+
+ /*
+ * 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.
+ */
+ for (int i = 0; i < lengthof(targets); 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 (OidIsValid(targetType))
+ 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 (!OidIsValid(targetType))
+ 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."),
+ parser_errposition(pstate, exprLocation(subExpr))));
+ }
+ else
+ targetType = TEXTOID;
+
+ /*
+ * We known from can_coerce_type that coercion will succeed, so
+ * coerce_type could be used. Note the implicit coercion context, which is
+ * required to handle subscripts of different types, similar to overloaded
+ * functions.
+ */
+ subExpr = coerce_type(pstate,
+ subExpr, subExprType,
+ targetType, -1,
+ COERCION_IMPLICIT,
+ COERCE_IMPLICIT_CAST,
+ -1);
+
+ return subExpr;
+}
/*
* Finish parse analysis of a SubscriptingRef expression for a jsonb.
@@ -74,71 +137,8 @@ jsonb_subscript_transform(SubscriptingRef *sbsref,
if (ai->uidx)
{
- Oid subExprType = InvalidOid,
- targetType = UNKNOWNOID;
-
subExpr = transformExpr(pstate, ai->uidx, pstate->p_expr_kind);
- subExprType = exprType(subExpr);
-
- if (subExprType != UNKNOWNOID)
- {
- Oid targets[2] = {INT4OID, TEXTOID};
-
- /*
- * 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.
- */
- 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)
- 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."),
- parser_errposition(pstate, exprLocation(subExpr))));
- }
- else
- targetType = TEXTOID;
-
- /*
- * We known from can_coerce_type that coercion will succeed, so
- * coerce_type could be used. Note the implicit coercion context,
- * which is required to handle subscripts of different types,
- * similar to overloaded functions.
- */
- subExpr = coerce_type(pstate,
- subExpr, subExprType,
- targetType, -1,
- COERCION_IMPLICIT,
- COERCE_IMPLICIT_CAST,
- -1);
- if (subExpr == NULL)
- ereport(ERROR,
- (errcode(ERRCODE_DATATYPE_MISMATCH),
- errmsg("jsonb subscript must have text type"),
- parser_errposition(pstate, exprLocation(subExpr))));
+ subExpr = coerce_jsonpath_subscript_to_int4_or_text(pstate, subExpr);
}
else
{
--
2.39.5 (Apple Git-154)
[application/octet-stream] v20-0001-Add-test-coverage-for-indirection-transformation.patch (6.9K, ../CAK98qZ2EhKC=Z23jNbMX=aGPGi9+n42a8Eiz1rKYmF388UCHUQ@mail.gmail.com/5-v20-0001-Add-test-coverage-for-indirection-transformation.patch)
download | inline diff:
From e89e5148504e8a3587f39c27c02895d71f7546ba Mon Sep 17 00:00:00 2001
From: Alexandra Wang <[email protected]>
Date: Thu, 18 Sep 2025 15:54:24 -0700
Subject: [PATCH v20 1/5] Add test coverage for indirection transformation
These tests cover nested arrays of composite data types,
single-argument functions, and casting using dot-notation, providing a
baseline for future enhancements to jsonb dot-notation support.
---
src/test/regress/expected/arrays.out | 26 ++++++--
src/test/regress/expected/jsonb.out | 88 ++++++++++++++++++++++++++++
src/test/regress/sql/arrays.sql | 7 ++-
src/test/regress/sql/jsonb.sql | 18 ++++++
4 files changed, 132 insertions(+), 7 deletions(-)
diff --git a/src/test/regress/expected/arrays.out b/src/test/regress/expected/arrays.out
index 69ea2cf5ad8..e1ab6dc278a 100644
--- a/src/test/regress/expected/arrays.out
+++ b/src/test/regress/expected/arrays.out
@@ -1782,17 +1782,17 @@ SELECT max(f1), min(f1), max(f2), min(f2), max(f3), min(f3) FROM arraggtest;
(1 row)
-- A few simple tests for arrays of composite types
-create type comptype as (f1 int, f2 text);
+create type comptype as (f1 int, f2 text, f3 int[]);
create table comptable (c1 comptype, c2 comptype[]);
-- XXX would like to not have to specify row() construct types here ...
insert into comptable
- values (row(1,'foo'), array[row(2,'bar')::comptype, row(3,'baz')::comptype]);
+ values (row(1,'foo',array[10,20]), array[row(2,'bar',array[30,40])::comptype, row(3,'baz',array[50,60])::comptype]);
-- check that implicitly named array type _comptype isn't a problem
create type _comptype as enum('fooey');
select * from comptable;
- c1 | c2
----------+-----------------------
- (1,foo) | {"(2,bar)","(3,baz)"}
+ c1 | c2
+-------------------+-----------------------------------------------
+ (1,foo,"{10,20}") | {"(2,bar,\"{30,40}\")","(3,baz,\"{50,60}\")"}
(1 row)
select c2[2].f2 from comptable;
@@ -1801,6 +1801,22 @@ select c2[2].f2 from comptable;
baz
(1 row)
+select c2[2].f3 from comptable;
+ f3
+---------
+ {50,60}
+(1 row)
+
+select c2[2].f3[1:2] from comptable;
+ f3
+---------
+ {50,60}
+(1 row)
+
+select c2[1:2].f3[1:2] from comptable;
+ERROR: column notation .f3 applied to type comptype[], which is not a composite type
+LINE 1: select c2[1:2].f3[1:2] from comptable;
+ ^
drop type _comptype;
drop table comptable;
drop type comptype;
diff --git a/src/test/regress/expected/jsonb.out b/src/test/regress/expected/jsonb.out
index 5a1eb18aba2..fb47c8a893a 100644
--- a/src/test/regress/expected/jsonb.out
+++ b/src/test/regress/expected/jsonb.out
@@ -5831,3 +5831,91 @@ select '12345.0000000000000000000000000000000000000000000005'::jsonb::int8;
12345
(1 row)
+-- single argument jsonb functions as jsonb_function(jsonb) and jsonb.jsonb_function
+select jsonb_typeof('{"a":1}'::jsonb);
+ jsonb_typeof
+--------------
+ object
+(1 row)
+
+select ('{"a":1}'::jsonb).jsonb_typeof;
+ jsonb_typeof
+--------------
+ object
+(1 row)
+
+select jsonb_array_length('["a", "b", "c"]'::jsonb);
+ jsonb_array_length
+--------------------
+ 3
+(1 row)
+
+select ('["a", "b", "c"]'::jsonb).jsonb_array_length;
+ jsonb_array_length
+--------------------
+ 3
+(1 row)
+
+select jsonb_object_keys('{"a":1, "b":2}'::jsonb);
+ jsonb_object_keys
+-------------------
+ a
+ b
+(2 rows)
+
+select ('{"a":1, "b":2}'::jsonb).jsonb_object_keys;
+ jsonb_object_keys
+-------------------
+ a
+ b
+(2 rows)
+
+-- cast jsonb to other types as (jsonb)::type and (jsonb).type
+select ('123.45'::jsonb)::numeric;
+ numeric
+---------
+ 123.45
+(1 row)
+
+select ('123.45'::jsonb).numeric;
+ numeric
+---------
+ 123.45
+(1 row)
+
+select ('[{"name": "alice"}, {"name": "bob"}]'::jsonb)::name;
+ name
+--------------------------------------
+ [{"name": "alice"}, {"name": "bob"}]
+(1 row)
+
+select ('[{"name": "alice"}, {"name": "bob"}]'::jsonb).name;
+ name
+--------------------------------------
+ [{"name": "alice"}, {"name": "bob"}]
+(1 row)
+
+select ('true'::jsonb)::bool;
+ bool
+------
+ t
+(1 row)
+
+select ('true'::jsonb).bool;
+ bool
+------
+ t
+(1 row)
+
+select ('{"text": "hello"}'::jsonb)::text;
+ text
+-------------------
+ {"text": "hello"}
+(1 row)
+
+select ('{"text": "hello"}'::jsonb).text;
+ text
+-------------------
+ {"text": "hello"}
+(1 row)
+
diff --git a/src/test/regress/sql/arrays.sql b/src/test/regress/sql/arrays.sql
index 47d62c1d38d..450389831a0 100644
--- a/src/test/regress/sql/arrays.sql
+++ b/src/test/regress/sql/arrays.sql
@@ -555,19 +555,22 @@ SELECT max(f1), min(f1), max(f2), min(f2), max(f3), min(f3) FROM arraggtest;
-- A few simple tests for arrays of composite types
-create type comptype as (f1 int, f2 text);
+create type comptype as (f1 int, f2 text, f3 int[]);
create table comptable (c1 comptype, c2 comptype[]);
-- XXX would like to not have to specify row() construct types here ...
insert into comptable
- values (row(1,'foo'), array[row(2,'bar')::comptype, row(3,'baz')::comptype]);
+ values (row(1,'foo',array[10,20]), array[row(2,'bar',array[30,40])::comptype, row(3,'baz',array[50,60])::comptype]);
-- check that implicitly named array type _comptype isn't a problem
create type _comptype as enum('fooey');
select * from comptable;
select c2[2].f2 from comptable;
+select c2[2].f3 from comptable;
+select c2[2].f3[1:2] from comptable;
+select c2[1:2].f3[1:2] from comptable;
drop type _comptype;
drop table comptable;
diff --git a/src/test/regress/sql/jsonb.sql b/src/test/regress/sql/jsonb.sql
index 57c11acddfe..970ed1cffca 100644
--- a/src/test/regress/sql/jsonb.sql
+++ b/src/test/regress/sql/jsonb.sql
@@ -1590,3 +1590,21 @@ select '12345.0000000000000000000000000000000000000000000005'::jsonb::float8;
select '12345.0000000000000000000000000000000000000000000005'::jsonb::int2;
select '12345.0000000000000000000000000000000000000000000005'::jsonb::int4;
select '12345.0000000000000000000000000000000000000000000005'::jsonb::int8;
+
+-- single argument jsonb functions as jsonb_function(jsonb) and jsonb.jsonb_function
+select jsonb_typeof('{"a":1}'::jsonb);
+select ('{"a":1}'::jsonb).jsonb_typeof;
+select jsonb_array_length('["a", "b", "c"]'::jsonb);
+select ('["a", "b", "c"]'::jsonb).jsonb_array_length;
+select jsonb_object_keys('{"a":1, "b":2}'::jsonb);
+select ('{"a":1, "b":2}'::jsonb).jsonb_object_keys;
+
+-- cast jsonb to other types as (jsonb)::type and (jsonb).type
+select ('123.45'::jsonb)::numeric;
+select ('123.45'::jsonb).numeric;
+select ('[{"name": "alice"}, {"name": "bob"}]'::jsonb)::name;
+select ('[{"name": "alice"}, {"name": "bob"}]'::jsonb).name;
+select ('true'::jsonb)::bool;
+select ('true'::jsonb).bool;
+select ('{"text": "hello"}'::jsonb)::text;
+select ('{"text": "hello"}'::jsonb).text;
--
2.39.5 (Apple Git-154)
[application/octet-stream] v20-0002-Add-an-alternative-transform-function-in-Subscri.patch (15.6K, ../CAK98qZ2EhKC=Z23jNbMX=aGPGi9+n42a8Eiz1rKYmF388UCHUQ@mail.gmail.com/6-v20-0002-Add-an-alternative-transform-function-in-Subscri.patch)
download | inline diff:
From 68e97c36a2e84dd42e39a4ce79a90058890dc1f5 Mon Sep 17 00:00:00 2001
From: Alexandra Wang <[email protected]>
Date: Thu, 18 Sep 2025 16:34:47 -0700
Subject: [PATCH v20 2/5] Add an alternative transform function in
SubscriptRoutines
Add a transform_partial() function pointer to enable processing a
prefix of indirection lists. Data types that support subscripting can
opt to use the transform() function that transforms the full input
indirection list (e.g., arrays, hstore), or can opt to use the
transform_partial() function to be more flexible on indirection node
types, and make best effort in transforming only a prefix of the
indirection list, letting the caller handle the remaining
indirections.
This allows transform functions to accept dot notation indirection as
input, preparing for future JSONB dot notation support.
---
contrib/hstore/hstore_subs.c | 1 +
src/backend/parser/parse_expr.c | 76 +++++++++++++++----------
src/backend/parser/parse_node.c | 92 +++++++++++++++++++++++--------
src/backend/parser/parse_target.c | 6 +-
src/backend/utils/adt/arraysubs.c | 2 +
src/backend/utils/adt/jsonbsubs.c | 19 +++++--
src/include/nodes/subscripting.h | 24 +++++++-
src/include/parser/parse_node.h | 3 +-
8 files changed, 163 insertions(+), 60 deletions(-)
diff --git a/contrib/hstore/hstore_subs.c b/contrib/hstore/hstore_subs.c
index 3d03f66fa0d..d678dc56f86 100644
--- a/contrib/hstore/hstore_subs.c
+++ b/contrib/hstore/hstore_subs.c
@@ -287,6 +287,7 @@ hstore_subscript_handler(PG_FUNCTION_ARGS)
{
static const SubscriptRoutines sbsroutines = {
.transform = hstore_subscript_transform,
+ .transform_partial = NULL,
.exec_setup = hstore_exec_setup,
.fetch_strict = true, /* fetch returns NULL for NULL inputs */
.fetch_leakproof = true, /* fetch returns NULL for bad subscript */
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index e1979a80c19..95ce330e506 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -436,44 +436,65 @@ transformIndirection(ParseState *pstate, A_Indirection *ind)
{
Node *last_srf = pstate->p_last_srf;
Node *result = transformExprRecurse(pstate, ind->arg);
- List *subscripts = NIL;
+ List *indirections = NIL;
int location = exprLocation(result);
ListCell *i;
/*
- * We have to split any field-selection operations apart from
- * subscripting. Adjacent A_Indices nodes have to be treated as a single
+ * Combine field names and subscripts into a single indirection list, as
+ * some subscripting containers, such as jsonb, support field access using
+ * dot notation. Adjacent A_Indices nodes have to be treated as a single
* multidimensional subscript operation.
*/
foreach(i, ind->indirection)
{
Node *n = lfirst(i);
- if (IsA(n, A_Indices))
- subscripts = lappend(subscripts, n);
- else if (IsA(n, A_Star))
+ if (IsA(n, A_Indices) || IsA(n, String))
+ indirections = lappend(indirections, n);
+ else
{
+ Assert(IsA(n, A_Star));
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("row expansion via \"*\" is not supported here"),
parser_errposition(pstate, location)));
}
- else
+ }
+
+ while (indirections)
+ {
+ /* try processing container subscripts first */
+ int transformed_count = 0;
+ Node *newresult = (Node *)
+ transformContainerSubscripts(pstate,
+ result,
+ exprType(result),
+ exprTypmod(result),
+ indirections,
+ false,
+ &transformed_count);
+
+ if (!newresult)
{
- Node *newresult;
+ /*
+ * generic subscripting failed; falling back to field selection
+ * for a composite type, or a single-argument function.
+ */
+ Node *n;
+
+ Assert(indirections);
- Assert(IsA(n, String));
+ n = linitial(indirections);
- /* process subscripts before this field selection */
- if (subscripts)
- result = (Node *) transformContainerSubscripts(pstate,
- result,
- exprType(result),
- exprTypmod(result),
- subscripts,
- false);
- subscripts = NIL;
+ if (!IsA(n, String))
+ ereport(ERROR,
+ (errcode(ERRCODE_DATATYPE_MISMATCH),
+ errmsg("cannot subscript type %s because it does not support subscripting",
+ format_type_be(exprType(result))),
+ parser_errposition(pstate, exprLocation(result))));
+ /* try to parse function or field selection */
newresult = ParseFuncOrColumn(pstate,
list_make1(n),
list_make1(result),
@@ -481,19 +502,18 @@ transformIndirection(ParseState *pstate, A_Indirection *ind)
NULL,
false,
location);
- if (newresult == NULL)
+
+ if (!newresult)
unknown_attribute(pstate, result, strVal(n), location);
- result = newresult;
+ else
+ transformed_count = 1;
}
+
+ /* remove the processed indirections */
+ indirections = list_delete_first_n(indirections, transformed_count);
+
+ result = newresult;
}
- /* process trailing subscripts, if any */
- if (subscripts)
- result = (Node *) transformContainerSubscripts(pstate,
- result,
- exprType(result),
- exprTypmod(result),
- subscripts,
- false);
return result;
}
diff --git a/src/backend/parser/parse_node.c b/src/backend/parser/parse_node.c
index 203b7a32178..71ff31a9638 100644
--- a/src/backend/parser/parse_node.c
+++ b/src/backend/parser/parse_node.c
@@ -238,20 +238,22 @@ transformContainerType(Oid *containerType, int32 *containerTypmod)
* containerTypMod typmod for the container
* indirection Untransformed list of subscripts (must not be NIL)
* isAssignment True if this will become a container assignment.
- */
+ * nSubscripts Output parameter for number of transformed subscripts.
+*/
SubscriptingRef *
transformContainerSubscripts(ParseState *pstate,
Node *containerBase,
Oid containerType,
int32 containerTypMod,
List *indirection,
- bool isAssignment)
+ bool isAssignment,
+ int *nSubscripts)
{
SubscriptingRef *sbsref;
const SubscriptRoutines *sbsroutines;
Oid elementType;
- bool isSlice = false;
- ListCell *idx;
+
+ *nSubscripts = 0;
/*
* Determine the actual container type, smashing any domain. In the
@@ -267,28 +269,15 @@ transformContainerSubscripts(ParseState *pstate,
*/
sbsroutines = getSubscriptingRoutines(containerType, &elementType);
if (!sbsroutines)
+ {
+ if (!isAssignment)
+ return NULL;
+
ereport(ERROR,
(errcode(ERRCODE_DATATYPE_MISMATCH),
errmsg("cannot subscript type %s because it does not support subscripting",
format_type_be(containerType)),
parser_errposition(pstate, exprLocation(containerBase))));
-
- /*
- * Detect whether any of the indirection items are slice specifiers.
- *
- * A list containing only simple subscripts refers to a single container
- * element. If any of the items are slice specifiers (lower:upper), then
- * the subscript expression means a container slice operation.
- */
- foreach(idx, indirection)
- {
- A_Indices *ai = lfirst_node(A_Indices, idx);
-
- if (ai->is_slice)
- {
- isSlice = true;
- break;
- }
}
/*
@@ -309,8 +298,65 @@ transformContainerSubscripts(ParseState *pstate,
* Call the container-type-specific logic to transform the subscripts and
* determine the subscripting result type.
*/
- sbsroutines->transform(sbsref, indirection, pstate,
- isSlice, isAssignment);
+ if (sbsroutines->transform_partial != NULL)
+ {
+ /*
+ * If the container type provides a partial transform function, use it
+ * here. This function can accept any node types in the indirection
+ * list as input, and is responsible for identifying and transforming
+ * as many leading elements as it can handle, which may be only a
+ * prefix of the indirection list. For example, it might process
+ * A_Indices nodes, String nodes (for jsonb dot-notation), or other
+ * node types, depending on the container's requirements. It returns
+ * the number of elements it transformed.
+ */
+ *nSubscripts = sbsroutines->transform_partial(sbsref, indirection, pstate, isAssignment);
+ }
+ else
+ {
+ /*
+ * If there is no partial transform function, use the full transform
+ * function, which only accepts bracket subscripts (A_Indices nodes).
+ * We pre-collect the leading A_Indices nodes from the indirection
+ * list, then call the transform function to process this prefix of
+ * subscripts.
+ */
+ List *subscriptlist = NIL;
+ ListCell *lc;
+ bool isSlice = false;
+
+ /* Collect leading A_Indices subscripts */
+ foreach(lc, indirection)
+ {
+ Node *n = lfirst(lc);
+
+ if (IsA(n, A_Indices))
+ {
+ A_Indices *ai = (A_Indices *) n;
+
+ subscriptlist = lappend(subscriptlist, n);
+ if (ai->is_slice)
+ isSlice = true;
+ }
+ else
+ break;
+ }
+
+ if (subscriptlist)
+ sbsroutines->transform(sbsref, subscriptlist, pstate, isSlice, isAssignment);
+
+ *nSubscripts = list_length(subscriptlist);
+ }
+
+ if (*nSubscripts == 0)
+ {
+ /* Fallback to field selection in caller */
+ if (!isAssignment)
+ return NULL;
+
+ /* This should not happen with well-behaved transform functions */
+ elog(ERROR, "subscripting transform function failed to consume any indirection elements");
+ }
/*
* Verify we got a valid type (this defends, for example, against someone
diff --git a/src/backend/parser/parse_target.c b/src/backend/parser/parse_target.c
index 905c975d83b..30cf77338ae 100644
--- a/src/backend/parser/parse_target.c
+++ b/src/backend/parser/parse_target.c
@@ -922,6 +922,7 @@ transformAssignmentSubscripts(ParseState *pstate,
Oid typeNeeded;
int32 typmodNeeded;
Oid collationNeeded;
+ int nSubscripts = 0;
Assert(subscripts != NIL);
@@ -936,7 +937,10 @@ transformAssignmentSubscripts(ParseState *pstate,
containerType,
containerTypMod,
subscripts,
- true);
+ true,
+ &nSubscripts);
+
+ Assert(nSubscripts == list_length(subscripts));
typeNeeded = sbsref->refrestype;
typmodNeeded = sbsref->reftypmod;
diff --git a/src/backend/utils/adt/arraysubs.c b/src/backend/utils/adt/arraysubs.c
index 2940fb8e8d7..0049907b942 100644
--- a/src/backend/utils/adt/arraysubs.c
+++ b/src/backend/utils/adt/arraysubs.c
@@ -541,6 +541,7 @@ array_subscript_handler(PG_FUNCTION_ARGS)
{
static const SubscriptRoutines sbsroutines = {
.transform = array_subscript_transform,
+ .transform_partial = NULL,
.exec_setup = array_exec_setup,
.fetch_strict = true, /* fetch returns NULL for NULL inputs */
.fetch_leakproof = true, /* fetch returns NULL for bad subscript */
@@ -568,6 +569,7 @@ raw_array_subscript_handler(PG_FUNCTION_ARGS)
{
static const SubscriptRoutines sbsroutines = {
.transform = array_subscript_transform,
+ .transform_partial = NULL,
.exec_setup = array_exec_setup,
.fetch_strict = true, /* fetch returns NULL for NULL inputs */
.fetch_leakproof = true, /* fetch returns NULL for bad subscript */
diff --git a/src/backend/utils/adt/jsonbsubs.c b/src/backend/utils/adt/jsonbsubs.c
index e8626d3b4fc..dfe7da55e8e 100644
--- a/src/backend/utils/adt/jsonbsubs.c
+++ b/src/backend/utils/adt/jsonbsubs.c
@@ -39,11 +39,10 @@ typedef struct JsonbSubWorkspace
* Transform the subscript expressions, coerce them to text,
* and determine the result type of the SubscriptingRef node.
*/
-static void
+static int
jsonb_subscript_transform(SubscriptingRef *sbsref,
List *indirection,
ParseState *pstate,
- bool isSlice,
bool isAssignment)
{
List *upperIndexpr = NIL;
@@ -55,10 +54,15 @@ jsonb_subscript_transform(SubscriptingRef *sbsref,
*/
foreach(idx, indirection)
{
- A_Indices *ai = lfirst_node(A_Indices, idx);
+ A_Indices *ai;
Node *subExpr;
- if (isSlice)
+ if (!IsA(lfirst(idx), A_Indices))
+ break;
+
+ ai = lfirst_node(A_Indices, idx);
+
+ if (ai->is_slice)
{
Node *expr = ai->uidx ? ai->uidx : ai->lidx;
@@ -142,7 +146,7 @@ jsonb_subscript_transform(SubscriptingRef *sbsref,
* Slice with omitted upper bound. Should not happen as we already
* errored out on slice earlier, but handle this just in case.
*/
- Assert(isSlice && ai->is_slice);
+ Assert(ai->is_slice);
ereport(ERROR,
(errcode(ERRCODE_DATATYPE_MISMATCH),
errmsg("jsonb subscript does not support slices"),
@@ -159,6 +163,8 @@ jsonb_subscript_transform(SubscriptingRef *sbsref,
/* Determine the result type of the subscripting operation; always jsonb */
sbsref->refrestype = JSONBOID;
sbsref->reftypmod = -1;
+
+ return list_length(upperIndexpr);
}
/*
@@ -402,7 +408,8 @@ Datum
jsonb_subscript_handler(PG_FUNCTION_ARGS)
{
static const SubscriptRoutines sbsroutines = {
- .transform = jsonb_subscript_transform,
+ .transform = NULL, /* jsonb uses partial transform instead */
+ .transform_partial = jsonb_subscript_transform,
.exec_setup = jsonb_exec_setup,
.fetch_strict = true, /* fetch returns NULL for NULL inputs */
.fetch_leakproof = true, /* fetch returns NULL for bad subscript */
diff --git a/src/include/nodes/subscripting.h b/src/include/nodes/subscripting.h
index e991f4bf826..3b819dc8d65 100644
--- a/src/include/nodes/subscripting.h
+++ b/src/include/nodes/subscripting.h
@@ -98,6 +98,25 @@ typedef void (*SubscriptTransform) (SubscriptingRef *sbsref,
bool isSlice,
bool isAssignment);
+/*
+ * SubscriptTransformPartial is an alternative to SubscriptTransform for
+ * container types that can accept non-A_Indices indirection as input
+ * (e.g., JSONB accepts dot-notation (String node) for field access).
+
+ * It may transform a prefix of the indirection list and leave the rest
+ * unprocessed. It returns the number of indirections it transformed.
+ * The caller will then remove that many items from the head of the
+ * list, and handle the
+ * remaining indirections differently or to raise an error as needed.
+ *
+ * If transform_partial is NULL, the complete transform function is used,
+ * which accepts only A_Indices (bracket) nodes.
+ */
+typedef int (*SubscriptTransformPartial) (SubscriptingRef *sbsref,
+ List *indirection,
+ ParseState *pstate,
+ bool isAssignment);
+
/*
* The exec_setup method is called during executor-startup compilation of a
* SubscriptingRef node in an expression. It must fill *methods with pointers
@@ -157,7 +176,10 @@ typedef void (*SubscriptExecSetup) (const SubscriptingRef *sbsref,
/* Struct returned by the SQL-visible subscript handler function */
typedef struct SubscriptRoutines
{
- SubscriptTransform transform; /* parse analysis function */
+ SubscriptTransform transform; /* parse analysis function, or NULL */
+ SubscriptTransformPartial transform_partial; /* alternative parse
+ * analysis function, or
+ * NULL */
SubscriptExecSetup exec_setup; /* expression compilation function */
bool fetch_strict; /* is fetch SubscriptRef strict? */
bool fetch_leakproof; /* is fetch SubscriptRef leakproof? */
diff --git a/src/include/parser/parse_node.h b/src/include/parser/parse_node.h
index f7d07c84542..80f486acff0 100644
--- a/src/include/parser/parse_node.h
+++ b/src/include/parser/parse_node.h
@@ -362,7 +362,8 @@ extern SubscriptingRef *transformContainerSubscripts(ParseState *pstate,
Oid containerType,
int32 containerTypMod,
List *indirection,
- bool isAssignment);
+ bool isAssignment,
+ int *consumed_count);
extern Const *make_const(ParseState *pstate, A_Const *aconst);
#endif /* PARSE_NODE_H */
--
2.39.5 (Apple Git-154)
[application/octet-stream] v20-0003-Export-jsonPathFromParseResult.patch (2.8K, ../CAK98qZ2EhKC=Z23jNbMX=aGPGi9+n42a8Eiz1rKYmF388UCHUQ@mail.gmail.com/7-v20-0003-Export-jsonPathFromParseResult.patch)
download | inline diff:
From c3eb1f90829a97693e703a0d11ba989e632f4b6a Mon Sep 17 00:00:00 2001
From: Nikita Glukhov <[email protected]>
Date: Tue, 8 Jul 2025 22:18:07 -0700
Subject: [PATCH v20 3/5] Export jsonPathFromParseResult()
This is a preparation step for a future commit that will reuse the
aforementioned function.
Authored-by: Nikita Glukhov <[email protected]>
Reviewed-by: Alexandra Wang <[email protected]>
Reviewed-by: Andrew Dunstan <[email protected]>
Reviewed-by: Matheus Alcantara <[email protected]>
Reviewed-by: Jian He <[email protected]>
Reviewed-by: Chao Li <[email protected]>
---
src/backend/utils/adt/jsonpath.c | 19 +++++++++++++++----
src/include/utils/jsonpath.h | 4 ++++
2 files changed, 19 insertions(+), 4 deletions(-)
diff --git a/src/backend/utils/adt/jsonpath.c b/src/backend/utils/adt/jsonpath.c
index 762f7e8a09d..c83774b2a16 100644
--- a/src/backend/utils/adt/jsonpath.c
+++ b/src/backend/utils/adt/jsonpath.c
@@ -166,15 +166,13 @@ jsonpath_send(PG_FUNCTION_ARGS)
* Converts C-string to a jsonpath value.
*
* Uses jsonpath parser to turn string into an AST, then
- * flattenJsonPathParseItem() does second pass turning AST into binary
+ * jsonPathFromParseResult() does second pass turning AST into binary
* representation of jsonpath.
*/
static Datum
jsonPathFromCstring(char *in, int len, struct Node *escontext)
{
JsonPathParseResult *jsonpath = parsejsonpath(in, len, escontext);
- JsonPath *res;
- StringInfoData buf;
if (SOFT_ERROR_OCCURRED(escontext))
return (Datum) 0;
@@ -185,8 +183,21 @@ jsonPathFromCstring(char *in, int len, struct Node *escontext)
errmsg("invalid input syntax for type %s: \"%s\"", "jsonpath",
in)));
+ return jsonPathFromParseResult(jsonpath, 4 * len, escontext);
+}
+
+/*
+ * Converts jsonpath Abstract Syntax Tree (AST) into jsonpath value in binary.
+ */
+Datum
+jsonPathFromParseResult(const JsonPathParseResult *jsonpath, int estimated_len,
+ struct Node *escontext)
+{
+ JsonPath *res;
+ StringInfoData buf;
+
initStringInfo(&buf);
- enlargeStringInfo(&buf, 4 * len /* estimation */ );
+ enlargeStringInfo(&buf, estimated_len);
appendStringInfoSpaces(&buf, JSONPATH_HDRSZ);
diff --git a/src/include/utils/jsonpath.h b/src/include/utils/jsonpath.h
index 23a76d233e9..0958bc22bb6 100644
--- a/src/include/utils/jsonpath.h
+++ b/src/include/utils/jsonpath.h
@@ -281,6 +281,10 @@ extern JsonPathParseResult *parsejsonpath(const char *str, int len,
extern bool jspConvertRegexFlags(uint32 xflags, int *result,
struct Node *escontext);
+extern Datum jsonPathFromParseResult(const JsonPathParseResult *jsonpath,
+ int estimated_len,
+ struct Node *escontext);
+
/*
* Struct for details about external variables passed into jsonpath executor
*/
--
2.39.5 (Apple Git-154)
view thread (68+ messages) latest in thread
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], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: SQL:2023 JSON simplified accessor support
In-Reply-To: <CAK98qZ2EhKC=Z23jNbMX=aGPGi9+n42a8Eiz1rKYmF388UCHUQ@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