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 18do_execsql_test json101-1.1.00 { 19 SELECT json_array(1,2.5,null,'hello'); 20} {[1,2.5,null,"hello"]} 21do_execsql_test json101-1.1.01 { 22 SELECT json_array(1,'{"abc":2.5,"def":null,"ghi":hello}',99); 23 -- the second term goes in as a string: 24} {[1,"{\\"abc\\":2.5,\\"def\\":null,\\"ghi\\":hello}",99]} 25do_execsql_test json101-1.1.02 { 26 SELECT json_array(1,json('{"abc":2.5,"def":null,"ghi":"hello"}'),99); 27 -- the second term goes in as JSON 28} {[1,{"abc":2.5,"def":null,"ghi":"hello"},99]} 29do_execsql_test json101-1.1.03 { 30 SELECT json_array(1,json_object('abc',2.5,'def',null,'ghi','hello'),99); 31 -- the second term goes in as JSON 32} {[1,{"abc":2.5,"def":null,"ghi":"hello"},99]} 33do_execsql_test json101-1.2 { 34 SELECT hex(json_array('String "\ Test')); 35} {5B22537472696E67205C225C5C2054657374225D} 36do_catchsql_test json101-1.3 { 37 SELECT json_array(1,printf('%.1000c','x'),x'abcd',3); 38} {1 {JSON cannot hold BLOB values}} 39do_execsql_test json101-1.4 { 40 SELECT json_array(-9223372036854775808,9223372036854775807,0,1,-1, 41 0.0, 1.0, -1.0, -1e99, +2e100, 42 'one','two','three', 43 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 44 19, NULL, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 45 'abcdefghijklmnopqrstuvwyxzABCDEFGHIJKLMNOPQRSTUVWXYZ', 46 'abcdefghijklmnopqrstuvwyxzABCDEFGHIJKLMNOPQRSTUVWXYZ', 47 'abcdefghijklmnopqrstuvwyxzABCDEFGHIJKLMNOPQRSTUVWXYZ', 48 99); 49} {[-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]} 50 51do_execsql_test json101-2.1 { 52 SELECT json_object('a',1,'b',2.5,'c',null,'d','String Test'); 53} {{{"a":1,"b":2.5,"c":null,"d":"String Test"}}} 54do_catchsql_test json101-2.2 { 55 SELECT json_object('a',printf('%.1000c','x'),2,2.5); 56} {1 {json_object() labels must be TEXT}} 57do_catchsql_test json101-2.3 { 58 SELECT json_object('a',1,'b'); 59} {1 {json_object() requires an even number of arguments}} 60do_catchsql_test json101-2.4 { 61 SELECT json_object('a',printf('%.1000c','x'),'b',x'abcd'); 62} {1 {JSON cannot hold BLOB values}} 63 64do_execsql_test json101-3.1 { 65 SELECT json_replace('{"a":1,"b":2}','$.a','[3,4,5]'); 66} {{{"a":"[3,4,5]","b":2}}} 67do_execsql_test json101-3.2 { 68 SELECT json_replace('{"a":1,"b":2}','$.a',json('[3,4,5]')); 69} {{{"a":[3,4,5],"b":2}}} 70do_execsql_test json101-3.3 { 71 SELECT json_type(json_set('{"a":1,"b":2}','$.b','{"x":3,"y":4}'),'$.b'); 72} {text} 73do_execsql_test json101-3.4 { 74 SELECT json_type(json_set('{"a":1,"b":2}','$.b',json('{"x":3,"y":4}')),'$.b'); 75} {object} 76ifcapable vtab { 77do_execsql_test json101-3.5 { 78 SELECT fullkey, atom, '|' FROM json_tree(json_set('{}','$.x',123,'$.x',456)); 79} {{$} {} | {$.x} 456 |} 80} 81 82# Per rfc7159, any JSON value is allowed at the top level, and whitespace 83# is permitting before and/or after that value. 84# 85do_execsql_test json101-4.1 { 86 CREATE TABLE j1(x); 87 INSERT INTO j1(x) 88 VALUES('true'),('false'),('null'),('123'),('-234'),('34.5e+6'), 89 ('""'),('"\""'),('"\\"'),('"abcdefghijlmnopqrstuvwxyz"'), 90 ('[]'),('{}'),('[true,false,null,123,-234,34.5e+6,{},[]]'), 91 ('{"a":true,"b":{"c":false}}'); 92 SELECT * FROM j1 WHERE NOT json_valid(x); 93} {} 94do_execsql_test json101-4.2 { 95 SELECT * FROM j1 WHERE NOT json_valid(char(0x20,0x09,0x0a,0x0d)||x); 96} {} 97do_execsql_test json101-4.3 { 98 SELECT * FROM j1 WHERE NOT json_valid(x||char(0x20,0x09,0x0a,0x0d)); 99} {} 100 101# But an empty string, or a string of pure whitespace is not valid JSON. 102# 103do_execsql_test json101-4.4 { 104 SELECT json_valid(''), json_valid(char(0x20,0x09,0x0a,0x0d)); 105} {0 0} 106 107# json_remove() and similar functions with no edit operations return their 108# input unchanged. 109# 110do_execsql_test json101-4.5 { 111 SELECT x FROM j1 WHERE json_remove(x)<>x; 112} {} 113do_execsql_test json101-4.6 { 114 SELECT x FROM j1 WHERE json_replace(x)<>x; 115} {} 116do_execsql_test json101-4.7 { 117 SELECT x FROM j1 WHERE json_set(x)<>x; 118} {} 119do_execsql_test json101-4.8 { 120 SELECT x FROM j1 WHERE json_insert(x)<>x; 121} {} 122 123# json_extract(JSON,'$') will return objects and arrays without change. 124# 125do_execsql_test json-4.10 { 126 SELECT count(*) FROM j1 WHERE json_type(x) IN ('object','array'); 127 SELECT x FROM j1 128 WHERE json_extract(x,'$')<>x 129 AND json_type(x) IN ('object','array'); 130} {4} 131 132do_execsql_test json-5.1 { 133 CREATE TABLE j2(id INTEGER PRIMARY KEY, json, src); 134 INSERT INTO j2(id,json,src) 135 VALUES(1,'{ 136 "firstName": "John", 137 "lastName": "Smith", 138 "isAlive": true, 139 "age": 25, 140 "address": { 141 "streetAddress": "21 2nd Street", 142 "city": "New York", 143 "state": "NY", 144 "postalCode": "10021-3100" 145 }, 146 "phoneNumbers": [ 147 { 148 "type": "home", 149 "number": "212 555-1234" 150 }, 151 { 152 "type": "office", 153 "number": "646 555-4567" 154 } 155 ], 156 "children": [], 157 "spouse": null 158 }','https://en.wikipedia.org/wiki/JSON'); 159 INSERT INTO j2(id,json,src) 160 VALUES(2, '{ 161 "id": "0001", 162 "type": "donut", 163 "name": "Cake", 164 "ppu": 0.55, 165 "batters": 166 { 167 "batter": 168 [ 169 { "id": "1001", "type": "Regular" }, 170 { "id": "1002", "type": "Chocolate" }, 171 { "id": "1003", "type": "Blueberry" }, 172 { "id": "1004", "type": "Devil''s Food" } 173 ] 174 }, 175 "topping": 176 [ 177 { "id": "5001", "type": "None" }, 178 { "id": "5002", "type": "Glazed" }, 179 { "id": "5005", "type": "Sugar" }, 180 { "id": "5007", "type": "Powdered Sugar" }, 181 { "id": "5006", "type": "Chocolate with Sprinkles" }, 182 { "id": "5003", "type": "Chocolate" }, 183 { "id": "5004", "type": "Maple" } 184 ] 185 }','https://adobe.github.io/Spry/samples/data_region/JSONDataSetSample.html'); 186 INSERT INTO j2(id,json,src) 187 VALUES(3,'[ 188 { 189 "id": "0001", 190 "type": "donut", 191 "name": "Cake", 192 "ppu": 0.55, 193 "batters": 194 { 195 "batter": 196 [ 197 { "id": "1001", "type": "Regular" }, 198 { "id": "1002", "type": "Chocolate" }, 199 { "id": "1003", "type": "Blueberry" }, 200 { "id": "1004", "type": "Devil''s Food" } 201 ] 202 }, 203 "topping": 204 [ 205 { "id": "5001", "type": "None" }, 206 { "id": "5002", "type": "Glazed" }, 207 { "id": "5005", "type": "Sugar" }, 208 { "id": "5007", "type": "Powdered Sugar" }, 209 { "id": "5006", "type": "Chocolate with Sprinkles" }, 210 { "id": "5003", "type": "Chocolate" }, 211 { "id": "5004", "type": "Maple" } 212 ] 213 }, 214 { 215 "id": "0002", 216 "type": "donut", 217 "name": "Raised", 218 "ppu": 0.55, 219 "batters": 220 { 221 "batter": 222 [ 223 { "id": "1001", "type": "Regular" } 224 ] 225 }, 226 "topping": 227 [ 228 { "id": "5001", "type": "None" }, 229 { "id": "5002", "type": "Glazed" }, 230 { "id": "5005", "type": "Sugar" }, 231 { "id": "5003", "type": "Chocolate" }, 232 { "id": "5004", "type": "Maple" } 233 ] 234 }, 235 { 236 "id": "0003", 237 "type": "donut", 238 "name": "Old Fashioned", 239 "ppu": 0.55, 240 "batters": 241 { 242 "batter": 243 [ 244 { "id": "1001", "type": "Regular" }, 245 { "id": "1002", "type": "Chocolate" } 246 ] 247 }, 248 "topping": 249 [ 250 { "id": "5001", "type": "None" }, 251 { "id": "5002", "type": "Glazed" }, 252 { "id": "5003", "type": "Chocolate" }, 253 { "id": "5004", "type": "Maple" } 254 ] 255 } 256 ]','https://adobe.github.io/Spry/samples/data_region/JSONDataSetSample.html'); 257 SELECT count(*) FROM j2; 258} {3} 259 260do_execsql_test json-5.2 { 261 SELECT id, json_valid(json), json_type(json), '|' FROM j2 ORDER BY id; 262} {1 1 object | 2 1 object | 3 1 array |} 263 264ifcapable !vtab { 265 finish_test 266 return 267} 268 269# fullkey is always the same as path+key (with appropriate formatting) 270# 271do_execsql_test json-5.3 { 272 SELECT j2.rowid, jx.rowid, fullkey, path, key 273 FROM j2, json_tree(j2.json) AS jx 274 WHERE fullkey!=(path || CASE WHEN typeof(key)=='integer' THEN '['||key||']' 275 ELSE '.'||key END); 276} {} 277do_execsql_test json-5.4 { 278 SELECT j2.rowid, jx.rowid, fullkey, path, key 279 FROM j2, json_each(j2.json) AS jx 280 WHERE fullkey!=(path || CASE WHEN typeof(key)=='integer' THEN '['||key||']' 281 ELSE '.'||key END); 282} {} 283 284 285# Verify that the json_each.json and json_tree.json output is always the 286# same as input. 287# 288do_execsql_test json-5.5 { 289 SELECT j2.rowid, jx.rowid, fullkey, path, key 290 FROM j2, json_each(j2.json) AS jx 291 WHERE jx.json<>j2.json; 292} {} 293do_execsql_test json-5.6 { 294 SELECT j2.rowid, jx.rowid, fullkey, path, key 295 FROM j2, json_tree(j2.json) AS jx 296 WHERE jx.json<>j2.json; 297} {} 298do_execsql_test json-5.7 { 299 SELECT j2.rowid, jx.rowid, fullkey, path, key 300 FROM j2, json_each(j2.json) AS jx 301 WHERE jx.value<>jx.atom AND type NOT IN ('array','object'); 302} {} 303do_execsql_test json-5.8 { 304 SELECT j2.rowid, jx.rowid, fullkey, path, key 305 FROM j2, json_tree(j2.json) AS jx 306 WHERE jx.value<>jx.atom AND type NOT IN ('array','object'); 307} {} 308 309do_execsql_test json-6.1 { 310 SELECT json_valid('{"a":55,"b":72,}'); 311} {0} 312do_execsql_test json-6.2 { 313 SELECT json_valid('{"a":55,"b":72}'); 314} {1} 315do_execsql_test json-6.3 { 316 SELECT json_valid('["a",55,"b",72,]'); 317} {0} 318do_execsql_test json-6.4 { 319 SELECT json_valid('["a",55,"b",72]'); 320} {1} 321 322# White-space tests. Note that form-feed is not white-space in JSON. 323# ticket [57eec374ae1d0a1d4a23077a95f4e173fe269113] 324# 325foreach {tn isvalid ws} { 326 7.1 1 char(0x20) 327 7.2 1 char(0x09) 328 7.3 1 char(0x0A) 329 7.4 1 char(0x0D) 330 7.5 0 char(0x0C) 331 7.6 1 char(0x20,0x09,0x0a,0x0d,0x20) 332 7.7 0 char(0x20,0x09,0x0a,0x0c,0x0d,0x20) 333} { 334 do_execsql_test json-$tn.1 \ 335 "SELECT json_valid(printf('%s{%s\"x\"%s:%s9%s}%s', 336 $::ws,$::ws,$::ws,$::ws,$::ws,$::ws));" \ 337 $isvalid 338} 339 340# Ticket https://www.sqlite.org/src/info/ad2559db380abf8e 341# Control characters must be escaped in JSON strings. 342# 343do_execsql_test json-8.1 { 344 DROP TABLE IF EXISTS t8; 345 CREATE TABLE t8(a,b); 346 INSERT INTO t8(a) VALUES('abc' || char(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35) || 'xyz'); 347 UPDATE t8 SET b=json_array(a); 348 SELECT b FROM t8; 349} {{["abc\u0001\u0002\u0003\u0004\u0005\u0006\u0007\b\t\n\u000b\f\r\u000e\u000f\u0010\u0011\u0012\u0013\u0014\u0015\u0016\u0017\u0018\u0019\u001a\u001b\u001c\u001d\u001e\u001f !\"#xyz"]}} 350do_execsql_test json-8.2 { 351 SELECT a=json_extract(b,'$[0]') FROM t8; 352} {1} 353 354# 2017-04-12. Regression reported on the mailing list by Rolf Ade 355# 356do_execsql_test json-8.3 { 357 SELECT json_valid(char(0x22,0xe4,0x22)); 358} {1} 359do_execsql_test json-8.4 { 360 SELECT unicode(json_extract(char(0x22,228,0x22),'$')); 361} {228} 362 363# The json_quote() function transforms an SQL value into a JSON value. 364# String values are quoted and interior quotes are escaped. NULL values 365# are rendered as the unquoted string "null". 366# 367do_execsql_test json-9.1 { 368 SELECT json_quote('abc"xyz'); 369} {{"abc\"xyz"}} 370do_execsql_test json-9.2 { 371 SELECT json_quote(3.14159); 372} {3.14159} 373do_execsql_test json-9.3 { 374 SELECT json_quote(12345); 375} {12345} 376do_execsql_test json-9.4 { 377 SELECT json_quote(null); 378} {"null"} 379do_catchsql_test json-9.5 { 380 SELECT json_quote(x'30313233'); 381} {1 {JSON cannot hold BLOB values}} 382do_catchsql_test json-9.6 { 383 SELECT json_quote(123,456) 384} {1 {wrong number of arguments to function json_quote()}} 385do_catchsql_test json-9.7 { 386 SELECT json_quote() 387} {1 {wrong number of arguments to function json_quote()}} 388 389# Make sure only valid backslash-escapes are accepted. 390# 391do_execsql_test json-10.1 { 392 SELECT json_valid('" \ "'); 393} {0} 394do_execsql_test json-10.2 { 395 SELECT json_valid('" \! "'); 396} {0} 397do_execsql_test json-10.3 { 398 SELECT json_valid('" \" "'); 399} {1} 400do_execsql_test json-10.4 { 401 SELECT json_valid('" \# "'); 402} {0} 403do_execsql_test json-10.5 { 404 SELECT json_valid('" \$ "'); 405} {0} 406do_execsql_test json-10.6 { 407 SELECT json_valid('" \% "'); 408} {0} 409do_execsql_test json-10.7 { 410 SELECT json_valid('" \& "'); 411} {0} 412do_execsql_test json-10.8 { 413 SELECT json_valid('" \'' "'); 414} {0} 415do_execsql_test json-10.9 { 416 SELECT json_valid('" \( "'); 417} {0} 418do_execsql_test json-10.10 { 419 SELECT json_valid('" \) "'); 420} {0} 421do_execsql_test json-10.11 { 422 SELECT json_valid('" \* "'); 423} {0} 424do_execsql_test json-10.12 { 425 SELECT json_valid('" \+ "'); 426} {0} 427do_execsql_test json-10.13 { 428 SELECT json_valid('" \, "'); 429} {0} 430do_execsql_test json-10.14 { 431 SELECT json_valid('" \- "'); 432} {0} 433do_execsql_test json-10.15 { 434 SELECT json_valid('" \. "'); 435} {0} 436do_execsql_test json-10.16 { 437 SELECT json_valid('" \/ "'); 438} {1} 439do_execsql_test json-10.17 { 440 SELECT json_valid('" \0 "'); 441} {0} 442do_execsql_test json-10.18 { 443 SELECT json_valid('" \1 "'); 444} {0} 445do_execsql_test json-10.19 { 446 SELECT json_valid('" \2 "'); 447} {0} 448do_execsql_test json-10.20 { 449 SELECT json_valid('" \3 "'); 450} {0} 451do_execsql_test json-10.21 { 452 SELECT json_valid('" \4 "'); 453} {0} 454do_execsql_test json-10.22 { 455 SELECT json_valid('" \5 "'); 456} {0} 457do_execsql_test json-10.23 { 458 SELECT json_valid('" \6 "'); 459} {0} 460do_execsql_test json-10.24 { 461 SELECT json_valid('" \7 "'); 462} {0} 463do_execsql_test json-10.25 { 464 SELECT json_valid('" \8 "'); 465} {0} 466do_execsql_test json-10.26 { 467 SELECT json_valid('" \9 "'); 468} {0} 469do_execsql_test json-10.27 { 470 SELECT json_valid('" \: "'); 471} {0} 472do_execsql_test json-10.28 { 473 SELECT json_valid('" \; "'); 474} {0} 475do_execsql_test json-10.29 { 476 SELECT json_valid('" \< "'); 477} {0} 478do_execsql_test json-10.30 { 479 SELECT json_valid('" \= "'); 480} {0} 481do_execsql_test json-10.31 { 482 SELECT json_valid('" \> "'); 483} {0} 484do_execsql_test json-10.32 { 485 SELECT json_valid('" \? "'); 486} {0} 487do_execsql_test json-10.33 { 488 SELECT json_valid('" \@ "'); 489} {0} 490do_execsql_test json-10.34 { 491 SELECT json_valid('" \A "'); 492} {0} 493do_execsql_test json-10.35 { 494 SELECT json_valid('" \B "'); 495} {0} 496do_execsql_test json-10.36 { 497 SELECT json_valid('" \C "'); 498} {0} 499do_execsql_test json-10.37 { 500 SELECT json_valid('" \D "'); 501} {0} 502do_execsql_test json-10.38 { 503 SELECT json_valid('" \E "'); 504} {0} 505do_execsql_test json-10.39 { 506 SELECT json_valid('" \F "'); 507} {0} 508do_execsql_test json-10.40 { 509 SELECT json_valid('" \G "'); 510} {0} 511do_execsql_test json-10.41 { 512 SELECT json_valid('" \H "'); 513} {0} 514do_execsql_test json-10.42 { 515 SELECT json_valid('" \I "'); 516} {0} 517do_execsql_test json-10.43 { 518 SELECT json_valid('" \J "'); 519} {0} 520do_execsql_test json-10.44 { 521 SELECT json_valid('" \K "'); 522} {0} 523do_execsql_test json-10.45 { 524 SELECT json_valid('" \L "'); 525} {0} 526do_execsql_test json-10.46 { 527 SELECT json_valid('" \M "'); 528} {0} 529do_execsql_test json-10.47 { 530 SELECT json_valid('" \N "'); 531} {0} 532do_execsql_test json-10.48 { 533 SELECT json_valid('" \O "'); 534} {0} 535do_execsql_test json-10.49 { 536 SELECT json_valid('" \P "'); 537} {0} 538do_execsql_test json-10.50 { 539 SELECT json_valid('" \Q "'); 540} {0} 541do_execsql_test json-10.51 { 542 SELECT json_valid('" \R "'); 543} {0} 544do_execsql_test json-10.52 { 545 SELECT json_valid('" \S "'); 546} {0} 547do_execsql_test json-10.53 { 548 SELECT json_valid('" \T "'); 549} {0} 550do_execsql_test json-10.54 { 551 SELECT json_valid('" \U "'); 552} {0} 553do_execsql_test json-10.55 { 554 SELECT json_valid('" \V "'); 555} {0} 556do_execsql_test json-10.56 { 557 SELECT json_valid('" \W "'); 558} {0} 559do_execsql_test json-10.57 { 560 SELECT json_valid('" \X "'); 561} {0} 562do_execsql_test json-10.58 { 563 SELECT json_valid('" \Y "'); 564} {0} 565do_execsql_test json-10.59 { 566 SELECT json_valid('" \Z "'); 567} {0} 568do_execsql_test json-10.60 { 569 SELECT json_valid('" \[ "'); 570} {0} 571do_execsql_test json-10.61 { 572 SELECT json_valid('" \\ "'); 573} {1} 574do_execsql_test json-10.62 { 575 SELECT json_valid('" \] "'); 576} {0} 577do_execsql_test json-10.63 { 578 SELECT json_valid('" \^ "'); 579} {0} 580do_execsql_test json-10.64 { 581 SELECT json_valid('" \_ "'); 582} {0} 583do_execsql_test json-10.65 { 584 SELECT json_valid('" \` "'); 585} {0} 586do_execsql_test json-10.66 { 587 SELECT json_valid('" \a "'); 588} {0} 589do_execsql_test json-10.67 { 590 SELECT json_valid('" \b "'); 591} {1} 592do_execsql_test json-10.68 { 593 SELECT json_valid('" \c "'); 594} {0} 595do_execsql_test json-10.69 { 596 SELECT json_valid('" \d "'); 597} {0} 598do_execsql_test json-10.70 { 599 SELECT json_valid('" \e "'); 600} {0} 601do_execsql_test json-10.71 { 602 SELECT json_valid('" \f "'); 603} {1} 604do_execsql_test json-10.72 { 605 SELECT json_valid('" \g "'); 606} {0} 607do_execsql_test json-10.73 { 608 SELECT json_valid('" \h "'); 609} {0} 610do_execsql_test json-10.74 { 611 SELECT json_valid('" \i "'); 612} {0} 613do_execsql_test json-10.75 { 614 SELECT json_valid('" \j "'); 615} {0} 616do_execsql_test json-10.76 { 617 SELECT json_valid('" \k "'); 618} {0} 619do_execsql_test json-10.77 { 620 SELECT json_valid('" \l "'); 621} {0} 622do_execsql_test json-10.78 { 623 SELECT json_valid('" \m "'); 624} {0} 625do_execsql_test json-10.79 { 626 SELECT json_valid('" \n "'); 627} {1} 628do_execsql_test json-10.80 { 629 SELECT json_valid('" \o "'); 630} {0} 631do_execsql_test json-10.81 { 632 SELECT json_valid('" \p "'); 633} {0} 634do_execsql_test json-10.82 { 635 SELECT json_valid('" \q "'); 636} {0} 637do_execsql_test json-10.83 { 638 SELECT json_valid('" \r "'); 639} {1} 640do_execsql_test json-10.84 { 641 SELECT json_valid('" \s "'); 642} {0} 643do_execsql_test json-10.85 { 644 SELECT json_valid('" \t "'); 645} {1} 646do_execsql_test json-10.86.0 { 647 SELECT json_valid('" \u "'); 648} {0} 649do_execsql_test json-10.86.1 { 650 SELECT json_valid('" \ua "'); 651} {0} 652do_execsql_test json-10.86.2 { 653 SELECT json_valid('" \uab "'); 654} {0} 655do_execsql_test json-10.86.3 { 656 SELECT json_valid('" \uabc "'); 657} {0} 658do_execsql_test json-10.86.4 { 659 SELECT json_valid('" \uabcd "'); 660} {1} 661do_execsql_test json-10.86.5 { 662 SELECT json_valid('" \uFEDC "'); 663} {1} 664do_execsql_test json-10.86.6 { 665 SELECT json_valid('" \u1234 "'); 666} {1} 667do_execsql_test json-10.87 { 668 SELECT json_valid('" \v "'); 669} {0} 670do_execsql_test json-10.88 { 671 SELECT json_valid('" \w "'); 672} {0} 673do_execsql_test json-10.89 { 674 SELECT json_valid('" \x "'); 675} {0} 676do_execsql_test json-10.90 { 677 SELECT json_valid('" \y "'); 678} {0} 679do_execsql_test json-10.91 { 680 SELECT json_valid('" \z "'); 681} {0} 682do_execsql_test json-10.92 { 683 SELECT json_valid('" \{ "'); 684} {0} 685do_execsql_test json-10.93 { 686 SELECT json_valid('" \| "'); 687} {0} 688do_execsql_test json-10.94 { 689 SELECT json_valid('" \} "'); 690} {0} 691do_execsql_test json-10.95 { 692 SELECT json_valid('" \~ "'); 693} {0} 694 695#-------------------------------------------------------------------------- 696# 2017-04-11. https://www.sqlite.org/src/info/981329adeef51011 697# Stack overflow on deeply nested JSON. 698# 699# The following tests confirm that deeply nested JSON is considered invalid. 700# 701do_execsql_test json-11.0 { 702 /* Shallow enough to be parsed */ 703 SELECT json_valid(printf('%.2000c0%.2000c','[',']')); 704} {1} 705do_execsql_test json-11.1 { 706 /* Too deep by one */ 707 SELECT json_valid(printf('%.2001c0%.2001c','[',']')); 708} {0} 709do_execsql_test json-11.2 { 710 /* Shallow enough to be parsed { */ 711 SELECT json_valid(replace(printf('%.2000c0%.2000c','[','}'),'[','{"a":')); 712 /* } */ 713} {1} 714do_execsql_test json-11.3 { 715 /* Too deep by one { */ 716 SELECT json_valid(replace(printf('%.2001c0%.2001c','[','}'),'[','{"a":')); 717 /* } */ 718} {0} 719 720# 2017-10-27. Demonstrate the ability to access an element from 721# a json structure even though the element name constains a "." 722# character, by quoting the element name in the path. 723# 724do_execsql_test json-12.100 { 725 CREATE TABLE t12(x); 726 INSERT INTO t12(x) VALUES( 727 '{"settings": 728 {"layer2": 729 {"hapax.legomenon": 730 {"forceDisplay":true, 731 "transliterate":true, 732 "add.footnote":true, 733 "summary.report":true}, 734 "dis.legomenon": 735 {"forceDisplay":true, 736 "transliterate":false, 737 "add.footnote":false, 738 "summary.report":true}, 739 "tris.legomenon": 740 {"forceDisplay":true, 741 "transliterate":false, 742 "add.footnote":false, 743 "summary.report":false} 744 } 745 } 746 }'); 747} {} 748do_execsql_test json-12.110 { 749 SELECT json_remove(x, '$.settings.layer2."dis.legomenon".forceDisplay') 750 FROM t12; 751} {{{"settings":{"layer2":{"hapax.legomenon":{"forceDisplay":true,"transliterate":true,"add.footnote":true,"summary.report":true},"dis.legomenon":{"transliterate":false,"add.footnote":false,"summary.report":true},"tris.legomenon":{"forceDisplay":true,"transliterate":false,"add.footnote":false,"summary.report":false}}}}}} 752do_execsql_test json-12.120 { 753 SELECT json_extract(x, '$.settings.layer2."tris.legomenon"."summary.report"') 754 FROM t12; 755} {0} 756 757# 2018-01-26 758# ticket https://www.sqlite.org/src/tktview/80177f0c226ff54f6ddd41 759# Make sure the query planner knows about the arguments to table-valued functions. 760# 761do_execsql_test json-13.100 { 762 DROP TABLE IF EXISTS t1; 763 DROP TABLE IF EXISTS t2; 764 CREATE TABLE t1(id, json); 765 INSERT INTO t1(id,json) VALUES(1,'{"items":[3,5]}'); 766 CREATE TABLE t2(id, json); 767 INSERT INTO t2(id,json) VALUES(2,'{"value":2}'); 768 INSERT INTO t2(id,json) VALUES(3,'{"value":3}'); 769 INSERT INTO t2(id,json) VALUES(4,'{"value":4}'); 770 INSERT INTO t2(id,json) VALUES(5,'{"value":5}'); 771 INSERT INTO t2(id,json) VALUES(6,'{"value":6}'); 772 SELECT * FROM t1 CROSS JOIN t2 773 WHERE EXISTS(SELECT 1 FROM json_each(t1.json,'$.items') AS Z 774 WHERE Z.value==t2.id); 775} {1 {{"items":[3,5]}} 3 {{"value":3}} 1 {{"items":[3,5]}} 5 {{"value":5}}} 776do_execsql_test json-13.110 { 777 SELECT * FROM t2 CROSS JOIN t1 778 WHERE EXISTS(SELECT 1 FROM json_each(t1.json,'$.items') AS Z 779 WHERE Z.value==t2.id); 780} {3 {{"value":3}} 1 {{"items":[3,5]}} 5 {{"value":5}} 1 {{"items":[3,5]}}} 781 782# 2018-05-16 783# Incorrect fullkey output from json_each() 784# when the input JSON is not an array or object. 785# 786do_execsql_test json-14.100 { 787 SELECT fullkey FROM json_each('123'); 788} {$} 789do_execsql_test json-14.110 { 790 SELECT fullkey FROM json_each('123.56'); 791} {$} 792do_execsql_test json-14.120 { 793 SELECT fullkey FROM json_each('"hello"'); 794} {$} 795do_execsql_test json-14.130 { 796 SELECT fullkey FROM json_each('null'); 797} {$} 798do_execsql_test json-14.140 { 799 SELECT fullkey FROM json_tree('123'); 800} {$} 801do_execsql_test json-14.150 { 802 SELECT fullkey FROM json_tree('123.56'); 803} {$} 804do_execsql_test json-14.160 { 805 SELECT fullkey FROM json_tree('"hello"'); 806} {$} 807do_execsql_test json-14.170 { 808 SELECT fullkey FROM json_tree('null'); 809} {$} 810 811# 2018-12-03 812# Make sure the table-valued functions contained within parentheses 813# work correctly. 814# 815# Bug reported via private email. See TH3 for more information. 816# 817do_execsql_test json-15.100 { 818 SELECT * FROM JSON_EACH('{"a":1, "b":2}'); 819} {a 1 integer 1 2 {} {$.a} {$} b 2 integer 2 4 {} {$.b} {$}} 820do_execsql_test json-15.110 { 821 SELECT xyz.* FROM JSON_EACH('{"a":1, "b":2}') AS xyz; 822} {a 1 integer 1 2 {} {$.a} {$} b 2 integer 2 4 {} {$.b} {$}} 823do_execsql_test json-15.120 { 824 SELECT * FROM (JSON_EACH('{"a":1, "b":2}')); 825} {a 1 integer 1 2 {} {$.a} {$} b 2 integer 2 4 {} {$.b} {$}} 826do_execsql_test json-15.130 { 827 SELECT xyz.* FROM (JSON_EACH('{"a":1, "b":2}')) AS xyz; 828} {a 1 integer 1 2 {} {$.a} {$} b 2 integer 2 4 {} {$.b} {$}} 829 830# 2019-11-10 831# Mailing list bug report on the handling of surrogate pairs 832# in JSON. 833# 834do_execsql_test json-16.10 { 835 SELECT length(json_extract('"abc\uD834\uDD1Exyz"','$')); 836} {7} 837do_execsql_test json-16.20 { 838 SELECT length(json_extract('"\uD834\uDD1E"','$')); 839} {1} 840do_execsql_test json-16.30 { 841 SELECT unicode(json_extract('"\uD834\uDD1E"','$')); 842} {119070} 843 844# 2022-01-30 dbsqlfuzz 4678cf825d27f87c9b8343720121e12cf944b71a 845do_execsql_test json-17.1 { 846 DROP TABLE IF EXISTS t1; 847 DROP TABLE IF EXISTS t2; 848 CREATE TABLE t1(a,b,c); 849 CREATE TABLE t2(d); 850 SELECT * FROM t1 LEFT JOIN t2 ON (SELECT b FROM json_each ORDER BY 1); 851} {} 852 853# 2022-04-04 forum post https://sqlite.org/forum/forumpost/c082aeab43 854do_execsql_test json-18.1 { 855 SELECT json_valid('{"":5}'); 856} {1} 857do_execsql_test json-18.2 { 858 SELECT json_extract('{"":5}', '$.""'); 859} {5} 860do_execsql_test json-18.3 { 861 SELECT json_extract('[3,{"a":4,"":[5,{"hi":6},7]},8]', '$[1].""[1].hi'); 862} {6} 863do_execsql_test json-18.4 { 864 SELECT json_extract('[3,{"a":4,"":[5,{"hi":6},7]},8]', '$[1].""[1]."hi"'); 865} {6} 866do_catchsql_test json-18.5 { 867 SELECT json_extract('{"":8}', '$.'); 868} {1 {JSON path error near ''}} 869 870# 2022-08-29 https://sqlite.org/forum/forumpost/9b9e4716c0d7bbd1 871# This is not a problem specifically with JSON functions. It is 872# a problem with transaction control. But the json() function makes 873# the problem more easily accessible, so it is tested here. 874# 875do_execsql_test json-19.1 { 876 DROP TABLE IF EXISTS t1; 877 CREATE TABLE t1(x); 878} {} 879do_catchsql_test json-19.2 { 880 BEGIN; 881 INSERT INTO t1 VALUES(0), (json('not-valid-json')); 882} {1 {malformed JSON}} 883do_execsql_test json-19.3 { 884 COMMIT; 885 SELECT * FROM t1; 886} {} 887 888finish_test 889