test/json102.test

   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-110 {
  25   SELECT json_object('ex',json('[52,3.14159]'));
  26 } {{{"ex":[52,3.14159]}}}
  27 do_execsql_test json102-120 {
  28   SELECT json_object('ex',json_array(52,3.14159));
  29 } {{{"ex":[52,3.14159]}}}
  30 do_execsql_test json102-130 {
  31   SELECT json(' { "this" : "is", "a": [ "test" ] } ');
  32 } {{{"this":"is","a":["test"]}}}
  33 do_execsql_test json102-140 {
  34   SELECT json_array(1,2,'3',4);
  35 } {{[1,2,"3",4]}}
  36 do_execsql_test json102-150 {
  37   SELECT json_array('[1,2]');
  38 } {{["[1,2]"]}}
  39 do_execsql_test json102-160 {
  40   SELECT json_array(json_array(1,2));
  41 } {{[[1,2]]}}
  42 do_execsql_test json102-170 {
  43   SELECT json_array(1,null,'3','[4,5]','{"six":7.7}');
  44 } {{[1,null,"3","[4,5]","{\"six\":7.7}"]}}
  45 do_execsql_test json102-180 {
  46   SELECT json_array(1,null,'3',json('[4,5]'),json('{"six":7.7}'));
  47 } {{[1,null,"3",[4,5],{"six":7.7}]}}
  48 do_execsql_test json102-190 {
  49   SELECT json_array_length('[1,2,3,4]');
  50 } {{4}}
  51 do_execsql_test json102-191 {
  52   SELECT json_array_length( json_remove('[1,2,3,4]','$[2]') );
  53 } {{3}}
  54 do_execsql_test json102-200 {
  55   SELECT json_array_length('[1,2,3,4]', '$');
  56 } {{4}}
  57 do_execsql_test json102-210 {
  58   SELECT json_array_length('[1,2,3,4]', '$[2]');
  59 } {{0}}
  60 do_execsql_test json102-220 {
  61   SELECT json_array_length('{"one":[1,2,3]}');
  62 } {{0}}
  63 do_execsql_test json102-230 {
  64   SELECT json_array_length('{"one":[1,2,3]}', '$.one');
  65 } {{3}}
  66 do_execsql_test json102-240 {
  67   SELECT json_array_length('{"one":[1,2,3]}', '$.two');
  68 } {{}}
  69 do_execsql_test json102-250 {
  70   SELECT json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$');
  71 } {{{"a":2,"c":[4,5,{"f":7}]}}}
  72 do_execsql_test json102-260 {
  73   SELECT json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.c');
  74 } {{[4,5,{"f":7}]}}
  75 do_execsql_test json102-270 {
  76   SELECT json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.c[2]');
  77 } {{{"f":7}}}
  78 do_execsql_test json102-280 {
  79   SELECT json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.c[2].f');
  80 } {{7}}
  81 do_execsql_test json102-290 {
  82   SELECT json_extract('{"a":2,"c":[4,5],"f":7}','$.c','$.a');
  83 } {{[[4,5],2]}}
  84 do_execsql_test json102-300 {
  85   SELECT json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.x');
  86 } {{}}
  87 do_execsql_test json102-310 {
  88   SELECT json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.x', '$.a');
  89 } {{[null,2]}}
  90 do_execsql_test json102-320 {
  91   SELECT json_insert('{"a":2,"c":4}', '$.a', 99);
  92 } {{{"a":2,"c":4}}}
  93 do_execsql_test json102-330 {
  94   SELECT json_insert('{"a":2,"c":4}', '$.e', 99);
  95 } {{{"a":2,"c":4,"e":99}}}
  96 do_execsql_test json102-340 {
  97   SELECT json_replace('{"a":2,"c":4}', '$.a', 99);
  98 } {{{"a":99,"c":4}}}
  99 do_execsql_test json102-350 {
 100   SELECT json_replace('{"a":2,"c":4}', '$.e', 99);
 101 } {{{"a":2,"c":4}}}
 102 do_execsql_test json102-360 {
 103   SELECT json_set('{"a":2,"c":4}', '$.a', 99);
 104 } {{{"a":99,"c":4}}}
 105 do_execsql_test json102-370 {
 106   SELECT json_set('{"a":2,"c":4}', '$.e', 99);
 107 } {{{"a":2,"c":4,"e":99}}}
 108 do_execsql_test json102-380 {
 109   SELECT json_set('{"a":2,"c":4}', '$.c', '[97,96]');
 110 } {{{"a":2,"c":"[97,96]"}}}
 111 do_execsql_test json102-390 {
 112   SELECT json_set('{"a":2,"c":4}', '$.c', json('[97,96]'));
 113 } {{{"a":2,"c":[97,96]}}}
 114 do_execsql_test json102-400 {
 115   SELECT json_set('{"a":2,"c":4}', '$.c', json_array(97,96));
 116 } {{{"a":2,"c":[97,96]}}}
 117 do_execsql_test json102-410 {
 118   SELECT json_object('a',2,'c',4);
 119 } {{{"a":2,"c":4}}}
 120 do_execsql_test json102-420 {
 121   SELECT json_object('a',2,'c','{e:5}');
 122 } {{{"a":2,"c":"{e:5}"}}}
 123 do_execsql_test json102-430 {
 124   SELECT json_object('a',2,'c',json_object('e',5));
 125 } {{{"a":2,"c":{"e":5}}}}
 126 do_execsql_test json102-440 {
 127   SELECT json_remove('[0,1,2,3,4]','$[2]');
 128 } {{[0,1,3,4]}}
 129 do_execsql_test json102-450 {
 130   SELECT json_remove('[0,1,2,3,4]','$[2]','$[0]');
 131 } {{[1,3,4]}}
 132 do_execsql_test json102-460 {
 133   SELECT json_remove('[0,1,2,3,4]','$[0]','$[2]');
 134 } {{[1,2,4]}}
 135 do_execsql_test json102-470 {
 136   SELECT json_remove('{"x":25,"y":42}');
 137 } {{{"x":25,"y":42}}}
 138 do_execsql_test json102-480 {
 139   SELECT json_remove('{"x":25,"y":42}','$.z');
 140 } {{{"x":25,"y":42}}}
 141 do_execsql_test json102-490 {
 142   SELECT json_remove('{"x":25,"y":42}','$.y');
 143 } {{{"x":25}}}
 144 do_execsql_test json102-500 {
 145   SELECT json_remove('{"x":25,"y":42}','$');
 146 } {{}}
 147 do_execsql_test json102-510 {
 148   SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}');
 149 } {{object}}
 150 do_execsql_test json102-520 {
 151   SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}','$');
 152 } {{object}}
 153 do_execsql_test json102-530 {
 154   SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a');
 155 } {{array}}
 156 do_execsql_test json102-540 {
 157   SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[0]');
 158 } {{integer}}
 159 do_execsql_test json102-550 {
 160   SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[1]');
 161 } {{real}}
 162 do_execsql_test json102-560 {
 163   SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[2]');
 164 } {{true}}
 165 do_execsql_test json102-570 {
 166   SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[3]');
 167 } {{false}}
 168 do_execsql_test json102-580 {
 169   SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[4]');
 170 } {{null}}
 171 do_execsql_test json102-590 {
 172   SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[5]');
 173 } {{text}}
 174 do_execsql_test json102-600 {
 175   SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[6]');
 176 } {{}}
 177 do_execsql_test json102-610 {
 178   SELECT json_valid(char(123)||'"x":35'||char(125));
 179 } {{1}}
 180 do_execsql_test json102-620 {
 181   SELECT json_valid(char(123)||'"x":35');
 182 } {{0}}
 183
 184 ifcapable vtab {
 185 do_execsql_test json102-1000 {
 186   CREATE TABLE user(name,phone);
 187   INSERT INTO user(name,phone) VALUES
 188      ('Alice','["919-555-2345","804-555-3621"]'),
 189      ('Bob','["201-555-8872"]'),
 190      ('Cindy','["704-555-9983"]'),
 191      ('Dave','["336-555-8421","704-555-4321","803-911-4421"]');
 192   SELECT DISTINCT user.name
 193     FROM user, json_each(user.phone)
 194    WHERE json_each.value LIKE '704-%'
 195    ORDER BY 1;
 196 } {Cindy Dave}
 197
 198 do_execsql_test json102-1010 {
 199   UPDATE user
 200      SET phone=json_extract(phone,'$[0]')
 201    WHERE json_array_length(phone)<2;
 202   SELECT name, substr(phone,1,5) FROM user ORDER BY name;
 203 } {Alice {["919} Bob 201-5 Cindy 704-5 Dave {["336}}
 204 do_execsql_test json102-1011 {
 205   SELECT name FROM user WHERE phone LIKE '704-%'
 206   UNION
 207   SELECT user.name
 208     FROM user, json_each(user.phone)
 209    WHERE json_valid(user.phone)
 210      AND json_each.value LIKE '704-%';
 211 } {Cindy Dave}
 212
 213 do_execsql_test json102-1100 {
 214   CREATE TABLE big(json JSON);
 215   INSERT INTO big(json) VALUES('{
 216     "id":123,
 217     "stuff":[1,2,3,4],
 218     "partlist":[
 219        {"uuid":"bb108722-572e-11e5-9320-7f3b63a4ca74"},
 220        {"uuid":"c690dc14-572e-11e5-95f9-dfc8861fd535"},
 221        {"subassembly":[
 222           {"uuid":"6fa5181e-5721-11e5-a04e-57f3d7b32808"}
 223        ]}
 224     ]
 225   }');
 226   INSERT INTO big(json) VALUES('{
 227     "id":456,
 228     "stuff":["hello","world","xyzzy"],
 229     "partlist":[
 230        {"uuid":false},
 231        {"uuid":"c690dc14-572e-11e5-95f9-dfc8861fd535"}
 232     ]
 233   }');
 234 } {}
 235 set correct_answer [list \
 236     1 {$.id} 123 \
 237     1 {$.stuff[0]} 1 \
 238     1 {$.stuff[1]} 2 \
 239     1 {$.stuff[2]} 3 \
 240     1 {$.stuff[3]} 4 \
 241     1 {$.partlist[0].uuid} bb108722-572e-11e5-9320-7f3b63a4ca74 \
 242     1 {$.partlist[1].uuid} c690dc14-572e-11e5-95f9-dfc8861fd535 \
 243     1 {$.partlist[2].subassembly[0].uuid} 6fa5181e-5721-11e5-a04e-57f3d7b32808 \
 244     2 {$.id} 456 \
 245     2 {$.stuff[0]} hello \
 246     2 {$.stuff[1]} world \
 247     2 {$.stuff[2]} xyzzy \
 248     2 {$.partlist[0].uuid} 0 \
 249     2 {$.partlist[1].uuid} c690dc14-572e-11e5-95f9-dfc8861fd535]
 250 do_execsql_test json102-1110 {
 251   SELECT big.rowid, fullkey, value
 252     FROM big, json_tree(big.json)
 253    WHERE json_tree.type NOT IN ('object','array')
 254    ORDER BY +big.rowid, +json_tree.id
 255 } $correct_answer
 256 do_execsql_test json102-1120 {
 257   SELECT big.rowid, fullkey, atom
 258     FROM big, json_tree(big.json)
 259    WHERE atom IS NOT NULL
 260    ORDER BY +big.rowid, +json_tree.id
 261 } $correct_answer
 262
 263 do_execsql_test json102-1130 {
 264   SELECT DISTINCT json_extract(big.json,'$.id')
 265     FROM big, json_tree(big.json,'$.partlist')
 266    WHERE json_tree.key='uuid'
 267      AND json_tree.value='6fa5181e-5721-11e5-a04e-57f3d7b32808';
 268 } {123}
 269 do_execsql_test json102-1131 {
 270   SELECT DISTINCT json_extract(big.json,'$.id')
 271     FROM big, json_tree(big.json,'$')
 272    WHERE json_tree.key='uuid'
 273      AND json_tree.value='6fa5181e-5721-11e5-a04e-57f3d7b32808';
 274 } {123}
 275 do_execsql_test json102-1132 {
 276   SELECT DISTINCT json_extract(big.json,'$.id')
 277     FROM big, json_tree(big.json)
 278    WHERE json_tree.key='uuid'
 279      AND json_tree.value='6fa5181e-5721-11e5-a04e-57f3d7b32808';
 280 } {123}
 281 } ;# end ifcapable vtab
 282
 283 #-------------------------------------------------------------------------
 284 # Test that json_valid() correctly identifies non-ascii range
 285 # characters as non-whitespace.
 286 #
 287 do_execsql_test json102-1201 { SELECT json_valid(char(32)  || '"xyz"') } 1
 288 do_execsql_test json102-1202 { SELECT json_valid(char(200) || '"xyz"') } 0
 289
 290 # Off-by-one error in jsonAppendString()
 291 #
 292 for {set i 0} {$i<100} {incr i} {
 293   set str abcdef[string repeat \" [expr {$i+50}]]uvwxyz
 294   do_test json102-[format %d [expr {$i+1300}]] {
 295     db eval {SELECT json_extract(json_array($::str),'$[0]')==$::str}
 296   } {1}
 297 }
 298
 299 #-------------------------------------------------------------------------
 300 # 2017-04-08 ticket b93be8729a895a528e2849fca99f7
 301 # JSON extension accepts invalid numeric values
 302 #
 303 # JSON does not allow leading zeros.  But the JSON extension was
 304 # allowing them.  The following tests verify that the problem is now
 305 # fixed.
 306 #
 307 foreach {id j x0 x5} {
 308   1401 {'{"x":01}'} 0 0
 309   1402 {'{"x":-01}'} 0 0
 310   1403 {'{"x":0}'} 1 1
 311   1404 {'{"x":-0}'} 1 1
 312   1405 {'{"x":0.1}'} 1 1
 313   1406 {'{"x":-0.1}'} 1 1
 314   1407 {'{"x":0.0000}'} 1 1
 315   1408 {'{"x":-0.0000}'} 1 1
 316   1409 {'{"x":01.5}'} 0 0
 317   1410 {'{"x":-01.5}'} 0 0
 318   1411 {'{"x":00}'} 0 0
 319   1412 {'{"x":-00}'} 0 0
 320   1413 {'{"x":+0}'} 0 1
 321   1414 {'{"x":+5}'} 0 1
 322   1415 {'{"x":+5.5}'} 0 1
 323 } {
 324   do_execsql_test json102-$id "
 325      SELECT json_valid($j), NOT json_error_position($j);
 326   " [list $x0 $x5]
 327 }
 328
 329 #------------------------------------------------------------------------
 330 # 2017-04-10 ticket 6c9b5514077fed34551f98e64c09a10dc2fc8e16
 331 # JSON extension accepts strings containing control characters.
 332 #
 333 # The JSON spec requires that all control characters be escaped.
 334 #
 335 do_execsql_test json102-1500 {
 336   WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<0x20)
 337   SELECT x FROM c WHERE json_valid(printf('{"a":"x%sz"}', char(x))) ORDER BY x;
 338 } {32}
 339
 340 # All control characters are escaped
 341 #
 342 do_execsql_test json102-1501 {
 343   WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<0x1f)
 344   SELECT sum(json_valid(json_quote('a'||char(x)||'z'))) FROM c ORDER BY x;
 345 } {31}
 346
 347 # 2022-01-10 tests for -> and ->> operators
 348 #
 349 reset_db
 350 do_execsql_test json102-1600 {
 351   CREATE TABLE t1(id INTEGER PRIMARY KEY, x JSON);
 352   INSERT INTO t1(id,x) VALUES
 353    (1, '{"a":null}'),
 354    (2, '{"a":123}'),
 355    (3, '{"a":4.5}'),
 356    (4, '{"a":"six"}'),
 357    (5, '{"a":[7,8]}'),
 358    (6, '{"a":{"b":9}}'),
 359    (7, '{"b":999}');
 360   SELECT
 361     id,
 362     x->'a' AS '->',
 363     CASE WHEN subtype(x->'a') THEN 'json' ELSE typeof(x->'a') END AS 'type',
 364     x->>'a' AS '->>',
 365     CASE WHEN subtype(x->>'a') THEN 'json' ELSE typeof(x->>'a') END AS 'type',
 366     json_extract(x,'$.a') AS 'json_extract',
 367     CASE WHEN subtype(json_extract(x,'$.a'))
 368          THEN 'json' ELSE typeof(json_extract(x,'$.a')) END AS 'type'
 369     FROM t1 ORDER BY id;
 370 } [list \
 371   1 null      json {}        null     {}        null          \
 372   2 123       json 123       integer  123       integer       \
 373   3 4.5       json 4.5       real     4.5       real          \
 374   4 {"six"}   json six       text     six       text          \
 375   5 {[7,8]}   json {[7,8]}   text     {[7,8]}   json          \
 376   6 {{"b":9}} json {{"b":9}} text     {{"b":9}} json          \
 377   7 {}        null {}        null     {}        null
 378 ]
 379 do_execsql_test json102-1610 {
 380   DELETE FROM t1;
 381   INSERT INTO t1(x) VALUES('[null,123,4.5,"six",[7,8],{"b":9}]');
 382   WITH c(y) AS (VALUES(0),(1),(2),(3),(4),(5),(6))
 383   SELECT
 384     y,
 385     x->y AS '->',
 386     CASE WHEN subtype(x->y) THEN 'json' ELSE typeof(x->y) END AS 'type',
 387     x->>y AS '->>',
 388     CASE WHEN subtype(x->>y) THEN 'json' ELSE typeof(x->>y) END AS 'type',
 389     json_extract(x,format('$[%d]',y)) AS 'json_extract',
 390     CASE WHEN subtype(json_extract(x,format('$[%d]',y)))
 391       THEN 'json' ELSE typeof(json_extract(x,format('$[%d]',y))) END AS 'type'
 392   FROM c, t1 ORDER BY y;
 393 } [list \
 394   0 null      json {}        null    {}        null       \
 395   1 123       json 123       integer 123       integer    \
 396   2 4.5       json 4.5       real    4.5       real       \
 397   3 {"six"}   json six       text    six       text       \
 398   4 {[7,8]}   json {[7,8]}   text    {[7,8]}   json       \
 399   5 {{"b":9}} json {{"b":9}} text    {{"b":9}} json       \
 400   6 {}        null {}        null    {}        null
 401 ]
 402
 403 reset_db
 404 do_execsql_test json102-1700 {
 405   CREATE TABLE t1(a1 DATE, a2 INTEGER PRIMARY KEY, a3 INTEGER, memo TEXT);
 406   CREATE INDEX t1x1 ON t1(a3, a1, memo->>'y');
 407   INSERT INTO t1(a2,a1,a3,memo) VALUES (876, '2023-08-03', 5, '{"x":77,"y":4}');
 408 }
 409 do_execsql_test json102-1710 {
 410   UPDATE t1 SET memo = JSON_REMOVE(memo, '$.y');
 411   PRAGMA integrity_check;
 412   SELECT * FROM t1;
 413 } {ok 2023-08-03 876 5 {{"x":77}}}
 414 do_execsql_test json102-1720 {
 415   UPDATE t1 SET memo = JSON_SET(memo, '$.y', 6)
 416     WHERE a2 IN (876) AND JSON_TYPE(memo, '$.y') IS NULL;
 417   PRAGMA integrity_check;
 418   SELECT * FROM t1;
 419 } {ok 2023-08-03 876 5 {{"x":77,"y":6}}}
 420
 421 finish_test