Skip to content

Commit c442953

Browse files
feodorfeodor
committed
add tests for any/each jsonb
1 parent 5cb65e2 commit c442953

File tree

2 files changed

+88
-20
lines changed

2 files changed

+88
-20
lines changed

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

Lines changed: 76 additions & 20 deletions
Original file line numberDiff line numberDiff line change
@@ -75,11 +75,11 @@ CONTEXT: JSON data, line 1: ...
7575
-- use octet_length here so we don't get an odd unicode char in the
7676
-- output
7777
SELECT octet_length('"\uaBcD"'::jsonb::text); -- OK, uppercase and lower case both OK
78-
octet_length
79-
--------------
80-
5
81-
(1 row)
82-
78+
ERROR: unsupported Unicode escape sequence
79+
LINE 1: SELECT octet_length('"\uaBcD"'::jsonb::text);
80+
^
81+
DETAIL: Unicode escape values cannot be used for code point values above 007F when the server encoding is not UTF8.
82+
CONTEXT: JSON data, line 1: ...
8383
-- Numbers.
8484
SELECT '1'::jsonb; -- OK
8585
jsonb
@@ -1957,11 +1957,11 @@ SELECT * FROM jsonb_populate_recordset(row('def',99,NULL)::jbpop,'[{"a":[100,200
19571957

19581958
-- handling of unicode surrogate pairs
19591959
SELECT octet_length((jsonb '{ "a": "\ud83d\ude04\ud83d\udc36" }' -> 'a')::text) AS correct_in_utf8;
1960-
correct_in_utf8
1961-
-----------------
1962-
10
1963-
(1 row)
1964-
1960+
ERROR: unsupported Unicode escape sequence
1961+
LINE 1: SELECT octet_length((jsonb '{ "a": "\ud83d\ude04\ud83d\udc3...
1962+
^
1963+
DETAIL: Unicode escape values cannot be used for code point values above 007F when the server encoding is not UTF8.
1964+
CONTEXT: JSON data, line 1: { "a":...
19651965
SELECT jsonb '{ "a": "\ud83d\ud83d" }' -> 'a'; -- 2 high surrogates in a row
19661966
ERROR: invalid input syntax for type json
19671967
LINE 1: SELECT jsonb '{ "a": "\ud83d\ud83d" }' -> 'a';
@@ -1988,11 +1988,11 @@ DETAIL: Unicode low surrogate must follow a high surrogate.
19881988
CONTEXT: JSON data, line 1: { "a":...
19891989
-- handling of simple unicode escapes
19901990
SELECT jsonb '{ "a": "the Copyright \u00a9 sign" }' as correct_in_utf8;
1991-
correct_in_utf8
1992-
-------------------------------
1993-
{"a": "the Copyright © sign"}
1994-
(1 row)
1995-
1991+
ERROR: unsupported Unicode escape sequence
1992+
LINE 1: SELECT jsonb '{ "a": "the Copyright \u00a9 sign" }' as corr...
1993+
^
1994+
DETAIL: Unicode escape values cannot be used for code point values above 007F when the server encoding is not UTF8.
1995+
CONTEXT: JSON data, line 1: { "a":...
19961996
SELECT jsonb '{ "a": "dollar \u0024 character" }' as correct_everywhere;
19971997
correct_everywhere
19981998
-----------------------------
@@ -2018,11 +2018,11 @@ SELECT jsonb '{ "a": "null \\u0000 escape" }' as not_an_escape;
20182018
(1 row)
20192019

20202020
SELECT jsonb '{ "a": "the Copyright \u00a9 sign" }' ->> 'a' as correct_in_utf8;
2021-
correct_in_utf8
2022-
----------------------
2023-
the Copyright © sign
2024-
(1 row)
2025-
2021+
ERROR: unsupported Unicode escape sequence
2022+
LINE 1: SELECT jsonb '{ "a": "the Copyright \u00a9 sign" }' ->> 'a'...
2023+
^
2024+
DETAIL: Unicode escape values cannot be used for code point values above 007F when the server encoding is not UTF8.
2025+
CONTEXT: JSON data, line 1: { "a":...
20262026
SELECT jsonb '{ "a": "dollar \u0024 character" }' ->> 'a' as correct_everywhere;
20272027
correct_everywhere
20282028
--------------------
@@ -2118,6 +2118,62 @@ SELECT count(*) FROM testjsonb WHERE j ?& ARRAY['public','disabled'];
21182118
42
21192119
(1 row)
21202120

2121+
SELECT count(*) FROM testjsonb WHERE ANY KEY OF j AS k SATISFIES ( k = '"pos"'::jsonb );
2122+
count
2123+
-------
2124+
203
2125+
(1 row)
2126+
2127+
SELECT count(*) FROM testjsonb WHERE ANY KEY OF j AS k SATISFIES ( k = '"age"'::jsonb );
2128+
count
2129+
-------
2130+
2
2131+
(1 row)
2132+
2133+
SELECT count(*) FROM testjsonb WHERE EACH KEY OF j AS k SATISFIES ( k = '"age"'::jsonb );
2134+
count
2135+
-------
2136+
120
2137+
(1 row)
2138+
2139+
SELECT count(*) FROM testjsonb WHERE ANY VALUE OF j AS v SATISFIES ( v = '"CAB"'::jsonb );
2140+
count
2141+
-------
2142+
30
2143+
(1 row)
2144+
2145+
SELECT count(*) FROM testjsonb WHERE ANY VALUE OF j AS v SATISFIES ( v = '"baz"'::jsonb );
2146+
count
2147+
-------
2148+
0
2149+
(1 row)
2150+
2151+
SELECT count(*) FROM testjsonb WHERE ANY VALUE ANYWHERE OF j AS v SATISFIES ( v = '"baz"'::jsonb );
2152+
count
2153+
-------
2154+
3
2155+
(1 row)
2156+
2157+
SELECT count(*) FROM testjsonb WHERE ANY ELEMENT ANYWHERE OF j AS v SATISFIES ( v = '"baz"'::jsonb );
2158+
count
2159+
-------
2160+
4
2161+
(1 row)
2162+
2163+
SELECT count(*) FROM testjsonb WHERE ANY ELEMENT OF j->'array' AS e SATISFIES ( e = '"baz"'::jsonb );
2164+
count
2165+
-------
2166+
4
2167+
(1 row)
2168+
2169+
SELECT count(*) FROM testjsonb WHERE ANY KEY OF j AS k SATISFIES (
2170+
k->>0 = 'array' AND ANY ELEMENT OF j->(k->>0) AS e SATISFIES ( e = '"baz"'::jsonb )
2171+
);
2172+
count
2173+
-------
2174+
4
2175+
(1 row)
2176+
21212177
CREATE INDEX jidx ON testjsonb USING gin (j);
21222178
SET enable_seqscan = off;
21232179
SELECT count(*) FROM testjsonb WHERE j @> '{"wait":null}';

‎src/test/regress/sql/jsonb.sql

Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -519,6 +519,18 @@ SELECT count(*) FROM testjsonb WHERE j ? 'bar';
519519
SELECT count(*) FROM testjsonb WHERE j ?| ARRAY['public','disabled'];
520520
SELECT count(*) FROM testjsonb WHERE j ?& ARRAY['public','disabled'];
521521

522+
SELECT count(*) FROM testjsonb WHERE ANY KEY OF j AS k SATISFIES ( k = '"pos"'::jsonb );
523+
SELECT count(*) FROM testjsonb WHERE ANY KEY OF j AS k SATISFIES ( k = '"age"'::jsonb );
524+
SELECT count(*) FROM testjsonb WHERE EACH KEY OF j AS k SATISFIES ( k = '"age"'::jsonb );
525+
SELECT count(*) FROM testjsonb WHERE ANY VALUE OF j AS v SATISFIES ( v = '"CAB"'::jsonb );
526+
SELECT count(*) FROM testjsonb WHERE ANY VALUE OF j AS v SATISFIES ( v = '"baz"'::jsonb );
527+
SELECT count(*) FROM testjsonb WHERE ANY VALUE ANYWHERE OF j AS v SATISFIES ( v = '"baz"'::jsonb );
528+
SELECT count(*) FROM testjsonb WHERE ANY ELEMENT ANYWHERE OF j AS v SATISFIES ( v = '"baz"'::jsonb );
529+
SELECT count(*) FROM testjsonb WHERE ANY ELEMENT OF j->'array' AS e SATISFIES ( e = '"baz"'::jsonb );
530+
SELECT count(*) FROM testjsonb WHERE ANY KEY OF j AS k SATISFIES (
531+
k->>0 = 'array' AND ANY ELEMENT OF j->(k->>0) AS e SATISFIES ( e = '"baz"'::jsonb )
532+
);
533+
522534
CREATE INDEX jidx ON testjsonb USING gin (j);
523535
SET enable_seqscan = off;
524536

0 commit comments

Comments
 (0)