Skip to content

Commit 90d0880

Browse files
l-wangl-wang
committed
Address feedback: add more json_query checks
1 parent 7f8e8f3 commit 90d0880

File tree

4 files changed

+201
-146
lines changed

4 files changed

+201
-146
lines changed

‎src/test/regress/expected/json.out

Lines changed: 86 additions & 64 deletions
Original file line numberDiff line numberDiff line change
@@ -2717,103 +2717,121 @@ select ts_headline('[]'::json, tsquery('aaa & bbb'));
27172717
(1 row)
27182718

27192719
-- simple dot notation
2720+
-- TODO: add comments
27202721
create table test_json_dot(id serial primary key, test_json json);
27212722
insert into test_json_dot values (1, '{"a": 1, "b": 42}');
27222723
insert into test_json_dot values (2, '{"a": 2, "b": {"c": 42}}');
27232724
insert into test_json_dot values (3, '{"a": 3, "b": {"c": "42"}, "d":[11, 12]}');
2724-
insert into test_json_dot values (4, '{"a": 3, "b": {"c": "42"}, "d":[{"x": [11, 12]}, {"y": [21, 22]}]}');
2725+
insert into test_json_dot values (4, '{"a": 4, "b": {"c": "42"}, "d":[{"x": [11, 12]}, {"y": [21, 22]}]}');
2726+
insert into test_json_dot values (5, '[{"a": 1, "b": 42}, {"a": 2, "b": {"c": 42}}]');
27252727
-- member object access
2726-
select id, (test_json_dot.test_json).b, json_query(test_json, 'lax $.b' WITH CONDITIONAL WRAPPER NULL ON EMPTY NULL ON ERROR) as expected from test_json_dot;
2727-
id | b | expected
2728-
----+-------------+-------------
2728+
select id, (test_json).b, json_query(test_json, 'lax $.b' WITH CONDITIONAL WRAPPER NULL ON EMPTY NULL ON ERROR) as expected from test_json_dot;
2729+
id | b | expected
2730+
----+-------------+-----------------
27292731
1 | 42 | 42
27302732
2 | {"c": 42} | {"c": 42}
27312733
3 | {"c": "42"} | {"c": "42"}
27322734
4 | {"c": "42"} | {"c": "42"}
2733-
(4 rows)
2735+
5 | | [42, {"c": 42}]
2736+
(5 rows)
27342737

2735-
select id, (test_json_dot.test_json).b.c, json_query(test_json, 'lax $.b.c' WITH CONDITIONAL WRAPPER NULL ON EMPTY NULL ON ERROR) as expected from test_json_dot;
2738+
select id, (test_json).b.c, json_query(test_json, 'lax $.b.c' WITH CONDITIONAL WRAPPER NULL ON EMPTY NULL ON ERROR) as expected from test_json_dot;
27362739
id | c | expected
27372740
----+------+----------
27382741
1 | |
27392742
2 | 42 | 42
27402743
3 | "42" | "42"
27412744
4 | "42" | "42"
2742-
(4 rows)
2745+
5 | | 42
2746+
(5 rows)
27432747

2744-
select id, (test_json_dot.test_json).d, json_query(test_json, 'lax $.d' WITH CONDITIONAL WRAPPER NULL ON EMPTY NULL ON ERROR) as expected from test_json_dot;
2748+
select id, (test_json).d, json_query(test_json, 'lax $.d' WITH CONDITIONAL WRAPPER NULL ON EMPTY NULL ON ERROR) as expected from test_json_dot;
27452749
id | d | expected
27462750
----+------------------------------------+------------------------------------
27472751
1 | |
27482752
2 | |
27492753
3 | [11, 12] | [11, 12]
27502754
4 | [{"x": [11, 12]}, {"y": [21, 22]}] | [{"x": [11, 12]}, {"y": [21, 22]}]
2751-
(4 rows)
2755+
5 | |
2756+
(5 rows)
27522757

2753-
select id, (test_json_dot.test_json)."d", json_query(test_json, 'lax $.d' WITH CONDITIONAL WRAPPER NULL ON EMPTY NULL ON ERROR) as expected from test_json_dot;
2758+
select id, (test_json)."d", json_query(test_json, 'lax $."d"' WITH CONDITIONAL WRAPPER NULL ON EMPTY NULL ON ERROR) as expected from test_json_dot;
27542759
id | d | expected
27552760
----+------------------------------------+------------------------------------
27562761
1 | |
27572762
2 | |
27582763
3 | [11, 12] | [11, 12]
27592764
4 | [{"x": [11, 12]}, {"y": [21, 22]}] | [{"x": [11, 12]}, {"y": [21, 22]}]
2760-
(4 rows)
2765+
5 | |
2766+
(5 rows)
27612767

