summaryrefslogtreecommitdiffstats
path: root/test/json102.test
diff options
context:
space:
mode:
Diffstat (limited to 'test/json102.test')
-rw-r--r--test/json102.test391
1 files changed, 391 insertions, 0 deletions
diff --git a/test/json102.test b/test/json102.test
new file mode 100644
index 0000000..f551c4b
--- /dev/null
+++ b/test/json102.test
@@ -0,0 +1,391 @@
+# 2015-08-12
+#
+# The author disclaims copyright to this source code. In place of
+# a legal notice, here is a blessing:
+#
+# May you do good and not evil.
+# May you find forgiveness for yourself and forgive others.
+# May you share freely, never taking more than you give.
+#
+#***********************************************************************
+# This file implements tests for JSON SQL functions extension to the
+# SQLite library.
+#
+# This file contains tests automatically generated from the json1
+# documentation.
+#
+
+set testdir [file dirname $argv0]
+source $testdir/tester.tcl
+
+do_execsql_test json102-100 {
+ SELECT json_object('ex','[52,3.14159]');
+} {{{"ex":"[52,3.14159]"}}}
+do_execsql_test json102-110 {
+ SELECT json_object('ex',json('[52,3.14159]'));
+} {{{"ex":[52,3.14159]}}}
+do_execsql_test json102-120 {
+ SELECT json_object('ex',json_array(52,3.14159));
+} {{{"ex":[52,3.14159]}}}
+do_execsql_test json102-130 {
+ SELECT json(' { "this" : "is", "a": [ "test" ] } ');
+} {{{"this":"is","a":["test"]}}}
+do_execsql_test json102-140 {
+ SELECT json_array(1,2,'3',4);
+} {{[1,2,"3",4]}}
+do_execsql_test json102-150 {
+ SELECT json_array('[1,2]');
+} {{["[1,2]"]}}
+do_execsql_test json102-160 {
+ SELECT json_array(json_array(1,2));
+} {{[[1,2]]}}
+do_execsql_test json102-170 {
+ SELECT json_array(1,null,'3','[4,5]','{"six":7.7}');
+} {{[1,null,"3","[4,5]","{\"six\":7.7}"]}}
+do_execsql_test json102-180 {
+ SELECT json_array(1,null,'3',json('[4,5]'),json('{"six":7.7}'));
+} {{[1,null,"3",[4,5],{"six":7.7}]}}
+do_execsql_test json102-190 {
+ SELECT json_array_length('[1,2,3,4]');
+} {{4}}
+do_execsql_test json102-200 {
+ SELECT json_array_length('[1,2,3,4]', '$');
+} {{4}}
+do_execsql_test json102-210 {
+ SELECT json_array_length('[1,2,3,4]', '$[2]');
+} {{0}}
+do_execsql_test json102-220 {
+ SELECT json_array_length('{"one":[1,2,3]}');
+} {{0}}
+do_execsql_test json102-230 {
+ SELECT json_array_length('{"one":[1,2,3]}', '$.one');
+} {{3}}
+do_execsql_test json102-240 {
+ SELECT json_array_length('{"one":[1,2,3]}', '$.two');
+} {{}}
+do_execsql_test json102-250 {
+ SELECT json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$');
+} {{{"a":2,"c":[4,5,{"f":7}]}}}
+do_execsql_test json102-260 {
+ SELECT json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.c');
+} {{[4,5,{"f":7}]}}
+do_execsql_test json102-270 {
+ SELECT json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.c[2]');
+} {{{"f":7}}}
+do_execsql_test json102-280 {
+ SELECT json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.c[2].f');
+} {{7}}
+do_execsql_test json102-290 {
+ SELECT json_extract('{"a":2,"c":[4,5],"f":7}','$.c','$.a');
+} {{[[4,5],2]}}
+do_execsql_test json102-300 {
+ SELECT json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.x');
+} {{}}
+do_execsql_test json102-310 {
+ SELECT json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.x', '$.a');
+} {{[null,2]}}
+do_execsql_test json102-320 {
+ SELECT json_insert('{"a":2,"c":4}', '$.a', 99);
+} {{{"a":2,"c":4}}}
+do_execsql_test json102-330 {
+ SELECT json_insert('{"a":2,"c":4}', '$.e', 99);
+} {{{"a":2,"c":4,"e":99}}}
+do_execsql_test json102-340 {
+ SELECT json_replace('{"a":2,"c":4}', '$.a', 99);
+} {{{"a":99,"c":4}}}
+do_execsql_test json102-350 {
+ SELECT json_replace('{"a":2,"c":4}', '$.e', 99);
+} {{{"a":2,"c":4}}}
+do_execsql_test json102-360 {
+ SELECT json_set('{"a":2,"c":4}', '$.a', 99);
+} {{{"a":99,"c":4}}}
+do_execsql_test json102-370 {
+ SELECT json_set('{"a":2,"c":4}', '$.e', 99);
+} {{{"a":2,"c":4,"e":99}}}
+do_execsql_test json102-380 {
+ SELECT json_set('{"a":2,"c":4}', '$.c', '[97,96]');
+} {{{"a":2,"c":"[97,96]"}}}
+do_execsql_test json102-390 {
+ SELECT json_set('{"a":2,"c":4}', '$.c', json('[97,96]'));
+} {{{"a":2,"c":[97,96]}}}
+do_execsql_test json102-400 {
+ SELECT json_set('{"a":2,"c":4}', '$.c', json_array(97,96));
+} {{{"a":2,"c":[97,96]}}}
+do_execsql_test json102-410 {
+ SELECT json_object('a',2,'c',4);
+} {{{"a":2,"c":4}}}
+do_execsql_test json102-420 {
+ SELECT json_object('a',2,'c','{e:5}');
+} {{{"a":2,"c":"{e:5}"}}}
+do_execsql_test json102-430 {
+ SELECT json_object('a',2,'c',json_object('e',5));
+} {{{"a":2,"c":{"e":5}}}}
+do_execsql_test json102-440 {
+ SELECT json_remove('[0,1,2,3,4]','$[2]');
+} {{[0,1,3,4]}}
+do_execsql_test json102-450 {
+ SELECT json_remove('[0,1,2,3,4]','$[2]','$[0]');
+} {{[1,3,4]}}
+do_execsql_test json102-460 {
+ SELECT json_remove('[0,1,2,3,4]','$[0]','$[2]');
+} {{[1,2,4]}}
+do_execsql_test json102-470 {
+ SELECT json_remove('{"x":25,"y":42}');
+} {{{"x":25,"y":42}}}
+do_execsql_test json102-480 {
+ SELECT json_remove('{"x":25,"y":42}','$.z');
+} {{{"x":25,"y":42}}}
+do_execsql_test json102-490 {
+ SELECT json_remove('{"x":25,"y":42}','$.y');
+} {{{"x":25}}}
+do_execsql_test json102-500 {
+ SELECT json_remove('{"x":25,"y":42}','$');
+} {{}}
+do_execsql_test json102-510 {
+ SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}');
+} {{object}}
+do_execsql_test json102-520 {
+ SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}','$');
+} {{object}}
+do_execsql_test json102-530 {
+ SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a');
+} {{array}}
+do_execsql_test json102-540 {
+ SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[0]');
+} {{integer}}
+do_execsql_test json102-550 {
+ SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[1]');
+} {{real}}
+do_execsql_test json102-560 {
+ SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[2]');
+} {{true}}
+do_execsql_test json102-570 {
+ SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[3]');
+} {{false}}
+do_execsql_test json102-580 {
+ SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[4]');
+} {{null}}
+do_execsql_test json102-590 {
+ SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[5]');
+} {{text}}
+do_execsql_test json102-600 {
+ SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[6]');
+} {{}}
+do_execsql_test json102-610 {
+ SELECT json_valid(char(123)||'"x":35'||char(125));
+} {{1}}
+do_execsql_test json102-620 {
+ SELECT json_valid(char(123)||'"x":35');
+} {{0}}
+
+ifcapable vtab {
+do_execsql_test json102-1000 {
+ CREATE TABLE user(name,phone);
+ INSERT INTO user(name,phone) VALUES
+ ('Alice','["919-555-2345","804-555-3621"]'),
+ ('Bob','["201-555-8872"]'),
+ ('Cindy','["704-555-9983"]'),
+ ('Dave','["336-555-8421","704-555-4321","803-911-4421"]');
+ SELECT DISTINCT user.name
+ FROM user, json_each(user.phone)
+ WHERE json_each.value LIKE '704-%'
+ ORDER BY 1;
+} {Cindy Dave}
+
+do_execsql_test json102-1010 {
+ UPDATE user
+ SET phone=json_extract(phone,'$[0]')
+ WHERE json_array_length(phone)<2;
+ SELECT name, substr(phone,1,5) FROM user ORDER BY name;
+} {Alice {["919} Bob 201-5 Cindy 704-5 Dave {["336}}
+do_execsql_test json102-1011 {
+ SELECT name FROM user WHERE phone LIKE '704-%'
+ UNION
+ SELECT user.name
+ FROM user, json_each(user.phone)
+ WHERE json_valid(user.phone)
+ AND json_each.value LIKE '704-%';
+} {Cindy Dave}
+
+do_execsql_test json102-1100 {
+ CREATE TABLE big(json JSON);
+ INSERT INTO big(json) VALUES('{
+ "id":123,
+ "stuff":[1,2,3,4],
+ "partlist":[
+ {"uuid":"bb108722-572e-11e5-9320-7f3b63a4ca74"},
+ {"uuid":"c690dc14-572e-11e5-95f9-dfc8861fd535"},
+ {"subassembly":[
+ {"uuid":"6fa5181e-5721-11e5-a04e-57f3d7b32808"}
+ ]}
+ ]
+ }');
+ INSERT INTO big(json) VALUES('{
+ "id":456,
+ "stuff":["hello","world","xyzzy"],
+ "partlist":[
+ {"uuid":false},
+ {"uuid":"c690dc14-572e-11e5-95f9-dfc8861fd535"}
+ ]
+ }');
+} {}
+set correct_answer [list \
+ 1 {$.id} 123 \
+ 1 {$.stuff[0]} 1 \
+ 1 {$.stuff[1]} 2 \
+ 1 {$.stuff[2]} 3 \
+ 1 {$.stuff[3]} 4 \
+ 1 {$.partlist[0].uuid} bb108722-572e-11e5-9320-7f3b63a4ca74 \
+ 1 {$.partlist[1].uuid} c690dc14-572e-11e5-95f9-dfc8861fd535 \
+ 1 {$.partlist[2].subassembly[0].uuid} 6fa5181e-5721-11e5-a04e-57f3d7b32808 \
+ 2 {$.id} 456 \
+ 2 {$.stuff[0]} hello \
+ 2 {$.stuff[1]} world \
+ 2 {$.stuff[2]} xyzzy \
+ 2 {$.partlist[0].uuid} 0 \
+ 2 {$.partlist[1].uuid} c690dc14-572e-11e5-95f9-dfc8861fd535]
+do_execsql_test json102-1110 {
+ SELECT big.rowid, fullkey, value
+ FROM big, json_tree(big.json)
+ WHERE json_tree.type NOT IN ('object','array')
+ ORDER BY +big.rowid, +json_tree.id
+} $correct_answer
+do_execsql_test json102-1120 {
+ SELECT big.rowid, fullkey, atom
+ FROM big, json_tree(big.json)
+ WHERE atom IS NOT NULL
+ ORDER BY +big.rowid, +json_tree.id
+} $correct_answer
+
+do_execsql_test json102-1130 {
+ SELECT DISTINCT json_extract(big.json,'$.id')
+ FROM big, json_tree(big.json,'$.partlist')
+ WHERE json_tree.key='uuid'
+ AND json_tree.value='6fa5181e-5721-11e5-a04e-57f3d7b32808';
+} {123}
+do_execsql_test json102-1131 {
+ SELECT DISTINCT json_extract(big.json,'$.id')
+ FROM big, json_tree(big.json,'$')
+ WHERE json_tree.key='uuid'
+ AND json_tree.value='6fa5181e-5721-11e5-a04e-57f3d7b32808';
+} {123}
+do_execsql_test json102-1132 {
+ SELECT DISTINCT json_extract(big.json,'$.id')
+ FROM big, json_tree(big.json)
+ WHERE json_tree.key='uuid'
+ AND json_tree.value='6fa5181e-5721-11e5-a04e-57f3d7b32808';
+} {123}
+} ;# end ifcapable vtab
+
+#-------------------------------------------------------------------------
+# Test that json_valid() correctly identifies non-ascii range
+# characters as non-whitespace.
+#
+do_execsql_test json102-1201 { SELECT json_valid(char(32) || '"xyz"') } 1
+do_execsql_test json102-1202 { SELECT json_valid(char(200) || '"xyz"') } 0
+
+# Off-by-one error in jsonAppendString()
+#
+for {set i 0} {$i<100} {incr i} {
+ set str abcdef[string repeat \" [expr {$i+50}]]uvwxyz
+ do_test json102-[format %d [expr {$i+1300}]] {
+ db eval {SELECT json_extract(json_array($::str),'$[0]')==$::str}
+ } {1}
+}
+
+#-------------------------------------------------------------------------
+# 2017-04-08 ticket b93be8729a895a528e2849fca99f7
+# JSON extension accepts invalid numeric values
+#
+# JSON does not allow leading zeros. But the JSON extension was
+# allowing them. The following tests verify that the problem is now
+# fixed.
+#
+do_execsql_test json102-1401 { SELECT json_valid('{"x":01}') } 0
+do_execsql_test json102-1402 { SELECT json_valid('{"x":-01}') } 0
+do_execsql_test json102-1403 { SELECT json_valid('{"x":0}') } 1
+do_execsql_test json102-1404 { SELECT json_valid('{"x":-0}') } 1
+do_execsql_test json102-1405 { SELECT json_valid('{"x":0.1}') } 1
+do_execsql_test json102-1406 { SELECT json_valid('{"x":-0.1}') } 1
+do_execsql_test json102-1407 { SELECT json_valid('{"x":0.0000}') } 1
+do_execsql_test json102-1408 { SELECT json_valid('{"x":-0.0000}') } 1
+do_execsql_test json102-1409 { SELECT json_valid('{"x":01.5}') } 0
+do_execsql_test json102-1410 { SELECT json_valid('{"x":-01.5}') } 0
+do_execsql_test json102-1411 { SELECT json_valid('{"x":00}') } 0
+do_execsql_test json102-1412 { SELECT json_valid('{"x":-00}') } 0
+
+#------------------------------------------------------------------------
+# 2017-04-10 ticket 6c9b5514077fed34551f98e64c09a10dc2fc8e16
+# JSON extension accepts strings containing control characters.
+#
+# The JSON spec requires that all control characters be escaped.
+#
+do_execsql_test json102-1500 {
+ WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<0x20)
+ SELECT x FROM c WHERE json_valid(printf('{"a":"x%sz"}', char(x))) ORDER BY x;
+} {32}
+
+# All control characters are escaped
+#
+do_execsql_test json102-1501 {
+ WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<0x1f)
+ SELECT sum(json_valid(json_quote('a'||char(x)||'z'))) FROM c ORDER BY x;
+} {31}
+
+# 2022-01-10 tests for -> and ->> operators
+#
+reset_db
+do_execsql_test json102-1600 {
+ CREATE TABLE t1(id INTEGER PRIMARY KEY, x JSON);
+ INSERT INTO t1(id,x) VALUES
+ (1, '{"a":null}'),
+ (2, '{"a":123}'),
+ (3, '{"a":4.5}'),
+ (4, '{"a":"six"}'),
+ (5, '{"a":[7,8]}'),
+ (6, '{"a":{"b":9}}'),
+ (7, '{"b":999}');
+ SELECT
+ id,
+ x->'a' AS '->',
+ CASE WHEN subtype(x->'a') THEN 'json' ELSE typeof(x->'a') END AS 'type',
+ x->>'a' AS '->>',
+ CASE WHEN subtype(x->>'a') THEN 'json' ELSE typeof(x->>'a') END AS 'type',
+ json_extract(x,'$.a') AS 'json_extract',
+ CASE WHEN subtype(json_extract(x,'$.a'))
+ THEN 'json' ELSE typeof(json_extract(x,'$.a')) END AS 'type'
+ FROM t1 ORDER BY id;
+} [list \
+ 1 null json {} null {} null \
+ 2 123 json 123 integer 123 integer \
+ 3 4.5 json 4.5 real 4.5 real \
+ 4 {"six"} json six text six text \
+ 5 {[7,8]} json {[7,8]} text {[7,8]} json \
+ 6 {{"b":9}} json {{"b":9}} text {{"b":9}} json \
+ 7 {} null {} null {} null
+]
+do_execsql_test json102-1610 {
+ DELETE FROM t1;
+ INSERT INTO t1(x) VALUES('[null,123,4.5,"six",[7,8],{"b":9}]');
+ WITH c(y) AS (VALUES(0),(1),(2),(3),(4),(5),(6))
+ SELECT
+ y,
+ x->y AS '->',
+ CASE WHEN subtype(x->y) THEN 'json' ELSE typeof(x->y) END AS 'type',
+ x->>y AS '->>',
+ CASE WHEN subtype(x->>y) THEN 'json' ELSE typeof(x->>y) END AS 'type',
+ json_extract(x,format('$[%d]',y)) AS 'json_extract',
+ CASE WHEN subtype(json_extract(x,format('$[%d]',y)))
+ THEN 'json' ELSE typeof(json_extract(x,format('$[%d]',y))) END AS 'type'
+ FROM c, t1 ORDER BY y;
+} [list \
+ 0 null json {} null {} null \
+ 1 123 json 123 integer 123 integer \
+ 2 4.5 json 4.5 real 4.5 real \
+ 3 {"six"} json six text six text \
+ 4 {[7,8]} json {[7,8]} text {[7,8]} json \
+ 5 {{"b":9}} json {{"b":9}} text {{"b":9}} json \
+ 6 {} null {} null {} null
+]
+
+finish_test
generated by cgit v1.2.3 (git 2.39.1) at 2024-06-19 21:47:55 +0000