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