v2-0001-Transform-JSON-dot-access-to-arrow-operator.txt
text/plain
Filename: v2-0001-Transform-JSON-dot-access-to-arrow-operator.txt
Type: text/plain
Part: 0
From 99ee1cbc45ebd76ce21881bda95933d8a5a104c6 Mon Sep 17 00:00:00 2001
From: Alexandra Wang <alexandra.wang.oss@gmail.com>
Date: Thu, 15 Aug 2024 02:11:33 -0700
Subject: [PATCH v2] Transform JSON dot access to arrow operator
Enabled dot-notation access to JSON/JSONB object by making a syntatic
sugar for the "->" operator in ParseFuncOrColumn() for arg of
JSON/JSONB type.
JSON array access via subscripting is not yet supported in this patch,
but can be implemented similarly by creating an OpExpr for the
json_array_element "->" operator.
Note that the output of the "->" operators are not wrapped by
brackets, which differs from the SQL standard specification for the
JSON simplified accessor equivalence shown below:
JSON_QUERY (VEP, 'lax $.JC' WITH CONDITIONAL ARRAY WRAPPER NULL ON EMPTY NULL ON ERROR)
---
src/backend/parser/parse_func.c | 57 +++++++++++++++++++++---
src/include/catalog/pg_operator.dat | 4 +-
src/include/parser/parse_type.h | 1 +
src/test/regress/expected/json.out | 67 +++++++++++++++++++++++++++++
src/test/regress/expected/jsonb.out | 55 +++++++++++++++++++++++
src/test/regress/sql/json.sql | 20 +++++++++
src/test/regress/sql/jsonb.sql | 17 ++++++++
7 files changed, 214 insertions(+), 7 deletions(-)
diff --git a/src/backend/parser/parse_func.c b/src/backend/parser/parse_func.c
index 9b23344a3b..431c9883f2 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 */
@@ -48,6 +50,8 @@ static void unify_hypothetical_args(ParseState *pstate,
static Oid FuncNameAsType(List *funcname);
static Node *ParseComplexProjection(ParseState *pstate, const char *funcname,
Node *first_arg, int location);
+static Node *ParseJsonSimplifiedAccessorProjection(ParseState *pstate, const char *funcname,
+ Node *first_arg, int location);
static Oid LookupFuncNameInternal(ObjectType objtype, List *funcname,
int nargs, const Oid *argtypes,
bool include_out_arguments, bool missing_ok,
@@ -226,17 +230,24 @@ ParseFuncOrColumn(ParseState *pstate, List *funcname, List *fargs,
!func_variadic && argnames == NIL &&
list_length(funcname) == 1 &&
(actual_arg_types[0] == RECORDOID ||
- ISCOMPLEX(actual_arg_types[0])));
+ ISCOMPLEX(actual_arg_types[0]) ||
+ ISJSON(actual_arg_types[0])));
/*
* If it's column syntax, check for column projection case first.
*/
if (could_be_projection && is_column)
{
- retval = ParseComplexProjection(pstate,
- strVal(linitial(funcname)),
- first_arg,
- location);
+ if (ISJSON(actual_arg_types[0]))
+ retval = ParseJsonSimplifiedAccessorProjection(pstate,
+ strVal(linitial(funcname)),
+ first_arg,
+ location);
+ else
+ retval = ParseComplexProjection(pstate,
+ strVal(linitial(funcname)),
+ first_arg,
+ location);
if (retval)
return retval;
@@ -1902,6 +1913,42 @@ FuncNameAsType(List *funcname)
return result;
}
+/*
+ * ParseJsonSimplifiedAccessorProjection -
+ * 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.
+ */
+static Node *
+ParseJsonSimplifiedAccessorProjection(ParseState *pstate, const char *funcname,
+ Node *first_arg, int location)
+{
+ OpExpr *result;
+ Node *rexpr;
+ rexpr = (Node *) makeConst(
+ TEXTOID,
+ -1,
+ InvalidOid,
+ -1,
+ CStringGetTextDatum(funcname),
+ false,
+ false);
+
+ result = makeNode(OpExpr);
+ if (exprType(first_arg) == JSONOID) {
+ result->opno = OID_JSON_OBJECT_FIELD_OP;
+ result->opresulttype = JSONOID;
+ } else {
+ Assert(exprType(first_arg) == JSONBOID);
+ result->opno = OID_JSONB_OBJECT_FIELD_OP;
+ result->opresulttype = JSONBOID;
+ }
+ result->opfuncid = get_opcode(result->opno);
+ 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..0ef9d80357 100644
--- a/src/include/catalog/pg_operator.dat
+++ b/src/include/catalog/pg_operator.dat
@@ -3154,7 +3154,7 @@
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',
@@ -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_type.h b/src/include/parser/parse_type.h
index b62e7a6ce9..9c8b3bfb2f 100644
--- a/src/include/parser/parse_type.h
+++ b/src/include/parser/parse_type.h
@@ -57,5 +57,6 @@ extern bool parseTypeString(const char *str, Oid *typeid_p, int32 *typmod_p,
/* true if typeid is composite, or domain over composite, but not RECORD */
#define ISCOMPLEX(typeid) (typeOrDomainTypeRelid(typeid) != InvalidOid)
+#define ISJSON(typeid) (typeid == JSONOID || typeid == JSONBOID)
#endif /* PARSE_TYPE_H */
diff --git a/src/test/regress/expected/json.out b/src/test/regress/expected/json.out
index 7df11c2f38..39bfa724c2 100644
--- a/src/test/regress/expected/json.out
+++ b/src/test/regress/expected/json.out
@@ -2713,3 +2713,70 @@ 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;
+ ^
+-- 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;
+ERROR: cannot subscript type json because it does not support subscripting
+LINE 1: select (test_json_dot.test_json).d[0], json_query(test_json,...
+ ^
+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;
+ERROR: cannot subscript type json because it does not support subscripting
+LINE 1: select (test_json_dot.test_json).d[1], json_query(test_json,...
+ ^
diff --git a/src/test/regress/expected/jsonb.out b/src/test/regress/expected/jsonb.out
index 7d163a156e..c30f3e174c 100644
--- a/src/test/regress/expected/jsonb.out
+++ b/src/test/regress/expected/jsonb.out
@@ -5715,3 +5715,58 @@ 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_json_dot.test_json).d from test_json_dot;
+ d
+------------------------------------
+
+
+ [11, 12]
+ [{"x": [11, 12]}, {"y": [21, 22]}]
+(4 rows)
+
+select (test_json_dot.test_json)."d" from test_json_dot;
+ d
+------------------------------------
+
+
+ [11, 12]
+ [{"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;
+ ^
+-- array element access
+select (test_jsonb_dot.test_jsonb).d[0] from test_jsonb_dot;
+ d
+----
+
+
+ 11
+(3 rows)
+
diff --git a/src/test/regress/sql/json.sql b/src/test/regress/sql/json.sql
index 5c886cd6b3..f9b2f7a35d 100644
--- a/src/test/regress/sql/json.sql
+++ b/src/test/regress/sql/json.sql
@@ -867,3 +867,23 @@ 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;
+
+-- 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;
diff --git a/src/test/regress/sql/jsonb.sql b/src/test/regress/sql/jsonb.sql
index 5f0190d5a2..f2b800910d 100644
--- a/src/test/regress/sql/jsonb.sql
+++ b/src/test/regress/sql/jsonb.sql
@@ -1559,3 +1559,20 @@ 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_json_dot.test_json).d 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;
+
+-- array element access
+select (test_jsonb_dot.test_jsonb).d[0] from test_jsonb_dot;
--
2.39.3 (Apple Git-146)