Debian Codegraph @

test/json102.test

raw
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 # This file contains tests automatically generated from the json1
15 # documentation.
16 #
17
18 set testdir [file dirname $argv0]
19 source $testdir/tester.tcl
20
21 do_execsql_test json102-100 {
22 SELECT json_object('ex','[52,3.14159]');
23 } {{{"ex":"[52,3.14159]"}}}
24 do_execsql_test json102-100b {
25 SELECT json(jsonb_object('ex','[52,3.14159]'));
26 } {{{"ex":"[52,3.14159]"}}}
27 do_execsql_test json102-110 {
28 SELECT json_object('ex',json('[52,3.14159]'));
29 } {{{"ex":[52,3.14159]}}}
30 do_execsql_test json102-110-2 {
31 SELECT json(jsonb_object('ex',json('[52,3.14159]')));
32 } {{{"ex":[52,3.14159]}}}
33 do_execsql_test json102-110-3 {
34 SELECT json_object('ex',jsonb('[52,3.14159]'));
35 } {{{"ex":[52,3.14159]}}}
36 do_execsql_test json102-110-3 {
37 SELECT json(jsonb_object('ex',jsonb('[52,3.14159]')));
38 } {{{"ex":[52,3.14159]}}}
39 do_execsql_test json102-120 {
40 SELECT json_object('ex',json_array(52,3.14159));
41 } {{{"ex":[52,3.14159]}}}
42 do_execsql_test json102-120-2 {
43 SELECT json(jsonb_object('ex',json_array(52,3.14159)));
44 } {{{"ex":[52,3.14159]}}}
45 do_execsql_test json102-120-3 {
46 SELECT json_object('ex',jsonb_array(52,3.14159));
47 } {{{"ex":[52,3.14159]}}}
48 do_execsql_test json102-120-4 {
49 SELECT json(jsonb_object('ex',jsonb_array(52,3.14159)));
50 } {{{"ex":[52,3.14159]}}}
51 do_execsql_test json102-130 {
52 SELECT json(' { "this" : "is", "a": [ "test" ] } ');
53 } {{{"this":"is","a":["test"]}}}
54 do_execsql_test json102-130b {
55 SELECT json(jsonb(' { "this" : "is", "a": [ "test" ] } '));
56 } {{{"this":"is","a":["test"]}}}
57 do_execsql_test json102-140 {
58 SELECT json_array(1,2,'3',4);
59 } {{[1,2,"3",4]}}
60 do_execsql_test json102-140b {
61 SELECT json(jsonb_array(1,2,'3',4));
62 } {{[1,2,"3",4]}}
63 do_execsql_test json102-150 {
64 SELECT json_array('[1,2]');
65 } {{["[1,2]"]}}
66 do_execsql_test json102-150b {
67 SELECT json(jsonb_array('[1,2]'));
68 } {{["[1,2]"]}}
69 do_execsql_test json102-160 {
70 SELECT json_array(json_array(1,2));
71 } {{[[1,2]]}}
72 do_execsql_test json102-160-2 {
73 SELECT json_array(jsonb_array(1,2));
74 } {{[[1,2]]}}
75 do_execsql_test json102-160-3 {
76 SELECT json(jsonb_array(json_array(1,2)));
77 } {{[[1,2]]}}
78 do_execsql_test json102-160-4 {
79 SELECT json(jsonb_array(jsonb_array(1,2)));
80 } {{[[1,2]]}}
81 do_execsql_test json102-170 {
82 SELECT json_array(1,null,'3','[4,5]','{"six":7.7}');
83 } {{[1,null,"3","[4,5]","{\"six\":7.7}"]}}
84 do_execsql_test json102-170b {
85 SELECT json(jsonb_array(1,null,'3','[4,5]','{"six":7.7}'));
86 } {{[1,null,"3","[4,5]","{\"six\":7.7}"]}}
87 do_execsql_test json102-180 {
88 SELECT json_array(1,null,'3',json('[4,5]'),json('{"six":7.7}'));
89 } {{[1,null,"3",[4,5],{"six":7.7}]}}
90 do_execsql_test json102-180-2 {
91 SELECT json_array(1,null,'3',jsonb('[4,5]'),json('{"six":7.7}'));
92 } {{[1,null,"3",[4,5],{"six":7.7}]}}
93 do_execsql_test json102-180-3 {
94 SELECT json(jsonb_array(1,null,'3',json('[4,5]'),json('{"six":7.7}')));
95 } {{[1,null,"3",[4,5],{"six":7.7}]}}
96 do_execsql_test json102-180-4 {
97 SELECT json(jsonb_array(1,null,'3',jsonb('[4,5]'),jsonb('{"six":7.7}')));
98 } {{[1,null,"3",[4,5],{"six":7.7}]}}
99 do_execsql_test json102-190 {
100 SELECT json_array_length('[1,2,3,4]');
101 } {{4}}
102 do_execsql_test json102-190b {
103 SELECT json_array_length(jsonb('[1,2,3,4]'));
104 } {{4}}
105 do_execsql_test json102-191 {
106 SELECT json_array_length( json_remove('[1,2,3,4]','$[2]') );
107 } {{3}}
108 do_execsql_test json102-191b {
109 SELECT json_array_length( jsonb_remove('[1,2,3,4]','$[2]') );
110 } {{3}}
111 do_execsql_test json102-200 {
112 SELECT json_array_length('[1,2,3,4]', '$');
113 } {{4}}
114 do_execsql_test json102-200b {
115 SELECT json_array_length(jsonb('[1,2,3,4]'), '$');
116 } {{4}}
117 do_execsql_test json102-210 {
118 SELECT json_array_length('[1,2,3,4]', '$[2]');
119 } {{0}}
120 do_execsql_test json102-210b {
121 SELECT json_array_length(jsonb('[1,2,3,4]'), '$[2]');
122 } {{0}}
123 do_execsql_test json102-220 {
124 SELECT json_array_length('{"one":[1,2,3]}');
125 } {{0}}
126 do_execsql_test json102-220 {
127 SELECT json_array_length('{"one":[1,2,3]}');
128 } {{0}}
129 do_execsql_test json102-230b {
130 SELECT json_array_length(jsonb('{"one":[1,2,3]}'), '$.one');
131 } {{3}}
132 do_execsql_test json102-240 {
133 SELECT json_array_length('{"one":[1,2,3]}', '$.two');
134 } {{}}
135 do_execsql_test json102-240b {
136 SELECT json_array_length(jsonb('{"one":[1,2,3]}'), '$.two');
137 } {{}}
138 do_execsql_test json102-250 {
139 SELECT json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$');
140 } {{{"a":2,"c":[4,5,{"f":7}]}}}
141 do_execsql_test json102-250-2 {
142 SELECT json_extract(jsonb('{"a":2,"c":[4,5,{"f":7}]}'), '$');
143 } {{{"a":2,"c":[4,5,{"f":7}]}}}
144 do_execsql_test json102-250-3 {
145 SELECT json(jsonb_extract('{"a":2,"c":[4,5,{"f":7}]}', '$'));
146 } {{{"a":2,"c":[4,5,{"f":7}]}}}
147 do_execsql_test json102-250-4 {
148 SELECT json(jsonb_extract(jsonb('{"a":2,"c":[4,5,{"f":7}]}'), '$'));
149 } {{{"a":2,"c":[4,5,{"f":7}]}}}
150 do_execsql_test json102-260 {
151 SELECT json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.c');
152 } {{[4,5,{"f":7}]}}
153 do_execsql_test json102-260-2 {
154 SELECT json_extract(jsonb('{"a":2,"c":[4,5,{"f":7}]}'), '$.c');
155 } {{[4,5,{"f":7}]}}
156 do_execsql_test json102-260-3 {
157 SELECT json(jsonb_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.c'));
158 } {{[4,5,{"f":7}]}}
159 do_execsql_test json102-260-4 {
160 SELECT json(jsonb_extract(jsonb('{"a":2,"c":[4,5,{"f":7}]}'), '$.c'));
161 } {{[4,5,{"f":7}]}}
162 do_execsql_test json102-270 {
163 SELECT json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.c[2]');
164 } {{{"f":7}}}
165 do_execsql_test json102-270-2 {
166 SELECT json_extract(jsonb('{"a":2,"c":[4,5,{"f":7}]}'), '$.c[2]');
167 } {{{"f":7}}}
168 do_execsql_test json102-270-3 {
169 SELECT json(jsonb_extract(jsonb('{"a":2,"c":[4,5,{"f":7}]}'), '$.c[2]'));
170 } {{{"f":7}}}
171 do_execsql_test json102-270-4 {
172 SELECT json(jsonb_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.c[2]'));
173 } {{{"f":7}}}
174 do_execsql_test json102-280 {
175 SELECT json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.c[2].f');
176 } {{7}}
177 do_execsql_test json102-280b {
178 SELECT jsonb_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.c[2].f');
179 } {{7}}
180 do_execsql_test json102-290 {
181 SELECT json_extract('{"a":2,"c":[4,5],"f":7}','$.c','$.a');
182 } {{[[4,5],2]}}
183 do_execsql_test json102-290-2 {
184 SELECT json_extract(jsonb('{"a":2,"c":[4,5],"f":7}'),'$.c','$.a');
185 } {{[[4,5],2]}}
186 do_execsql_test json102-290-3 {
187 SELECT json(jsonb_extract('{"a":2,"c":[4,5],"f":7}','$.c','$.a'));
188 } {{[[4,5],2]}}
189 do_execsql_test json102-290-4 {
190 SELECT json(jsonb_extract(jsonb('{"a":2,"c":[4,5],"f":7}'),'$.c','$.a'));
191 } {{[[4,5],2]}}
192 do_execsql_test json102-300 {
193 SELECT json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.x');
194 } {{}}
195 do_execsql_test json102-300b {
196 SELECT jsonb_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.x');
197 } {{}}
198 do_execsql_test json102-310 {
199 SELECT json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.x', '$.a');
200 } {{[null,2]}}
201 do_execsql_test json102-310-2 {
202 SELECT json_extract(jsonb('{"a":2,"c":[4,5,{"f":7}]}'), '$.x', '$.a');
203 } {{[null,2]}}
204 do_execsql_test json102-310-3 {
205 SELECT json(jsonb_extract(jsonb('{"a":2,"c":[4,5,{"f":7}]}'), '$.x', '$.a'));
206 } {{[null,2]}}
207 do_execsql_test json102-310-43 {
208 SELECT json(jsonb_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.x', '$.a'));
209 } {{[null,2]}}
210 do_execsql_test json102-320 {
211 SELECT json_insert('{"a":2,"c":4}', '$.a', 99);
212 } {{{"a":2,"c":4}}}
213 do_execsql_test json102-320-2 {
214 SELECT json_insert(jsonb('{"a":2,"c":4}'), '$.a', 99);
215 } {{{"a":2,"c":4}}}
216 do_execsql_test json102-320-3 {
217 SELECT json(jsonb_insert('{"a":2,"c":4}', '$.a', 99));
218 } {{{"a":2,"c":4}}}
219 do_execsql_test json102-320-4 {
220 SELECT json(jsonb_insert(jsonb('{"a":2,"c":4}'), '$.a', 99));
221 } {{{"a":2,"c":4}}}
222 do_execsql_test json102-330 {
223 SELECT json_insert('{"a":2,"c":4}', '$.e', 99);
224 } {{{"a":2,"c":4,"e":99}}}
225 do_execsql_test json102-330-2 {
226 SELECT json_insert(jsonb('{"a":2,"c":4}'), '$.e', 99);
227 } {{{"a":2,"c":4,"e":99}}}
228 do_execsql_test json102-330-3 {
229 SELECT json(jsonb_insert('{"a":2,"c":4}', '$.e', 99));
230 } {{{"a":2,"c":4,"e":99}}}
231 do_execsql_test json102-330-4 {
232 SELECT json(jsonb_insert(jsonb('{"a":2,"c":4}'), '$.e', 99));
233 } {{{"a":2,"c":4,"e":99}}}
234 do_execsql_test json102-340 {
235 SELECT json_replace('{"a":2,"c":4}', '$.a', 99);
236 } {{{"a":99,"c":4}}}
237 do_execsql_test json102-340-2 {
238 SELECT json_replace(jsonb('{"a":2,"c":4}'), '$.a', 99);
239 } {{{"a":99,"c":4}}}
240 do_execsql_test json102-340-3 {
241 SELECT json(jsonb_replace('{"a":2,"c":4}', '$.a', 99));
242 } {{{"a":99,"c":4}}}
243 do_execsql_test json102-340-4 {
244 SELECT json(jsonb_replace(jsonb('{"a":2,"c":4}'), '$.a', 99));
245 } {{{"a":99,"c":4}}}
246 do_execsql_test json102-350 {
247 SELECT json_replace('{"a":2,"c":4}', '$.e', 99);
248 } {{{"a":2,"c":4}}}
249 do_execsql_test json102-350-2 {
250 SELECT json_replace(jsonb('{"a":2,"c":4}'), '$.e', 99);
251 } {{{"a":2,"c":4}}}
252 do_execsql_test json102-350-3 {
253 SELECT json(jsonb_replace('{"a":2,"c":4}', '$.e', 99));
254 } {{{"a":2,"c":4}}}
255 do_execsql_test json102-350-4 {
256 SELECT json(jsonb_replace(jsonb('{"a":2,"c":4}'), '$.e', 99));
257 } {{{"a":2,"c":4}}}
258 do_execsql_test json102-360 {
259 SELECT json_set('{"a":2,"c":4}', '$.a', 99);
260 } {{{"a":99,"c":4}}}
261 do_execsql_test json102-360-2 {
262 SELECT json_set(jsonb('{"a":2,"c":4}'), '$.a', 99);
263 } {{{"a":99,"c":4}}}
264 do_execsql_test json102-360-3 {
265 SELECT json(jsonb_set('{"a":2,"c":4}', '$.a', 99));
266 } {{{"a":99,"c":4}}}
267 do_execsql_test json102-360-4 {
268 SELECT json(jsonb_set(jsonb('{"a":2,"c":4}'), '$.a', 99));
269 } {{{"a":99,"c":4}}}
270 do_execsql_test json102-370 {
271 SELECT json_set('{"a":2,"c":4}', '$.e', 99);
272 } {{{"a":2,"c":4,"e":99}}}
273 do_execsql_test json102-370-2 {
274 SELECT json_set(jsonb('{"a":2,"c":4}'), '$.e', 99);
275 } {{{"a":2,"c":4,"e":99}}}
276 do_execsql_test json102-370-3 {
277 SELECT json(jsonb_set('{"a":2,"c":4}', '$.e', 99));
278 } {{{"a":2,"c":4,"e":99}}}
279 do_execsql_test json102-370-4 {
280 SELECT json(jsonb_set(jsonb('{"a":2,"c":4}'), '$.e', 99));
281 } {{{"a":2,"c":4,"e":99}}}
282 do_execsql_test json102-380 {
283 SELECT json_set('{"a":2,"c":4}', '$.c', '[97,96]');
284 } {{{"a":2,"c":"[97,96]"}}}
285 do_execsql_test json102-380-2 {
286 SELECT json_set(jsonb('{"a":2,"c":4}'), '$.c', '[97,96]');
287 } {{{"a":2,"c":"[97,96]"}}}
288 do_execsql_test json102-380-3 {
289 SELECT json(jsonb_set('{"a":2,"c":4}', '$.c', '[97,96]'));
290 } {{{"a":2,"c":"[97,96]"}}}
291 do_execsql_test json102-380-4 {
292 SELECT json(jsonb_set(jsonb('{"a":2,"c":4}'), '$.c', '[97,96]'));
293 } {{{"a":2,"c":"[97,96]"}}}
294 do_execsql_test json102-390 {
295 SELECT json_set('{"a":2,"c":4}', '$.c', json('[97,96]'));
296 } {{{"a":2,"c":[97,96]}}}
297 do_execsql_test json102-390-2 {
298 SELECT json_set(jsonb('{"a":2,"c":4}'), '$.c', json('[97,96]'));
299 } {{{"a":2,"c":[97,96]}}}
300 do_execsql_test json102-390-3 {
301 SELECT json(jsonb_set('{"a":2,"c":4}', '$.c', json('[97,96]')));
302 } {{{"a":2,"c":[97,96]}}}
303 do_execsql_test json102-390-4 {
304 SELECT json(jsonb_set(jsonb('{"a":2,"c":4}'), '$.c', json('[97,96]')));
305 } {{{"a":2,"c":[97,96]}}}
306 do_execsql_test json102-390-5 {
307 SELECT json_set('{"a":2,"c":4}', '$.c', jsonb('[97,96]'));
308 } {{{"a":2,"c":[97,96]}}}
309 do_execsql_test json102-390-6 {
310 SELECT json_set(jsonb('{"a":2,"c":4}'), '$.c', jsonb('[97,96]'));
311 } {{{"a":2,"c":[97,96]}}}
312 do_execsql_test json102-390-7 {
313 SELECT json(jsonb_set('{"a":2,"c":4}', '$.c', jsonb('[97,96]')));
314 } {{{"a":2,"c":[97,96]}}}
315 do_execsql_test json102-390-8 {
316 SELECT json(jsonb_set(jsonb('{"a":2,"c":4}'), '$.c', jsonb('[97,96]')));
317 } {{{"a":2,"c":[97,96]}}}
318 do_execsql_test json102-400 {
319 SELECT json_set('{"a":2,"c":4}', '$.c', json_array(97,96));
320 } {{{"a":2,"c":[97,96]}}}
321 do_execsql_test json102-400-2 {
322 SELECT json_set(jsonb('{"a":2,"c":4}'), '$.c', json_array(97,96));
323 } {{{"a":2,"c":[97,96]}}}
324 do_execsql_test json102-400-3 {
325 SELECT json(jsonb_set('{"a":2,"c":4}', '$.c', json_array(97,96)));
326 } {{{"a":2,"c":[97,96]}}}
327 do_execsql_test json102-400-4 {
328 SELECT json(jsonb_set(jsonb('{"a":2,"c":4}'), '$.c', json_array(97,96)));
329 } {{{"a":2,"c":[97,96]}}}
330 do_execsql_test json102-400-5 {
331 SELECT json_set('{"a":2,"c":4}', '$.c', jsonb_array(97,96));
332 } {{{"a":2,"c":[97,96]}}}
333 do_execsql_test json102-400-6 {
334 SELECT json_set(jsonb('{"a":2,"c":4}'), '$.c', jsonb_array(97,96));
335 } {{{"a":2,"c":[97,96]}}}
336 do_execsql_test json102-400-7 {
337 SELECT json(jsonb_set('{"a":2,"c":4}', '$.c', jsonb_array(97,96)));
338 } {{{"a":2,"c":[97,96]}}}
339 do_execsql_test json102-400-8 {
340 SELECT json(jsonb_set(jsonb('{"a":2,"c":4}'), '$.c', jsonb_array(97,96)));
341 } {{{"a":2,"c":[97,96]}}}
342 do_execsql_test json102-410 {
343 SELECT json_object('a',2,'c',4);
344 } {{{"a":2,"c":4}}}
345 do_execsql_test json102-410b {
346 SELECT json(jsonb_object('a',2,'c',4));
347 } {{{"a":2,"c":4}}}
348 do_execsql_test json102-420 {
349 SELECT json_object('a',2,'c','{e:5}');
350 } {{{"a":2,"c":"{e:5}"}}}
351 do_execsql_test json102-420b {
352 SELECT json(jsonb_object('a',2,'c','{e:5}'));
353 } {{{"a":2,"c":"{e:5}"}}}
354 do_execsql_test json102-430 {
355 SELECT json_object('a',2,'c',json_object('e',5));
356 } {{{"a":2,"c":{"e":5}}}}
357 do_execsql_test json102-430-2 {
358 SELECT json(jsonb_object('a',2,'c',json_object('e',5)));
359 } {{{"a":2,"c":{"e":5}}}}
360 do_execsql_test json102-430-3 {
361 SELECT json_object('a',2,'c',jsonb_object('e',5));
362 } {{{"a":2,"c":{"e":5}}}}
363 do_execsql_test json102-430-4 {
364 SELECT json(jsonb_object('a',2,'c',jsonb_object('e',5)));
365 } {{{"a":2,"c":{"e":5}}}}
366 do_execsql_test json102-440 {
367 SELECT json_remove('[0,1,2,3,4]','$[2]');
368 } {{[0,1,3,4]}}
369 do_execsql_test json102-440-2 {
370 SELECT json_remove(jsonb('[0,1,2,3,4]'),'$[2]');
371 } {{[0,1,3,4]}}
372 do_execsql_test json102-440-3 {
373 SELECT json(jsonb_remove('[0,1,2,3,4]','$[2]'));
374 } {{[0,1,3,4]}}
375 do_execsql_test json102-440-4 {
376 SELECT json(jsonb_remove(jsonb('[0,1,2,3,4]'),'$[2]'));
377 } {{[0,1,3,4]}}
378 do_execsql_test json102-445-1 {
379 SELECT json_remove('[0,1,2,3,4]','$[5]');
380 } {{[0,1,2,3,4]}}
381 do_execsql_test json102-445-2 {
382 SELECT json_remove('[0,1,2,3,4]','$[6]');
383 } {{[0,1,2,3,4]}}
384 do_execsql_test json102-445-3 {
385 SELECT json_remove('[0,1,2,3,4]','$[4294967295]');
386 } {{[0,1,2,3,4]}}
387 do_execsql_test json102-445-4 {
388 SELECT json_remove('[0,1,2,3,4]','$[4294967296]');
389 } {{[0,1,2,3,4]}}
390 do_execsql_test json102-445-5 {
391 SELECT json_remove('[0,1,2,3,4]','$[4294967297]');
392 } {{[0,1,2,3,4]}}
393 do_execsql_test json102-445-6 {
394 SELECT json_remove('[0,1,2,3,4]','$[42949672950]');
395 } {{[0,1,2,3,4]}}
396 do_execsql_test json102-445-7 {
397 SELECT json_remove('[0,1,2,3,4]','$[42949672960]');
398 } {{[0,1,2,3,4]}}
399 do_execsql_test json102-450 {
400 SELECT json_remove('[0,1,2,3,4]','$[2]','$[0]');
401 } {{[1,3,4]}}
402 do_execsql_test json102-450-2 {
403 SELECT json_remove(jsonb('[0,1,2,3,4]'),'$[2]','$[0]');
404 } {{[1,3,4]}}
405 do_execsql_test json102-450-3 {
406 SELECT json(jsonb_remove('[0,1,2,3,4]','$[2]','$[0]'));
407 } {{[1,3,4]}}
408 do_execsql_test json102-450-4 {
409 SELECT json(jsonb_remove(jsonb('[0,1,2,3,4]'),'$[2]','$[0]'));
410 } {{[1,3,4]}}
411 do_execsql_test json102-460 {
412 SELECT json_remove('[0,1,2,3,4]','$[0]','$[2]');
413 } {{[1,2,4]}}
414 do_execsql_test json102-460-2 {
415 SELECT json_remove(jsonb('[0,1,2,3,4]'),'$[0]','$[2]');
416 } {{[1,2,4]}}
417 do_execsql_test json102-460-3 {
418 SELECT json(jsonb_remove('[0,1,2,3,4]','$[0]','$[2]'));
419 } {{[1,2,4]}}
420 do_execsql_test json102-460-4 {
421 SELECT json(jsonb_remove(jsonb('[0,1,2,3,4]'),'$[0]','$[2]'));
422 } {{[1,2,4]}}
423 do_execsql_test json102-470 {
424 SELECT json_remove('{"x":25,"y":42}');
425 } {{{"x":25,"y":42}}}
426 do_execsql_test json102-470-2 {
427 SELECT json_remove(jsonb('{"x":25,"y":42}'));
428 } {{{"x":25,"y":42}}}
429 do_execsql_test json102-470-3 {
430 SELECT json(jsonb_remove('{"x":25,"y":42}'));
431 } {{{"x":25,"y":42}}}
432 do_execsql_test json102-470-4 {
433 SELECT json(jsonb_remove(jsonb('{"x":25,"y":42}')));
434 } {{{"x":25,"y":42}}}
435 do_execsql_test json102-480 {
436 SELECT json_remove('{"x":25,"y":42}','$.z');
437 } {{{"x":25,"y":42}}}
438 do_execsql_test json102-480-2 {
439 SELECT json_remove(jsonb('{"x":25,"y":42}'),'$.z');
440 } {{{"x":25,"y":42}}}
441 do_execsql_test json102-480-3 {
442 SELECT json(jsonb_remove('{"x":25,"y":42}','$.z'));
443 } {{{"x":25,"y":42}}}
444 do_execsql_test json102-480-4 {
445 SELECT json(jsonb_remove(jsonb('{"x":25,"y":42}'),'$.z'));
446 } {{{"x":25,"y":42}}}
447 do_execsql_test json102-490 {
448 SELECT json_remove('{"x":25,"y":42}','$.y');
449 } {{{"x":25}}}
450 do_execsql_test json102-490-2 {
451 SELECT json_remove(jsonb('{"x":25,"y":42}'),'$.y');
452 } {{{"x":25}}}
453 do_execsql_test json102-490-3 {
454 SELECT json(jsonb_remove('{"x":25,"y":42}','$.y'));
455 } {{{"x":25}}}
456 do_execsql_test json102-490-4 {
457 SELECT json(jsonb_remove(jsonb('{"x":25,"y":42}'),'$.y'));
458 } {{{"x":25}}}
459 do_execsql_test json102-500 {
460 SELECT json_remove('{"x":25,"y":42}','$');
461 } {{}}
462 do_execsql_test json102-500-2 {
463 SELECT json_remove(jsonb('{"x":25,"y":42}'),'$');
464 } {{}}
465 do_execsql_test json102-500-3 {
466 SELECT json(jsonb_remove('{"x":25,"y":42}','$'));
467 } {{}}
468 do_execsql_test json102-500-4 {
469 SELECT json(jsonb_remove(jsonb('{"x":25,"y":42}'),'$'));
470 } {{}}
471 do_execsql_test json102-510 {
472 SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}');
473 } {{object}}
474 do_execsql_test json102-510b {
475 SELECT json_type(x'cc0f1761cb0b133235332e350102001778');
476 } {{object}}
477 do_execsql_test json102-520 {
478 SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}','$');
479 } {{object}}
480 do_execsql_test json102-520b {
481 SELECT json_type(x'cc0f1761cb0b133235332e350102001778','$');
482 } {{object}}
483 do_execsql_test json102-530 {
484 SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a');
485 } {{array}}
486 do_execsql_test json102-530b {
487 SELECT json_type(x'cc0f1761cb0b133235332e350102001778','$.a');
488 } {{array}}
489 do_execsql_test json102-540 {
490 SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[0]');
491 } {{integer}}
492 do_execsql_test json102-540b {
493 SELECT json_type(x'cc0f1761cb0b133235332e350102001778','$.a[0]');
494 } {{integer}}
495 do_execsql_test json102-550 {
496 SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[1]');
497 } {{real}}
498 do_execsql_test json102-550b {
499 SELECT json_type(x'cc0f1761cb0b133235332e350102001778','$.a[1]');
500 } {{real}}
501 do_execsql_test json102-560 {
502 SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[2]');
503 } {{true}}
504 do_execsql_test json102-560b {
505 SELECT json_type(x'cc0f1761cb0b133235332e350102001778','$.a[2]');
506 } {{true}}
507 do_execsql_test json102-570 {
508 SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[3]');
509 } {{false}}
510 do_execsql_test json102-570b {
511 SELECT json_type(x'cc0f1761cb0b133235332e350102001778','$.a[3]');
512 } {{false}}
513 do_execsql_test json102-580 {
514 SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[4]');
515 } {{null}}
516 do_execsql_test json102-580b {
517 SELECT json_type(x'cc0f1761cb0b133235332e350102001778','$.a[4]');
518 } {{null}}
519 do_execsql_test json102-590 {
520 SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[5]');
521 } {{text}}
522 do_execsql_test json102-590b {
523 SELECT json_type(x'cc0f1761cb0b133235332e350102001778','$.a[5]');
524 } {{text}}
525 do_execsql_test json102-600 {
526 SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[6]');
527 } {{}}
528 do_execsql_test json102-600b {
529 SELECT json_type(x'cc0f1761cb0b133235332e350102001778','$.a[6]');
530 } {{}}
531 do_execsql_test json102-610 {
532 SELECT json_valid(char(123)||'"x":35'||char(125));
533 } {{1}}
534 do_execsql_test json102-620 {
535 SELECT json_valid(char(123)||'"x":35');
536 } {{0}}
537
538 ifcapable vtab {
539 do_execsql_test json102-1000 {
540 CREATE TABLE user(name,phone,phoneb);
541 INSERT INTO user(name,phone) VALUES
542 ('Alice','["919-555-2345","804-555-3621"]'),
543 ('Bob','["201-555-8872"]'),
544 ('Cindy','["704-555-9983"]'),
545 ('Dave','["336-555-8421","704-555-4321","803-911-4421"]');
546 UPDATE user SET phoneb=jsonb(phone);
547 SELECT DISTINCT user.name
548 FROM user, json_each(user.phone)
549 WHERE json_each.value LIKE '704-%'
550 ORDER BY 1;
551 } {Cindy Dave}
552 do_execsql_test json102-1000b {
553 SELECT DISTINCT user.name
554 FROM user, json_each(user.phoneb)
555 WHERE json_each.value LIKE '704-%'
556 ORDER BY 1;
557 } {Cindy Dave}
558
559 do_execsql_test json102-1010 {
560 UPDATE user
561 SET phone=json_extract(phone,'$[0]')
562 WHERE json_array_length(phone)<2;
563 SELECT name, substr(phone,1,5) FROM user ORDER BY name;
564 } {Alice {["919} Bob 201-5 Cindy 704-5 Dave {["336}}
565 do_execsql_test json102-1011 {
566 SELECT name FROM user WHERE phone LIKE '704-%'
567 UNION
568 SELECT user.name
569 FROM user, json_each(user.phone)
570 WHERE json_valid(user.phone)
571 AND json_each.value LIKE '704-%';
572 } {Cindy Dave}
573
574 do_execsql_test json102-1100 {
575 CREATE TABLE big(json JSON);
576 INSERT INTO big(json) VALUES('{
577 "id":123,
578 "stuff":[1,2,3,4],
579 "partlist":[
580 {"uuid":"bb108722-572e-11e5-9320-7f3b63a4ca74"},
581 {"uuid":"c690dc14-572e-11e5-95f9-dfc8861fd535"},
582 {"subassembly":[
583 {"uuid":"6fa5181e-5721-11e5-a04e-57f3d7b32808"}
584 ]}
585 ]
586 }');
587 INSERT INTO big(json) VALUES('{
588 "id":456,
589 "stuff":["hello","world","xyzzy"],
590 "partlist":[
591 {"uuid":false},
592 {"uuid":"c690dc14-572e-11e5-95f9-dfc8861fd535"}
593 ]
594 }');
595 } {}
596 set correct_answer [list \
597 1 {$.id} 123 \
598 1 {$.stuff[0]} 1 \
599 1 {$.stuff[1]} 2 \
600 1 {$.stuff[2]} 3 \
601 1 {$.stuff[3]} 4 \
602 1 {$.partlist[0].uuid} bb108722-572e-11e5-9320-7f3b63a4ca74 \
603 1 {$.partlist[1].uuid} c690dc14-572e-11e5-95f9-dfc8861fd535 \
604 1 {$.partlist[2].subassembly[0].uuid} 6fa5181e-5721-11e5-a04e-57f3d7b32808 \
605 2 {$.id} 456 \
606 2 {$.stuff[0]} hello \
607 2 {$.stuff[1]} world \
608 2 {$.stuff[2]} xyzzy \
609 2 {$.partlist[0].uuid} 0 \
610 2 {$.partlist[1].uuid} c690dc14-572e-11e5-95f9-dfc8861fd535]
611 do_execsql_test json102-1110 {
612 SELECT big.rowid, fullkey, value
613 FROM big, json_tree(big.json)
614 WHERE json_tree.type NOT IN ('object','array')
615 ORDER BY +big.rowid, +json_tree.id
616 } $correct_answer
617 do_execsql_test json102-1110b {
618 SELECT big.rowid, fullkey, value
619 FROM big, json_tree(jsonb(big.json))
620 WHERE json_tree.type NOT IN ('object','array')
621 ORDER BY +big.rowid, +json_tree.id
622 } $correct_answer
623 do_execsql_test json102-1120 {
624 SELECT big.rowid, fullkey, atom
625 FROM big, json_tree(big.json)
626 WHERE atom IS NOT NULL
627 ORDER BY +big.rowid, +json_tree.id
628 } $correct_answer
629
630 do_execsql_test json102-1130 {
631 SELECT DISTINCT json_extract(big.json,'$.id')
632 FROM big, json_tree(big.json,'$.partlist')
633 WHERE json_tree.key='uuid'
634 AND json_tree.value='6fa5181e-5721-11e5-a04e-57f3d7b32808';
635 } {123}
636 do_execsql_test json102-1131 {
637 SELECT DISTINCT json_extract(big.json,'$.id')
638 FROM big, json_tree(big.json,'$')
639 WHERE json_tree.key='uuid'
640 AND json_tree.value='6fa5181e-5721-11e5-a04e-57f3d7b32808';
641 } {123}
642 do_execsql_test json102-1132 {
643 SELECT DISTINCT json_extract(big.json,'$.id')
644 FROM big, json_tree(big.json)
645 WHERE json_tree.key='uuid'
646 AND json_tree.value='6fa5181e-5721-11e5-a04e-57f3d7b32808';
647 } {123}
648 } ;# end ifcapable vtab
649
650 #-------------------------------------------------------------------------
651 # Test that json_valid() correctly identifies non-ascii range
652 # characters as non-whitespace.
653 #
654 do_execsql_test json102-1201 { SELECT json_valid(char(32) || '"xyz"') } 1
655 do_execsql_test json102-1202 { SELECT json_valid(char(200) || '"xyz"') } 0
656
657 # Off-by-one error in jsonAppendString()
658 #
659 for {set i 0} {$i<100} {incr i} {
660 set str abcdef[string repeat \" [expr {$i+50}]]uvwxyz
661 do_test json102-[format %d [expr {$i+1300}]] {
662 db eval {SELECT json_extract(json_array($::str),'$[0]')==$::str}
663 } {1}
664 }
665
666 #-------------------------------------------------------------------------
667 # 2017-04-08 ticket b93be8729a895a528e2849fca99f7
668 # JSON extension accepts invalid numeric values
669 #
670 # JSON does not allow leading zeros. But the JSON extension was
671 # allowing them. The following tests verify that the problem is now
672 # fixed.
673 #
674 foreach {id j x0 x5} {
675 1401 {'{"x":01}'} 0 0
676 1402 {'{"x":-01}'} 0 0
677 1403 {'{"x":0}'} 1 1
678 1404 {'{"x":-0}'} 1 1
679 1405 {'{"x":0.1}'} 1 1
680 1406 {'{"x":-0.1}'} 1 1
681 1407 {'{"x":0.0000}'} 1 1
682 1408 {'{"x":-0.0000}'} 1 1
683 1409 {'{"x":01.5}'} 0 0
684 1410 {'{"x":-01.5}'} 0 0
685 1411 {'{"x":00}'} 0 0
686 1412 {'{"x":-00}'} 0 0
687 1413 {'{"x":+0}'} 0 1
688 1414 {'{"x":+5}'} 0 1
689 1415 {'{"x":+5.5}'} 0 1
690 } {
691 do_execsql_test json102-$id "
692 SELECT json_valid($j), NOT json_error_position($j);
693 " [list $x0 $x5]
694 }
695
696 #------------------------------------------------------------------------
697 # 2017-04-10 ticket 6c9b5514077fed34551f98e64c09a10dc2fc8e16
698 # JSON extension accepts strings containing control characters.
699 #
700 # The JSON spec requires that all control characters be escaped.
701 #
702 do_execsql_test json102-1500 {
703 WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<0x20)
704 SELECT x FROM c WHERE json_valid(printf('{"a":"x%sz"}', char(x))) ORDER BY x;
705 } {32}
706
707 # All control characters are escaped
708 #
709 do_execsql_test json102-1501 {
710 WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<0x1f)
711 SELECT sum(json_valid(json_quote('a'||char(x)||'z'))) FROM c ORDER BY x;
712 } {31}
713
714 # 2022-01-10 tests for -> and ->> operators
715 #
716 reset_db
717 do_execsql_test json102-1600 {
718 CREATE TABLE t1(id INTEGER PRIMARY KEY, x JSON);
719 INSERT INTO t1(id,x) VALUES
720 (1, '{"a":null}'),
721 (2, '{"a":123}'),
722 (3, '{"a":4.5}'),
723 (4, '{"a":"six"}'),
724 (5, '{"a":[7,8]}'),
725 (6, '{"a":{"b":9}}'),
726 (7, '{"b":999}');
727 SELECT
728 id,
729 x->'a' AS '->',
730 CASE WHEN subtype(x->'a') THEN 'json' ELSE typeof(x->'a') END AS 'type',
731 x->>'a' AS '->>',
732 CASE WHEN subtype(x->>'a') THEN 'json' ELSE typeof(x->>'a') END AS 'type',
733 json_extract(x,'$.a') AS 'json_extract',
734 CASE WHEN subtype(json_extract(x,'$.a'))
735 THEN 'json' ELSE typeof(json_extract(x,'$.a')) END AS 'type'
736 FROM t1 ORDER BY id;
737 } [list \
738 1 null json {} null {} null \
739 2 123 json 123 integer 123 integer \
740 3 4.5 json 4.5 real 4.5 real \
741 4 {"six"} json six text six text \
742 5 {[7,8]} json {[7,8]} text {[7,8]} json \
743 6 {{"b":9}} json {{"b":9}} text {{"b":9}} json \
744 7 {} null {} null {} null
745 ]
746 do_execsql_test json102-1610 {
747 DELETE FROM t1;
748 INSERT INTO t1(x) VALUES('[null,123,4.5,"six",[7,8],{"b":9}]');
749 WITH c(y) AS (VALUES(0),(1),(2),(3),(4),(5),(6))
750 SELECT
751 y,
752 x->y AS '->',
753 CASE WHEN subtype(x->y) THEN 'json' ELSE typeof(x->y) END AS 'type',
754 x->>y AS '->>',
755 CASE WHEN subtype(x->>y) THEN 'json' ELSE typeof(x->>y) END AS 'type',
756 json_extract(x,format('$[%d]',y)) AS 'json_extract',
757 CASE WHEN subtype(json_extract(x,format('$[%d]',y)))
758 THEN 'json' ELSE typeof(json_extract(x,format('$[%d]',y))) END AS 'type'
759 FROM c, t1 ORDER BY y;
760 } [list \
761 0 null json {} null {} null \
762 1 123 json 123 integer 123 integer \
763 2 4.5 json 4.5 real 4.5 real \
764 3 {"six"} json six text six text \
765 4 {[7,8]} json {[7,8]} text {[7,8]} json \
766 5 {{"b":9}} json {{"b":9}} text {{"b":9}} json \
767 6 {} null {} null {} null
768 ]
769 do_execsql_test json102-1620 {
770 DELETE FROM t1;
771 INSERT INTO t1(x) VALUES('[null,123,4.5,"six",[7,8],{"b":9}]');
772 WITH c(y) AS (VALUES(0),(1),(2),(3),(4),(5),(6))
773 SELECT
774 y,
775 x->y AS '->',
776 CASE WHEN subtype(if(json_valid(x),x->y)) THEN 'json'
777 ELSE typeof(x->y) END AS 'type',
778 x->>y AS '->>',
779 CASE WHEN subtype(x->>y) THEN 'json' ELSE typeof(x->>y) END AS 'type',
780 json_extract(x,format('$[%d]',y)) AS 'json_extract',
781 CASE WHEN subtype(json_extract(x,format('$[%d]',y)))
782 THEN 'json' ELSE typeof(json_extract(x,format('$[%d]',y))) END AS 'type'
783 FROM c, t1 ORDER BY y;
784 } [list \
785 0 null json {} null {} null \
786 1 123 json 123 integer 123 integer \
787 2 4.5 json 4.5 real 4.5 real \
788 3 {"six"} json six text six text \
789 4 {[7,8]} json {[7,8]} text {[7,8]} json \
790 5 {{"b":9}} json {{"b":9}} text {{"b":9}} json \
791 6 {} null {} null {} null
792 ]
793
794 reset_db
795 do_execsql_test json102-1700 {
796 CREATE TABLE t1(a1 DATE, a2 INTEGER PRIMARY KEY, a3 INTEGER, memo TEXT);
797 CREATE INDEX t1x1 ON t1(a3, a1, memo->>'y');
798 INSERT INTO t1(a2,a1,a3,memo) VALUES (876, '2023-08-03', 5, '{"x":77,"y":4}');
799 }
800 do_execsql_test json102-1710 {
801 UPDATE t1 SET memo = JSON_REMOVE(memo, '$.y');
802 PRAGMA integrity_check;
803 SELECT * FROM t1;
804 } {ok 2023-08-03 876 5 {{"x":77}}}
805 do_execsql_test json102-1720 {
806 UPDATE t1 SET memo = JSON_SET(memo, '$.y', 6)
807 WHERE a2 IN (876) AND JSON_TYPE(memo, '$.y') IS NULL;
808 PRAGMA integrity_check;
809 SELECT * FROM t1;
810 } {ok 2023-08-03 876 5 {{"x":77,"y":6}}}
811
812 # 2024-05-21 https://sqlite.org/forum/forumpost/9e52cdfe15c3926e
813 # What if the RHS of the -> or ->> operator is a string that looks
814 # like a number? PostgreSQL treats it as a string.
815 #
816 do_execsql_test json102-1800 {
817 SELECT '{"1":"one","2":"two","3":"three"}'->>'2';
818 } two
819 db null NULL
820 do_execsql_test json102-1801 {
821 SELECT '{"1":"one","2":"two","3":"three"}'->>2;
822 } NULL
823 do_execsql_test json102-1810 {
824 SELECT '["zero","one","two"]'->>'1';
825 } NULL
826 do_execsql_test json102-1811 {
827 SELECT '["zero","one","two"]'->>1;
828 } one
829 do_execsql_test json102-1820 {
830 SELECT '{"1":"one","2":"two","3":"three"}'->'2';
831 } {{"two"}}
832 do_execsql_test json102-1821 {
833 SELECT '{"1":"one","2":"two","3":"three"}'->2;
834 } {NULL}
835 do_execsql_test json102-1830 {
836 SELECT '["zero","one","two"]'->'1';
837 } {NULL}
838 do_execsql_test json102-1831 {
839 SELECT '["zero","one","two"]'->1;
840 } {{"one"}}
841
842
843 finish_test