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
23 do_execsql_test json101-1.1.00 {
24 SELECT json_array(1,2.5,null,'hello');
25 } {[1,2.5,null,"hello"]}
26 do_execsql_test json101-1.1.01 {
27 SELECT json_array(1,'{"abc":2.5,"def":null,"ghi":hello}',99);
28 -- the second term goes in as a string:
29 } {[1,"{\\"abc\\":2.5,\\"def\\":null,\\"ghi\\":hello}",99]}
30 do_execsql_test json101-1.1.02 {
31 SELECT json_array(1,json('{"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]}
34 do_execsql_test json101-1.1.03 {
35 SELECT json_array(1,json_object('abc',2.5,'def',null,'ghi','hello'),99);
36 -- the second term goes in as JSON
37 } {[1,{"abc":2.5,"def":null,"ghi":"hello"},99]}
38 do_execsql_test json101-1.2 {
39 SELECT hex(json_array('String "\ Test'));
40 } {5B22537472696E67205C225C5C2054657374225D}
41 do_catchsql_test json101-1.3 {
42 SELECT json_array(1,printf('%.1000c','x'),x'abcd',3);
43 } {1 {JSON cannot hold BLOB values}}
44 do_execsql_test json101-1.4 {
45 SELECT json_array(-9223372036854775808,9223372036854775807,0,1,-1,
46 0.0, 1.0, -1.0, -1e99, +2e100,
48 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18,
49 19, NULL, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31,
50 'abcdefghijklmnopqrstuvwyxzABCDEFGHIJKLMNOPQRSTUVWXYZ',
51 'abcdefghijklmnopqrstuvwyxzABCDEFGHIJKLMNOPQRSTUVWXYZ',
52 'abcdefghijklmnopqrstuvwyxzABCDEFGHIJKLMNOPQRSTUVWXYZ',
54 } {[-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]}
56 do_execsql_test json101-2.1 {
57 SELECT json_object('a',1,'b',2.5,'c',null,'d','String Test');
58 } {{{"a":1,"b":2.5,"c":null,"d":"String Test"}}}
59 do_catchsql_test json101-2.2 {
60 SELECT json_object('a',printf('%.1000c','x'),2,2.5);
61 } {1 {json_object() labels must be TEXT}}
62 do_catchsql_test json101-2.3 {
63 SELECT json_object('a',1,'b');
64 } {1 {json_object() requires an even number of arguments}}
65 do_catchsql_test json101-2.4 {
66 SELECT json_object('a',printf('%.1000c','x'),'b',x'abcd');
67 } {1 {JSON cannot hold BLOB values}}
69 do_execsql_test json101-3.1 {
70 SELECT json_replace('{"a":1,"b":2}','$.a','[3,4,5]');
71 } {{{"a":"[3,4,5]","b":2}}}
72 do_execsql_test json101-3.2 {
73 SELECT json_replace('{"a":1,"b":2}','$.a',json('[3,4,5]'));
74 } {{{"a":[3,4,5],"b":2}}}
75 do_execsql_test json101-3.3 {
76 SELECT json_type(json_set('{"a":1,"b":2}','$.b','{"x":3,"y":4}'),'$.b');
78 do_execsql_test json101-3.4 {
79 SELECT json_type(json_set('{"a":1,"b":2}','$.b',json('{"x":3,"y":4}')),'$.b');
82 do_execsql_test json101-3.5 {
83 SELECT fullkey, atom, '|' FROM json_tree(json_set('{}','$.x',123,'$.x',456));
84 } {{$} {} | {$.x} 456 |}
87 # Per rfc7159, any JSON value is allowed at the top level, and whitespace
88 # is permitting before and/or after that value.
90 do_execsql_test json101-4.1 {
93 VALUES('true'),('false'),('null'),('123'),('-234'),('34.5e+6'),
94 ('""'),('"\""'),('"\\"'),('"abcdefghijlmnopqrstuvwxyz"'),
95 ('[]'),('{}'),('[true,false,null,123,-234,34.5e+6,{},[]]'),
96 ('{"a":true,"b":{"c":false}}');
97 SELECT * FROM j1 WHERE NOT json_valid(x);
99 do_execsql_test json101-4.2 {
100 SELECT * FROM j1 WHERE NOT json_valid(char(0x20,0x09,0x0a,0x0d)||x);
102 do_execsql_test json101-4.3 {
103 SELECT * FROM j1 WHERE NOT json_valid(x||char(0x20,0x09,0x0a,0x0d));
106 # But an empty string, or a string of pure whitespace is not valid JSON.
108 do_execsql_test json101-4.4 {
109 SELECT json_valid(''), json_valid(char(0x20,0x09,0x0a,0x0d));
112 # json_remove() and similar functions with no edit operations return their
115 do_execsql_test json101-4.5 {
116 SELECT x FROM j1 WHERE json_remove(x)<>x;
118 do_execsql_test json101-4.6 {
119 SELECT x FROM j1 WHERE json_replace(x)<>x;
121 do_execsql_test json101-4.7 {
122 SELECT x FROM j1 WHERE json_set(x)<>x;
124 do_execsql_test json101-4.8 {
125 SELECT x FROM j1 WHERE json_insert(x)<>x;
128 # json_extract(JSON,'$') will return objects and arrays without change.
130 do_execsql_test json-4.10 {
131 SELECT count(*) FROM j1 WHERE json_type(x) IN ('object','array');
133 WHERE json_extract(x,'$')<>x
134 AND json_type(x) IN ('object','array');
137 do_execsql_test json-5.1 {
138 CREATE TABLE j2(id INTEGER PRIMARY KEY, json, src);
139 INSERT INTO j2(id,json,src)
146 "streetAddress": "21 2nd Street",
149 "postalCode": "10021-3100"
154 "number": "212 555-1234"
158 "number": "646 555-4567"
163 }','https://en.wikipedia.org/wiki/JSON');
164 INSERT INTO j2(id,json,src)
174 { "id": "1001", "type": "Regular" },
175 { "id": "1002", "type": "Chocolate" },
176 { "id": "1003", "type": "Blueberry" },
177 { "id": "1004", "type": "Devil''s Food" }
182 { "id": "5001", "type": "None" },
183 { "id": "5002", "type": "Glazed" },
184 { "id": "5005", "type": "Sugar" },
185 { "id": "5007", "type": "Powdered Sugar" },
186 { "id": "5006", "type": "Chocolate with Sprinkles" },
187 { "id": "5003", "type": "Chocolate" },
188 { "id": "5004", "type": "Maple" }
190 }','https://adobe.github.io/Spry/samples/data_region/JSONDataSetSample.html');
191 INSERT INTO j2(id,json,src)
202 { "id": "1001", "type": "Regular" },
203 { "id": "1002", "type": "Chocolate" },
204 { "id": "1003", "type": "Blueberry" },
205 { "id": "1004", "type": "Devil''s Food" }
210 { "id": "5001", "type": "None" },
211 { "id": "5002", "type": "Glazed" },
212 { "id": "5005", "type": "Sugar" },
213 { "id": "5007", "type": "Powdered Sugar" },
214 { "id": "5006", "type": "Chocolate with Sprinkles" },
215 { "id": "5003", "type": "Chocolate" },
216 { "id": "5004", "type": "Maple" }
228 { "id": "1001", "type": "Regular" }
233 { "id": "5001", "type": "None" },
234 { "id": "5002", "type": "Glazed" },
235 { "id": "5005", "type": "Sugar" },
236 { "id": "5003", "type": "Chocolate" },
237 { "id": "5004", "type": "Maple" }
243 "name": "Old Fashioned",
249 { "id": "1001", "type": "Regular" },
250 { "id": "1002", "type": "Chocolate" }
255 { "id": "5001", "type": "None" },
256 { "id": "5002", "type": "Glazed" },
257 { "id": "5003", "type": "Chocolate" },
258 { "id": "5004", "type": "Maple" }
261 ]','https://adobe.github.io/Spry/samples/data_region/JSONDataSetSample.html');
262 SELECT count(*) FROM j2;
265 do_execsql_test json-5.2 {
266 SELECT id, json_valid(json), json_type(json), '|' FROM j2 ORDER BY id;
267 } {1 1 object | 2 1 object | 3 1 array |}
274 # fullkey is always the same as path+key (with appropriate formatting)
276 do_execsql_test json-5.3 {
277 SELECT j2.rowid, jx.rowid, fullkey, path, key
278 FROM j2, json_tree(j2.json) AS jx
279 WHERE fullkey!=(path || CASE WHEN typeof(key)=='integer' THEN '['||key||']'
282 do_execsql_test json-5.4 {
283 SELECT j2.rowid, jx.rowid, fullkey, path, key
284 FROM j2, json_each(j2.json) AS jx
285 WHERE fullkey!=(path || CASE WHEN typeof(key)=='integer' THEN '['||key||']'
290 # Verify that the json_each.json and json_tree.json output is always the
293 do_execsql_test json-5.5 {
294 SELECT j2.rowid, jx.rowid, fullkey, path, key
295 FROM j2, json_each(j2.json) AS jx
296 WHERE jx.json<>j2.json;
298 do_execsql_test json-5.6 {
299 SELECT j2.rowid, jx.rowid, fullkey, path, key
300 FROM j2, json_tree(j2.json) AS jx
301 WHERE jx.json<>j2.json;
303 do_execsql_test json-5.7 {
304 SELECT j2.rowid, jx.rowid, fullkey, path, key
305 FROM j2, json_each(j2.json) AS jx
306 WHERE jx.value<>jx.atom AND type NOT IN ('array','object');
308 do_execsql_test json-5.8 {
309 SELECT j2.rowid, jx.rowid, fullkey, path, key
310 FROM j2, json_tree(j2.json) AS jx
311 WHERE jx.value<>jx.atom AND type NOT IN ('array','object');
314 do_execsql_test json-6.1 {
315 SELECT json_valid('{"a":55,"b":72,}');
317 do_execsql_test json-6.2 {
318 SELECT json_valid('{"a":55,"b":72}');
320 do_execsql_test json-6.3 {
321 SELECT json_valid('["a",55,"b",72,]');
323 do_execsql_test json-6.4 {
324 SELECT json_valid('["a",55,"b",72]');
327 # White-space tests. Note that form-feed is not white-space in JSON.
328 # ticket [57eec374ae1d0a1d4a23077a95f4e173fe269113]
330 foreach {tn isvalid ws} {
336 7.6 1 char(0x20,0x09,0x0a,0x0d,0x20)
337 7.7 0 char(0x20,0x09,0x0a,0x0c,0x0d,0x20)
339 do_execsql_test json-$tn.1 \
340 "SELECT json_valid(printf('%s{%s\"x\"%s:%s9%s}%s',
341 $::ws,$::ws,$::ws,$::ws,$::ws,$::ws));" \
345 # Ticket https://www.sqlite.org/src/info/ad2559db380abf8e
346 # Control characters must be escaped in JSON strings.
348 do_execsql_test json-8.1 {
349 DROP TABLE IF EXISTS t8;
350 CREATE TABLE t8(a,b);
351 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');
352 UPDATE t8 SET b=json_array(a);
354 } {{["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"]}}
355 do_execsql_test json-8.2 {
356 SELECT a=json_extract(b,'$[0]') FROM t8;
359 # The json_quote() function transforms an SQL value into a JSON value.
360 # String values are quoted and interior quotes are escaped. NULL values
361 # are rendered as the unquoted string "null".
363 do_execsql_test json-9.1 {
364 SELECT json_quote('abc"xyz');
366 do_execsql_test json-9.2 {
367 SELECT json_quote(3.14159);
369 do_execsql_test json-9.3 {
370 SELECT json_quote(12345);
372 do_execsql_test json-9.4 {
373 SELECT json_quote(null);
375 do_catchsql_test json-9.5 {
376 SELECT json_quote(x'30313233');
377 } {1 {JSON cannot hold BLOB values}}
378 do_catchsql_test json-9.6 {
379 SELECT json_quote(123,456)
380 } {1 {wrong number of arguments to function json_quote()}}
381 do_catchsql_test json-9.7 {
383 } {1 {wrong number of arguments to function json_quote()}}