On Fri, Oct 4, 2024 at 7:33 AM jian he <[email protected]> wrote:
>
> On Thu, Sep 26, 2024 at 11:45 PM Alexandra Wang
> <[email protected]> wrote:
> >
> > Hi,
> >
> > I didn’t run pgindent earlier, so here’s the updated version with the
> > correct indentation. Hope this helps!
> >
>
> the attached patch solves the domain type issue, Andrew mentioned in the
> thread.
>
> I also added a test case: composite over jsonb domain type,
>
>
> it still works. for example:
> create domain json_d as jsonb;
> create type test as (a int, b json_d);
> create table t1(a test);
> insert into t1 select $$(1,"{""a"": 3, ""key1"": {""c"": ""42""},
> ""key2"": [11, 12]}") $$;
> insert into t1 select $$ (1,"{""a"": 3, ""key1"": {""c"": ""42""},
> ""key2"": [11, 12, {""x"": [31, 42]}]}") $$;
>
> select (t1.a).b.key2[2].x[1] from t1;
> select (t1.a).b.key1.c from t1;
Thank you so much, Jian, for reviewing the patch and providing a fix!
I’ve integrated your fix into the attached v5 patch. Inspired by your
test case, I discovered another issue with domains over JSON:
top-level JSON array access to a domain over JSON when the domain is a
field of a composite type. Here’s an example:
create domain json_d as json;
create type test as (a int, b json_d);
create table t1(a test);
insert into t1 select $$ (1,"[{""a"": 3}, {""key1"": {""c"": ""42""}},
{""key2"": [11, 12]}]") $$;
select (t1.a).b[0] from t1;
The v5 patch includes the following updates:
- Fixed the aforementioned issue and added more tests covering composite
types with domains, nested domains, and arrays of domains over
JSON/JSONB.
- Refactored the logic for parsing JSON/JSONB object fields by moving it
from ParseFuncOrColumn() to transformIndirection() for improved
readability. The ParseFuncOrColumn() function is already handling both
single-argument function calls and composite types, and it has other
callers besides transformIndirection().
Best,
Alex
From 44d71c9364fb8e9e8c57a5390ea5c4a3f7e371e0 Mon Sep 17 00:00:00 2001
From: Alexandra Wang <[email protected]>
Date: Thu, 7 Nov 2024 15:14:50 -0600
Subject: [PATCH v5] Add JSON/JSONB simplified accessor
This patch implements JSON/JSONB member accessor and JSON/JSONB array
accessor as specified in SQL 2023. Specifically, the following sytaxes
are added:
1. Simple dot-notation access to JSON and JSONB object fields
2. Subscripting for indexed access to JSON array elements
Examples:
-- Setup
create table t(x int, y json);
insert into t select 1, '{"a": 1, "b": 42}'::json;
insert into t select 1, '{"a": 2, "b": {"c": 42}}'::json;
insert into t select 1, '{"a": 3, "b": {"c": "42"}, "d":[11, 12]}'::json;
-- Existing syntax 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 is the <value expression primary> and JC is the <JSON
simplified accessor op chain>. For example, the JSON_QUERY equalalence
of the above queries is:
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;
This implementation enables dot-notation access to JSON/JSONB object
by making a syntatic sugar for the json_object_field "->" operator in
ParseFuncOrColumn() for arg of JSON/JSONB type.
Similarly, JSON array access via subscripting is enabled by creating
an OpExpr for the existing "->" operator. Note that the JSON subscripting
implementation is different from the JSONB subscripting counterpart,
as the former leverages the "->" operator directly, while the latter
uses the more generic SubscriptingRef interface.
Domains over JSON/JSONB types are also suppported.
---
src/backend/parser/parse_expr.c | 37 ++++-
src/backend/parser/parse_func.c | 85 +++++++++-
src/include/catalog/pg_operator.dat | 6 +-
src/include/parser/parse_func.h | 4 +
src/test/regress/expected/json.out | 246 ++++++++++++++++++++++++++++
src/test/regress/expected/jsonb.out | 228 ++++++++++++++++++++++++++
src/test/regress/sql/json.sql | 86 ++++++++++
src/test/regress/sql/jsonb.sql | 83 ++++++++++
8 files changed, 763 insertions(+), 12 deletions(-)
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index c2806297aa..9a0ef59b4c 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -451,7 +451,16 @@ transformIndirection(ParseState *pstate, A_Indirection *ind)
Node *n = lfirst(i);
if (IsA(n, A_Indices))
- subscripts = lappend(subscripts, n);
+ {
+ if (exprType(result) == JSONOID || getBaseType(exprType(result)) == JSONOID)
+ result = ParseJsonSimplifiedAccessorArrayElement(pstate,
+ castNode(A_Indices, n),
+ result,
+ location);
+ else
+ subscripts = lappend(subscripts, n);
+ }
+
else if (IsA(n, A_Star))
{
ereport(ERROR,
@@ -462,6 +471,8 @@ transformIndirection(ParseState *pstate, A_Indirection *ind)
else
{
Node *newresult;
+ Oid result_typid;
+ Oid result_basetypid;
Assert(IsA(n, String));
@@ -475,13 +486,23 @@ transformIndirection(ParseState *pstate, A_Indirection *ind)
false);
subscripts = NIL;
- newresult = ParseFuncOrColumn(pstate,
- list_make1(n),
- list_make1(result),
- last_srf,
- NULL,
- false,
- location);
+ result_typid = exprType(result);
+ result_basetypid = (result_typid == JSONOID || result_typid == JSONBOID) ?
+ result_typid : getBaseType(result_typid);
+
+ if (result_basetypid == JSONOID || result_basetypid == JSONBOID)
+ newresult = ParseJsonSimplifiedAccessorObjectField(pstate,
+ strVal(n),
+ result,
+ location, result_basetypid);
+ else
+ newresult = ParseFuncOrColumn(pstate,
+ list_make1(n),
+ list_make1(result),
+ last_srf,
+ NULL,
+ false,
+ location);
if (newresult == NULL)
unknown_attribute(pstate, result, strVal(n), location);
result = newresult;
diff --git a/src/backend/parser/parse_func.c b/src/backend/parser/parse_func.c
index 9b23344a3b..1f53736ce0 100644
--- a/src/backend/parser/parse_func.c
+++ b/src/backend/parser/parse_func.c
@@ -33,6 +33,8 @@
#include "utils/builtins.h"
#include "utils/lsyscache.h"
#include "utils/syscache.h"
+#include "parser/parse_oper.h"
+#include "catalog/pg_operator_d.h"
/* Possible error codes from LookupFuncNameInternal */
@@ -53,7 +55,6 @@ static Oid LookupFuncNameInternal(ObjectType objtype, List *funcname,
bool include_out_arguments, bool missing_ok,
FuncLookupError *lookupError);
-
/*
* Parse a function call
*
@@ -1902,6 +1903,88 @@ FuncNameAsType(List *funcname)
return result;
}
+/*
+ * ParseJsonSimplifiedAccessorArrayElement -
+ * transform json subscript into json_array_element operator.
+ */
+Node *
+ParseJsonSimplifiedAccessorArrayElement(ParseState *pstate, A_Indices *subscript,
+ Node *first_arg, int location)
+{
+ OpExpr *result;
+ Node *index;
+
+ if (exprType(first_arg) != JSONOID && getBaseType(exprType(first_arg)) != JSONOID)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("json subscript does not support type oid: %u",
+ exprType(first_arg))),
+ parser_errposition(pstate, location));
+
+ if (subscript->is_slice)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("json subscript does not support slices")),
+ parser_errposition(pstate, location));
+
+ index = transformExpr(pstate, subscript->uidx, pstate->p_expr_kind);
+ if (!IsA(index, Const) ||
+ castNode(Const, index)->consttype != INT4OID)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("json subscript must be coercible to integer")),
+ parser_errposition(pstate, exprLocation(index)));
+
+ result = makeNode(OpExpr);
+ result->opno = OID_JSON_ARRAY_ELEMENT_OP;
+ result->opresulttype = JSONOID;
+ result->opfuncid = get_opcode(result->opno);
+ result->args = list_make2(first_arg, index);
+ result->location = exprLocation(index);
+
+ return (Node *) result;
+}
+
+/*
+ * ParseJsonSimplifiedAccessorObjectField -
+ * handles function calls with a single argument that is of json type.
+ * If the function call is actually a column projection, return a suitably
+ * transformed expression tree. If not, return NULL.
+ */
+Node *
+ParseJsonSimplifiedAccessorObjectField(ParseState *pstate, const char *funcname,
+ Node *first_arg, int location, Oid basetypid)
+{
+ OpExpr *result;
+ Node *rexpr;
+
+ result = makeNode(OpExpr);
+ result->opresulttype = basetypid;
+ switch (basetypid)
+ {
+ case JSONOID:
+ result->opno = OID_JSON_OBJECT_FIELD_OP;
+ break;
+ case JSONBOID:
+ result->opno = OID_JSONB_OBJECT_FIELD_OP;
+ break;
+ default:
+ elog(ERROR, "unsupported type OID: %u", basetypid);
+ }
+ result->opfuncid = get_opcode(result->opno);
+ rexpr = (Node *) makeConst(
+ TEXTOID,
+ -1,
+ InvalidOid,
+ -1,
+ CStringGetTextDatum(funcname),
+ false,
+ false);
+ result->args = list_make2(first_arg, rexpr);
+ result->location = location;
+ return (Node *) result;
+}
+
/*
* ParseComplexProjection -
* handles function calls with a single argument that is of complex type.
diff --git a/src/include/catalog/pg_operator.dat b/src/include/catalog/pg_operator.dat
index 0e7511dde1..e375c49252 100644
--- a/src/include/catalog/pg_operator.dat
+++ b/src/include/catalog/pg_operator.dat
@@ -3154,13 +3154,13 @@
oprname => '*', oprleft => 'anyrange', oprright => 'anyrange',
oprresult => 'anyrange', oprcom => '*(anyrange,anyrange)',
oprcode => 'range_intersect' },
-{ oid => '3962', descr => 'get json object field',
+{ oid => '3962', oid_symbol => 'OID_JSON_OBJECT_FIELD_OP', descr => 'get json object field',
oprname => '->', oprleft => 'json', oprright => 'text', oprresult => 'json',
oprcode => 'json_object_field' },
{ oid => '3963', descr => 'get json object field as text',
oprname => '->>', oprleft => 'json', oprright => 'text', oprresult => 'text',
oprcode => 'json_object_field_text' },
-{ oid => '3964', descr => 'get json array element',
+{ oid => '3964', oid_symbol => 'OID_JSON_ARRAY_ELEMENT_OP', descr => 'get json array element',
oprname => '->', oprleft => 'json', oprright => 'int4', oprresult => 'json',
oprcode => 'json_array_element' },
{ oid => '3965', descr => 'get json array element as text',
@@ -3172,7 +3172,7 @@
{ oid => '3967', descr => 'get value from json as text with path elements',
oprname => '#>>', oprleft => 'json', oprright => '_text', oprresult => 'text',
oprcode => 'json_extract_path_text' },
-{ oid => '3211', descr => 'get jsonb object field',
+{ oid => '3211', oid_symbol => 'OID_JSONB_OBJECT_FIELD_OP', descr => 'get jsonb object field',
oprname => '->', oprleft => 'jsonb', oprright => 'text', oprresult => 'jsonb',
oprcode => 'jsonb_object_field' },
{ oid => '3477', descr => 'get jsonb object field as text',
diff --git a/src/include/parser/parse_func.h b/src/include/parser/parse_func.h
index c7ba99dee7..64a600079c 100644
--- a/src/include/parser/parse_func.h
+++ b/src/include/parser/parse_func.h
@@ -34,6 +34,10 @@ typedef enum
extern Node *ParseFuncOrColumn(ParseState *pstate, List *funcname, List *fargs,
Node *last_srf, FuncCall *fn, bool proc_call,
int location);
+extern Node *ParseJsonSimplifiedAccessorObjectField(ParseState *pstate, const char *funcname,
+ Node *first_arg, int location, Oid basetypid);
+extern Node *ParseJsonSimplifiedAccessorArrayElement(ParseState *pstate, A_Indices *subscript,
+ Node *first_arg, int location);
extern FuncDetailCode func_get_detail(List *funcname,
List *fargs, List *fargnames,
diff --git a/src/test/regress/expected/json.out b/src/test/regress/expected/json.out
index 96c40911cb..9da7dba068 100644
--- a/src/test/regress/expected/json.out
+++ b/src/test/regress/expected/json.out
@@ -2716,3 +2716,249 @@ select ts_headline('[]'::json, tsquery('aaa & bbb'));
[]
(1 row)
+-- simple dot notation
+drop table if exists test_json_dot;
+NOTICE: table "test_json_dot" does not exist, skipping
+create table test_json_dot(id int, test_json json);
+insert into test_json_dot select 1, '{"a": 1, "b": 42}'::json;
+insert into test_json_dot select 1, '{"a": 2, "b": {"c": 42}}'::json;
+insert into test_json_dot select 1, '{"a": 3, "b": {"c": "42"}, "d":[11, 12]}'::json;
+insert into test_json_dot select 1, '{"a": 3, "b": {"c": "42"}, "d":[{"x": [11, 12]}, {"y": [21, 22]}]}'::json;
+-- member object access
+select (test_json_dot.test_json).b, json_query(test_json, 'lax $.b' WITH CONDITIONAL WRAPPER NULL ON EMPTY NULL ON ERROR) as expected from test_json_dot;
+ b | expected
+-------------+-------------
+ 42 | 42
+ {"c": 42} | {"c": 42}
+ {"c": "42"} | {"c": "42"}
+ {"c": "42"} | {"c": "42"}
+(4 rows)
+
+select (test_json_dot.test_json).b.c, json_query(test_json, 'lax $.b.c' WITH CONDITIONAL WRAPPER NULL ON EMPTY NULL ON ERROR) as expected from test_json_dot;
+ c | expected
+------+----------
+ |
+ 42 | 42
+ "42" | "42"
+ "42" | "42"
+(4 rows)
+
+select (test_json_dot.test_json).d, json_query(test_json, 'lax $.d' WITH CONDITIONAL WRAPPER NULL ON EMPTY NULL ON ERROR) as expected from test_json_dot;
+ d | expected
+------------------------------------+------------------------------------
+ |
+ |
+ [11, 12] | [11, 12]
+ [{"x": [11, 12]}, {"y": [21, 22]}] | [{"x": [11, 12]}, {"y": [21, 22]}]
+(4 rows)
+
+select (test_json_dot.test_json)."d", json_query(test_json, 'lax $.d' WITH CONDITIONAL WRAPPER NULL ON EMPTY NULL ON ERROR) as expected from test_json_dot;
+ d | expected
+------------------------------------+------------------------------------
+ |
+ |
+ [11, 12] | [11, 12]
+ [{"x": [11, 12]}, {"y": [21, 22]}] | [{"x": [11, 12]}, {"y": [21, 22]}]
+(4 rows)
+
+select (test_json_dot.test_json).'d' from test_json_dot;
+ERROR: syntax error at or near "'d'"
+LINE 1: select (test_json_dot.test_json).'d' from test_json_dot;
+ ^
+select (test_json_dot.test_json)['d'] from test_json_dot;
+ERROR: json subscript must be coercible to integer
+LINE 1: select (test_json_dot.test_json)['d'] from test_json_dot;
+ ^
+-- wildcard access is not supported
+select (test_json_dot.test_json).* from test_json_dot;
+ERROR: type json is not composite
+-- array element access
+select (test_json_dot.test_json).d->0 from test_json_dot;
+ ?column?
+-----------------
+
+
+ 11
+ {"x": [11, 12]}
+(4 rows)
+
+select (test_json_dot.test_json).d[0], json_query(test_json, 'lax $.d[0]' WITH CONDITIONAL WRAPPER NULL ON EMPTY NULL ON ERROR) as expected from test_json_dot;
+ d | expected
+-----------------+-----------------
+ |
+ |
+ 11 | 11
+ {"x": [11, 12]} | {"x": [11, 12]}
+(4 rows)
+
+select (test_json_dot.test_json).d[1], json_query(test_json, 'lax $.d[1]' WITH CONDITIONAL WRAPPER NULL ON EMPTY NULL ON ERROR) as expected from test_json_dot;
+ d | expected
+-----------------+-----------------
+ |
+ |
+ 12 | 12
+ {"y": [21, 22]} | {"y": [21, 22]}
+(4 rows)
+
+select (test_json_dot.test_json).d[0:] from test_json_dot;
+ERROR: json subscript does not support slices
+LINE 1: select (test_json_dot.test_json).d[0:] from test_json_dot;
+ ^
+select (test_json_dot.test_json).d[0::int] from test_json_dot;
+ d
+-----------------
+
+
+ 11
+ {"x": [11, 12]}
+(4 rows)
+
+select (test_json_dot.test_json).d[0::float] from test_json_dot;
+ERROR: json subscript must be coercible to integer
+LINE 1: select (test_json_dot.test_json).d[0::float] from test_json_...
+ ^
+select (test_json_dot.test_json).d[0].x[1], json_query(test_json, 'lax $.d[0].x[1]' WITH CONDITIONAL WRAPPER NULL ON EMPTY NULL ON ERROR) as expected from test_json_dot;
+ x | expected
+----+----------
+ |
+ |
+ |
+ 12 | 12
+(4 rows)
+
+-- complex type with domain over json
+create domain json_d as json;
+create type comp_json as (a int, b json_d);
+create table test_json_domain_dot(a comp_json);
+insert into test_json_domain_dot select $$ (1,"{""a"": 3, ""key1"": {""c"": ""42""}, ""key2"": [11, 12]}") $$;
+insert into test_json_domain_dot select $$ (1,"{""a"": 3, ""key1"": {""c"": ""42""}, ""key2"": [11, 12, {""x"": [31, 42]}]}") $$;
+--object access
+select (test_json_domain_dot.a).b.key1.c from test_json_domain_dot;
+ c
+------
+ "42"
+ "42"
+(2 rows)
+
+select (test_json_domain_dot.a).b.key2 from test_json_domain_dot;
+ key2
+---------------------------
+ [11, 12]
+ [11, 12, {"x": [31, 42]}]
+(2 rows)
+
+select (test_json_domain_dot.a).b.key2[0] from test_json_domain_dot;
+ key2
+------
+ 11
+ 11
+(2 rows)
+
+select (test_json_domain_dot.a).b.key2[0::text] from test_json_domain_dot;
+ERROR: json subscript must be coercible to integer
+LINE 1: select (test_json_domain_dot.a).b.key2[0::text] from test_js...
+ ^
+select (test_json_domain_dot.a).b.key2[2].x[1] from test_json_domain_dot;
+ x
+----
+
+ 42
+(2 rows)
+
+-- array access
+insert into test_json_domain_dot select $$ (1,"[{""a"": 3}, {""key1"": {""c"": ""42""}}, {""key2"": [11, 12]}]") $$;
+select (test_json_domain_dot.a).b[0] from test_json_domain_dot;
+ b
+----------
+
+
+ {"a": 3}
+(3 rows)
+
+select (test_json_domain_dot.a).b[0:] from test_json_domain_dot;
+ERROR: json subscript does not support slices
+LINE 1: select (test_json_domain_dot.a).b[0:] from test_json_domain_...
+ ^
+drop table test_json_domain_dot cascade;
+drop type comp_json cascade;
+drop domain json_d cascade;
+-- nested domains over json
+CREATE DOMAIN json_with_name AS json
+ CHECK (
+ -- check that JSON has a "name" field and that it is a string
+ json_typeof(VALUE->'name') = 'string'
+ );
+CREATE DOMAIN json_with_name_and_email AS json_with_name
+ CHECK (
+ -- ensure that if "email" exists, it follows a simple email format
+ VALUE->'email' IS NULL OR (VALUE->>'email' ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$')
+ );
+CREATE DOMAIN json_user_profile AS json_with_name_and_email
+ CHECK (
+ -- ensure that if "phone" exists, it follows a basic phone format
+ VALUE->'phone' IS NULL OR (VALUE->>'phone' ~ '^\+\d{1,3}-\d{3}-\d{3}-\d{4}$')
+ );
+CREATE TABLE json_users (id SERIAL PRIMARY KEY, profile json_user_profile);
+INSERT INTO json_users (profile) VALUES ('{"name": "Alice", "email": "[email protected]", "phone": "+1-123-456-7890"}');
+INSERT INTO json_users (profile) VALUES ('{"name": "Bob", "email": "[email protected]", "phone": "+9-876-543-3210", "address": [123, "1st street", "New York", "New York", 12345]}');
+SELECT (json_users.profile).name from json_users;
+ name
+---------
+ "Alice"
+ "Bob"
+(2 rows)
+
+SELECT (json_users.profile).email from json_users;
+ email
+---------------------
+ "[email protected]"
+ "[email protected]"
+(2 rows)
+
+SELECT (json_users.profile).phone from json_users;
+ phone
+-------------------
+ "+1-123-456-7890"
+ "+9-876-543-3210"
+(2 rows)
+
+SELECT (json_users.profile).address from json_users;
+ address
+----------------------------------------------------
+
+ [123, "1st street", "New York", "New York", 12345]
+(2 rows)
+
+SELECT (json_users.profile).address[3] from json_users;
+ address
+------------
+
+ "New York"
+(2 rows)
+
+-- array of nested domains over json
+CREATE TABLE json_user_arrs (id SERIAL PRIMARY KEY, profiles json_user_profile[]);
+INSERT INTO json_user_arrs (profiles) VALUES (ARRAY['{"name": "Alice", "email": "[email protected]", "phone": "+1-123-456-7890"}'::json_user_profile, '{"name": "Bob", "email": "[email protected]", "phone": "+9-876-543-3210", "address": [123, "1st street", "New York", "New York", 12345]}'::json_user_profile]);
+SELECT json_user_arrs.profiles[1] from json_user_arrs;
+ profiles
+-----------------------------------------------------------------------------
+ {"name": "Alice", "email": "[email protected]", "phone": "+1-123-456-7890"}
+(1 row)
+
+SELECT json_user_arrs.profiles[2] from json_user_arrs;
+ profiles
+----------------------------------------------------------------------------------------------------------------------------------------
+ {"name": "Bob", "email": "[email protected]", "phone": "+9-876-543-3210", "address": [123, "1st street", "New York", "New York", 12345]}
+(1 row)
+
+SELECT json_user_arrs.profiles[2].address[0] from json_user_arrs;
+ address
+---------
+ 123
+(1 row)
+
+drop table json_users;
+drop table json_user_arrs;
+drop domain json_user_profile;
+drop domain json_with_name_and_email;
+drop domain json_with_name;
diff --git a/src/test/regress/expected/jsonb.out b/src/test/regress/expected/jsonb.out
index 7d163a156e..357b2f4356 100644
--- a/src/test/regress/expected/jsonb.out
+++ b/src/test/regress/expected/jsonb.out
@@ -5715,3 +5715,231 @@ select '12345.0000000000000000000000000000000000000000000005'::jsonb::int8;
12345
(1 row)
+-- simple dot notation
+drop table if exists test_jsonb_dot;
+NOTICE: table "test_jsonb_dot" does not exist, skipping
+create table test_jsonb_dot(id int, test_jsonb jsonb);
+insert into test_jsonb_dot select 1, '{"a": 1, "b": 42}'::json;
+insert into test_jsonb_dot select 1, '{"a": 2, "b": {"c": 42}}'::json;
+insert into test_jsonb_dot select 1, '{"a": 3, "b": {"c": "42"}, "d":[11, 12]}'::json;
+-- member object access
+select (test_jsonb_dot.test_jsonb).b from test_jsonb_dot;
+ b
+-------------
+ 42
+ {"c": 42}
+ {"c": "42"}
+(3 rows)
+
+select (test_jsonb_dot.test_jsonb).b.c from test_jsonb_dot;
+ c
+------
+
+ 42
+ "42"
+(3 rows)
+
+select (test_jsonb_dot.test_jsonb).d from test_jsonb_dot;
+ d
+----------
+
+
+ [11, 12]
+(3 rows)
+
+select (test_jsonb_dot.test_jsonb)."d" from test_jsonb_dot;
+ d
+----------
+
+
+ [11, 12]
+(3 rows)
+
+select (test_jsonb_dot.test_jsonb).'d' from test_jsonb_dot;
+ERROR: syntax error at or near "'d'"
+LINE 1: select (test_jsonb_dot.test_jsonb).'d' from test_jsonb_dot;
+ ^
+select (test_jsonb_dot.test_jsonb)['d'] from test_jsonb_dot;
+ test_jsonb
+------------
+
+
+ [11, 12]
+(3 rows)
+
+-- array element access
+select (test_jsonb_dot.test_jsonb).d[0] from test_jsonb_dot;
+ d
+----
+
+
+ 11
+(3 rows)
+
+select (test_jsonb_dot.test_jsonb).d[0:] from test_jsonb_dot;
+ERROR: jsonb subscript does not support slices
+LINE 1: select (test_jsonb_dot.test_jsonb).d[0:] from test_jsonb_dot...
+ ^
+select (test_jsonb_dot.test_jsonb).d[0::int] from test_jsonb_dot;
+ d
+----
+
+
+ 11
+(3 rows)
+
+select (test_jsonb_dot.test_jsonb).d[0::float] from test_jsonb_dot;
+ERROR: subscript type double precision is not supported
+LINE 1: select (test_jsonb_dot.test_jsonb).d[0::float] from test_jso...
+ ^
+HINT: jsonb subscript must be coercible to either integer or text.
+select (test_jsonb_dot.test_jsonb).d[0].x[1] from test_jsonb_dot;
+ x
+---
+
+
+
+(3 rows)
+
+-- wildcard access is not supported
+select (test_jsonb_dot.test_jsonb).* from test_jsonb_dot;
+ERROR: type jsonb is not composite
+-- complex type with domain over jsonb
+create domain jsonb_d as jsonb;
+create type comp_jsonb as (a int, b jsonb_d);
+create table test_jsonb_domain_dot(a comp_jsonb);
+insert into test_jsonb_domain_dot select $$ (1,"{""a"": 3, ""key1"": {""c"": ""42""}, ""key2"": [11, 12]}") $$;
+insert into test_jsonb_domain_dot select $$ (1,"{""a"": 3, ""key1"": {""c"": ""42""}, ""key2"": [11, 12, {""x"": [31, 42]}]}") $$;
+-- object access
+select (test_jsonb_domain_dot.a).b.key1.c from test_jsonb_domain_dot;
+ c
+------
+ "42"
+ "42"
+(2 rows)
+
+select (test_jsonb_domain_dot.a).b.key2 from test_jsonb_domain_dot;
+ key2
+---------------------------
+ [11, 12]
+ [11, 12, {"x": [31, 42]}]
+(2 rows)
+
+select (test_jsonb_domain_dot.a).b.key2[0] from test_jsonb_domain_dot;
+ key2
+------
+ 11
+ 11
+(2 rows)
+
+select (test_jsonb_domain_dot.a).b.key2[0::text] from test_jsonb_domain_dot;
+ key2
+------
+ 11
+ 11
+(2 rows)
+
+select (test_jsonb_domain_dot.a).b.key2[2].x[1] from test_jsonb_domain_dot;
+ x
+----
+
+ 42
+(2 rows)
+
+-- array access
+insert into test_jsonb_domain_dot select $$ (1,"[{""a"": 3}, {""key1"": {""c"": ""42""}}, {""key2"": [11, 12]}]") $$;
+select (test_jsonb_domain_dot.a).b[0] from test_jsonb_domain_dot;
+ b
+----------
+
+
+ {"a": 3}
+(3 rows)
+
+select (test_jsonb_domain_dot.a).b[0:] from test_jsonb_domain_dot;
+ERROR: jsonb subscript does not support slices
+LINE 1: select (test_jsonb_domain_dot.a).b[0:] from test_jsonb_domai...
+ ^
+drop table test_jsonb_domain_dot cascade;
+drop type comp_jsonb cascade;
+drop domain jsonb_d cascade;
+-- nested domains over jsonb
+CREATE DOMAIN jsonb_with_name AS JSONB
+ CHECK (
+ -- check that JSON has a "name" field and that it is a string
+ VALUE ? 'name' AND jsonb_typeof(VALUE->'name') = 'string'
+ );
+CREATE DOMAIN jsonb_with_name_and_email AS jsonb_with_name
+ CHECK (
+ -- ensure that if "email" exists, it follows a simple email format
+ NOT VALUE ? 'email' OR (VALUE->>'email' ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$')
+ );
+CREATE DOMAIN jsonb_user_profile AS jsonb_with_name_and_email
+ CHECK (
+ -- ensure that if "phone" exists, it follows a basic phone format
+ NOT VALUE ? 'phone' OR (VALUE->>'phone' ~ '^\+\d{1,3}-\d{3}-\d{3}-\d{4}$')
+ );
+CREATE TABLE jsonb_users (id SERIAL PRIMARY KEY, profile jsonb_user_profile);
+INSERT INTO jsonb_users (profile) VALUES ('{"name": "Alice", "email": "[email protected]", "phone": "+1-123-456-7890"}');
+INSERT INTO jsonb_users (profile) VALUES ('{"name": "Bob", "email": "[email protected]", "phone": "+9-876-543-3210", "address": [123, "1st street", "New York", "New York", 12345]}');
+SELECT (jsonb_users.profile).name from jsonb_users;
+ name
+---------
+ "Alice"
+ "Bob"
+(2 rows)
+
+SELECT (jsonb_users.profile).email from jsonb_users;
+ email
+---------------------
+ "[email protected]"
+ "[email protected]"
+(2 rows)
+
+SELECT (jsonb_users.profile).phone from jsonb_users;
+ phone
+-------------------
+ "+1-123-456-7890"
+ "+9-876-543-3210"
+(2 rows)
+
+SELECT (jsonb_users.profile).address from jsonb_users;
+ address
+----------------------------------------------------
+
+ [123, "1st street", "New York", "New York", 12345]
+(2 rows)
+
+SELECT (jsonb_users.profile).address[3] from jsonb_users;
+ address
+------------
+
+ "New York"
+(2 rows)
+
+-- array of nested domains over jsonb
+CREATE TABLE jsonb_user_arrs (id SERIAL PRIMARY KEY, profiles jsonb_user_profile[]);
+INSERT INTO jsonb_user_arrs (profiles) VALUES (ARRAY['{"name": "Alice", "email": "[email protected]", "phone": "+1-123-456-7890"}'::jsonb_user_profile, '{"name": "Bob", "email": "[email protected]", "phone": "+9-876-543-3210", "address": [123, "1st street", "New York", "New York", 12345]}'::jsonb_user_profile]);
+SELECT jsonb_user_arrs.profiles[1] from jsonb_user_arrs;
+ profiles
+-----------------------------------------------------------------------------
+ {"name": "Alice", "email": "[email protected]", "phone": "+1-123-456-7890"}
+(1 row)
+
+SELECT jsonb_user_arrs.profiles[2] from jsonb_user_arrs;
+ profiles
+----------------------------------------------------------------------------------------------------------------------------------------
+ {"name": "Bob", "email": "[email protected]", "phone": "+9-876-543-3210", "address": [123, "1st street", "New York", "New York", 12345]}
+(1 row)
+
+SELECT jsonb_user_arrs.profiles[2].address[0] from jsonb_user_arrs;
+ address
+---------
+ 123
+(1 row)
+
+drop table jsonb_users;
+drop table jsonb_user_arrs;
+drop domain jsonb_user_profile;
+drop domain jsonb_with_name_and_email;
+drop domain jsonb_with_name;
diff --git a/src/test/regress/sql/json.sql b/src/test/regress/sql/json.sql
index 8251f4f400..25fb1259ab 100644
--- a/src/test/regress/sql/json.sql
+++ b/src/test/regress/sql/json.sql
@@ -869,3 +869,89 @@ select ts_headline('english', '{"a": "aaa bbb", "b": {"c": "ccc ddd fff", "c1":
select ts_headline('null'::json, tsquery('aaa & bbb'));
select ts_headline('{}'::json, tsquery('aaa & bbb'));
select ts_headline('[]'::json, tsquery('aaa & bbb'));
+
+-- simple dot notation
+drop table if exists test_json_dot;
+create table test_json_dot(id int, test_json json);
+insert into test_json_dot select 1, '{"a": 1, "b": 42}'::json;
+insert into test_json_dot select 1, '{"a": 2, "b": {"c": 42}}'::json;
+insert into test_json_dot select 1, '{"a": 3, "b": {"c": "42"}, "d":[11, 12]}'::json;
+insert into test_json_dot select 1, '{"a": 3, "b": {"c": "42"}, "d":[{"x": [11, 12]}, {"y": [21, 22]}]}'::json;
+
+-- member object access
+select (test_json_dot.test_json).b, json_query(test_json, 'lax $.b' WITH CONDITIONAL WRAPPER NULL ON EMPTY NULL ON ERROR) as expected from test_json_dot;
+select (test_json_dot.test_json).b.c, json_query(test_json, 'lax $.b.c' WITH CONDITIONAL WRAPPER NULL ON EMPTY NULL ON ERROR) as expected from test_json_dot;
+select (test_json_dot.test_json).d, json_query(test_json, 'lax $.d' WITH CONDITIONAL WRAPPER NULL ON EMPTY NULL ON ERROR) as expected from test_json_dot;
+select (test_json_dot.test_json)."d", json_query(test_json, 'lax $.d' WITH CONDITIONAL WRAPPER NULL ON EMPTY NULL ON ERROR) as expected from test_json_dot;
+select (test_json_dot.test_json).'d' from test_json_dot;
+select (test_json_dot.test_json)['d'] from test_json_dot;
+
+-- wildcard access is not supported
+select (test_json_dot.test_json).* from test_json_dot;
+
+-- array element access
+select (test_json_dot.test_json).d->0 from test_json_dot;
+select (test_json_dot.test_json).d[0], json_query(test_json, 'lax $.d[0]' WITH CONDITIONAL WRAPPER NULL ON EMPTY NULL ON ERROR) as expected from test_json_dot;
+select (test_json_dot.test_json).d[1], json_query(test_json, 'lax $.d[1]' WITH CONDITIONAL WRAPPER NULL ON EMPTY NULL ON ERROR) as expected from test_json_dot;
+select (test_json_dot.test_json).d[0:] from test_json_dot;
+select (test_json_dot.test_json).d[0::int] from test_json_dot;
+select (test_json_dot.test_json).d[0::float] from test_json_dot;
+select (test_json_dot.test_json).d[0].x[1], json_query(test_json, 'lax $.d[0].x[1]' WITH CONDITIONAL WRAPPER NULL ON EMPTY NULL ON ERROR) as expected from test_json_dot;
+
+-- complex type with domain over json
+create domain json_d as json;
+create type comp_json as (a int, b json_d);
+create table test_json_domain_dot(a comp_json);
+insert into test_json_domain_dot select $$ (1,"{""a"": 3, ""key1"": {""c"": ""42""}, ""key2"": [11, 12]}") $$;
+insert into test_json_domain_dot select $$ (1,"{""a"": 3, ""key1"": {""c"": ""42""}, ""key2"": [11, 12, {""x"": [31, 42]}]}") $$;
+--object access
+select (test_json_domain_dot.a).b.key1.c from test_json_domain_dot;
+select (test_json_domain_dot.a).b.key2 from test_json_domain_dot;
+select (test_json_domain_dot.a).b.key2[0] from test_json_domain_dot;
+select (test_json_domain_dot.a).b.key2[0::text] from test_json_domain_dot;
+select (test_json_domain_dot.a).b.key2[2].x[1] from test_json_domain_dot;
+-- array access
+insert into test_json_domain_dot select $$ (1,"[{""a"": 3}, {""key1"": {""c"": ""42""}}, {""key2"": [11, 12]}]") $$;
+select (test_json_domain_dot.a).b[0] from test_json_domain_dot;
+select (test_json_domain_dot.a).b[0:] from test_json_domain_dot;
+drop table test_json_domain_dot cascade;
+drop type comp_json cascade;
+drop domain json_d cascade;
+
+-- nested domains over json
+CREATE DOMAIN json_with_name AS json
+ CHECK (
+ -- check that JSON has a "name" field and that it is a string
+ json_typeof(VALUE->'name') = 'string'
+ );
+CREATE DOMAIN json_with_name_and_email AS json_with_name
+ CHECK (
+ -- ensure that if "email" exists, it follows a simple email format
+ VALUE->'email' IS NULL OR (VALUE->>'email' ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$')
+ );
+CREATE DOMAIN json_user_profile AS json_with_name_and_email
+ CHECK (
+ -- ensure that if "phone" exists, it follows a basic phone format
+ VALUE->'phone' IS NULL OR (VALUE->>'phone' ~ '^\+\d{1,3}-\d{3}-\d{3}-\d{4}$')
+ );
+CREATE TABLE json_users (id SERIAL PRIMARY KEY, profile json_user_profile);
+INSERT INTO json_users (profile) VALUES ('{"name": "Alice", "email": "[email protected]", "phone": "+1-123-456-7890"}');
+INSERT INTO json_users (profile) VALUES ('{"name": "Bob", "email": "[email protected]", "phone": "+9-876-543-3210", "address": [123, "1st street", "New York", "New York", 12345]}');
+SELECT (json_users.profile).name from json_users;
+SELECT (json_users.profile).email from json_users;
+SELECT (json_users.profile).phone from json_users;
+SELECT (json_users.profile).address from json_users;
+SELECT (json_users.profile).address[3] from json_users;
+
+-- array of nested domains over json
+CREATE TABLE json_user_arrs (id SERIAL PRIMARY KEY, profiles json_user_profile[]);
+INSERT INTO json_user_arrs (profiles) VALUES (ARRAY['{"name": "Alice", "email": "[email protected]", "phone": "+1-123-456-7890"}'::json_user_profile, '{"name": "Bob", "email": "[email protected]", "phone": "+9-876-543-3210", "address": [123, "1st street", "New York", "New York", 12345]}'::json_user_profile]);
+SELECT json_user_arrs.profiles[1] from json_user_arrs;
+SELECT json_user_arrs.profiles[2] from json_user_arrs;
+SELECT json_user_arrs.profiles[2].address[0] from json_user_arrs;
+
+drop table json_users;
+drop table json_user_arrs;
+drop domain json_user_profile;
+drop domain json_with_name_and_email;
+drop domain json_with_name;
diff --git a/src/test/regress/sql/jsonb.sql b/src/test/regress/sql/jsonb.sql
index 5f0190d5a2..b6096021bc 100644
--- a/src/test/regress/sql/jsonb.sql
+++ b/src/test/regress/sql/jsonb.sql
@@ -1559,3 +1559,86 @@ select '12345.0000000000000000000000000000000000000000000005'::jsonb::float8;
select '12345.0000000000000000000000000000000000000000000005'::jsonb::int2;
select '12345.0000000000000000000000000000000000000000000005'::jsonb::int4;
select '12345.0000000000000000000000000000000000000000000005'::jsonb::int8;
+
+-- simple dot notation
+drop table if exists test_jsonb_dot;
+create table test_jsonb_dot(id int, test_jsonb jsonb);
+insert into test_jsonb_dot select 1, '{"a": 1, "b": 42}'::json;
+insert into test_jsonb_dot select 1, '{"a": 2, "b": {"c": 42}}'::json;
+insert into test_jsonb_dot select 1, '{"a": 3, "b": {"c": "42"}, "d":[11, 12]}'::json;
+
+-- member object access
+select (test_jsonb_dot.test_jsonb).b from test_jsonb_dot;
+select (test_jsonb_dot.test_jsonb).b.c from test_jsonb_dot;
+select (test_jsonb_dot.test_jsonb).d from test_jsonb_dot;
+select (test_jsonb_dot.test_jsonb)."d" from test_jsonb_dot;
+select (test_jsonb_dot.test_jsonb).'d' from test_jsonb_dot;
+select (test_jsonb_dot.test_jsonb)['d'] from test_jsonb_dot;
+
+-- array element access
+select (test_jsonb_dot.test_jsonb).d[0] from test_jsonb_dot;
+select (test_jsonb_dot.test_jsonb).d[0:] from test_jsonb_dot;
+select (test_jsonb_dot.test_jsonb).d[0::int] from test_jsonb_dot;
+select (test_jsonb_dot.test_jsonb).d[0::float] from test_jsonb_dot;
+select (test_jsonb_dot.test_jsonb).d[0].x[1] from test_jsonb_dot;
+
+-- wildcard access is not supported
+select (test_jsonb_dot.test_jsonb).* from test_jsonb_dot;
+
+-- complex type with domain over jsonb
+create domain jsonb_d as jsonb;
+create type comp_jsonb as (a int, b jsonb_d);
+create table test_jsonb_domain_dot(a comp_jsonb);
+insert into test_jsonb_domain_dot select $$ (1,"{""a"": 3, ""key1"": {""c"": ""42""}, ""key2"": [11, 12]}") $$;
+insert into test_jsonb_domain_dot select $$ (1,"{""a"": 3, ""key1"": {""c"": ""42""}, ""key2"": [11, 12, {""x"": [31, 42]}]}") $$;
+-- object access
+select (test_jsonb_domain_dot.a).b.key1.c from test_jsonb_domain_dot;
+select (test_jsonb_domain_dot.a).b.key2 from test_jsonb_domain_dot;
+select (test_jsonb_domain_dot.a).b.key2[0] from test_jsonb_domain_dot;
+select (test_jsonb_domain_dot.a).b.key2[0::text] from test_jsonb_domain_dot;
+select (test_jsonb_domain_dot.a).b.key2[2].x[1] from test_jsonb_domain_dot;
+-- array access
+insert into test_jsonb_domain_dot select $$ (1,"[{""a"": 3}, {""key1"": {""c"": ""42""}}, {""key2"": [11, 12]}]") $$;
+select (test_jsonb_domain_dot.a).b[0] from test_jsonb_domain_dot;
+select (test_jsonb_domain_dot.a).b[0:] from test_jsonb_domain_dot;
+drop table test_jsonb_domain_dot cascade;
+drop type comp_jsonb cascade;
+drop domain jsonb_d cascade;
+
+-- nested domains over jsonb
+CREATE DOMAIN jsonb_with_name AS JSONB
+ CHECK (
+ -- check that JSON has a "name" field and that it is a string
+ VALUE ? 'name' AND jsonb_typeof(VALUE->'name') = 'string'
+ );
+CREATE DOMAIN jsonb_with_name_and_email AS jsonb_with_name
+ CHECK (
+ -- ensure that if "email" exists, it follows a simple email format
+ NOT VALUE ? 'email' OR (VALUE->>'email' ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$')
+ );
+CREATE DOMAIN jsonb_user_profile AS jsonb_with_name_and_email
+ CHECK (
+ -- ensure that if "phone" exists, it follows a basic phone format
+ NOT VALUE ? 'phone' OR (VALUE->>'phone' ~ '^\+\d{1,3}-\d{3}-\d{3}-\d{4}$')
+ );
+CREATE TABLE jsonb_users (id SERIAL PRIMARY KEY, profile jsonb_user_profile);
+INSERT INTO jsonb_users (profile) VALUES ('{"name": "Alice", "email": "[email protected]", "phone": "+1-123-456-7890"}');
+INSERT INTO jsonb_users (profile) VALUES ('{"name": "Bob", "email": "[email protected]", "phone": "+9-876-543-3210", "address": [123, "1st street", "New York", "New York", 12345]}');
+SELECT (jsonb_users.profile).name from jsonb_users;
+SELECT (jsonb_users.profile).email from jsonb_users;
+SELECT (jsonb_users.profile).phone from jsonb_users;
+SELECT (jsonb_users.profile).address from jsonb_users;
+SELECT (jsonb_users.profile).address[3] from jsonb_users;
+
+-- array of nested domains over jsonb
+CREATE TABLE jsonb_user_arrs (id SERIAL PRIMARY KEY, profiles jsonb_user_profile[]);
+INSERT INTO jsonb_user_arrs (profiles) VALUES (ARRAY['{"name": "Alice", "email": "[email protected]", "phone": "+1-123-456-7890"}'::jsonb_user_profile, '{"name": "Bob", "email": "[email protected]", "phone": "+9-876-543-3210", "address": [123, "1st street", "New York", "New York", 12345]}'::jsonb_user_profile]);
+SELECT jsonb_user_arrs.profiles[1] from jsonb_user_arrs;
+SELECT jsonb_user_arrs.profiles[2] from jsonb_user_arrs;
+SELECT jsonb_user_arrs.profiles[2].address[0] from jsonb_user_arrs;
+
+drop table jsonb_users;
+drop table jsonb_user_arrs;
+drop domain jsonb_user_profile;
+drop domain jsonb_with_name_and_email;
+drop domain jsonb_with_name;
--
2.39.5 (Apple Git-154)
