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