2762-
select id, (test_json_dot.test_json).'d' from test_json_dot;
2768+
select id, (test_json).'d' from test_json_dot;
27632769
ERROR: syntax error at or near "'d'"
2764-
LINE 1: select id, (test_json_dot.test_json).'d' from test_json_dot;
2765-
^
2766-
select id, (test_json_dot.test_json)['d'] from test_json_dot;
2770+
LINE 1: select id, (test_json).'d' from test_json_dot;
2771+
^
2772+
select id, (test_json)['d'] from test_json_dot;
27672773
ERROR: json subscript must be coercible to integer
2768-
LINE 1: select id, (test_json_dot.test_json)['d'] from test_json_dot...
2769-
^
2774+
LINE 1: select id, (test_json)['d'] from test_json_dot;
2775+
^
27702776
-- wildcard access is not supported
2771-
select (test_json_dot.test_json).* from test_json_dot;
2777+
select (test_json).* from test_json_dot;
27722778
ERROR: type json is not composite
27732779
-- array element access
2774-
select id, (test_json_dot.test_json).d[0], json_query(test_json, 'lax $.d[0]' WITH CONDITIONAL WRAPPER NULL ON EMPTY NULL ON ERROR) as expected from test_json_dot;
2780+
select id, (test_json).d[0], json_query(test_json, 'lax $.d[0]' WITH CONDITIONAL WRAPPER NULL ON EMPTY NULL ON ERROR) as expected from test_json_dot;
27752781
id | d | expected
27762782
----+-----------------+-----------------
27772783
1 | |
27782784
2 | |
27792785
3 | 11 | 11
27802786
4 | {"x": [11, 12]} | {"x": [11, 12]}
2781-
(4 rows)
2787+
5 | |
2788+
(5 rows)
27822789

2783-
select id, (test_json_dot.test_json).d[1], json_query(test_json, 'lax $.d[1]' WITH CONDITIONAL WRAPPER NULL ON EMPTY NULL ON ERROR) as expected from test_json_dot;
2790+
select id, (test_json).d[1], json_query(test_json, 'lax $.d[1]' WITH CONDITIONAL WRAPPER NULL ON EMPTY NULL ON ERROR) as expected from test_json_dot;
27842791
id | d | expected
27852792
----+-----------------+-----------------
27862793
1 | |
27872794
2 | |
27882795
3 | 12 | 12
27892796
4 | {"y": [21, 22]} | {"y": [21, 22]}
2790-
(4 rows)
2797+
5 | |
2798+
(5 rows)
27912799

