From 28610f6eedba94aa6838bf87acc1fd50bb2f3840 Mon Sep 17 00:00:00 2001 From: jian he 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