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_array(1,2,'3',4); 24} {{[1,2,"3",4]}} 25do_execsql_test json102-110 { 26 SELECT json_array('[1,2]'); 27} {{["[1,2]"]}} 28do_execsql_test json102-120 { 29 SELECT json_array(1,null,'3','[4,5]','{"six":7.7}'); 30} {{[1,null,"3","[4,5]","{\"six\":7.7}"]}} 31do_execsql_test json102-130 { 32 SELECT json_array_length('[1,2,3,4]'); 33} {{4}} 34do_execsql_test json102-140 { 35 SELECT json_array_length('{"one":[1,2,3]}'); 36} {{0}} 37do_execsql_test json102-150 { 38 SELECT json_array_length('{"one":[1,2,3]}', '$.one'); 39} {{3}} 40do_execsql_test json102-160 { 41 SELECT json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$'); 42} {{{"a":2,"c":[4,5,{"f":7}]}}} 43do_execsql_test json102-170 { 44 SELECT json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.c'); 45} {{[4,5,{"f":7}]}} 46do_execsql_test json102-180 { 47 SELECT json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.c[2]'); 48} {{{"f":7}}} 49do_execsql_test json102-190 { 50 SELECT json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.c[2].f'); 51} {{7}} 52do_execsql_test json102-200 { 53 SELECT json_extract('{"a":2,"c":[4,5],"f":7}','$.c','$.a'); 54} {{[[4,5],2]}} 55do_execsql_test json102-210 { 56 SELECT json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.x'); 57} {{}} 58do_execsql_test json102-220 { 59 SELECT json_insert('{"a":2,"c":4}', '$.a', 99); 60} {{{"a":2,"c":4}}} 61do_execsql_test json102-230 { 62 SELECT json_insert('{"a":2,"c":4}', '$.e', 99); 63} {{{"a":2,"c":4,"e":99}}} 64do_execsql_test json102-240 { 65 SELECT json_replace('{"a":2,"c":4}', '$.a', 99); 66} {{{"a":99,"c":4}}} 67do_execsql_test json102-250 { 68 SELECT json_replace('{"a":2,"c":4}', '$.e', 99); 69} {{{"a":2,"c":4}}} 70do_execsql_test json102-260 { 71 SELECT json_set('{"a":2,"c":4}', '$.a', 99); 72} {{{"a":99,"c":4}}} 73do_execsql_test json102-270 { 74 SELECT json_set('{"a":2,"c":4}', '$.e', 99); 75} {{{"a":2,"c":4,"e":99}}} 76do_execsql_test json102-280 { 77 SELECT json_object('a',2,'c',4); 78} {{{"a":2,"c":4}}} 79do_execsql_test json102-290 { 80 SELECT json_object('a',2,'c','{e:5}'); 81} {{{"a":2,"c":"{e:5}"}}} 82do_execsql_test json102-300 { 83 SELECT json_remove('[0,1,2,3,4]','$[2]'); 84} {{[0,1,3,4]}} 85do_execsql_test json102-310 { 86 SELECT json_remove('[0,1,2,3,4]','$[2]','$[0]'); 87} {{[1,3,4]}} 88do_execsql_test json102-320 { 89 SELECT json_remove('[0,1,2,3,4]','$[0]','$[2]'); 90} {{[1,2,4]}} 91do_execsql_test json102-330 { 92 SELECT json_remove('{"x":25,"y":42}'); 93} {{{"x":25,"y":42}}} 94do_execsql_test json102-340 { 95 SELECT json_remove('{"x":25,"y":42}','$.z'); 96} {{{"x":25,"y":42}}} 97do_execsql_test json102-350 { 98 SELECT json_remove('{"x":25,"y":42}','$.y'); 99} {{{"x":25}}} 100do_execsql_test json102-360 { 101 SELECT json_remove('{"x":25,"y":42}','$'); 102} {{}} 103do_execsql_test json102-370 { 104 SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}'); 105} {{object}} 106do_execsql_test json102-380 { 107 SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}','$'); 108} {{object}} 109do_execsql_test json102-390 { 110 SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a'); 111} {{array}} 112do_execsql_test json102-400 { 113 SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[0]'); 114} {{integer}} 115do_execsql_test json102-410 { 116 SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[1]'); 117} {{real}} 118do_execsql_test json102-420 { 119 SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[2]'); 120} {{true}} 121do_execsql_test json102-430 { 122 SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[3]'); 123} {{false}} 124do_execsql_test json102-440 { 125 SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[4]'); 126} {{null}} 127do_execsql_test json102-450 { 128 SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[5]'); 129} {{text}} 130do_execsql_test json102-460 { 131 SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[6]'); 132} {{}} 133do_execsql_test json102-470 { 134 SELECT json_valid('{"x":35}'); 135} {{1}} 136do_execsql_test json102-480 { 137 SELECT json_valid('{"x":35'); -- } 138} {{0}} 139 140do_execsql_test json102-500 { 141 CREATE TABLE user(name,phone); 142 INSERT INTO user(name,phone) VALUES 143 ('Alice','["919-555-2345","804-555-3621"]'), 144 ('Bob','["201-555-8872"]'), 145 ('Cindy','["704-555-9983"]'), 146 ('Dave','["336-555-8421","704-555-4321","803-911-4421"]'); 147 SELECT DISTINCT user.name 148 FROM user, json_each(user.phone) 149 WHERE json_each.value LIKE '704-%' 150 ORDER BY 1; 151} {Cindy Dave} 152 153do_execsql_test json102-510 { 154 UPDATE user 155 SET phone=json_extract(phone,'$[0]') 156 WHERE json_array_length(phone)<2; 157 SELECT name, substr(phone,1,5) FROM user ORDER BY name; 158} {Alice {["919} Bob 201-5 Cindy 704-5 Dave {["336}} 159do_execsql_test json102-511 { 160 SELECT name FROM user WHERE phone LIKE '704-%' 161 UNION 162 SELECT user.name 163 FROM user, json_each(user.phone) 164 WHERE json_valid(user.phone) 165 AND json_each.value LIKE '704-%'; 166} {Cindy Dave} 167 168do_execsql_test json102-600 { 169 CREATE TABLE big(json JSON); 170 INSERT INTO big(json) VALUES('{ 171 "id":123, 172 "stuff":[1,2,3,4], 173 "partlist":[ 174 {"uuid":"bb108722-572e-11e5-9320-7f3b63a4ca74"}, 175 {"uuid":"c690dc14-572e-11e5-95f9-dfc8861fd535"}, 176 {"subassembly":[ 177 {"uuid":"6fa5181e-5721-11e5-a04e-57f3d7b32808"} 178 ]} 179 ] 180 }'); 181 INSERT INTO big(json) VALUES('{ 182 "id":456, 183 "stuff":["hello","world","xyzzy"], 184 "partlist":[ 185 {"uuid":false}, 186 {"uuid":"c690dc14-572e-11e5-95f9-dfc8861fd535"} 187 ] 188 }'); 189} {} 190set correct_answer [list \ 191 1 {$.id} 123 \ 192 1 {$.stuff[0]} 1 \ 193 1 {$.stuff[1]} 2 \ 194 1 {$.stuff[2]} 3 \ 195 1 {$.stuff[3]} 4 \ 196 1 {$.partlist[0].uuid} bb108722-572e-11e5-9320-7f3b63a4ca74 \ 197 1 {$.partlist[1].uuid} c690dc14-572e-11e5-95f9-dfc8861fd535 \ 198 1 {$.partlist[2].subassembly[0].uuid} 6fa5181e-5721-11e5-a04e-57f3d7b32808 \ 199 2 {$.id} 456 \ 200 2 {$.stuff[0]} hello \ 201 2 {$.stuff[1]} world \ 202 2 {$.stuff[2]} xyzzy \ 203 2 {$.partlist[0].uuid} 0 \ 204 2 {$.partlist[1].uuid} c690dc14-572e-11e5-95f9-dfc8861fd535] 205do_execsql_test json102-610 { 206 SELECT big.rowid, fullkey, value 207 FROM big, json_tree(big.json) 208 WHERE json_tree.type NOT IN ('object','array') 209 ORDER BY +big.rowid, +json_tree.id 210} $correct_answer 211do_execsql_test json102-620 { 212 SELECT big.rowid, fullkey, atom 213 FROM big, json_tree(big.json) 214 WHERE atom IS NOT NULL 215 ORDER BY +big.rowid, +json_tree.id 216} $correct_answer 217 218do_execsql_test json102-630 { 219 SELECT DISTINCT json_extract(big.json,'$.id') 220 FROM big, json_tree(big.json,'$.partlist') 221 WHERE json_tree.key='uuid' 222 AND json_tree.value='6fa5181e-5721-11e5-a04e-57f3d7b32808'; 223} {123} 224do_execsql_test json102-631 { 225 SELECT DISTINCT json_extract(big.json,'$.id') 226 FROM big, json_tree(big.json,'$') 227 WHERE json_tree.key='uuid' 228 AND json_tree.value='6fa5181e-5721-11e5-a04e-57f3d7b32808'; 229} {123} 230do_execsql_test json102-632 { 231 SELECT DISTINCT json_extract(big.json,'$.id') 232 FROM big, json_tree(big.json) 233 WHERE json_tree.key='uuid' 234 AND json_tree.value='6fa5181e-5721-11e5-a04e-57f3d7b32808'; 235} {123} 236 237 238finish_test 239