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