1# 2015-08-12 2# 3# The author disclaims copyright to this source code. In place of 4# a legal notice, here is a blessing: 5# 6# May you do good and not evil. 7# May you find forgiveness for yourself and forgive others. 8# May you share freely, never taking more than you give. 9# 10#*********************************************************************** 11# This file implements tests for JSON SQL functions extension to the 12# SQLite library. 13# 14# This file contains tests automatically generated from the json1 15# documentation. 16# 17 18set testdir [file dirname $argv0] 19source $testdir/tester.tcl 20 21ifcapable !json1 { 22 finish_test 23 return 24} 25 26do_execsql_test json102-100 { 27 SELECT json_object('ex','[52,3.14159]'); 28} {{{"ex":"[52,3.14159]"}}} 29do_execsql_test json102-110 { 30 SELECT json_object('ex',json('[52,3.14159]')); 31} {{{"ex":[52,3.14159]}}} 32do_execsql_test json102-120 { 33 SELECT json_object('ex',json_array(52,3.14159)); 34} {{{"ex":[52,3.14159]}}} 35do_execsql_test json102-130 { 36 SELECT json(' { "this" : "is", "a": [ "test" ] } '); 37} {{{"this":"is","a":["test"]}}} 38do_execsql_test json102-140 { 39 SELECT json_array(1,2,'3',4); 40} {{[1,2,"3",4]}} 41do_execsql_test json102-150 { 42 SELECT json_array('[1,2]'); 43} {{["[1,2]"]}} 44do_execsql_test json102-160 { 45 SELECT json_array(json_array(1,2)); 46} {{[[1,2]]}} 47do_execsql_test json102-170 { 48 SELECT json_array(1,null,'3','[4,5]','{"six":7.7}'); 49} {{[1,null,"3","[4,5]","{\"six\":7.7}"]}} 50do_execsql_test json102-180 { 51 SELECT json_array(1,null,'3',json('[4,5]'),json('{"six":7.7}')); 52} {{[1,null,"3",[4,5],{"six":7.7}]}} 53do_execsql_test json102-190 { 54 SELECT json_array_length('[1,2,3,4]'); 55} {{4}} 56do_execsql_test json102-200 { 57 SELECT json_array_length('[1,2,3,4]', '$'); 58} {{4}} 59do_execsql_test json102-210 { 60 SELECT json_array_length('[1,2,3,4]', '$[2]'); 61} {{0}} 62do_execsql_test json102-220 { 63 SELECT json_array_length('{"one":[1,2,3]}'); 64} {{0}} 65do_execsql_test json102-230 { 66 SELECT json_array_length('{"one":[1,2,3]}', '$.one'); 67} {{3}} 68do_execsql_test json102-240 { 69 SELECT json_array_length('{"one":[1,2,3]}', '$.two'); 70} {{}} 71do_execsql_test json102-250 { 72 SELECT json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$'); 73} {{{"a":2,"c":[4,5,{"f":7}]}}} 74do_execsql_test json102-251 { 75 SELECT json_nextract('{"a":2,"c":[4,5,{"f":7}]}', '$'); 76} {{{"a":2,"c":[4,5,{"f":7}]}}} 77do_execsql_test json102-252 { 78 SELECT '{"a":2,"c":[4,5,{"f":7}]}' -> '$'; 79} {{{"a":2,"c":[4,5,{"f":7}]}}} 80do_execsql_test json102-260 { 81 SELECT json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.c'); 82} {{[4,5,{"f":7}]}} 83do_execsql_test json102-261 { 84 SELECT '{"a":2,"c":[4,5,{"f":7}]}' -> '$.c'; 85} {{[4,5,{"f":7}]}} 86do_execsql_test json102-262 { 87 SELECT '{"a":2,"c":[4,5,{"f":7}]}' -> 'c'; 88} {{[4,5,{"f":7}]}} 89do_catchsql_test json102-265 { 90 SELECT json_extract('[1,2,3', '$[2]'); 91} {1 {malformed JSON}} 92do_catchsql_test json102-266 { 93 SELECT json_nextract('[1,2,3', '$[2]'); 94} {0 {{}}} 95do_catchsql_test json102-267 { 96 SELECT json_extract('[1,2,3', '$'); 97} {1 {malformed JSON}} 98do_catchsql_test json102-268 { 99 SELECT json_nextract('[1,2,3', '$'); 100} {0 {{"[1,2,3"}}} 101do_catchsql_test json102-269 { 102 SELECT '[1,2,3' ->> '$'; 103} {0 {{"[1,2,3"}}} 104 105do_execsql_test json102-270 { 106 SELECT json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.c[2]'); 107} {{{"f":7}}} 108do_execsql_test json102-271 { 109 SELECT '{"a":2,"c":[4,5,{"f":7}]}' -> '$.c[2]'; 110} {{{"f":7}}} 111do_execsql_test json102-272 { 112 SELECT '{"a":2,"c":[4,5,{"f":7}]}' -> 'c' -> 2; 113} {{{"f":7}}} 114do_execsql_test json102-280 { 115 SELECT json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.c[2].f'); 116} {{7}} 117do_execsql_test json102-281 { 118 SELECT '{"a":2,"c":[4,5,{"f":7}]}' -> 'c' -> 2 -> 'f'; 119} {{7}} 120do_execsql_test json102-282 { 121 SELECT '{"a":2,"c":[4,5,{"f":7}]}' -> 'c' -> '[2]' -> 'f'; 122} {{7}} 123do_execsql_test json102-290 { 124 SELECT json_extract('{"a":2,"c":[4,5],"f":7}','$.c','$.a'); 125} {{[[4,5],2]}} 126do_execsql_test json102-300 { 127 SELECT json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.x'); 128} {{}} 129do_execsql_test json102-301 { 130 SELECT '{"a":2,"c":[4,5,{"f":7}]}' -> 'x'; 131} {{}} 132do_execsql_test json102-302 { 133 SELECT '{"a":2,"c":[4,5,{"f":7}]}' -> NULL; 134} {{}} 135do_execsql_test json102-310 { 136 SELECT json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.x', '$.a'); 137} {{[null,2]}} 138do_execsql_test json102-320 { 139 SELECT json_insert('{"a":2,"c":4}', '$.a', 99); 140} {{{"a":2,"c":4}}} 141do_execsql_test json102-330 { 142 SELECT json_insert('{"a":2,"c":4}', '$.e', 99); 143} {{{"a":2,"c":4,"e":99}}} 144do_execsql_test json102-340 { 145 SELECT json_replace('{"a":2,"c":4}', '$.a', 99); 146} {{{"a":99,"c":4}}} 147do_execsql_test json102-350 { 148 SELECT json_replace('{"a":2,"c":4}', '$.e', 99); 149} {{{"a":2,"c":4}}} 150do_execsql_test json102-360 { 151 SELECT json_set('{"a":2,"c":4}', '$.a', 99); 152} {{{"a":99,"c":4}}} 153do_execsql_test json102-370 { 154 SELECT json_set('{"a":2,"c":4}', '$.e', 99); 155} {{{"a":2,"c":4,"e":99}}} 156do_execsql_test json102-380 { 157 SELECT json_set('{"a":2,"c":4}', '$.c', '[97,96]'); 158} {{{"a":2,"c":"[97,96]"}}} 159do_execsql_test json102-390 { 160 SELECT json_set('{"a":2,"c":4}', '$.c', json('[97,96]')); 161} {{{"a":2,"c":[97,96]}}} 162do_execsql_test json102-400 { 163 SELECT json_set('{"a":2,"c":4}', '$.c', json_array(97,96)); 164} {{{"a":2,"c":[97,96]}}} 165do_execsql_test json102-410 { 166 SELECT json_object('a',2,'c',4); 167} {{{"a":2,"c":4}}} 168do_execsql_test json102-420 { 169 SELECT json_object('a',2,'c','{e:5}'); 170} {{{"a":2,"c":"{e:5}"}}} 171do_execsql_test json102-430 { 172 SELECT json_object('a',2,'c',json_object('e',5)); 173} {{{"a":2,"c":{"e":5}}}} 174do_execsql_test json102-440 { 175 SELECT json_remove('[0,1,2,3,4]','$[2]'); 176} {{[0,1,3,4]}} 177do_execsql_test json102-450 { 178 SELECT json_remove('[0,1,2,3,4]','$[2]','$[0]'); 179} {{[1,3,4]}} 180do_execsql_test json102-460 { 181 SELECT json_remove('[0,1,2,3,4]','$[0]','$[2]'); 182} {{[1,2,4]}} 183do_execsql_test json102-470 { 184 SELECT json_remove('{"x":25,"y":42}'); 185} {{{"x":25,"y":42}}} 186do_execsql_test json102-480 { 187 SELECT json_remove('{"x":25,"y":42}','$.z'); 188} {{{"x":25,"y":42}}} 189do_execsql_test json102-490 { 190 SELECT json_remove('{"x":25,"y":42}','$.y'); 191} {{{"x":25}}} 192do_execsql_test json102-500 { 193 SELECT json_remove('{"x":25,"y":42}','$'); 194} {{}} 195do_execsql_test json102-510 { 196 SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}'); 197} {{object}} 198do_execsql_test json102-511 { 199 SELECT json_ntype('{"a":[2,3.5,true,false,null,"x"]}'); 200} {{object}} 201do_execsql_test json102-520 { 202 SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}','$'); 203} {{object}} 204do_execsql_test json102-530 { 205 SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a'); 206} {{array}} 207do_execsql_test json102-540 { 208 SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[0]'); 209} {{integer}} 210do_execsql_test json102-550 { 211 SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[1]'); 212} {{real}} 213do_execsql_test json102-560 { 214 SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[2]'); 215} {{true}} 216do_execsql_test json102-570 { 217 SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[3]'); 218} {{false}} 219do_execsql_test json102-580 { 220 SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[4]'); 221} {{null}} 222do_execsql_test json102-590 { 223 SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[5]'); 224} {{text}} 225do_execsql_test json102-600 { 226 SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[6]'); 227} {{}} 228do_execsql_test json102-610 { 229 SELECT json_valid(char(123)||'"x":35'||char(125)); 230} {{1}} 231do_execsql_test json102-620 { 232 SELECT json_valid(char(123)||'"x":35'); 233} {{0}} 234do_catchsql_test json102-630 { 235 SELECT json_type('["a",'); 236} {1 {malformed JSON}} 237do_catchsql_test json102-631 { 238 SELECT json_ntype('["a",'); 239} {0 {{}}} 240 241ifcapable vtab { 242do_execsql_test json102-1000 { 243 CREATE TABLE user(name,phone); 244 INSERT INTO user(name,phone) VALUES 245 ('Alice','["919-555-2345","804-555-3621"]'), 246 ('Bob','["201-555-8872"]'), 247 ('Cindy','["704-555-9983"]'), 248 ('Dave','["336-555-8421","704-555-4321","803-911-4421"]'); 249 SELECT DISTINCT user.name 250 FROM user, json_each(user.phone) 251 WHERE json_each.value LIKE '704-%' 252 ORDER BY 1; 253} {Cindy Dave} 254 255do_execsql_test json102-1010 { 256 UPDATE user 257 SET phone=json_extract(phone,'$[0]') 258 WHERE json_array_length(phone)<2; 259 SELECT name, substr(phone,1,5) FROM user ORDER BY name; 260} {Alice {["919} Bob 201-5 Cindy 704-5 Dave {["336}} 261do_execsql_test json102-1011 { 262 SELECT name FROM user WHERE phone LIKE '704-%' 263 UNION 264 SELECT user.name 265 FROM user, json_each(user.phone) 266 WHERE json_valid(user.phone) 267 AND json_each.value LIKE '704-%'; 268} {Cindy Dave} 269 270do_execsql_test json102-1100 { 271 CREATE TABLE big(json JSON); 272 INSERT INTO big(json) VALUES('{ 273 "id":123, 274 "stuff":[1,2,3,4], 275 "partlist":[ 276 {"uuid":"bb108722-572e-11e5-9320-7f3b63a4ca74"}, 277 {"uuid":"c690dc14-572e-11e5-95f9-dfc8861fd535"}, 278 {"subassembly":[ 279 {"uuid":"6fa5181e-5721-11e5-a04e-57f3d7b32808"} 280 ]} 281 ] 282 }'); 283 INSERT INTO big(json) VALUES('{ 284 "id":456, 285 "stuff":["hello","world","xyzzy"], 286 "partlist":[ 287 {"uuid":false}, 288 {"uuid":"c690dc14-572e-11e5-95f9-dfc8861fd535"} 289 ] 290 }'); 291} {} 292set correct_answer [list \ 293 1 {$.id} 123 \ 294 1 {$.stuff[0]} 1 \ 295 1 {$.stuff[1]} 2 \ 296 1 {$.stuff[2]} 3 \ 297 1 {$.stuff[3]} 4 \ 298 1 {$.partlist[0].uuid} bb108722-572e-11e5-9320-7f3b63a4ca74 \ 299 1 {$.partlist[1].uuid} c690dc14-572e-11e5-95f9-dfc8861fd535 \ 300 1 {$.partlist[2].subassembly[0].uuid} 6fa5181e-5721-11e5-a04e-57f3d7b32808 \ 301 2 {$.id} 456 \ 302 2 {$.stuff[0]} hello \ 303 2 {$.stuff[1]} world \ 304 2 {$.stuff[2]} xyzzy \ 305 2 {$.partlist[0].uuid} 0 \ 306 2 {$.partlist[1].uuid} c690dc14-572e-11e5-95f9-dfc8861fd535] 307do_execsql_test json102-1110 { 308 SELECT big.rowid, fullkey, value 309 FROM big, json_tree(big.json) 310 WHERE json_tree.type NOT IN ('object','array') 311 ORDER BY +big.rowid, +json_tree.id 312} $correct_answer 313do_execsql_test json102-1120 { 314 SELECT big.rowid, fullkey, atom 315 FROM big, json_tree(big.json) 316 WHERE atom IS NOT NULL 317 ORDER BY +big.rowid, +json_tree.id 318} $correct_answer 319 320do_execsql_test json102-1130 { 321 SELECT DISTINCT json_extract(big.json,'$.id') 322 FROM big, json_tree(big.json,'$.partlist') 323 WHERE json_tree.key='uuid' 324 AND json_tree.value='6fa5181e-5721-11e5-a04e-57f3d7b32808'; 325} {123} 326do_execsql_test json102-1131 { 327 SELECT DISTINCT json_extract(big.json,'$.id') 328 FROM big, json_tree(big.json,'$') 329 WHERE json_tree.key='uuid' 330 AND json_tree.value='6fa5181e-5721-11e5-a04e-57f3d7b32808'; 331} {123} 332do_execsql_test json102-1132 { 333 SELECT DISTINCT json_extract(big.json,'$.id') 334 FROM big, json_tree(big.json) 335 WHERE json_tree.key='uuid' 336 AND json_tree.value='6fa5181e-5721-11e5-a04e-57f3d7b32808'; 337} {123} 338} ;# end ifcapable vtab 339 340#------------------------------------------------------------------------- 341# Test that json_valid() correctly identifies non-ascii range 342# characters as non-whitespace. 343# 344do_execsql_test json102-1201 { SELECT json_valid(char(32) || '"xyz"') } 1 345do_execsql_test json102-1202 { SELECT json_valid(char(200) || '"xyz"') } 0 346 347# Off-by-one error in jsonAppendString() 348# 349for {set i 0} {$i<100} {incr i} { 350 set str abcdef[string repeat \" [expr {$i+50}]]uvwxyz 351 do_test json102-[format %d [expr {$i+1300}]] { 352 db eval {SELECT json_extract(json_array($::str),'$[0]')==$::str} 353 } {1} 354} 355 356#------------------------------------------------------------------------- 357# 2017-04-08 ticket b93be8729a895a528e2849fca99f7 358# JSON extension accepts invalid numeric values 359# 360# JSON does not allow leading zeros. But the JSON extension was 361# allowing them. The following tests verify that the problem is now 362# fixed. 363# 364do_execsql_test json102-1401 { SELECT json_valid('{"x":01}') } 0 365do_execsql_test json102-1402 { SELECT json_valid('{"x":-01}') } 0 366do_execsql_test json102-1403 { SELECT json_valid('{"x":0}') } 1 367do_execsql_test json102-1404 { SELECT json_valid('{"x":-0}') } 1 368do_execsql_test json102-1405 { SELECT json_valid('{"x":0.1}') } 1 369do_execsql_test json102-1406 { SELECT json_valid('{"x":-0.1}') } 1 370do_execsql_test json102-1407 { SELECT json_valid('{"x":0.0000}') } 1 371do_execsql_test json102-1408 { SELECT json_valid('{"x":-0.0000}') } 1 372do_execsql_test json102-1409 { SELECT json_valid('{"x":01.5}') } 0 373do_execsql_test json102-1410 { SELECT json_valid('{"x":-01.5}') } 0 374do_execsql_test json102-1411 { SELECT json_valid('{"x":00}') } 0 375do_execsql_test json102-1412 { SELECT json_valid('{"x":-00}') } 0 376 377#------------------------------------------------------------------------ 378# 2017-04-10 ticket 6c9b5514077fed34551f98e64c09a10dc2fc8e16 379# JSON extension accepts strings containing control characters. 380# 381# The JSON spec requires that all control characters be escaped. 382# 383do_execsql_test json102-1500 { 384 WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<0x20) 385 SELECT x FROM c WHERE json_valid(printf('{"a":"x%sz"}', char(x))) ORDER BY x; 386} {32} 387 388# All control characters are escaped 389# 390do_execsql_test json102-1501 { 391 WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<0x1f) 392 SELECT sum(json_valid(json_quote('a'||char(x)||'z'))) FROM c ORDER BY x; 393} {31} 394 395finish_test 396