v1-0001-refactor-sqljson_jsontable-related-tests.no-cfbot
application/octet-stream
Filename: v1-0001-refactor-sqljson_jsontable-related-tests.no-cfbot
Type: application/octet-stream
Part: 1
Message:
Re: remaining sql/json patches
From a316292396ee95cd22c569fb3726e74c8394dd7f Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
Date: Thu, 25 Jan 2024 17:07:18 +0800
Subject: [PATCH v1 1/1] refactor sqljson_jsontable related tests.
some of the tests in src/test/regress/sql/sqljson_jsontable.sql
output is way too wide.
make it short.
---
.../regress/expected/sqljson_jsontable.out | 291 +++++++++++++-----
src/test/regress/sql/sqljson_jsontable.sql | 103 +++++--
2 files changed, 281 insertions(+), 113 deletions(-)
diff --git a/src/test/regress/expected/sqljson_jsontable.out b/src/test/regress/expected/sqljson_jsontable.out
index 7e2ca899..f7ae70a0 100644
--- a/src/test/regress/expected/sqljson_jsontable.out
+++ b/src/test/regress/expected/sqljson_jsontable.out
@@ -182,36 +182,12 @@ FROM json_table_test vals
(14 rows)
-- JSON_TABLE: Test backward parsing
-CREATE VIEW jsonb_table_view AS
+CREATE VIEW jsonb_table_view1 AS
SELECT * FROM
JSON_TABLE(
jsonb 'null', 'lax $[*]' PASSING 1 + 2 AS a, json '"foo"' AS "b c"
COLUMNS (
id FOR ORDINALITY,
- "int" int PATH '$',
- "text" text PATH '$',
- "char(4)" char(4) PATH '$',
- "bool" bool PATH '$',
- "numeric" numeric PATH '$',
- "domain" jsonb_test_domain PATH '$',
- js json PATH '$',
- jb jsonb PATH '$',
- jst text FORMAT JSON PATH '$',
- jsc char(4) FORMAT JSON PATH '$',
- jsv varchar(4) FORMAT JSON PATH '$',
- jsb jsonb FORMAT JSON PATH '$',
- jsbq jsonb FORMAT JSON PATH '$' OMIT QUOTES,
- aaa int, -- implicit path '$."aaa"',
- aaa1 int PATH '$.aaa',
- exists1 bool EXISTS PATH '$.aaa',
- exists2 int EXISTS PATH '$.aaa' TRUE ON ERROR,
- exists3 text EXISTS PATH 'strict $.aaa' UNKNOWN ON ERROR,
- js2 json PATH '$',
- jsb2w jsonb PATH '$' WITH WRAPPER,
- jsb2q jsonb PATH '$' OMIT QUOTES,
- ia int[] PATH '$',
- ta text[] PATH '$',
- jba jsonb[] PATH '$',
NESTED PATH '$[1]' AS p1 COLUMNS (
a1 int,
NESTED PATH '$[*]' AS "p1 1" COLUMNS (
@@ -229,33 +205,58 @@ SELECT * FROM
)
)
);
-\sv jsonb_table_view
-CREATE OR REPLACE VIEW public.jsonb_table_view AS
+CREATE VIEW jsonb_table_view2 AS
+SELECT * FROM
+ JSON_TABLE(
+ jsonb 'null', 'lax $[*]' PASSING 1 + 2 AS a, json '"foo"' AS "b c"
+ COLUMNS (
+ "int" int PATH '$',
+ "text" text PATH '$',
+ "char(4)" char(4) PATH '$',
+ "bool" bool PATH '$',
+ "numeric" numeric PATH '$',
+ "domain" jsonb_test_domain PATH '$'));
+CREATE VIEW jsonb_table_view3 AS
+SELECT * FROM
+ JSON_TABLE(
+ jsonb 'null', 'lax $[*]' PASSING 1 + 2 AS a, json '"foo"' AS "b c"
+ COLUMNS (
+ js json PATH '$',
+ jb jsonb PATH '$',
+ jst text FORMAT JSON PATH '$',
+ jsc char(4) FORMAT JSON PATH '$',
+ jsv varchar(4) FORMAT JSON PATH '$'));
+CREATE VIEW jsonb_table_view4 AS
+SELECT * FROM
+ JSON_TABLE(
+ jsonb 'null', 'lax $[*]' PASSING 1 + 2 AS a, json '"foo"' AS "b c"
+ COLUMNS (
+ jsb jsonb FORMAT JSON PATH '$',
+ jsbq jsonb FORMAT JSON PATH '$' OMIT QUOTES,
+ aaa int, -- implicit path '$."aaa"',
+ aaa1 int PATH '$.aaa'));
+CREATE VIEW jsonb_table_view5 AS
+SELECT * FROM
+ JSON_TABLE(
+ jsonb 'null', 'lax $[*]' PASSING 1 + 2 AS a, json '"foo"' AS "b c"
+ COLUMNS (
+ exists1 bool EXISTS PATH '$.aaa',
+ exists2 int EXISTS PATH '$.aaa' TRUE ON ERROR,
+ exists3 text EXISTS PATH 'strict $.aaa' UNKNOWN ON ERROR));
+CREATE VIEW jsonb_table_view6 AS
+SELECT * FROM
+ JSON_TABLE(
+ jsonb 'null', 'lax $[*]' PASSING 1 + 2 AS a, json '"foo"' AS "b c"
+ COLUMNS (
+ js2 json PATH '$',
+ jsb2w jsonb PATH '$' WITH WRAPPER,
+ jsb2q jsonb PATH '$' OMIT QUOTES,
+ ia int[] PATH '$',
+ ta text[] PATH '$',
+ jba jsonb[] PATH '$'));
+\sv jsonb_table_view1
+CREATE OR REPLACE VIEW public.jsonb_table_view1 AS
SELECT id,
- "int",
- text,
- "char(4)",
- bool,
- "numeric",
- domain,
- js,
- jb,
- jst,
- jsc,
- jsv,
- jsb,
- jsbq,
- aaa,
- aaa1,
- exists1,
- exists2,
- exists3,
- js2,
- jsb2w,
- jsb2q,
- ia,
- ta,
- jba,
a1,
b1,
a11,
@@ -268,59 +269,178 @@ CREATE OR REPLACE VIEW public.jsonb_table_view AS
'"foo"'::json AS "b c"
COLUMNS (
id FOR ORDINALITY,
+ NESTED PATH '$[1]' AS p1
+ COLUMNS (
+ a1 integer PATH '$."a1"',
+ b1 text PATH '$."b1"',
+ NESTED PATH '$[*]' AS "p1 1"
+ COLUMNS (
+ a11 text PATH '$."a11"'
+ )
+ ),
+ NESTED PATH '$[2]' AS p2
+ COLUMNS (
+ NESTED PATH '$[*]' AS "p2:1"
+ COLUMNS (
+ a21 text PATH '$."a21"'
+ ),
+ NESTED PATH '$[*]' AS p22
+ COLUMNS (
+ a22 text PATH '$."a22"'
+ )
+ )
+ )
+ PLAN (json_table_path_0 OUTER ((p1 OUTER "p1 1") UNION (p2 OUTER ("p2:1" UNION p22))))
+ )
+\sv jsonb_table_view2
+CREATE OR REPLACE VIEW public.jsonb_table_view2 AS
+ SELECT "int",
+ text,
+ "char(4)",
+ bool,
+ "numeric",
+ domain
+ FROM JSON_TABLE(
+ 'null'::jsonb, '$[*]' AS json_table_path_0
+ PASSING
+ 1 + 2 AS a,
+ '"foo"'::json AS "b c"
+ COLUMNS (
"int" integer PATH '$',
text text PATH '$',
"char(4)" character(4) PATH '$',
bool boolean PATH '$',
"numeric" numeric PATH '$',
- domain jsonb_test_domain PATH '$',
+ domain jsonb_test_domain PATH '$'
+ )
+ PLAN (json_table_path_0)
+ )
+\sv jsonb_table_view3
+CREATE OR REPLACE VIEW public.jsonb_table_view3 AS
+ SELECT js,
+ jb,
+ jst,
+ jsc,
+ jsv
+ FROM JSON_TABLE(
+ 'null'::jsonb, '$[*]' AS json_table_path_0
+ PASSING
+ 1 + 2 AS a,
+ '"foo"'::json AS "b c"
+ COLUMNS (
js json PATH '$',
jb jsonb PATH '$',
jst text FORMAT JSON PATH '$',
jsc character(4) FORMAT JSON PATH '$',
- jsv character varying(4) FORMAT JSON PATH '$',
+ jsv character varying(4) FORMAT JSON PATH '$'
+ )
+ PLAN (json_table_path_0)
+ )
+\sv jsonb_table_view4
+CREATE OR REPLACE VIEW public.jsonb_table_view4 AS
+ SELECT jsb,
+ jsbq,
+ aaa,
+ aaa1
+ FROM JSON_TABLE(
+ 'null'::jsonb, '$[*]' AS json_table_path_0
+ PASSING
+ 1 + 2 AS a,
+ '"foo"'::json AS "b c"
+ COLUMNS (
jsb jsonb PATH '$',
jsbq jsonb PATH '$' OMIT QUOTES,
aaa integer PATH '$."aaa"',
- aaa1 integer PATH '$."aaa"',
+ aaa1 integer PATH '$."aaa"'
+ )
+ PLAN (json_table_path_0)
+ )
+\sv jsonb_table_view5
+CREATE OR REPLACE VIEW public.jsonb_table_view5 AS
+ SELECT exists1,
+ exists2,
+ exists3
+ FROM JSON_TABLE(
+ 'null'::jsonb, '$[*]' AS json_table_path_0
+ PASSING
+ 1 + 2 AS a,
+ '"foo"'::json AS "b c"
+ COLUMNS (
exists1 boolean EXISTS PATH '$."aaa"',
exists2 integer EXISTS PATH '$."aaa"' TRUE ON ERROR,
- exists3 text EXISTS PATH 'strict $."aaa"' UNKNOWN ON ERROR,
+ exists3 text EXISTS PATH 'strict $."aaa"' UNKNOWN ON ERROR
+ )
+ PLAN (json_table_path_0)
+ )
+\sv jsonb_table_view6
+CREATE OR REPLACE VIEW public.jsonb_table_view6 AS
+ SELECT js2,
+ jsb2w,
+ jsb2q,
+ ia,
+ ta,
+ jba
+ FROM JSON_TABLE(
+ 'null'::jsonb, '$[*]' AS json_table_path_0
+ PASSING
+ 1 + 2 AS a,
+ '"foo"'::json AS "b c"
+ COLUMNS (
js2 json PATH '$',
jsb2w jsonb PATH '$' WITH UNCONDITIONAL WRAPPER,
jsb2q jsonb PATH '$' OMIT QUOTES,
ia integer[] PATH '$',
ta text[] PATH '$',
- jba jsonb[] PATH '$',
- NESTED PATH '$[1]' AS p1
- COLUMNS (
- a1 integer PATH '$."a1"',
- b1 text PATH '$."b1"',
- NESTED PATH '$[*]' AS "p1 1"
- COLUMNS (
- a11 text PATH '$."a11"'
- )
- ),
- NESTED PATH '$[2]' AS p2
- COLUMNS (
- NESTED PATH '$[*]' AS "p2:1"
- COLUMNS (
- a21 text PATH '$."a21"'
- ),
- NESTED PATH '$[*]' AS p22
- COLUMNS (
- a22 text PATH '$."a22"'
- )
- )
+ jba jsonb[] PATH '$'
)
- PLAN (json_table_path_0 OUTER ((p1 OUTER "p1 1") UNION (p2 OUTER ("p2:1" UNION p22))))
+ PLAN (json_table_path_0)
)
-EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM jsonb_table_view;
- QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM jsonb_table_view1;
+ QUERY PLAN
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Table Function Scan on "JSON_TABLE" "json_table"
- Output: "json_table".id, "json_table"."int", "json_table".text, "json_table"."char(4)", "json_table".bool, "json_table"."numeric", "json_table".domain, "json_table".js, "json_table".jb, "json_table".jst, "json_table".jsc, "json_table".jsv, "json_table".jsb, "json_table".jsbq, "json_table".aaa, "json_table".aaa1, "json_table".exists1, "json_table".exists2, "json_table".exists3, "json_table".js2, "json_table".jsb2w, "json_table".jsb2q, "json_table".ia, "json_table".ta, "json_table".jba, "json_table".a1, "json_table".b1, "json_table".a11, "json_table".a21, "json_table".a22
- Table Function Call: JSON_TABLE('null'::jsonb, '$[*]' AS json_table_path_0 PASSING 3 AS a, '"foo"'::jsonb AS "b c" COLUMNS (id FOR ORDINALITY, "int" integer PATH '$', text text PATH '$', "char(4)" character(4) PATH '$', bool boolean PATH '$', "numeric" numeric PATH '$', domain jsonb_test_domain PATH '$', js json PATH '$', jb jsonb PATH '$', jst text FORMAT JSON PATH '$', jsc character(4) FORMAT JSON PATH '$', jsv character varying(4) FORMAT JSON PATH '$', jsb jsonb PATH '$', jsbq jsonb PATH '$' OMIT QUOTES, aaa integer PATH '$."aaa"', aaa1 integer PATH '$."aaa"', exists1 boolean EXISTS PATH '$."aaa"', exists2 integer EXISTS PATH '$."aaa"' TRUE ON ERROR, exists3 text EXISTS PATH 'strict $."aaa"' UNKNOWN ON ERROR, js2 json PATH '$', jsb2w jsonb PATH '$' WITH UNCONDITIONAL WRAPPER, jsb2q jsonb PATH '$' OMIT QUOTES, ia integer[] PATH '$', ta text[] PATH '$', jba jsonb[] PATH '$', NESTED PATH '$[1]' AS p1 COLUMNS (a1 integer PATH '$."a1"', b1 text PATH '$."b1"', NESTED PATH '$[*]' AS "p1 1" COLUMNS (a11 text PATH '$."a11"')), NESTED PATH '$[2]' AS p2 COLUMNS ( NESTED PATH '$[*]' AS "p2:1" COLUMNS (a21 text PATH '$."a21"'), NESTED PATH '$[*]' AS p22 COLUMNS (a22 text PATH '$."a22"'))) PLAN (json_table_path_0 OUTER ((p1 OUTER "p1 1") UNION (p2 OUTER ("p2:1" UNION p22)))))
+ Output: "json_table".id, "json_table".a1, "json_table".b1, "json_table".a11, "json_table".a21, "json_table".a22
+ Table Function Call: JSON_TABLE('null'::jsonb, '$[*]' AS json_table_path_0 PASSING 3 AS a, '"foo"'::jsonb AS "b c" COLUMNS (id FOR ORDINALITY, NESTED PATH '$[1]' AS p1 COLUMNS (a1 integer PATH '$."a1"', b1 text PATH '$."b1"', NESTED PATH '$[*]' AS "p1 1" COLUMNS (a11 text PATH '$."a11"')), NESTED PATH '$[2]' AS p2 COLUMNS ( NESTED PATH '$[*]' AS "p2:1" COLUMNS (a21 text PATH '$."a21"'), NESTED PATH '$[*]' AS p22 COLUMNS (a22 text PATH '$."a22"'))) PLAN (json_table_path_0 OUTER ((p1 OUTER "p1 1") UNION (p2 OUTER ("p2:1" UNION p22)))))
+(3 rows)
+
+EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM jsonb_table_view2;
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Table Function Scan on "JSON_TABLE" "json_table"
+ Output: "json_table"."int", "json_table".text, "json_table"."char(4)", "json_table".bool, "json_table"."numeric", "json_table".domain
+ Table Function Call: JSON_TABLE('null'::jsonb, '$[*]' AS json_table_path_0 PASSING 3 AS a, '"foo"'::jsonb AS "b c" COLUMNS ("int" integer PATH '$', text text PATH '$', "char(4)" character(4) PATH '$', bool boolean PATH '$', "numeric" numeric PATH '$', domain jsonb_test_domain PATH '$') PLAN (json_table_path_0))
+(3 rows)
+
+EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM jsonb_table_view3;
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Table Function Scan on "JSON_TABLE" "json_table"
+ Output: "json_table".js, "json_table".jb, "json_table".jst, "json_table".jsc, "json_table".jsv
+ Table Function Call: JSON_TABLE('null'::jsonb, '$[*]' AS json_table_path_0 PASSING 3 AS a, '"foo"'::jsonb AS "b c" COLUMNS (js json PATH '$', jb jsonb PATH '$', jst text FORMAT JSON PATH '$', jsc character(4) FORMAT JSON PATH '$', jsv character varying(4) FORMAT JSON PATH '$') PLAN (json_table_path_0))
+(3 rows)
+
+EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM jsonb_table_view4;
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Table Function Scan on "JSON_TABLE" "json_table"
+ Output: "json_table".jsb, "json_table".jsbq, "json_table".aaa, "json_table".aaa1
+ Table Function Call: JSON_TABLE('null'::jsonb, '$[*]' AS json_table_path_0 PASSING 3 AS a, '"foo"'::jsonb AS "b c" COLUMNS (jsb jsonb PATH '$', jsbq jsonb PATH '$' OMIT QUOTES, aaa integer PATH '$."aaa"', aaa1 integer PATH '$."aaa"') PLAN (json_table_path_0))
+(3 rows)
+
+EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM jsonb_table_view5;
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Table Function Scan on "JSON_TABLE" "json_table"
+ Output: "json_table".exists1, "json_table".exists2, "json_table".exists3
+ Table Function Call: JSON_TABLE('null'::jsonb, '$[*]' AS json_table_path_0 PASSING 3 AS a, '"foo"'::jsonb AS "b c" COLUMNS (exists1 boolean EXISTS PATH '$."aaa"', exists2 integer EXISTS PATH '$."aaa"' TRUE ON ERROR, exists3 text EXISTS PATH 'strict $."aaa"' UNKNOWN ON ERROR) PLAN (json_table_path_0))
+(3 rows)
+
+EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM jsonb_table_view6;
+ QUERY PLAN
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Table Function Scan on "JSON_TABLE" "json_table"
+ Output: "json_table".js2, "json_table".jsb2w, "json_table".jsb2q, "json_table".ia, "json_table".ta, "json_table".jba
+ Table Function Call: JSON_TABLE('null'::jsonb, '$[*]' AS json_table_path_0 PASSING 3 AS a, '"foo"'::jsonb AS "b c" COLUMNS (js2 json PATH '$', jsb2w jsonb PATH '$' WITH UNCONDITIONAL WRAPPER, jsb2q jsonb PATH '$' OMIT QUOTES, ia integer[] PATH '$', ta text[] PATH '$', jba jsonb[] PATH '$') PLAN (json_table_path_0))
(3 rows)
-- JSON_TABLE() with alias
@@ -340,7 +460,12 @@ SELECT * FROM
Table Function Call: JSON_TABLE('null'::jsonb, '$[*]' AS json_table_path_0 PASSING 3 AS a, '"foo"'::jsonb AS "b c" COLUMNS (id FOR ORDINALITY, "int" integer PATH '$', text text PATH '$') PLAN (json_table_path_0))
(3 rows)
-DROP VIEW jsonb_table_view;
+DROP VIEW jsonb_table_view1;
+DROP VIEW jsonb_table_view2;
+DROP VIEW jsonb_table_view3;
+DROP VIEW jsonb_table_view4;
+DROP VIEW jsonb_table_view5;
+DROP VIEW jsonb_table_view6;
DROP DOMAIN jsonb_test_domain;
-- JSON_TABLE: only one FOR ORDINALITY columns allowed
SELECT * FROM JSON_TABLE(jsonb '1', '$' COLUMNS (id FOR ORDINALITY, id2 FOR ORDINALITY, a int PATH '$.a' ERROR ON EMPTY)) jt;
diff --git a/src/test/regress/sql/sqljson_jsontable.sql b/src/test/regress/sql/sqljson_jsontable.sql
index 4c81093d..677a14fd 100644
--- a/src/test/regress/sql/sqljson_jsontable.sql
+++ b/src/test/regress/sql/sqljson_jsontable.sql
@@ -103,38 +103,12 @@ FROM json_table_test vals
-- JSON_TABLE: Test backward parsing
-CREATE VIEW jsonb_table_view AS
+CREATE VIEW jsonb_table_view1 AS
SELECT * FROM
JSON_TABLE(
jsonb 'null', 'lax $[*]' PASSING 1 + 2 AS a, json '"foo"' AS "b c"
COLUMNS (
id FOR ORDINALITY,
- "int" int PATH '$',
- "text" text PATH '$',
- "char(4)" char(4) PATH '$',
- "bool" bool PATH '$',
- "numeric" numeric PATH '$',
- "domain" jsonb_test_domain PATH '$',
- js json PATH '$',
- jb jsonb PATH '$',
- jst text FORMAT JSON PATH '$',
- jsc char(4) FORMAT JSON PATH '$',
- jsv varchar(4) FORMAT JSON PATH '$',
- jsb jsonb FORMAT JSON PATH '$',
- jsbq jsonb FORMAT JSON PATH '$' OMIT QUOTES,
- aaa int, -- implicit path '$."aaa"',
- aaa1 int PATH '$.aaa',
- exists1 bool EXISTS PATH '$.aaa',
- exists2 int EXISTS PATH '$.aaa' TRUE ON ERROR,
- exists3 text EXISTS PATH 'strict $.aaa' UNKNOWN ON ERROR,
-
- js2 json PATH '$',
- jsb2w jsonb PATH '$' WITH WRAPPER,
- jsb2q jsonb PATH '$' OMIT QUOTES,
- ia int[] PATH '$',
- ta text[] PATH '$',
- jba jsonb[] PATH '$',
-
NESTED PATH '$[1]' AS p1 COLUMNS (
a1 int,
NESTED PATH '$[*]' AS "p1 1" COLUMNS (
@@ -153,9 +127,73 @@ SELECT * FROM
)
);
-\sv jsonb_table_view
+CREATE VIEW jsonb_table_view2 AS
+SELECT * FROM
+ JSON_TABLE(
+ jsonb 'null', 'lax $[*]' PASSING 1 + 2 AS a, json '"foo"' AS "b c"
+ COLUMNS (
+ "int" int PATH '$',
+ "text" text PATH '$',
+ "char(4)" char(4) PATH '$',
+ "bool" bool PATH '$',
+ "numeric" numeric PATH '$',
+ "domain" jsonb_test_domain PATH '$'));
-EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM jsonb_table_view;
+CREATE VIEW jsonb_table_view3 AS
+SELECT * FROM
+ JSON_TABLE(
+ jsonb 'null', 'lax $[*]' PASSING 1 + 2 AS a, json '"foo"' AS "b c"
+ COLUMNS (
+ js json PATH '$',
+ jb jsonb PATH '$',
+ jst text FORMAT JSON PATH '$',
+ jsc char(4) FORMAT JSON PATH '$',
+ jsv varchar(4) FORMAT JSON PATH '$'));
+
+CREATE VIEW jsonb_table_view4 AS
+SELECT * FROM
+ JSON_TABLE(
+ jsonb 'null', 'lax $[*]' PASSING 1 + 2 AS a, json '"foo"' AS "b c"
+ COLUMNS (
+ jsb jsonb FORMAT JSON PATH '$',
+ jsbq jsonb FORMAT JSON PATH '$' OMIT QUOTES,
+ aaa int, -- implicit path '$."aaa"',
+ aaa1 int PATH '$.aaa'));
+
+CREATE VIEW jsonb_table_view5 AS
+SELECT * FROM
+ JSON_TABLE(
+ jsonb 'null', 'lax $[*]' PASSING 1 + 2 AS a, json '"foo"' AS "b c"
+ COLUMNS (
+ exists1 bool EXISTS PATH '$.aaa',
+ exists2 int EXISTS PATH '$.aaa' TRUE ON ERROR,
+ exists3 text EXISTS PATH 'strict $.aaa' UNKNOWN ON ERROR));
+
+CREATE VIEW jsonb_table_view6 AS
+SELECT * FROM
+ JSON_TABLE(
+ jsonb 'null', 'lax $[*]' PASSING 1 + 2 AS a, json '"foo"' AS "b c"
+ COLUMNS (
+ js2 json PATH '$',
+ jsb2w jsonb PATH '$' WITH WRAPPER,
+ jsb2q jsonb PATH '$' OMIT QUOTES,
+ ia int[] PATH '$',
+ ta text[] PATH '$',
+ jba jsonb[] PATH '$'));
+
+\sv jsonb_table_view1
+\sv jsonb_table_view2
+\sv jsonb_table_view3
+\sv jsonb_table_view4
+\sv jsonb_table_view5
+\sv jsonb_table_view6
+
+EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM jsonb_table_view1;
+EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM jsonb_table_view2;
+EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM jsonb_table_view3;
+EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM jsonb_table_view4;
+EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM jsonb_table_view5;
+EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM jsonb_table_view6;
-- JSON_TABLE() with alias
EXPLAIN (COSTS OFF, VERBOSE)
@@ -168,7 +206,12 @@ SELECT * FROM
"text" text PATH '$'
)) json_table_func;
-DROP VIEW jsonb_table_view;
+DROP VIEW jsonb_table_view1;
+DROP VIEW jsonb_table_view2;
+DROP VIEW jsonb_table_view3;
+DROP VIEW jsonb_table_view4;
+DROP VIEW jsonb_table_view5;
+DROP VIEW jsonb_table_view6;
DROP DOMAIN jsonb_test_domain;
-- JSON_TABLE: only one FOR ORDINALITY columns allowed
--
2.34.1