From dcbe7a63d010e330ec5b9b1d464b0bd9ad3ef608 Mon Sep 17 00:00:00 2001 From: jian he Date: Thu, 4 Apr 2024 15:45:04 +0800 Subject: [PATCH v1 1/1] add test for nested path clause json_table NOTE: the last view (jsonb_table_view7) definition is WRONG!!!! there is no sql example with "NESTED PATH" within "NESTED PATH". so i added some real example to it. non-nesting columns will be sorted first, after that nested column order will be as is. i add a example to demo it. example to demo that passing clause work fine with every path level. --- .../regress/expected/sqljson_jsontable.out | 204 ++++++++++++++++++ src/test/regress/sql/sqljson_jsontable.sql | 65 ++++++ 2 files changed, 269 insertions(+) diff --git a/src/test/regress/expected/sqljson_jsontable.out b/src/test/regress/expected/sqljson_jsontable.out index f11c78c2..1e1275f9 100644 --- a/src/test/regress/expected/sqljson_jsontable.out +++ b/src/test/regress/expected/sqljson_jsontable.out @@ -908,3 +908,207 @@ FROM 4 | 3 | [3, 4, 5, 6] | 6 (52 rows) +drop table if exists s cascade; +NOTICE: table "s" does not exist, skipping +create table s(js jsonb); +insert into s values + ('{"a":{"za":[{"z1": [11,2222]},{"z21": [22, 234,2345]},{"z22": [32, 204,145]}]},"c": 3}'), + ('{"a":{"za":[{"z1": [21,4222]},{"z21": [32, 134,1345]}]},"c": 10}'); +--should error +SELECT sub.* FROM s,JSON_TABLE(js, '$' PASSING 32 AS x, 13 AS y COLUMNS( + xx int path '$.c', + NESTED PATH '$.a.za[1]' columns (NESTED PATH '$.z21[*]' COLUMNS (z21 int path '$?(@ >= $"x")' error on error)) + ))sub; +ERROR: no SQL/JSON item +---xx1, xx column (not nesting columns) will be sorted first. +SELECT sub.* FROM s,(values(23)) x(x),generate_series(13, 13) y, + JSON_TABLE(js, '$' as c1 PASSING x AS x, y AS y COLUMNS( + NESTED PATH '$.a.za[2]' columns (NESTED PATH '$.z22[*]' as z22 COLUMNS (c int path '$')), + NESTED PATH '$.a.za[1]' columns (d int[] PATH '$.z21'), + NESTED PATH '$.a.za[0]' columns (NESTED PATH '$.z1[*]' as z1 COLUMNS (a int path '$')), + xx1 int path '$.c', + NESTED PATH '$.a.za[1]' columns (NESTED PATH '$.z21[*]' as z21 COLUMNS (b int path '$')), + xx int path '$.c' + ))sub; + xx1 | xx | c | d | a | b +-----+----+-----+---------------+------+------ + 3 | 3 | 32 | | | + 3 | 3 | 204 | | | + 3 | 3 | 145 | | | + 3 | 3 | | {22,234,2345} | | + 3 | 3 | | | 11 | + 3 | 3 | | | 2222 | + 3 | 3 | | | | 22 + 3 | 3 | | | | 234 + 3 | 3 | | | | 2345 + 10 | 10 | | {32,134,1345} | | + 10 | 10 | | | 21 | + 10 | 10 | | | 4222 | + 10 | 10 | | | | 32 + 10 | 10 | | | | 134 + 10 | 10 | | | | 1345 +(15 rows) + +--compare applying jsonpath variable at different nesting level +SELECT sub.* FROM s,(values(23)) x(x),generate_series(13, 13) y, + JSON_TABLE(js, '$' as c1 PASSING x AS x, y AS y COLUMNS( + xx1 int path '$.c', + NESTED PATH '$.a.za[0].z1[*]' columns (NESTED PATH '$ ?(@ >= ($"x" -2))' COLUMNS (a int path '$')), + NESTED PATH '$.a.za[0]' columns (NESTED PATH '$.z1[*] ? (@ >= ($"x" -2))' COLUMNS (b int path '$')) + ))sub; + xx1 | a | b +-----+------+------ + 3 | | + 3 | 2222 | + 3 | | 2222 + 10 | 21 | + 10 | 4222 | + 10 | | 21 + 10 | | 4222 +(7 rows) + +--apply jsonpath variable to all the level +SELECT sub.* FROM s,(values(23)) x(x),generate_series(13, 13) y, + JSON_TABLE(js, '$' as c1 PASSING x AS x, y AS y COLUMNS( + xx1 int path '$.c', + NESTED PATH '$.a.za[1]' columns (NESTED PATH '$.z21[*]' COLUMNS (b int path '$')), + NESTED PATH '$.a.za[1] ? (@.z21[*] >= ($"x"-1))' columns + (NESTED PATH '$.z21[*] ? (@ >= ($"y" + 3))' as z22 COLUMNS (a int path '$ ? (@ >= ($"y" + 12))')), + NESTED PATH '$.a.za[1]' columns + (NESTED PATH '$.z21[*] ? (@ >= ($"y" +121))' as z21 COLUMNS (c int path '$ ? (@ > ($"x" +111))')) + ))sub; + xx1 | b | a | c +-----+------+------+------ + 3 | 22 | | + 3 | 234 | | + 3 | 2345 | | + 3 | | | + 3 | | 234 | + 3 | | 2345 | + 3 | | | 234 + 3 | | | 2345 + 10 | 32 | | + 10 | 134 | | + 10 | 1345 | | + 10 | | 32 | + 10 | | 134 | + 10 | | 1345 | + 10 | | | + 10 | | | 1345 +(16 rows) + +----- test on empty behavior +SELECT sub.* FROM s,(values(23)) x(x),generate_series(13, 13) y, + JSON_TABLE(js, '$' as c1 PASSING x AS x, y AS y COLUMNS( + xx1 int path '$.c', + NESTED PATH '$.a.za[2]' columns (NESTED PATH '$.z22[*]' as z22 COLUMNS (c int path '$')), + NESTED PATH '$.a.za[1]' columns (d json PATH '$ ? (@.z21[*] == ($"x" -1))'), + NESTED PATH '$.a.za[0]' columns (NESTED PATH '$.z1[*] ? (@ >= ($"x" -2))' as z1 COLUMNS (a int path '$')), + NESTED PATH '$.a.za[1]' columns + (NESTED PATH '$.z21[*] ? (@ >= ($"y" +121))' as z21 COLUMNS (b int path '$ ? (@ > ($"x" +111))' default 0 on empty)) + ))sub; + xx1 | c | d | a | b +-----+-----+--------------------------+------+------ + 3 | 32 | | | + 3 | 204 | | | + 3 | 145 | | | + 3 | | {"z21": [22, 234, 2345]} | | + 3 | | | 2222 | + 3 | | | | 234 + 3 | | | | 2345 + 10 | | | | + 10 | | | 21 | + 10 | | | 4222 | + 10 | | | | 0 + 10 | | | | 1345 +(12 rows) + +create or replace view jsonb_table_view7 AS +SELECT sub.* FROM s,(values(23)) x(x),generate_series(13, 13) y, + JSON_TABLE(js, '$' as c1 PASSING x AS x, y AS y COLUMNS( + xx1 int path '$.c', + NESTED PATH '$.a.za[2]' columns (NESTED PATH '$.z22[*]' as z22 COLUMNS (c int path '$' without WRAPPER omit quotes)), + NESTED PATH '$.a.za[1]' columns (d json PATH '$ ? (@.z21[*] == ($"x" -1))' with WRAPPER), + NESTED PATH '$.a.za[0]' columns (NESTED PATH '$.z1[*] ? (@ >= ($"x" -2))' as z1 COLUMNS (a int path '$' keep quotes)), + NESTED PATH '$.a.za[1]' columns + (NESTED PATH '$.z21[*] ? (@ >= ($"y" +121))' as z21 COLUMNS (b int path '$ ? (@ > ($"x" +111))' default 0 on empty)) + ))sub; +\sv jsonb_table_view7 +CREATE OR REPLACE VIEW public.jsonb_table_view7 AS + SELECT sub.xx1, + sub.c, + sub.d, + sub.a, + sub.b + FROM s, + ( VALUES (23)) x(x), + generate_series(13, 13) y(y), + LATERAL JSON_TABLE( + s.js, '$' AS c1 + PASSING + x.x AS x, + y.y AS y + COLUMNS ( + xx1 integer PATH '$."c"', + c integer PATH '$' OMIT QUOTES, + d json PATH '$?(@."z21"[*] == $"x" - 1)' WITH UNCONDITIONAL WRAPPER, + a integer PATH '$', + b integer PATH '$?(@ > $"x" + 111)' DEFAULT 0 ON EMPTY, + NESTED PATH '$."a"."za"[2]' AS json_table_path_0 + COLUMNS ( + xx1 integer PATH '$."c"', + c integer PATH '$' OMIT QUOTES, + d json PATH '$?(@."z21"[*] == $"x" - 1)' WITH UNCONDITIONAL WRAPPER, + a integer PATH '$', + b integer PATH '$?(@ > $"x" + 111)' DEFAULT 0 ON EMPTY + NESTED PATH '$."z22"[*]' AS z22 + COLUMNS ( + xx1 integer PATH '$."c"', + c integer PATH '$' OMIT QUOTES, + d json PATH '$?(@."z21"[*] == $"x" - 1)' WITH UNCONDITIONAL WRAPPER, + a integer PATH '$', + b integer PATH '$?(@ > $"x" + 111)' DEFAULT 0 ON EMPTY + ) + ), + NESTED PATH '$."a"."za"[1]' AS json_table_path_1 + COLUMNS ( + xx1 integer PATH '$."c"', + c integer PATH '$' OMIT QUOTES, + d json PATH '$?(@."z21"[*] == $"x" - 1)' WITH UNCONDITIONAL WRAPPER, + a integer PATH '$', + b integer PATH '$?(@ > $"x" + 111)' DEFAULT 0 ON EMPTY + ), + NESTED PATH '$."a"."za"[0]' AS json_table_path_2 + COLUMNS ( + xx1 integer PATH '$."c"', + c integer PATH '$' OMIT QUOTES, + d json PATH '$?(@."z21"[*] == $"x" - 1)' WITH UNCONDITIONAL WRAPPER, + a integer PATH '$', + b integer PATH '$?(@ > $"x" + 111)' DEFAULT 0 ON EMPTY + NESTED PATH '$."z1"[*]?(@ >= $"x" - 2)' AS z1 + COLUMNS ( + xx1 integer PATH '$."c"', + c integer PATH '$' OMIT QUOTES, + d json PATH '$?(@."z21"[*] == $"x" - 1)' WITH UNCONDITIONAL WRAPPER, + a integer PATH '$', + b integer PATH '$?(@ > $"x" + 111)' DEFAULT 0 ON EMPTY + ) + ), + NESTED PATH '$."a"."za"[1]' AS json_table_path_3 + COLUMNS ( + xx1 integer PATH '$."c"', + c integer PATH '$' OMIT QUOTES, + d json PATH '$?(@."z21"[*] == $"x" - 1)' WITH UNCONDITIONAL WRAPPER, + a integer PATH '$', + b integer PATH '$?(@ > $"x" + 111)' DEFAULT 0 ON EMPTY + NESTED PATH '$."z21"[*]?(@ >= $"y" + 121)' AS z21 + COLUMNS ( + xx1 integer PATH '$."c"', + c integer PATH '$' OMIT QUOTES, + d json PATH '$?(@."z21"[*] == $"x" - 1)' WITH UNCONDITIONAL WRAPPER, + a integer PATH '$', + b integer PATH '$?(@ > $"x" + 111)' DEFAULT 0 ON EMPTY + ) + ) + ) + ) sub diff --git a/src/test/regress/sql/sqljson_jsontable.sql b/src/test/regress/sql/sqljson_jsontable.sql index a00bc030..7cd22d34 100644 --- a/src/test/regress/sql/sqljson_jsontable.sql +++ b/src/test/regress/sql/sqljson_jsontable.sql @@ -417,3 +417,68 @@ FROM ) ) ) jt; + +drop table if exists s cascade; +create table s(js jsonb); +insert into s values + ('{"a":{"za":[{"z1": [11,2222]},{"z21": [22, 234,2345]},{"z22": [32, 204,145]}]},"c": 3}'), + ('{"a":{"za":[{"z1": [21,4222]},{"z21": [32, 134,1345]}]},"c": 10}'); + +--should error +SELECT sub.* FROM s,JSON_TABLE(js, '$' PASSING 32 AS x, 13 AS y COLUMNS( + xx int path '$.c', + NESTED PATH '$.a.za[1]' columns (NESTED PATH '$.z21[*]' COLUMNS (z21 int path '$?(@ >= $"x")' error on error)) + ))sub; + +---xx1, xx column (not nesting columns) will be sorted first. +SELECT sub.* FROM s,(values(23)) x(x),generate_series(13, 13) y, + JSON_TABLE(js, '$' as c1 PASSING x AS x, y AS y COLUMNS( + NESTED PATH '$.a.za[2]' columns (NESTED PATH '$.z22[*]' as z22 COLUMNS (c int path '$')), + NESTED PATH '$.a.za[1]' columns (d int[] PATH '$.z21'), + NESTED PATH '$.a.za[0]' columns (NESTED PATH '$.z1[*]' as z1 COLUMNS (a int path '$')), + xx1 int path '$.c', + NESTED PATH '$.a.za[1]' columns (NESTED PATH '$.z21[*]' as z21 COLUMNS (b int path '$')), + xx int path '$.c' + ))sub; + +--compare applying jsonpath variable at different nesting level +SELECT sub.* FROM s,(values(23)) x(x),generate_series(13, 13) y, + JSON_TABLE(js, '$' as c1 PASSING x AS x, y AS y COLUMNS( + xx1 int path '$.c', + NESTED PATH '$.a.za[0].z1[*]' columns (NESTED PATH '$ ?(@ >= ($"x" -2))' COLUMNS (a int path '$')), + NESTED PATH '$.a.za[0]' columns (NESTED PATH '$.z1[*] ? (@ >= ($"x" -2))' COLUMNS (b int path '$')) + ))sub; + +--apply jsonpath variable to all the level +SELECT sub.* FROM s,(values(23)) x(x),generate_series(13, 13) y, + JSON_TABLE(js, '$' as c1 PASSING x AS x, y AS y COLUMNS( + xx1 int path '$.c', + NESTED PATH '$.a.za[1]' columns (NESTED PATH '$.z21[*]' COLUMNS (b int path '$')), + NESTED PATH '$.a.za[1] ? (@.z21[*] >= ($"x"-1))' columns + (NESTED PATH '$.z21[*] ? (@ >= ($"y" + 3))' as z22 COLUMNS (a int path '$ ? (@ >= ($"y" + 12))')), + NESTED PATH '$.a.za[1]' columns + (NESTED PATH '$.z21[*] ? (@ >= ($"y" +121))' as z21 COLUMNS (c int path '$ ? (@ > ($"x" +111))')) + ))sub; + +----- test on empty behavior +SELECT sub.* FROM s,(values(23)) x(x),generate_series(13, 13) y, + JSON_TABLE(js, '$' as c1 PASSING x AS x, y AS y COLUMNS( + xx1 int path '$.c', + NESTED PATH '$.a.za[2]' columns (NESTED PATH '$.z22[*]' as z22 COLUMNS (c int path '$')), + NESTED PATH '$.a.za[1]' columns (d json PATH '$ ? (@.z21[*] == ($"x" -1))'), + NESTED PATH '$.a.za[0]' columns (NESTED PATH '$.z1[*] ? (@ >= ($"x" -2))' as z1 COLUMNS (a int path '$')), + NESTED PATH '$.a.za[1]' columns + (NESTED PATH '$.z21[*] ? (@ >= ($"y" +121))' as z21 COLUMNS (b int path '$ ? (@ > ($"x" +111))' default 0 on empty)) + ))sub; + +create or replace view jsonb_table_view7 AS +SELECT sub.* FROM s,(values(23)) x(x),generate_series(13, 13) y, + JSON_TABLE(js, '$' as c1 PASSING x AS x, y AS y COLUMNS( + xx1 int path '$.c', + NESTED PATH '$.a.za[2]' columns (NESTED PATH '$.z22[*]' as z22 COLUMNS (c int path '$' without WRAPPER omit quotes)), + NESTED PATH '$.a.za[1]' columns (d json PATH '$ ? (@.z21[*] == ($"x" -1))' with WRAPPER), + NESTED PATH '$.a.za[0]' columns (NESTED PATH '$.z1[*] ? (@ >= ($"x" -2))' as z1 COLUMNS (a int path '$' keep quotes)), + NESTED PATH '$.a.za[1]' columns + (NESTED PATH '$.z21[*] ? (@ >= ($"y" +121))' as z21 COLUMNS (b int path '$ ? (@ > ($"x" +111))' default 0 on empty)) + ))sub; +\sv jsonb_table_view7 \ No newline at end of file base-commit: 3a4a3537a999932642ba7a459900fe3c4f5cad02 prerequisite-patch-id: 19eaa6cd330842b7147245e46120a3962de325bc prerequisite-patch-id: df4bbca0c4426425ebf4a412dfd2f22c68432c3e -- 2.34.1