v1-0001-add-test-for-nested-path-clause-json_table.for_v49_0002
application/octet-stream
Filename: v1-0001-add-test-for-nested-path-clause-json_table.for_v49_0002
Type: application/octet-stream
Part: 0
Message:
Re: remaining sql/json patches
From dcbe7a63d010e330ec5b9b1d464b0bd9ad3ef608 Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
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