gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/json102.test (about) 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 18 set testdir [file dirname $argv0] 19 source $testdir/tester.tcl 20 21 do_execsql_test json102-100 { 22 SELECT json_object('ex','[52,3.14159]'); 23 } {{{"ex":"[52,3.14159]"}}} 24 do_execsql_test json102-110 { 25 SELECT json_object('ex',json('[52,3.14159]')); 26 } {{{"ex":[52,3.14159]}}} 27 do_execsql_test json102-120 { 28 SELECT json_object('ex',json_array(52,3.14159)); 29 } {{{"ex":[52,3.14159]}}} 30 do_execsql_test json102-130 { 31 SELECT json(' { "this" : "is", "a": [ "test" ] } '); 32 } {{{"this":"is","a":["test"]}}} 33 do_execsql_test json102-140 { 34 SELECT json_array(1,2,'3',4); 35 } {{[1,2,"3",4]}} 36 do_execsql_test json102-150 { 37 SELECT json_array('[1,2]'); 38 } {{["[1,2]"]}} 39 do_execsql_test json102-160 { 40 SELECT json_array(json_array(1,2)); 41 } {{[[1,2]]}} 42 do_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}"]}} 45 do_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}]}} 48 do_execsql_test json102-190 { 49 SELECT json_array_length('[1,2,3,4]'); 50 } {{4}} 51 do_execsql_test json102-200 { 52 SELECT json_array_length('[1,2,3,4]', '$'); 53 } {{4}} 54 do_execsql_test json102-210 { 55 SELECT json_array_length('[1,2,3,4]', '$[2]'); 56 } {{0}} 57 do_execsql_test json102-220 { 58 SELECT json_array_length('{"one":[1,2,3]}'); 59 } {{0}} 60 do_execsql_test json102-230 { 61 SELECT json_array_length('{"one":[1,2,3]}', '$.one'); 62 } {{3}} 63 do_execsql_test json102-240 { 64 SELECT json_array_length('{"one":[1,2,3]}', '$.two'); 65 } {{}} 66 do_execsql_test json102-250 { 67 SELECT json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$'); 68 } {{{"a":2,"c":[4,5,{"f":7}]}}} 69 do_execsql_test json102-260 { 70 SELECT json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.c'); 71 } {{[4,5,{"f":7}]}} 72 do_execsql_test json102-270 { 73 SELECT json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.c[2]'); 74 } {{{"f":7}}} 75 do_execsql_test json102-280 { 76 SELECT json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.c[2].f'); 77 } {{7}} 78 do_execsql_test json102-290 { 79 SELECT json_extract('{"a":2,"c":[4,5],"f":7}','$.c','$.a'); 80 } {{[[4,5],2]}} 81 do_execsql_test json102-300 { 82 SELECT json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.x'); 83 } {{}} 84 do_execsql_test json102-310 { 85 SELECT json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.x', '$.a'); 86 } {{[null,2]}} 87 do_execsql_test json102-320 { 88 SELECT json_insert('{"a":2,"c":4}', '$.a', 99); 89 } {{{"a":2,"c":4}}} 90 do_execsql_test json102-330 { 91 SELECT json_insert('{"a":2,"c":4}', '$.e', 99); 92 } {{{"a":2,"c":4,"e":99}}} 93 do_execsql_test json102-340 { 94 SELECT json_replace('{"a":2,"c":4}', '$.a', 99); 95 } {{{"a":99,"c":4}}} 96 do_execsql_test json102-350 { 97 SELECT json_replace('{"a":2,"c":4}', '$.e', 99); 98 } {{{"a":2,"c":4}}} 99 do_execsql_test json102-360 { 100 SELECT json_set('{"a":2,"c":4}', '$.a', 99); 101 } {{{"a":99,"c":4}}} 102 do_execsql_test json102-370 { 103 SELECT json_set('{"a":2,"c":4}', '$.e', 99); 104 } {{{"a":2,"c":4,"e":99}}} 105 do_execsql_test json102-380 { 106 SELECT json_set('{"a":2,"c":4}', '$.c', '[97,96]'); 107 } {{{"a":2,"c":"[97,96]"}}} 108 do_execsql_test json102-390 { 109 SELECT json_set('{"a":2,"c":4}', '$.c', json('[97,96]')); 110 } {{{"a":2,"c":[97,96]}}} 111 do_execsql_test json102-400 { 112 SELECT json_set('{"a":2,"c":4}', '$.c', json_array(97,96)); 113 } {{{"a":2,"c":[97,96]}}} 114 do_execsql_test json102-410 { 115 SELECT json_object('a',2,'c',4); 116 } {{{"a":2,"c":4}}} 117 do_execsql_test json102-420 { 118 SELECT json_object('a',2,'c','{e:5}'); 119 } {{{"a":2,"c":"{e:5}"}}} 120 do_execsql_test json102-430 { 121 SELECT json_object('a',2,'c',json_object('e',5)); 122 } {{{"a":2,"c":{"e":5}}}} 123 do_execsql_test json102-440 { 124 SELECT json_remove('[0,1,2,3,4]','$[2]'); 125 } {{[0,1,3,4]}} 126 do_execsql_test json102-450 { 127 SELECT json_remove('[0,1,2,3,4]','$[2]','$[0]'); 128 } {{[1,3,4]}} 129 do_execsql_test json102-460 { 130 SELECT json_remove('[0,1,2,3,4]','$[0]','$[2]'); 131 } {{[1,2,4]}} 132 do_execsql_test json102-470 { 133 SELECT json_remove('{"x":25,"y":42}'); 134 } {{{"x":25,"y":42}}} 135 do_execsql_test json102-480 { 136 SELECT json_remove('{"x":25,"y":42}','$.z'); 137 } {{{"x":25,"y":42}}} 138 do_execsql_test json102-490 { 139 SELECT json_remove('{"x":25,"y":42}','$.y'); 140 } {{{"x":25}}} 141 do_execsql_test json102-500 { 142 SELECT json_remove('{"x":25,"y":42}','$'); 143 } {{}} 144 do_execsql_test json102-510 { 145 SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}'); 146 } {{object}} 147 do_execsql_test json102-520 { 148 SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}','$'); 149 } {{object}} 150 do_execsql_test json102-530 { 151 SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a'); 152 } {{array}} 153 do_execsql_test json102-540 { 154 SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[0]'); 155 } {{integer}} 156 do_execsql_test json102-550 { 157 SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[1]'); 158 } {{real}} 159 do_execsql_test json102-560 { 160 SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[2]'); 161 } {{true}} 162 do_execsql_test json102-570 { 163 SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[3]'); 164 } {{false}} 165 do_execsql_test json102-580 { 166 SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[4]'); 167 } {{null}} 168 do_execsql_test json102-590 { 169 SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[5]'); 170 } {{text}} 171 do_execsql_test json102-600 { 172 SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[6]'); 173 } {{}} 174 do_execsql_test json102-610 { 175 SELECT json_valid(char(123)||'"x":35'||char(125)); 176 } {{1}} 177 do_execsql_test json102-620 { 178 SELECT json_valid(char(123)||'"x":35'); 179 } {{0}} 180 181 ifcapable vtab { 182 do_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 195 do_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}} 201 do_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 210 do_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 } {} 232 set 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] 247 do_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 253 do_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 260 do_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} 266 do_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} 272 do_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 # 284 do_execsql_test json102-1201 { SELECT json_valid(char(32) || '"xyz"') } 1 285 do_execsql_test json102-1202 { SELECT json_valid(char(200) || '"xyz"') } 0 286 287 # Off-by-one error in jsonAppendString() 288 # 289 for {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 # 304 do_execsql_test json102-1401 { SELECT json_valid('{"x":01}') } 0 305 do_execsql_test json102-1402 { SELECT json_valid('{"x":-01}') } 0 306 do_execsql_test json102-1403 { SELECT json_valid('{"x":0}') } 1 307 do_execsql_test json102-1404 { SELECT json_valid('{"x":-0}') } 1 308 do_execsql_test json102-1405 { SELECT json_valid('{"x":0.1}') } 1 309 do_execsql_test json102-1406 { SELECT json_valid('{"x":-0.1}') } 1 310 do_execsql_test json102-1407 { SELECT json_valid('{"x":0.0000}') } 1 311 do_execsql_test json102-1408 { SELECT json_valid('{"x":-0.0000}') } 1 312 do_execsql_test json102-1409 { SELECT json_valid('{"x":01.5}') } 0 313 do_execsql_test json102-1410 { SELECT json_valid('{"x":-01.5}') } 0 314 do_execsql_test json102-1411 { SELECT json_valid('{"x":00}') } 0 315 do_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 # 323 do_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 # 330 do_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 # 337 reset_db 338 do_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 ] 367 do_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 391 finish_test