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 15set testdir [file dirname $argv0] 16source $testdir/tester.tcl 17 18load_static_extension db json 19do_execsql_test json101-1.1.00 { 20 SELECT json_array(1,2.5,null,'hello'); 21} {[1,2.5,null,"hello"]} 22do_execsql_test json101-1.1.01 { 23 SELECT json_array(1,'{"abc":2.5,"def":null,"ghi":hello}',99); 24 -- the second term goes in as a string: 25} {[1,"{\\"abc\\":2.5,\\"def\\":null,\\"ghi\\":hello}",99]} 26do_execsql_test json101-1.1.02 { 27 SELECT json_array(1,json('{"abc":2.5,"def":null,"ghi":"hello"}'),99); 28 -- the second term goes in as JSON 29} {[1,{"abc":2.5,"def":null,"ghi":"hello"},99]} 30do_execsql_test json101-1.1.03 { 31 SELECT json_array(1,json_object('abc',2.5,'def',null,'ghi','hello'),99); 32 -- the second term goes in as JSON 33} {[1,{"abc":2.5,"def":null,"ghi":"hello"},99]} 34do_execsql_test json101-1.2 { 35 SELECT hex(json_array('String "\ Test')); 36} {5B22537472696E67205C225C5C2054657374225D} 37do_catchsql_test json101-1.3 { 38 SELECT json_array(1,printf('%.1000c','x'),x'abcd',3); 39} {1 {JSON cannot hold BLOB values}} 40do_execsql_test json101-1.4 { 41 SELECT json_array(-9223372036854775808,9223372036854775807,0,1,-1, 42 0.0, 1.0, -1.0, -1e99, +2e100, 43 'one','two','three', 44 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 45 19, NULL, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 46 'abcdefghijklmnopqrstuvwyxzABCDEFGHIJKLMNOPQRSTUVWXYZ', 47 'abcdefghijklmnopqrstuvwyxzABCDEFGHIJKLMNOPQRSTUVWXYZ', 48 'abcdefghijklmnopqrstuvwyxzABCDEFGHIJKLMNOPQRSTUVWXYZ', 49 99); 50} {[-9223372036854775808,9223372036854775807,0,1,-1,0.0,1.0,-1.0,-1.0e+99,2.0e+100,"one","two","three",4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,null,21,22,23,24,25,26,27,28,29,30,31,"abcdefghijklmnopqrstuvwyxzABCDEFGHIJKLMNOPQRSTUVWXYZ","abcdefghijklmnopqrstuvwyxzABCDEFGHIJKLMNOPQRSTUVWXYZ","abcdefghijklmnopqrstuvwyxzABCDEFGHIJKLMNOPQRSTUVWXYZ",99]} 51 52do_execsql_test json101-2.1 { 53 SELECT json_object('a',1,'b',2.5,'c',null,'d','String Test'); 54} {{{"a":1,"b":2.5,"c":null,"d":"String Test"}}} 55do_catchsql_test json101-2.2 { 56 SELECT json_object('a',printf('%.1000c','x'),2,2.5); 57} {1 {json_object() labels must be TEXT}} 58do_catchsql_test json101-2.3 { 59 SELECT json_object('a',1,'b'); 60} {1 {json_object() requires an even number of arguments}} 61do_catchsql_test json101-2.4 { 62 SELECT json_object('a',printf('%.1000c','x'),'b',x'abcd'); 63} {1 {JSON cannot hold BLOB values}} 64 65do_execsql_test json101-3.1 { 66 SELECT json_replace('{"a":1,"b":2}','$.a','[3,4,5]'); 67} {{{"a":"[3,4,5]","b":2}}} 68do_execsql_test json101-3.2 { 69 SELECT json_replace('{"a":1,"b":2}','$.a',json('[3,4,5]')); 70} {{{"a":[3,4,5],"b":2}}} 71do_execsql_test json101-3.3 { 72 SELECT json_type(json_set('{"a":1,"b":2}','$.b','{"x":3,"y":4}'),'$.b'); 73} {text} 74do_execsql_test json101-3.4 { 75 SELECT json_type(json_set('{"a":1,"b":2}','$.b',json('{"x":3,"y":4}')),'$.b'); 76} {object} 77ifcapable vtab { 78do_execsql_test json101-3.5 { 79 SELECT fullkey, atom, '|' FROM json_tree(json_set('{}','$.x',123,'$.x',456)); 80} {{$} {} | {$.x} 456 |} 81} 82 83# Per rfc7159, any JSON value is allowed at the top level, and whitespace 84# is permitting before and/or after that value. 85# 86do_execsql_test json101-4.1 { 87 CREATE TABLE j1(x); 88 INSERT INTO j1(x) 89 VALUES('true'),('false'),('null'),('123'),('-234'),('34.5e+6'), 90 ('""'),('"\""'),('"\\"'),('"abcdefghijlmnopqrstuvwxyz"'), 91 ('[]'),('{}'),('[true,false,null,123,-234,34.5e+6,{},[]]'), 92 ('{"a":true,"b":{"c":false}}'); 93 SELECT * FROM j1 WHERE NOT json_valid(x); 94} {} 95do_execsql_test json101-4.2 { 96 SELECT * FROM j1 WHERE NOT json_valid(char(0x20,0x09,0x0a,0x0d)||x); 97} {} 98do_execsql_test json101-4.3 { 99 SELECT * FROM j1 WHERE NOT json_valid(x||char(0x20,0x09,0x0a,0x0d)); 100} {} 101 102# But an empty string, or a string of pure whitespace is not valid JSON. 103# 104do_execsql_test json101-4.4 { 105 SELECT json_valid(''), json_valid(char(0x20,0x09,0x0a,0x0d)); 106} {0 0} 107 108# json_remove() and similar functions with no edit operations return their 109# input unchanged. 110# 111do_execsql_test json101-4.5 { 112 SELECT x FROM j1 WHERE json_remove(x)<>x; 113} {} 114do_execsql_test json101-4.6 { 115 SELECT x FROM j1 WHERE json_replace(x)<>x; 116} {} 117do_execsql_test json101-4.7 { 118 SELECT x FROM j1 WHERE json_set(x)<>x; 119} {} 120do_execsql_test json101-4.8 { 121 SELECT x FROM j1 WHERE json_insert(x)<>x; 122} {} 123 124# json_extract(JSON,'$') will return objects and arrays without change. 125# 126do_execsql_test json-4.10 { 127 SELECT count(*) FROM j1 WHERE json_type(x) IN ('object','array'); 128 SELECT x FROM j1 129 WHERE json_extract(x,'$')<>x 130 AND json_type(x) IN ('object','array'); 131} {4} 132 133do_execsql_test json-5.1 { 134 CREATE TABLE j2(id INTEGER PRIMARY KEY, json, src); 135 INSERT INTO j2(id,json,src) 136 VALUES(1,'{ 137 "firstName": "John", 138 "lastName": "Smith", 139 "isAlive": true, 140 "age": 25, 141 "address": { 142 "streetAddress": "21 2nd Street", 143 "city": "New York", 144 "state": "NY", 145 "postalCode": "10021-3100" 146 }, 147 "phoneNumbers": [ 148 { 149 "type": "home", 150 "number": "212 555-1234" 151 }, 152 { 153 "type": "office", 154 "number": "646 555-4567" 155 } 156 ], 157 "children": [], 158 "spouse": null 159 }','https://en.wikipedia.org/wiki/JSON'); 160 INSERT INTO j2(id,json,src) 161 VALUES(2, '{ 162 "id": "0001", 163 "type": "donut", 164 "name": "Cake", 165 "ppu": 0.55, 166 "batters": 167 { 168 "batter": 169 [ 170 { "id": "1001", "type": "Regular" }, 171 { "id": "1002", "type": "Chocolate" }, 172 { "id": "1003", "type": "Blueberry" }, 173 { "id": "1004", "type": "Devil''s Food" } 174 ] 175 }, 176 "topping": 177 [ 178 { "id": "5001", "type": "None" }, 179 { "id": "5002", "type": "Glazed" }, 180 { "id": "5005", "type": "Sugar" }, 181 { "id": "5007", "type": "Powdered Sugar" }, 182 { "id": "5006", "type": "Chocolate with Sprinkles" }, 183 { "id": "5003", "type": "Chocolate" }, 184 { "id": "5004", "type": "Maple" } 185 ] 186 }','https://adobe.github.io/Spry/samples/data_region/JSONDataSetSample.html'); 187 INSERT INTO j2(id,json,src) 188 VALUES(3,'[ 189 { 190 "id": "0001", 191 "type": "donut", 192 "name": "Cake", 193 "ppu": 0.55, 194 "batters": 195 { 196 "batter": 197 [ 198 { "id": "1001", "type": "Regular" }, 199 { "id": "1002", "type": "Chocolate" }, 200 { "id": "1003", "type": "Blueberry" }, 201 { "id": "1004", "type": "Devil''s Food" } 202 ] 203 }, 204 "topping": 205 [ 206 { "id": "5001", "type": "None" }, 207 { "id": "5002", "type": "Glazed" }, 208 { "id": "5005", "type": "Sugar" }, 209 { "id": "5007", "type": "Powdered Sugar" }, 210 { "id": "5006", "type": "Chocolate with Sprinkles" }, 211 { "id": "5003", "type": "Chocolate" }, 212 { "id": "5004", "type": "Maple" } 213 ] 214 }, 215 { 216 "id": "0002", 217 "type": "donut", 218 "name": "Raised", 219 "ppu": 0.55, 220 "batters": 221 { 222 "batter": 223 [ 224 { "id": "1001", "type": "Regular" } 225 ] 226 }, 227 "topping": 228 [ 229 { "id": "5001", "type": "None" }, 230 { "id": "5002", "type": "Glazed" }, 231 { "id": "5005", "type": "Sugar" }, 232 { "id": "5003", "type": "Chocolate" }, 233 { "id": "5004", "type": "Maple" } 234 ] 235 }, 236 { 237 "id": "0003", 238 "type": "donut", 239 "name": "Old Fashioned", 240 "ppu": 0.55, 241 "batters": 242 { 243 "batter": 244 [ 245 { "id": "1001", "type": "Regular" }, 246 { "id": "1002", "type": "Chocolate" } 247 ] 248 }, 249 "topping": 250 [ 251 { "id": "5001", "type": "None" }, 252 { "id": "5002", "type": "Glazed" }, 253 { "id": "5003", "type": "Chocolate" }, 254 { "id": "5004", "type": "Maple" } 255 ] 256 } 257 ]','https://adobe.github.io/Spry/samples/data_region/JSONDataSetSample.html'); 258 SELECT count(*) FROM j2; 259} {3} 260 261do_execsql_test json-5.2 { 262 SELECT id, json_valid(json), json_type(json), '|' FROM j2 ORDER BY id; 263} {1 1 object | 2 1 object | 3 1 array |} 264 265ifcapable !vtab { 266 finish_test 267 return 268} 269 270# fullkey is always the same as path+key (with appropriate formatting) 271# 272do_execsql_test json-5.3 { 273 SELECT j2.rowid, jx.rowid, fullkey, path, key 274 FROM j2, json_tree(j2.json) AS jx 275 WHERE fullkey!=(path || CASE WHEN typeof(key)=='integer' THEN '['||key||']' 276 ELSE '.'||key END); 277} {} 278do_execsql_test json-5.4 { 279 SELECT j2.rowid, jx.rowid, fullkey, path, key 280 FROM j2, json_each(j2.json) AS jx 281 WHERE fullkey!=(path || CASE WHEN typeof(key)=='integer' THEN '['||key||']' 282 ELSE '.'||key END); 283} {} 284 285 286# Verify that the json_each.json and json_tree.json output is always the 287# same as input. 288# 289do_execsql_test json-5.5 { 290 SELECT j2.rowid, jx.rowid, fullkey, path, key 291 FROM j2, json_each(j2.json) AS jx 292 WHERE jx.json<>j2.json; 293} {} 294do_execsql_test json-5.6 { 295 SELECT j2.rowid, jx.rowid, fullkey, path, key 296 FROM j2, json_tree(j2.json) AS jx 297 WHERE jx.json<>j2.json; 298} {} 299do_execsql_test json-5.7 { 300 SELECT j2.rowid, jx.rowid, fullkey, path, key 301 FROM j2, json_each(j2.json) AS jx 302 WHERE jx.value<>jx.atom AND type NOT IN ('array','object'); 303} {} 304do_execsql_test json-5.8 { 305 SELECT j2.rowid, jx.rowid, fullkey, path, key 306 FROM j2, json_tree(j2.json) AS jx 307 WHERE jx.value<>jx.atom AND type NOT IN ('array','object'); 308} {} 309 310do_execsql_test json-6.1 { 311 SELECT json_valid('{"a":55,"b":72,}'); 312} {0} 313do_execsql_test json-6.2 { 314 SELECT json_valid('{"a":55,"b":72}'); 315} {1} 316do_execsql_test json-6.3 { 317 SELECT json_valid('["a",55,"b",72,]'); 318} {0} 319do_execsql_test json-6.4 { 320 SELECT json_valid('["a",55,"b",72]'); 321} {1} 322 323 324finish_test 325