3 # The author disclaims copyright to this source code. In place of
4 # a legal notice, here is a blessing:
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.
10 #***********************************************************************
11 # This file implements tests for JSON SQL functions extension to the
15 set testdir [file dirname $argv0]
16 source $testdir/tester.tcl
18 do_execsql_test json101-1.1.00 {
19 SELECT json_array(1,2.5,null,'hello');
20 } {[1,2.5,null,"hello"]}
21 do_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]}
25 do_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]}
29 do_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]}
33 do_execsql_test json101-1.2 {
34 SELECT hex(json_array('String "\ Test'));
35 } {5B22537472696E67205C225C5C2054657374225D}
36 do_catchsql_test json101-1.3 {
37 SELECT json_array(1,printf('%.1000c','x'),x'abcd',3);
38 } {1 {JSON cannot hold BLOB values}}
39 do_catchsql_test json101-1.3b {
40 SELECT jsonb_array(1,printf('%.1000c','x'),x'abcd',3);
41 } {1 {JSON cannot hold BLOB values}}
42 do_execsql_test json101-1.4 {
43 SELECT json_array(-9223372036854775808,9223372036854775807,0,1,-1,
44 0.0, 1.0, -1.0, -1e99, +2e100,
46 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18,
47 19, NULL, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31,
48 'abcdefghijklmnopqrstuvwyxzABCDEFGHIJKLMNOPQRSTUVWXYZ',
49 'abcdefghijklmnopqrstuvwyxzABCDEFGHIJKLMNOPQRSTUVWXYZ',
50 'abcdefghijklmnopqrstuvwyxzABCDEFGHIJKLMNOPQRSTUVWXYZ',
52 } {[-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]}
53 do_execsql_test json101-1.4b {
54 SELECT json(jsonb_array(-9223372036854775808,9223372036854775807,0,1,-1,
55 0.0, 1.0, -1.0, -1e99, +2e100,
57 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18,
58 19, NULL, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31,
59 'abcdefghijklmnopqrstuvwyxzABCDEFGHIJKLMNOPQRSTUVWXYZ',
60 'abcdefghijklmnopqrstuvwyxzABCDEFGHIJKLMNOPQRSTUVWXYZ',
61 'abcdefghijklmnopqrstuvwyxzABCDEFGHIJKLMNOPQRSTUVWXYZ',
63 } {[-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]}
65 do_execsql_test json101-2.1 {
66 SELECT json_object('a',1,'b',2.5,'c',null,'d','String Test');
67 } {{{"a":1,"b":2.5,"c":null,"d":"String Test"}}}
68 do_execsql_test json101-2.1b {
69 SELECT json(jsonb_object('a',1,'b',2.5,'c',null,'d','String Test'));
70 } {{{"a":1,"b":2.5,"c":null,"d":"String Test"}}}
71 do_catchsql_test json101-2.2 {
72 SELECT json_object('a',printf('%.1000c','x'),2,2.5);
73 } {1 {json_object() labels must be TEXT}}
74 do_catchsql_test json101-2.2b {
75 SELECT jsonb_object('a',printf('%.1000c','x'),2,2.5);
76 } {1 {json_object() labels must be TEXT}}
77 do_execsql_test json101-2.2.2 {
78 SELECT json_object('a',json_array('xyx',77,4.5),'x',2.5);
79 } {{{"a":["xyx",77,4.5],"x":2.5}}}
80 do_execsql_test json101-2.2.2b {
81 SELECT json(jsonb_object('a',json_array('xyx',77,4.5),'x',2.5));
82 } {{{"a":["xyx",77,4.5],"x":2.5}}}
83 do_execsql_test json101-2.2.3 {
84 SELECT json_object('a',jsonb_array('xyx',77,4.5),'x',2.5);
85 } {{{"a":["xyx",77,4.5],"x":2.5}}}
86 do_execsql_test json101-2.2.3b {
87 SELECT json(jsonb_object('a',jsonb_array('xyx',77,4.5),'x',2.5));
88 } {{{"a":["xyx",77,4.5],"x":2.5}}}
89 do_catchsql_test json101-2.3 {
90 SELECT json_object('a',1,'b');
91 } {1 {json_object() requires an even number of arguments}}
92 do_catchsql_test json101-2.4 {
93 SELECT json_object('a',printf('%.1000c','x'),'b',x'abcd');
94 } {1 {JSON cannot hold BLOB values}}
95 do_execsql_test json101-2.5 {
96 SELECT json_object('a',printf('%.10c','x'),'b',jsonb_array(1,2,3));
97 } {{{"a":"xxxxxxxxxx","b":[1,2,3]}}}
99 do_execsql_test json101-3.1 {
100 SELECT json_replace('{"a":1,"b":2}','$.a','[3,4,5]');
101 } {{{"a":"[3,4,5]","b":2}}}
102 do_execsql_test json101-3.1b {
103 SELECT json(jsonb_replace('{"a":1,"b":2}','$.a','[3,4,5]'));
104 } {{{"a":"[3,4,5]","b":2}}}
105 do_execsql_test json101-3.2 {
106 SELECT json_replace('{"a":1,"b":2}','$.a',json('[3,4,5]'));
107 } {{{"a":[3,4,5],"b":2}}}
108 do_execsql_test json101-3.2b {
109 SELECT json_replace('{"a":1,"b":2}','$.a',jsonb('[3,4,5]'));
110 } {{{"a":[3,4,5],"b":2}}}
111 do_execsql_test json101-3.3 {
112 SELECT json_type(json_set('{"a":1,"b":2}','$.b','{"x":3,"y":4}'),'$.b');
114 do_execsql_test json101-3.3b {
115 SELECT json_type(jsonb_set('{"a":1,"b":2}','$.b','{"x":3,"y":4}'),'$.b');
117 do_execsql_test json101-3.4 {
118 SELECT json_type(json_set('{"a":1,"b":2}','$.b',json('{"x":3,"y":4}')),'$.b');
120 do_execsql_test json101-3.4b {
121 SELECT json_type(jsonb_set('{"a":1,"b":2}','$.b',jsonb('{"x":3,"y":4}')),'$.b');
124 do_execsql_test json101-3.5 {
125 SELECT fullkey, atom, '|' FROM json_tree(json_set('{}','$.x',123,'$.x',456));
126 } {{$} {} | {$.x} 456 |}
127 do_execsql_test json101-3.5b {
128 SELECT fullkey, atom, '|' FROM json_tree(jsonb_set('{}','$.x',123,'$.x',456));
129 } {{$} {} | {$.x} 456 |}
132 # Per rfc7159, any JSON value is allowed at the top level, and whitespace
133 # is permitting before and/or after that value.
135 do_execsql_test json101-4.1 {
138 VALUES('true'),('false'),('null'),('123'),('-234'),('34.5e+6'),
139 ('""'),('"\""'),('"\\"'),('"abcdefghijlmnopqrstuvwxyz"'),
140 ('[]'),('{}'),('[true,false,null,123,-234,34.5e+6,{},[]]'),
141 ('{"a":true,"b":{"c":false}}');
142 SELECT * FROM j1 WHERE NOT json_valid(x);
144 do_execsql_test json101-4.2 {
145 SELECT * FROM j1 WHERE NOT json_valid(char(0x20,0x09,0x0a,0x0d)||x);
147 do_execsql_test json101-4.3 {
148 SELECT * FROM j1 WHERE NOT json_valid(x||char(0x20,0x09,0x0a,0x0d));
151 # But an empty string, or a string of pure whitespace is not valid JSON.
153 do_execsql_test json101-4.4 {
154 SELECT json_valid(''), json_valid(char(0x20,0x09,0x0a,0x0d));
157 # json_remove() and similar functions with no edit operations return their
160 do_execsql_test json101-4.5 {
161 SELECT x FROM j1 WHERE json_remove(x)<>x;
163 do_execsql_test json101-4.6 {
164 SELECT x FROM j1 WHERE json_replace(x)<>x;
166 do_execsql_test json101-4.7 {
167 SELECT x FROM j1 WHERE json_set(x)<>x;
169 do_execsql_test json101-4.8 {
170 SELECT x FROM j1 WHERE json_insert(x)<>x;
172 do_execsql_test json101-4.9 {
173 SELECT json_insert('{"a":1}','$.b',CAST(x'0000' AS text));
174 } {{{"a":1,"b":"\u0000\u0000"}}}
176 # json_extract(JSON,'$') will return objects and arrays without change.
178 do_execsql_test json101-4.10 {
179 SELECT count(*) FROM j1 WHERE json_type(x) IN ('object','array');
181 WHERE json_extract(x,'$')<>x
182 AND json_type(x) IN ('object','array');
184 do_execsql_test json101-4.10b {
185 CREATE TABLE j1b AS SELECT jsonb(x) AS "x" FROM j1;
186 SELECT count(*) FROM j1b WHERE json_type(x) IN ('object','array');
187 SELECT json(x) FROM j1b
188 WHERE json_extract(x,'$')<>json(x)
189 AND json_type(x) IN ('object','array');
192 do_execsql_test json101-5.1 {
193 CREATE TABLE j2(id INTEGER PRIMARY KEY, json, src);
194 INSERT INTO j2(id,json,src)
201 "streetAddress": "21 2nd Street",
204 "postalCode": "10021-3100"
209 "number": "212 555-1234"
213 "number": "646 555-4567"
218 }','https://en.wikipedia.org/wiki/JSON');
219 INSERT INTO j2(id,json,src)
229 { "id": "1001", "type": "Regular" },
230 { "id": "1002", "type": "Chocolate" },
231 { "id": "1003", "type": "Blueberry" },
232 { "id": "1004", "type": "Devil''s Food" }
237 { "id": "5001", "type": "None" },
238 { "id": "5002", "type": "Glazed" },
239 { "id": "5005", "type": "Sugar" },
240 { "id": "5007", "type": "Powdered Sugar" },
241 { "id": "5006", "type": "Chocolate with Sprinkles" },
242 { "id": "5003", "type": "Chocolate" },
243 { "id": "5004", "type": "Maple" }
245 }','https://adobe.github.io/Spry/samples/data_region/JSONDataSetSample.html');
246 INSERT INTO j2(id,json,src)
257 { "id": "1001", "type": "Regular" },
258 { "id": "1002", "type": "Chocolate" },
259 { "id": "1003", "type": "Blueberry" },
260 { "id": "1004", "type": "Devil''s Food" }
265 { "id": "5001", "type": "None" },
266 { "id": "5002", "type": "Glazed" },
267 { "id": "5005", "type": "Sugar" },
268 { "id": "5007", "type": "Powdered Sugar" },
269 { "id": "5006", "type": "Chocolate with Sprinkles" },
270 { "id": "5003", "type": "Chocolate" },
271 { "id": "5004", "type": "Maple" }
283 { "id": "1001", "type": "Regular" }
288 { "id": "5001", "type": "None" },
289 { "id": "5002", "type": "Glazed" },
290 { "id": "5005", "type": "Sugar" },
291 { "id": "5003", "type": "Chocolate" },
292 { "id": "5004", "type": "Maple" }
298 "name": "Old Fashioned",
304 { "id": "1001", "type": "Regular" },
305 { "id": "1002", "type": "Chocolate" }
310 { "id": "5001", "type": "None" },
311 { "id": "5002", "type": "Glazed" },
312 { "id": "5003", "type": "Chocolate" },
313 { "id": "5004", "type": "Maple" }
316 ]','https://adobe.github.io/Spry/samples/data_region/JSONDataSetSample.html');
317 SELECT count(*) FROM j2;
318 CREATE TABLE j2b(id INTEGER PRIMARY KEY, json, src);
319 INSERT INTO J2b(id,json,src) SELECT id, jsonb(json), src FROM j2;
320 SELECT count(*) FROM j2b;
323 do_execsql_test json101-5.2 {
324 SELECT id, json_valid(json), json_type(json), '|' FROM j2 ORDER BY id;
325 } {1 1 object | 2 1 object | 3 1 array |}
326 do_execsql_test json101-5.2b {
327 SELECT id, json_valid(json,5), json_type(json), '|' FROM j2b ORDER BY id;
328 } {1 1 object | 2 1 object | 3 1 array |}
335 # fullkey is always the same as path+key (with appropriate formatting)
337 do_execsql_test json101-5.3 {
338 SELECT j2.rowid, jx.rowid, fullkey, path, key
339 FROM j2, json_tree(j2.json) AS jx
340 WHERE fullkey!=(path || CASE WHEN typeof(key)=='integer' THEN '['||key||']'
343 do_execsql_test json101-5.3b {
344 SELECT j2b.rowid, jx.rowid, fullkey, path, key
345 FROM j2b, json_tree(j2b.json) AS jx
346 WHERE fullkey!=(path || CASE WHEN typeof(key)=='integer' THEN '['||key||']'
349 do_execsql_test json101-5.4 {
350 SELECT j2.rowid, jx.rowid, fullkey, path, key
351 FROM j2, json_each(j2.json) AS jx
352 WHERE fullkey!=(path || CASE WHEN typeof(key)=='integer' THEN '['||key||']'
357 # Verify that the json_each.json and json_tree.json output is always the
360 do_execsql_test json101-5.5 {
361 SELECT j2.rowid, jx.rowid, fullkey, path, key
362 FROM j2, json_each(j2.json) AS jx
363 WHERE jx.json<>j2.json;
365 do_execsql_test json101-5.6 {
366 SELECT j2.rowid, jx.rowid, fullkey, path, key
367 FROM j2, json_tree(j2.json) AS jx
368 WHERE jx.json<>j2.json;
370 do_execsql_test json101-5.7 {
371 SELECT j2.rowid, jx.rowid, fullkey, path, key
372 FROM j2, json_each(j2.json) AS jx
373 WHERE jx.value<>jx.atom AND type NOT IN ('array','object');
375 do_execsql_test json101-5.8 {
376 SELECT j2.rowid, jx.rowid, fullkey, path, key
377 FROM j2, json_tree(j2.json) AS jx
378 WHERE jx.value<>jx.atom AND type NOT IN ('array','object');
381 do_execsql_test json101-6.1 {
382 SELECT json_valid('{"a":55,"b":72,}');
384 do_execsql_test json101-6.2 {
385 SELECT json_error_position('{"a":55,"b":72,}');
387 do_execsql_test json101-6.3 {
388 SELECT json_valid(json('{"a":55,"b":72,}'));
390 do_execsql_test json101-6.4 {
391 SELECT json_valid('{"a":55,"b":72 , }');
393 do_execsql_test json101-6.5 {
394 SELECT json_error_position('{"a":55,"b":72 , }');
396 do_execsql_test json101-6.6 {
397 SELECT json_error_position('{"a":55,"b":72,,}');
399 do_execsql_test json101-6.7 {
400 SELECT json_valid('{"a":55,"b":72}');
402 do_execsql_test json101-6.8 {
403 SELECT json_error_position('["a",55,"b",72,]');
405 do_execsql_test json101-6.9 {
406 SELECT json_error_position('["a",55,"b",72 , ]');
408 do_execsql_test json101-6.10 {
409 SELECT json_error_position('["a",55,"b",72,,]');
411 do_execsql_test json101-6.11 {
412 SELECT json_valid('["a",55,"b",72]');
415 # White-space tests. Note that form-feed is not white-space in JSON.
416 # ticket [57eec374ae1d0a1d4a23077a95f4e173fe269113]
418 foreach {tn isvalid ws} {
424 7.6 1 char(0x20,0x09,0x0a,0x0d,0x20)
425 7.7 0 char(0x20,0x09,0x0a,0x0c,0x0d,0x20)
427 do_execsql_test json101-$tn.1 \
428 "SELECT json_valid(printf('%s{%s\"x\"%s:%s9%s}%s',
429 $::ws,$::ws,$::ws,$::ws,$::ws,$::ws));" \
433 # Ticket https://www.sqlite.org/src/info/ad2559db380abf8e
434 # Control characters must be escaped in JSON strings.
436 do_execsql_test json101-8.1 {
437 DROP TABLE IF EXISTS t8;
438 CREATE TABLE t8(a,b);
439 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');
440 UPDATE t8 SET b=json_array(a);
442 } {{["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"]}}
443 do_execsql_test json101-8.1b {
444 DROP TABLE IF EXISTS t8;
445 CREATE TABLE t8(a,b);
446 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');
447 UPDATE t8 SET b=jsonb_array(a);
448 SELECT json(b) FROM t8;
449 } {{["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"]}}
450 do_execsql_test json101-8.2 {
451 SELECT a=json_extract(b,'$[0]') FROM t8;
454 # 2017-04-12. Regression reported on the mailing list by Rolf Ade
456 do_execsql_test json101-8.3 {
457 SELECT json_valid(char(0x22,0xe4,0x22));
459 do_execsql_test json101-8.4 {
460 SELECT unicode(json_extract(char(0x22,228,0x22),'$'));
463 # The json_quote() function transforms an SQL value into a JSON value.
464 # String values are quoted and interior quotes are escaped. NULL values
465 # are rendered as the unquoted string "null".
467 do_execsql_test json101-9.1 {
468 SELECT json_quote('abc"xyz');
470 do_execsql_test json101-9.2 {
471 SELECT json_quote(3.14159);
473 do_execsql_test json101-9.3 {
474 SELECT json_quote(12345);
476 do_execsql_test json101-9.4 {
477 SELECT json_quote(null);
479 do_catchsql_test json101-9.5 {
480 SELECT json_quote(x'3031323334');
481 } {1 {JSON cannot hold BLOB values}}
482 do_catchsql_test json101-9.6 {
483 SELECT json_quote(123,456)
484 } {1 {wrong number of arguments to function json_quote()}}
485 do_catchsql_test json101-9.7 {
487 } {1 {wrong number of arguments to function json_quote()}}
489 # Make sure only valid backslash-escapes are accepted.
491 do_execsql_test json101-10.1 {
492 SELECT json_valid('" \ "');
494 do_execsql_test json101-10.2 {
495 SELECT json_valid('" \! "');
497 do_execsql_test json101-10.3 {
498 SELECT json_valid('" \" "');
500 do_execsql_test json101-10.4 {
501 SELECT json_valid('" \# "');
503 do_execsql_test json101-10.5 {
504 SELECT json_valid('" \$ "');
506 do_execsql_test json101-10.6 {
507 SELECT json_valid('" \% "');
509 do_execsql_test json101-10.7 {
510 SELECT json_valid('" \& "');
512 do_execsql_test json101-10.8 {
513 SELECT json_valid('" \'' "');
515 do_execsql_test json101-10.9 {
516 SELECT json_valid('" \( "');
518 do_execsql_test json101-10.10 {
519 SELECT json_valid('" \) "');
521 do_execsql_test json101-10.11 {
522 SELECT json_valid('" \* "');
524 do_execsql_test json101-10.12 {
525 SELECT json_valid('" \+ "');
527 do_execsql_test json101-10.13 {
528 SELECT json_valid('" \, "');
530 do_execsql_test json101-10.14 {
531 SELECT json_valid('" \- "');
533 do_execsql_test json101-10.15 {
534 SELECT json_valid('" \. "');
536 do_execsql_test json101-10.16 {
537 SELECT json_valid('" \/ "');
539 do_execsql_test json101-10.17 {
540 SELECT json_valid('" \0 "');
542 do_execsql_test json101-10.18 {
543 SELECT json_valid('" \1 "');
545 do_execsql_test json101-10.19 {
546 SELECT json_valid('" \2 "');
548 do_execsql_test json101-10.20 {
549 SELECT json_valid('" \3 "');
551 do_execsql_test json101-10.21 {
552 SELECT json_valid('" \4 "');
554 do_execsql_test json101-10.22 {
555 SELECT json_valid('" \5 "');
557 do_execsql_test json101-10.23 {
558 SELECT json_valid('" \6 "');
560 do_execsql_test json101-10.24 {
561 SELECT json_valid('" \7 "');
563 do_execsql_test json101-10.25 {
564 SELECT json_valid('" \8 "');
566 do_execsql_test json101-10.26 {
567 SELECT json_valid('" \9 "');
569 do_execsql_test json101-10.27 {
570 SELECT json_valid('" \: "');
572 do_execsql_test json101-10.28 {
573 SELECT json_valid('" \; "');
575 do_execsql_test json101-10.29 {
576 SELECT json_valid('" \< "');
578 do_execsql_test json101-10.30 {
579 SELECT json_valid('" \= "');
581 do_execsql_test json101-10.31 {
582 SELECT json_valid('" \> "');
584 do_execsql_test json101-10.32 {
585 SELECT json_valid('" \? "');
587 do_execsql_test json101-10.33 {
588 SELECT json_valid('" \@ "');
590 do_execsql_test json101-10.34 {
591 SELECT json_valid('" \A "');
593 do_execsql_test json101-10.35 {
594 SELECT json_valid('" \B "');
596 do_execsql_test json101-10.36 {
597 SELECT json_valid('" \C "');
599 do_execsql_test json101-10.37 {
600 SELECT json_valid('" \D "');
602 do_execsql_test json101-10.38 {
603 SELECT json_valid('" \E "');
605 do_execsql_test json101-10.39 {
606 SELECT json_valid('" \F "');
608 do_execsql_test json101-10.40 {
609 SELECT json_valid('" \G "');
611 do_execsql_test json101-10.41 {
612 SELECT json_valid('" \H "');
614 do_execsql_test json101-10.42 {
615 SELECT json_valid('" \I "');
617 do_execsql_test json101-10.43 {
618 SELECT json_valid('" \J "');
620 do_execsql_test json101-10.44 {
621 SELECT json_valid('" \K "');
623 do_execsql_test json101-10.45 {
624 SELECT json_valid('" \L "');
626 do_execsql_test json101-10.46 {
627 SELECT json_valid('" \M "');
629 do_execsql_test json101-10.47 {
630 SELECT json_valid('" \N "');
632 do_execsql_test json101-10.48 {
633 SELECT json_valid('" \O "');
635 do_execsql_test json101-10.49 {
636 SELECT json_valid('" \P "');
638 do_execsql_test json101-10.50 {
639 SELECT json_valid('" \Q "');
641 do_execsql_test json101-10.51 {
642 SELECT json_valid('" \R "');
644 do_execsql_test json101-10.52 {
645 SELECT json_valid('" \S "');
647 do_execsql_test json101-10.53 {
648 SELECT json_valid('" \T "');
650 do_execsql_test json101-10.54 {
651 SELECT json_valid('" \U "');
653 do_execsql_test json101-10.55 {
654 SELECT json_valid('" \V "');
656 do_execsql_test json101-10.56 {
657 SELECT json_valid('" \W "');
659 do_execsql_test json101-10.57 {
660 SELECT json_valid('" \X "');
662 do_execsql_test json101-10.58 {
663 SELECT json_valid('" \Y "');
665 do_execsql_test json101-10.59 {
666 SELECT json_valid('" \Z "');
668 do_execsql_test json101-10.60 {
669 SELECT json_valid('" \[ "');
671 do_execsql_test json101-10.61 {
672 SELECT json_valid('" \\ "');
674 do_execsql_test json101-10.62 {
675 SELECT json_valid('" \] "');
677 do_execsql_test json101-10.63 {
678 SELECT json_valid('" \^ "');
680 do_execsql_test json101-10.64 {
681 SELECT json_valid('" \_ "');
683 do_execsql_test json101-10.65 {
684 SELECT json_valid('" \` "');
686 do_execsql_test json101-10.66 {
687 SELECT json_valid('" \a "');
689 do_execsql_test json101-10.67 {
690 SELECT json_valid('" \b "');
692 do_execsql_test json101-10.68 {
693 SELECT json_valid('" \c "');
695 do_execsql_test json101-10.69 {
696 SELECT json_valid('" \d "');
698 do_execsql_test json101-10.70 {
699 SELECT json_valid('" \e "');
701 do_execsql_test json101-10.71 {
702 SELECT json_valid('" \f "');
704 do_execsql_test json101-10.72 {
705 SELECT json_valid('" \g "');
707 do_execsql_test json101-10.73 {
708 SELECT json_valid('" \h "');
710 do_execsql_test json101-10.74 {
711 SELECT json_valid('" \i "');
713 do_execsql_test json101-10.75 {
714 SELECT json_valid('" \j "');
716 do_execsql_test json101-10.76 {
717 SELECT json_valid('" \k "');
719 do_execsql_test json101-10.77 {
720 SELECT json_valid('" \l "');
722 do_execsql_test json101-10.78 {
723 SELECT json_valid('" \m "');
725 do_execsql_test json101-10.79 {
726 SELECT json_valid('" \n "');
728 do_execsql_test json101-10.80 {
729 SELECT json_valid('" \o "');
731 do_execsql_test json101-10.81 {
732 SELECT json_valid('" \p "');
734 do_execsql_test json101-10.82 {
735 SELECT json_valid('" \q "');
737 do_execsql_test json101-10.83 {
738 SELECT json_valid('" \r "');
740 do_execsql_test json101-10.84 {
741 SELECT json_valid('" \s "');
743 do_execsql_test json101-10.85 {
744 SELECT json_valid('" \t "');
746 do_execsql_test json101-10.86.0 {
747 SELECT json_valid('" \u "');
749 do_execsql_test json101-10.86.1 {
750 SELECT json_valid('" \ua "');
752 do_execsql_test json101-10.86.2 {
753 SELECT json_valid('" \uab "');
755 do_execsql_test json101-10.86.3 {
756 SELECT json_valid('" \uabc "');
758 do_execsql_test json101-10.86.4 {
759 SELECT json_valid('" \uabcd "');
761 do_execsql_test json101-10.86.5 {
762 SELECT json_valid('" \uFEDC "');
764 do_execsql_test json101-10.86.6 {
765 SELECT json_valid('" \u1234 "');
767 do_execsql_test json101-10.87 {
768 SELECT json_valid('" \v "');
770 do_execsql_test json101-10.88 {
771 SELECT json_valid('" \w "');
773 do_execsql_test json101-10.89 {
774 SELECT json_valid('" \x "');
776 do_execsql_test json101-10.90 {
777 SELECT json_valid('" \y "');
779 do_execsql_test json101-10.91 {
780 SELECT json_valid('" \z "');
782 do_execsql_test json101-10.92 {
783 SELECT json_valid('" \{ "');
785 do_execsql_test json101-10.93 {
786 SELECT json_valid('" \| "');
788 do_execsql_test json101-10.94 {
789 SELECT json_valid('" \} "');
791 do_execsql_test json101-10.95 {
792 SELECT json_valid('" \~ "');
795 #--------------------------------------------------------------------------
796 # 2017-04-11. https://www.sqlite.org/src/info/981329adeef51011
797 # Stack overflow on deeply nested JSON.
799 # The following tests confirm that deeply nested JSON is considered invalid.
801 do_execsql_test json101-11.0 {
802 /* Shallow enough to be parsed */
803 SELECT json_valid(printf('%.1000c0%.1000c','[',']'));
805 do_execsql_test json101-11.1 {
806 /* Too deep by one */
807 SELECT json_valid(printf('%.1001c0%.1001c','[',']'));
809 do_execsql_test json101-11.2 {
810 /* Shallow enough to be parsed { */
811 SELECT json_valid(replace(printf('%.1000c0%.1000c','[','}'),'[','{"a":'));
814 do_execsql_test json101-11.3 {
815 /* Too deep by one { */
816 SELECT json_valid(replace(printf('%.1001c0%.1001c','[','}'),'[','{"a":'));
820 # 2017-10-27. Demonstrate the ability to access an element from
821 # a json structure even though the element name constains a "."
822 # character, by quoting the element name in the path.
824 do_execsql_test json101-12.100 {
826 INSERT INTO t12(x) VALUES(
830 {"forceDisplay":true,
831 "transliterate":true,
833 "summary.report":true},
835 {"forceDisplay":true,
836 "transliterate":false,
837 "add.footnote":false,
838 "summary.report":true},
840 {"forceDisplay":true,
841 "transliterate":false,
842 "add.footnote":false,
843 "summary.report":false}
849 do_execsql_test json101-12.110 {
850 SELECT json_remove(x, '$.settings.layer2."dis.legomenon".forceDisplay')
852 } {{{"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}}}}}}
853 do_execsql_test json101-12.110b {
854 SELECT json_remove(jsonb(x), '$.settings.layer2."dis.legomenon".forceDisplay')
856 } {{{"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}}}}}}
857 do_execsql_test json101-12.120 {
858 SELECT json_extract(x, '$.settings.layer2."tris.legomenon"."summary.report"')
861 do_execsql_test json101-12.120b {
862 SELECT json_extract(jsonb(x), '$.settings.layer2."tris.legomenon"."summary.report"')
867 # ticket https://www.sqlite.org/src/tktview/80177f0c226ff54f6ddd41
868 # Make sure the query planner knows about the arguments to table-valued functions.
870 do_execsql_test json101-13.100 {
871 DROP TABLE IF EXISTS t1;
872 DROP TABLE IF EXISTS t2;
873 CREATE TABLE t1(id, json);
874 INSERT INTO t1(id,json) VALUES(1,'{"items":[3,5]}');
875 CREATE TABLE t2(id, json);
876 INSERT INTO t2(id,json) VALUES(2,'{"value":2}');
877 INSERT INTO t2(id,json) VALUES(3,'{"value":3}');
878 INSERT INTO t2(id,json) VALUES(4,'{"value":4}');
879 INSERT INTO t2(id,json) VALUES(5,'{"value":5}');
880 INSERT INTO t2(id,json) VALUES(6,'{"value":6}');
881 SELECT * FROM t1 CROSS JOIN t2
882 WHERE EXISTS(SELECT 1 FROM json_each(t1.json,'$.items') AS Z
883 WHERE Z.value==t2.id);
884 } {1 {{"items":[3,5]}} 3 {{"value":3}} 1 {{"items":[3,5]}} 5 {{"value":5}}}
885 do_execsql_test json101-13.110 {
886 SELECT * FROM t2 CROSS JOIN t1
887 WHERE EXISTS(SELECT 1 FROM json_each(t1.json,'$.items') AS Z
888 WHERE Z.value==t2.id);
889 } {3 {{"value":3}} 1 {{"items":[3,5]}} 5 {{"value":5}} 1 {{"items":[3,5]}}}
892 # Incorrect fullkey output from json_each()
893 # when the input JSON is not an array or object.
895 do_execsql_test json101-14.100 {
896 SELECT fullkey FROM json_each('123');
898 do_execsql_test json101-14.110 {
899 SELECT fullkey FROM json_each('123.56');
901 do_execsql_test json101-14.120 {
902 SELECT fullkey FROM json_each('"hello"');
904 do_execsql_test json101-14.130 {
905 SELECT fullkey FROM json_each('null');
907 do_execsql_test json101-14.140 {
908 SELECT fullkey FROM json_tree('123');
910 do_execsql_test json101-14.150 {
911 SELECT fullkey FROM json_tree('123.56');
913 do_execsql_test json101-14.160 {
914 SELECT fullkey FROM json_tree('"hello"');
916 do_execsql_test json101-14.170 {
917 SELECT fullkey FROM json_tree('null');
921 # Make sure the table-valued functions contained within parentheses
924 # Bug reported via private email. See TH3 for more information.
926 do_execsql_test json101-15.100 {
927 SELECT * FROM JSON_EACH('{"a":1, "b":2}');
928 } {a 1 integer 1 1 {} {$.a} {$} b 2 integer 2 5 {} {$.b} {$}}
929 do_execsql_test json101-15.110 {
930 SELECT xyz.* FROM JSON_EACH('{"a":1, "b":2}') AS xyz;
931 } {a 1 integer 1 1 {} {$.a} {$} b 2 integer 2 5 {} {$.b} {$}}
932 do_execsql_test json101-15.120 {
933 SELECT * FROM (JSON_EACH('{"a":1, "b":2}'));
934 } {a 1 integer 1 1 {} {$.a} {$} b 2 integer 2 5 {} {$.b} {$}}
935 do_execsql_test json101-15.130 {
936 SELECT xyz.* FROM (JSON_EACH('{"a":1, "b":2}')) AS xyz;
937 } {a 1 integer 1 1 {} {$.a} {$} b 2 integer 2 5 {} {$.b} {$}}
940 # Mailing list bug report on the handling of surrogate pairs
943 do_execsql_test json101-16.10 {
944 SELECT length(json_extract('"abc\uD834\uDD1Exyz"','$'));
946 do_execsql_test json101-16.20 {
947 SELECT length(json_extract('"\uD834\uDD1E"','$'));
949 do_execsql_test json101-16.30 {
950 SELECT unicode(json_extract('"\uD834\uDD1E"','$'));
953 # 2022-01-30 dbsqlfuzz 4678cf825d27f87c9b8343720121e12cf944b71a
954 do_execsql_test json101-17.1 {
955 DROP TABLE IF EXISTS t1;
956 DROP TABLE IF EXISTS t2;
957 CREATE TABLE t1(a,b,c);
959 SELECT * FROM t1 LEFT JOIN t2 ON (SELECT b FROM json_each ORDER BY 1);
962 # 2022-04-04 forum post https://sqlite.org/forum/forumpost/c082aeab43
963 do_execsql_test json101-18.1 {
964 SELECT json_valid('{"":5}');
966 do_execsql_test json101-18.2 {
967 SELECT json_extract('{"":5}', '$.""');
969 do_execsql_test json101-18.3 {
970 SELECT json_extract('[3,{"a":4,"":[5,{"hi":6},7]},8]', '$[1].""[1].hi');
972 do_execsql_test json101-18.4 {
973 SELECT json_extract('[3,{"a":4,"":[5,{"hi":6},7]},8]', '$[1].""[1]."hi"');
975 do_catchsql_test json101-18.5 {
976 SELECT json_extract('{"":8}', '$.');
977 } {1 {bad JSON path: '$.'}}
979 # 2022-08-29 https://sqlite.org/forum/forumpost/9b9e4716c0d7bbd1
980 # This is not a problem specifically with JSON functions. It is
981 # a problem with transaction control. But the json() function makes
982 # the problem more easily accessible, so it is tested here.
984 do_execsql_test json101-19.1 {
985 DROP TABLE IF EXISTS t1;
988 do_catchsql_test json101-19.2 {
990 INSERT INTO t1 VALUES(0), (json('not-valid-json'));
991 } {1 {malformed JSON}}
992 do_execsql_test json101-19.3 {
997 # 2023-03-17 positive and negative infinities
999 do_execsql_test json101-20.1 {
1000 SELECT json_object('a',2e370,'b',-3e380);
1001 } {{{"a":9.0e+999,"b":-9.0e+999}}}
1002 do_execsql_test json101-20.2 {
1003 SELECT json_object('a',2e370,'b',-3e380)->>'a';
1005 do_execsql_test json101-20.3 {
1006 SELECT json_object('a',2e370,'b',-3e380)->>'b';
1009 # 2023-05-02 https://sqlite.org/forum/forumpost/06c6334412
1010 # JSON functions should normally return NULL when given
1011 # a NULL value as the JSON input.
1014 if {[db exists {SELECT * FROM pragma_compile_options WHERE compile_options LIKE '%legacy_json_valid%'}]} {
1015 do_execsql_test json101-21.1-legacy {
1016 SELECT json_valid(NULL);
1019 do_execsql_test json101-21.1-correct {
1020 SELECT json_valid(NULL);
1023 do_execsql_test json101-21.2 {
1024 SELECT json_error_position(NULL);
1026 do_execsql_test json101-21.3 {
1029 do_execsql_test json101-21.4 {
1030 SELECT json_array(NULL);
1032 do_execsql_test json101-21.5 {
1033 SELECT json_extract(NULL);
1035 do_execsql_test json101-21.6 {
1036 SELECT json_insert(NULL,'$',123);
1038 do_execsql_test json101-21.7 {
1041 do_execsql_test json101-21.8 {
1044 do_execsql_test json101-21.9 {
1045 SELECT '{a:5}'->NULL;
1047 do_execsql_test json101-21.10 {
1048 SELECT '{a:5}'->>NULL;
1050 do_catchsql_test json101-21.11 {
1051 SELECT json_object(NULL,5);
1052 } {1 {json_object() labels must be TEXT}}
1053 do_execsql_test json101-21.12 {
1054 SELECT json_patch(NULL,'{a:5}');
1056 do_execsql_test json101-21.13 {
1057 SELECT json_patch('{a:5}',NULL);
1059 do_execsql_test json101-21.14 {
1060 SELECT json_patch(NULL,NULL);
1062 do_execsql_test json101-21.15 {
1063 SELECT json_remove(NULL,'$');
1065 do_execsql_test json101-21.16 {
1066 SELECT json_remove('{a:5,b:7}',NULL);
1068 do_execsql_test json101-21.17 {
1069 SELECT json_replace(NULL,'$.a',123);
1071 do_execsql_test json101-21.18 {
1072 SELECT json_replace('{a:5,b:7}',NULL,NULL);
1074 do_execsql_test json101-21.19 {
1075 SELECT json_set(NULL,'$.a',123);
1077 do_execsql_test json101-21.20 {
1078 SELECT json_set('{a:5,b:7}',NULL,NULL);
1080 do_execsql_test json101-21.21 {
1081 SELECT json_type(NULL);
1083 do_execsql_test json101-21.22 {
1084 SELECT json_type('{a:5,b:7}',NULL);
1086 do_execsql_test json101-21.23 {
1087 SELECT json_quote(NULL);
1089 do_execsql_test json101-21.24 {
1090 SELECT count(*) FROM json_each(NULL);
1092 do_execsql_test json101-21.25 {
1093 SELECT count(*) FROM json_tree(NULL);
1095 do_execsql_test json101-21.26 {
1096 WITH c(x) AS (VALUES(1),(2.0),(NULL),('three'))
1097 SELECT json_group_array(x) FROM c;
1098 } {[1,2.0,null,"three"]}
1099 do_execsql_test json101-21.27 {
1100 WITH c(x,y) AS (VALUES('a',1),('b',2.0),('c',NULL),(NULL,'three'),('e','four'))
1101 SELECT json_group_object(x,y) FROM c;
1102 } {{{"a":1,"b":2.0,"c":null,:"three","e":"four"}}}
1104 # 2023-10-09 https://sqlite.org/forum/forumpost/b25edc1d46
1105 # UAF due to JSON cache overflow
1107 do_execsql_test json101-22.1 {
1117 } {{{"a":2,"b":4,"c":6}}}
1118 do_execsql_test json101-22.2 {
1119 SELECT json_replace(
1120 '{"a":7,"b":8,"c":9}',
1128 } {{{"a":2,"b":4,"c":6}}}
1130 # 2023-10-17 https://sqlite.org/forum/forumpost/fc0e3f1e2a
1131 # Incorrect accesss to '$[0]' in parsed + edited JSON.
1133 do_execsql_test json101-23.1 {
1134 SELECT j, j->>0, j->>1
1135 FROM (SELECT json_set(json_set('[]','$[#]',0), '$[#]',1) AS j);
1137 do_execsql_test json101-23.2 {
1138 SELECT j, j->>0, j->>1
1139 FROM (SELECT json_set('[]','$[#]',0,'$[#]',1) AS j);
1142 # Insert/Set/Replace where the path specifies substructure that
1143 # does not yet exist
1145 proc tx x {return [string map [list ( \173 ) \175 ' \042 < \133 > \135] $x]}
1146 foreach {id start path ins set repl} {
1147 1 {{}} {$.a.b.c} ('a':('b':('c':9))) ('a':('b':('c':9))) ()
1148 2 {{a:4}} {$.a.b.c} ('a':4) ('a':4) ('a':4)
1149 3 {{a:{}}} {$.a.b.c} ('a':('b':('c':9))) ('a':('b':('c':9))) ('a':())
1150 4 {[0,1,2]} {$[3].a[0].b} <0,1,2,('a':<('b':9)>)> <0,1,2,('a':<('b':9)>)> <0,1,2>
1151 5 {[0,1,2]} {$[1].a[0].b} <0,1,2> <0,1,2> <0,1,2>
1152 6 {[0,{},2]} {$[1].a[0].b} <0,('a':<('b':9)>),2> <0,('a':<('b':9)>),2> <0,(),2>
1153 7 {[0,1,2]} {$[3][0].b} <0,1,2,<('b':9)>> <0,1,2,<('b':9)>> <0,1,2>
1154 8 {[0,1,2]} {$[1][0].b} <0,1,2> <0,1,2> <0,1,2>
1156 do_execsql_test json101-24.$id.insert {
1157 SELECT json_insert($start,$path,9);
1159 do_execsql_test json101-24.$id.set {
1160 SELECT json_set($start,$path,9);
1162 do_execsql_test json101-24.$id.replace {
1163 SELECT json_replace($start,$path,9);