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