2792-
select id, (test_json_dot.test_json).d[0:] from test_json_dot;
2800+
select id, (test_json).d[0:] from test_json_dot;
27932801
ERROR: json subscript does not support slices
2794-
LINE 1: select id, (test_json_dot.test_json).d[0:] from test_json_do...
2802+
LINE 1: select id, (test_json).d[0:] from test_json_dot;
27952803
^
2796-
select id, (test_json_dot.test_json).d[0::int] from test_json_dot;
2804+
select id, json_query(test_json, 'lax $.d[0:]' WITH CONDITIONAL WRAPPER NULL ON EMPTY NULL ON ERROR) from test_json_dot;
2805+
ERROR: syntax error at or near ":" of jsonpath input
2806+
LINE 1: select id, json_query(test_json, 'lax $.d[0:]' WITH CONDITIO...
2807+
^
2808+
select id, (test_json).d[0::int] from test_json_dot;
27972809
id | d
27982810
----+-----------------
27992811
1 |
28002812
2 |
28012813
3 | 11
28022814
4 | {"x": [11, 12]}
2803-
(4 rows)
2815+
5 |
2816+
(5 rows)
28042817

2805-
select id, (test_json_dot.test_json).d[0::float] from test_json_dot;
2818+
select id, json_query(test_json, 'lax $.d[0::int]' WITH CONDITIONAL WRAPPER NULL ON EMPTY NULL ON ERROR) from test_json_dot;
2819+
ERROR: syntax error at or near ":" of jsonpath input
2820+
LINE 1: select id, json_query(test_json, 'lax $.d[0::int]' WITH COND...
2821+
^
2822+
select id, (test_json).d[0::float] from test_json_dot;
28062823
ERROR: json subscript must be coercible to integer
2807-
LINE 1: select id, (test_json_dot.test_json).d[0::float] from test_j...
2808-
^
2809-
select id, (test_json_dot.test_json).d[0].x[1], json_query(test_json, 'lax $.d[0].x[1]' WITH CONDITIONAL WRAPPER NULL ON EMPTY NULL ON ERROR) as expected from test_json_dot;
2824+
LINE 1: select id, (test_json).d[0::float] from test_json_dot;
2825+
^
2826+
select id, (test_json).d[0].x[1], json_query(test_json, 'lax $.d[0].x[1]' WITH CONDITIONAL WRAPPER NULL ON EMPTY NULL ON ERROR) as expected from test_json_dot;
28102827
id | x | expected
28112828
----+----+----------
28122829
1 | |
28132830
2 | |
28142831
3 | |
28152832
4 | 12 | 12
2816-
(4 rows)
2833+
5 | |
2834+
(5 rows)
28172835

28182836
-- complex type with domain over json
28192837
create domain json_d as json;
@@ -2823,49 +2841,53 @@ insert into test_json_domain_dot (compjd) values (ROW(1, '{"a": 3, "key1": {"c":
28232841
insert into test_json_domain_dot (compjd) values (ROW(2, '{"a": 3, "key1": {"c": "42"}, "key2": [11, 12, {"x": [31, 42]}]}'));
28242842
insert into test_json_domain_dot (compjd) values (ROW(3, '[{"a": 3}, {"key1": {"c": "42"}}, {"key2": [11, 12]}]'));
28252843
--object access
2826-
select id, (test_json_domain_dot.compjd).f2.key1.c from test_json_domain_dot;
2827-
id | c
2828-
----+------
2829-
1 | "42"
2830-
2 | "42"
2831-
3 |
2844+
select id, (compjd).f2.key1.c, json_query((compjd).f2, 'lax $.key1.c' WITH CONDITIONAL WRAPPER NULL ON EMPTY NULL ON ERROR) as expected from test_json_domain_dot;
2845+
id | c | expected
2846+
----+------+----------
2847+
1 | "42" | "42"
2848+
2 | "42" | "42"
2849+
3 | | "42"
28322850
(3 rows)
28332851

2834-
select id, (test_json_domain_dot.compjd).f2.key2 from test_json_domain_dot;
2835-
id | key2
2836-
----+---------------------------
2837-
1 | [11, 12]
2838-
2 | [11, 12, {"x": [31, 42]}]
2839-
3 |
2852+
select id, (compjd).f2.key2, json_query((compjd).f2, 'lax $.key2' WITH CONDITIONAL WRAPPER NULL ON EMPTY NULL ON ERROR) as expected from test_json_domain_dot;
2853+
id | key2 | expected
2854+
----+---------------------------+---------------------------
2855+
1 | [11, 12] | [11, 12]
2856+
2 | [11, 12, {"x": [31, 42]}] | [11, 12, {"x": [31, 42]}]
2857+
3 | | [11, 12]
28402858
(3 rows)
28412859

2842-
select id, (test_json_domain_dot.compjd).f2.key2[0] from test_json_domain_dot;
2843-
id | key2
2844-
----+------
2845-
1 | 11
2846-
2 | 11
2847-
3 |
2860+
select id, (compjd).f2.key2[0], json_query((compjd).f2, 'lax $.key2[0]' WITH CONDITIONAL WRAPPER NULL ON EMPTY NULL ON ERROR) as expected from test_json_domain_dot;
2861+
id | key2 | expected
2862+
----+------+----------
2863+
1 | 11 | 11
2864+
2 | 11 | 11
2865+
3 | | 11
28482866
(3 rows)
28492867

2850-
select id, (test_json_domain_dot.compjd).f2.key2[0::text] from test_json_domain_dot;
2868+
select id, (compjd).f2.key2[0::text] from test_json_domain_dot;
28512869
ERROR: json subscript must be coercible to integer
2852-
LINE 1: select id, (test_json_domain_dot.compjd).f2.key2[0::text] fr...
2853-
^
2854-
select id, (test_json_domain_dot.compjd).f2.key2[2].x[1] from test_json_domain_dot;
2855-
id | x
2856-
----+----
2857-
1 |
2858-
2 | 42
2859-
3 |
2870+
LINE 1: select id, (compjd).f2.key2[0::text] from test_json_domain_d...
2871+
^
2872+
select json_query((compjd).f2, 'lax $.key2[0::text]' WITH CONDITIONAL WRAPPER NULL ON EMPTY NULL ON ERROR) from test_json_domain_dot;
2873+
ERROR: syntax error at or near ":" of jsonpath input
2874+
LINE 1: select json_query((compjd).f2, 'lax $.key2[0::text]' WITH CO...
2875+
^
2876+
select id, (test_json_domain_dot.compjd).f2.key2[2].x[1], json_query((compjd).f2, 'lax $.key2[2].x[1]' WITH CONDITIONAL WRAPPER NULL ON EMPTY NULL ON ERROR) as expected from test_json_domain_dot;
2877+
id | x | expected
2878+
----+----+----------
2879+
1 | |
2880+
2 | 42 | 42
2881+
3 | |
28602882
(3 rows)
28612883

28622884
-- array access
2863-
select id, (test_json_domain_dot.compjd).f2[0] from test_json_domain_dot;
2864-
id | f2
2865-
----+----------
2866-
1 |
2867-
2 |
2868-
3 | {"a": 3}
2885+
select id, (compjd).f2[0], json_query((compjd).f2, 'lax $[0]' WITH CONDITIONAL WRAPPER NULL ON EMPTY NULL ON ERROR) as expected from test_json_domain_dot;
2886+
id | f2 | expected
2887+
----+----------+------------------------------------------------------------------
2888+
1 | | {"a": 3, "key1": {"c": "42"}, "key2": [11, 12]}
2889+
2 | | {"a": 3, "key1": {"c": "42"}, "key2": [11, 12, {"x": [31, 42]}]}
2890+
3 | {"a": 3} | {"a": 3}
28692891
(3 rows)
28702892

28712893
select id, (test_json_domain_dot.compjd).f2[0:] from test_json_domain_dot;

0 commit comments

Comments
 (0)