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