v47-0001-add-more-json_table-tests.no-cfbot

From 28610f6eedba94aa6838bf87acc1fd50bb2f3840 Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
Date: Mon, 1 Apr 2024 16:45:09 +0800
Subject: [PATCH v47 1/1] add more json_table tests

---
 .../regress/expected/sqljson_jsontable.out    | 73 +++++++++++++++++++
 src/test/regress/sql/sqljson_jsontable.sql    | 54 ++++++++++++++
 2 files changed, 127 insertions(+)

diff --git a/src/test/regress/expected/sqljson_jsontable.out b/src/test/regress/expected/sqljson_jsontable.out
index fb4e1da9..60d9aed8 100644
--- a/src/test/regress/expected/sqljson_jsontable.out
+++ b/src/test/regress/expected/sqljson_jsontable.out
@@ -887,3 +887,76 @@ SELECT * FROM JSON_TABLE(jsonb '{"a": 123}', '$' || '.' || 'a' COLUMNS (foo int)
 ERROR:  only string constants are supported in JSON_TABLE path specification
 LINE 1: SELECT * FROM JSON_TABLE(jsonb '{"a": 123}', '$' || '.' || '...
                                                      ^
+create table s(js jsonb);
+insert into s select '{"a":{"za":[{"z1": [11,2222]},{"z21": [22, 234,2345]}]},"c": 3}';
+--passing clause apply to path_expression.
+--expepct zero rows, does not meet the filter expression
+SELECT sub.* FROM s, JSON_TABLE(js, '$.c ? (@ > $x)' PASSING 10 AS x COLUMNS(
+	xx text path '$'
+))sub;
+ xx 
+----
+(0 rows)
+
+--passing clause apply to path_expression
+SELECT sub.* FROM s, JSON_TABLE(js, '$.c ? (@ < $x)' PASSING 10 AS x COLUMNS(
+	xx text path '$'
+))sub;
+ xx 
+----
+ 3
+(1 row)
+
+--nested path with nested path
+SELECT sub.* FROM s,
+	(values(23)) x(x),
+	generate_series(13, 13) y,
+	JSON_TABLE(js, '$' PASSING x AS x, y AS y COLUMNS(
+		xx int path '$.c',
+		NESTED PATH '$.a.za[1]' as n1 columns (NESTED PATH '$.z21[*]' as n2 COLUMNS (a12 int path '$')),
+		NESTED PATH '$.a.za[0]' as n4 columns (NESTED PATH '$.z1[*]' as n3 COLUMNS (a13 int path  '$')))
+	)sub;
+ xx | a12  | a13  
+----+------+------
+  3 |   22 |     
+  3 |  234 |     
+  3 | 2345 |     
+  3 |      |   11
+  3 |      | 2222
+(5 rows)
+
+--fail, passing variable cannot use in nested's nested path
+SELECT sub.*, x, y FROM s,
+	(values(23)) x(x),
+	generate_series(13, 13) y,
+	JSON_TABLE(js, '$' PASSING x AS x, y AS y COLUMNS(
+		xx int path '$.c',
+		NESTED PATH '$.a.za[1]' as n1 columns (NESTED PATH '$.z21[*]' as n2 COLUMNS (a12 int path '$?(@ >= $"x")')),
+		NESTED PATH '$.a.za[0]' as n4 columns (NESTED PATH '$.z1[*]' as n3 COLUMNS (a13 int path  '$?(@ >= $"y")')))
+	)sub;
+ERROR:  could not find jsonpath variable "x"
+--not ok
+SELECT sub.*, x, y FROM s,
+	(values(23)) x(x),
+	generate_series(13, 13) y,
+	JSON_TABLE(js, '$' PASSING x AS x, y AS y COLUMNS(
+		xx int path '$.c',
+		NESTED PATH '$.a.za[1].z21[*]' as n1 columns (n2 int path '$?(@ >= $"x")'),
+		NESTED PATH '$.a.za[0].z1[*]' as n4 columns (n3 int path '$')))sub;
+ERROR:  could not find jsonpath variable "x"
+--ok
+SELECT sub.* FROM s,
+	(values(23)) x(x),
+	generate_series(13, 13) y,
+	JSON_TABLE(js, '$' PASSING x AS x, y AS y COLUMNS(
+		xx int path '$.c',
+		NESTED PATH '$.a.za[1].z21[*] ?(@ >= $"x")' as n1 columns (n2 int path '$'),
+		NESTED PATH '$.a.za[0].z1[*] ?(@ >= $"y")' as n4 columns (n3 int path '$')))sub;
+ xx |  n2  |  n3  
+----+------+------
+  3 |  234 |     
+  3 | 2345 |     
+  3 |      | 2222
+(3 rows)
+
+drop table s;
diff --git a/src/test/regress/sql/sqljson_jsontable.sql b/src/test/regress/sql/sqljson_jsontable.sql
index 22629188..638be2d1 100644
--- a/src/test/regress/sql/sqljson_jsontable.sql
+++ b/src/test/regress/sql/sqljson_jsontable.sql
@@ -407,3 +407,57 @@ FROM JSON_TABLE(
 
 -- Should fail (not supported)
 SELECT * FROM JSON_TABLE(jsonb '{"a": 123}', '$' || '.' || 'a' COLUMNS (foo int));
+
+create table s(js jsonb);
+insert into s select '{"a":{"za":[{"z1": [11,2222]},{"z21": [22, 234,2345]}]},"c": 3}';
+
+--passing clause apply to path_expression.
+--expepct zero rows, does not meet the filter expression
+SELECT sub.* FROM s, JSON_TABLE(js, '$.c ? (@ > $x)' PASSING 10 AS x COLUMNS(
+	xx text path '$'
+))sub;
+
+--passing clause apply to path_expression
+SELECT sub.* FROM s, JSON_TABLE(js, '$.c ? (@ < $x)' PASSING 10 AS x COLUMNS(
+	xx text path '$'
+))sub;
+
+--nested path with nested path
+SELECT sub.* FROM s,
+	(values(23)) x(x),
+	generate_series(13, 13) y,
+	JSON_TABLE(js, '$' PASSING x AS x, y AS y COLUMNS(
+		xx int path '$.c',
+		NESTED PATH '$.a.za[1]' as n1 columns (NESTED PATH '$.z21[*]' as n2 COLUMNS (a12 int path '$')),
+		NESTED PATH '$.a.za[0]' as n4 columns (NESTED PATH '$.z1[*]' as n3 COLUMNS (a13 int path  '$')))
+	)sub;
+
+
+--fail, passing variable cannot use in nested's nested path
+SELECT sub.*, x, y FROM s,
+	(values(23)) x(x),
+	generate_series(13, 13) y,
+	JSON_TABLE(js, '$' PASSING x AS x, y AS y COLUMNS(
+		xx int path '$.c',
+		NESTED PATH '$.a.za[1]' as n1 columns (NESTED PATH '$.z21[*]' as n2 COLUMNS (a12 int path '$?(@ >= $"x")')),
+		NESTED PATH '$.a.za[0]' as n4 columns (NESTED PATH '$.z1[*]' as n3 COLUMNS (a13 int path  '$?(@ >= $"y")')))
+	)sub;
+
+--not ok
+SELECT sub.*, x, y FROM s,
+	(values(23)) x(x),
+	generate_series(13, 13) y,
+	JSON_TABLE(js, '$' PASSING x AS x, y AS y COLUMNS(
+		xx int path '$.c',
+		NESTED PATH '$.a.za[1].z21[*]' as n1 columns (n2 int path '$?(@ >= $"x")'),
+		NESTED PATH '$.a.za[0].z1[*]' as n4 columns (n3 int path '$')))sub;
+
+--ok
+SELECT sub.* FROM s,
+	(values(23)) x(x),
+	generate_series(13, 13) y,
+	JSON_TABLE(js, '$' PASSING x AS x, y AS y COLUMNS(
+		xx int path '$.c',
+		NESTED PATH '$.a.za[1].z21[*] ?(@ >= $"x")' as n1 columns (n2 int path '$'),
+		NESTED PATH '$.a.za[0].z1[*] ?(@ >= $"y")' as n4 columns (n3 int path '$')))sub;
+drop table s;
\ No newline at end of file

base-commit: f5a227895e178bf528b18f82bbe554435fb3e64f
prerequisite-patch-id: 98e54cda59b4c1906dac45238e194573d0037d2c
prerequisite-patch-id: 521030bebfaee72fe06021d0ca85739c7e95bacd
-- 
2.34.1