diff options
Diffstat (limited to '')
| -rw-r--r-- | test/json/README.md | 66 | ||||
| -rw-r--r-- | test/json/json-generator.tcl | 401 | ||||
| -rw-r--r-- | test/json/json-q1.txt | 24 | ||||
| -rwxr-xr-x | test/json/json-speed-check.sh | 87 | ||||
| -rw-r--r-- | test/json/jsonb-q1.txt | 24 | ||||
| -rw-r--r-- | test/json101.test | 1167 | ||||
| -rw-r--r-- | test/json102.test | 767 | ||||
| -rw-r--r-- | test/json103.test | 93 | ||||
| -rw-r--r-- | test/json104.test | 195 | ||||
| -rw-r--r-- | test/json105.test | 113 | ||||
| -rw-r--r-- | test/json106.test | 73 | ||||
| -rw-r--r-- | test/json107.test | 86 | ||||
| -rw-r--r-- | test/json501.test | 309 | ||||
| -rw-r--r-- | test/json502.test | 67 | ||||
| -rw-r--r-- | test/jsonb01.test | 53 |
15 files changed, 3525 insertions, 0 deletions
diff --git a/test/json/README.md b/test/json/README.md new file mode 100644 index 0000000..4ebbda6 --- /dev/null +++ b/test/json/README.md @@ -0,0 +1,66 @@ +The files in this subdirectory are used to help measure the performance +of the SQLite JSON functions, especially in relation to handling large +JSON inputs. + +# 1.0 Prerequisites + + * Standard SQLite build environment (SQLite source tree, compiler, make, etc.) + + * Valgrind + + * Fossil (only the "fossil xdiff" command is used by this procedure) + + * tclsh + +# 2.0 Setup + + * Run: "`tclsh json-generator.tcl | sqlite3 json100mb.db`" to create + the 100 megabyte test database. Do this so that the "json100mb.db" + file lands in the directory from which you will run tests, not in + the test/json subdirectory of the source tree. + + * Make a copy of "json100mb.db" into "jsonb100mb.db" - change the prefix + from "json" to "jsonb". + + * Bring up jsonb100mb.db in the sqlite3 command-line shell. + Convert all of the content into JSONB using a commands like this: + +> UPDATE data1 SET x=jsonb(x); +> VACUUM; + + * Build the baseline sqlite3.c file with sqlite3.h and shell.c. + +> make clean sqlite3.c + + * Run "`sh json-speed-check.sh trunk`". This creates the baseline + profile in "jout-trunk.txt" for the preformance test using text JSON. + + * Run "`sh json-speed-check.sh trunk --jsonb`". This creates the + baseline profile in "joutb-trunk.txt" for the performance test + for processing JSONB + + * (Optional) Verify that the json100mb.db database really does contain + approximately 100MB of JSON content by running: + +> SELECT sum(length(x)) FROM data1; +> SELECT * FROM data1 WHERE NOT json_valid(x); + +# 3.0 Testing + + * Build the sqlite3.c (with sqlite3.h and shell.c) to be tested. + + * Run "`sh json-speed-check.sh x1`". The profile output will appear + in jout-x1.txt. Substitute any label you want in place of "x1". + + * Run "`sh json-speed-check.sh x1 --jsonb`". The profile output will appear + in joutb-x1.txt. Substitute any label you want in place of "x1". + + * Run the script shown below in the CLI. + Divide 2500 by the real elapse time from this test + to get an estimate for number of MB/s that the JSON parser is + able to process. + +> .open json100mb.db +> .timer on +> WITH RECURSIVE c(n) AS (VALUES(1) UNION ALL SELECT n+1 FROM c WHERE n<25) +> SELECT sum(json_valid(x)) FROM c, data1; diff --git a/test/json/json-generator.tcl b/test/json/json-generator.tcl new file mode 100644 index 0000000..d499bc7 --- /dev/null +++ b/test/json/json-generator.tcl @@ -0,0 +1,401 @@ +#!/usr/bin/tclsh +# +# Generate SQL that will populate an SQLite database with about 100 megabytes +# of pseudo-random JSON text. +# +# tclsh json-generator.tcl | sqlite3 json110mb.db +# +# srand() is used to initialize the random seed so that the same JSON +# is generated for every run. +# +expr srand(12345678) +set wordlist { + ability able abroad access account act + action active actor add address adept + adroit advance advice affect age ageless + agency agent agile agree air airfare + airline airport alert almond alpha always + amend amount amplify analyst anchor angel + angelic angle ankle annual answer antique + anybody anyhow appeal apple apricot apt + area argon arm army arrival arsenic + art artful article arugula aside ask + aspect assist assume atom atone attempt + author autumn average avocado award awl + azure back bacon bag bagel bake + baker balance ball balloon bamboo banana + band banjo bank barium base basil + basin basis basket bass bat bath + battery beach beak bean bear bearcub + beauty beef beet beige being bell + belly belt bench bend benefit best + beta better beyond bicycle bid big + bike bill bird biscuit bismuth bisque + bit black blank blest blind bliss + block bloom blue board boat body + bokchoy bone bonus book bookish boot + border boron boss bossy bottle bottom + bow bowl bowtie box brain brainy + branch brave bravely bread break breath + breezy brick bridge brie brief briefly + bright broad broil bromine bronze brother + brow brown brush buddy budget buffalo + bug bugle bull bunch burger burly + burrito bus busy butter button buy + buyer byte cab cabbage cabinet cable + cadet cadmium caesium cake calcium caliper + call caller calm calmly camera camp + can canary cancel candle candy cap + capable caper capital captain car carbon + card care career careful carp carpet + carrot carry case cash cassava casual + cat catch catfish catsear catsup cause + cave celery cell century chain chair + chalk chance change channel chapter chard + charge charity chart check cheddar cheery + cheese chicken chicory chiffon child chin + chip chives choice chowder chum church + circle city claim clam class classic + classy clay clean cleaner clear clearly + clerk click client climate clock clorine + closet clothes cloud clown club clue + cluster coach coast coat cobbler cobolt + cod code coffee colby cold collar + college comb combine comet comfort command + comment common company complex concept concern + concert conduit consist contact contest context + control convert cook cookie copilot copper + copy coral cordial corn corner corny + correct cost count counter country county + couple courage course court cover cow + cowbird crab crack craft crash crazy + cream credit creek cress crevice crew + crimson croaker crop cross crowd cube + cuckoo cuisine culture cup current curve + cut cyan cycle dagger daily dance + dare darter data date day daylily + deal dear dearly debate debit decade + decimal deep deft deftly degree delay + deluxe deposit depth design desk detail + device dew diamond diet dig dill + dinner dip direct dirt dish disk + display diver divide divine doctor dodger + donut door dot double dough draft + drag dragon drama draw drawer drawing + dream drill drink drive driver drop + drum dry dryer drywall duck due + dump dusk dust duty dye eagle + ear earring earth ease east easy + eat economy edge editor eel effect + effort egg eight elbow elegant element + elf elk email emerald employ end + endive endless energy engine enjoy enter + entry equal equip error escape essay + eternal evening event exam example excuse + exit expert extent extreme eye face + fact factor factual fail failure fair + fajita fall family fan fang farm + farmer fat fault feature feed feel + feeling fench fennel festive few fiber + field fig figure file fill film + filter final finance finding finger finish + fire fish fishing fit fitting five + fix flier flight floor floral florine + flour flow flower fly flying focus + fold folding food foot force forest + forever forgive form formal format fortune + forum frame free freedom freely fresh + friend frog front fruit fuchsia fuel + fun funny future gain galaxy gallium + game gamma gap garage garden garlic + gas gate gather gauge gear gem + gene general gentle gently gherkin ghost + gift give glad glass gleeful glossy + glove glue goal goat goby gold + goldeye golf good gouda goulash gourd + grab grace grade gram grand grape + grapes grass gravy gray great green + grits grocery ground group grouper grout + growth guard guave guess guest guide + guitar gumbo guppy habit hacksaw haddock + hafnium hagfish hair half halibut hall + hammer hand handle handy hanger happy + hat havarti hay haybale head health + healthy hearing heart hearty heat heavy + heel height helium hello help helpful + herald herring hide high highly highway + hill hip hipster hire history hit + hoki hold hole holiday holly home + honest honey hook hope hopeful horizon + horn horse host hotel hour house + housing human humane humor hunt hurry + ice icecube icefish icy idea ideal + image impact impress inch income indigo + initial inkpen insect inside intense invite + iodine iridium iron island issue item + ivory jacket jargon javelin jello jelly + jewel job jocund join joint joke + jovial joy joyful joyous judge juice + jump junior jury just justice kale + keel keep kelp ketchup key keyhole + keyway khaki kick kid kidney kiloohm + kind kindly king kitchen kite kiwi + knee knife krill krypton kumquat lab + lace lack ladder lake lamp lamprey + land laser laugh law lawn lawyer + layer lead leader leading leaf leafy + league leather leave lecture leek leg + lemon length lentil lesson let letter + lettuce level library life lift light + lily lime limit line linen link + lip list listen lithium lively living + lizard load loan lobster local lock + log long longfin look lotus love + lovely loving low lucid luck luffa + lunch lung machine magenta magnet mail + main major make mall manager mango + manner many map march market maroon + martian master match math matter maximum + maybe meal meaning meat media medium + meet meeting melody melon member memory + mention menu mercury merry mess message + messy metal meter method micron middle + might mile milk mind mine minimum + minnow minor mint minute mirror miss + mission misty mix mixer mixture mobile + mode model moment monitor monk month + moon moray morning most motor mouse + mouth move mover movie much mud + mudfish muffin mullet munster muon muscle + music mustard nail name nation native + natural nature navy neat neatly nebula + neck needle neon nerve net network + neutron news nibble nice nickel night + niobium nobody noise noodle normal north + nose note nothing notice nova novel + number nurse nursery oar object offer + office officer oil okay okra old + olive one onion open opening opinion + option orange orbit orchid order oregano + other ounce outcome outside oven owner + oxygen oyster pace pack package page + pager paint pair pale pan pancake + papaya paper pardon parent park parking + parsley parsnip part partner party pass + passage past pasta path patient pattern + pause pay pea peace peach peacock + peahen peak peanut pear pearl pen + penalty pencil pension people pepper perch + perfect period permit person phase phone + photo phrase physics piano pick picture + pie piece pigeon pike pilot pin + pink pinkie pious pipe pitch pizza + place plan plane planet plant planter + plastic plate play player playful plenty + pliers plum pod poem poet poetry + point police policy pollock pony pool + pop popover poptart pork port portal + post pot potato pound powder power + present press price pride primary print + prior private prize problem process produce + product profile profit program project promise + prompt proof proper protein proton public + puff puffer pull pumpkin pup pupfish + pure purple purpose push put quality + quark quarter quiet quill quit quote + rabbit raccoon race radiant radio radish + radium radon rain rainbow raise ramp + ranch range rasp rate ratio ray + razor reach read reading real reality + reason recipe record recover red redeem + reed reef refuse region regret regular + relaxed release relief relish remote remove + rent repair repeat reply report request + reserve resist resolve resort rest result + return reveal review reward ribbon rice + rich ride ridge right ring rise + risk river rivet road roast rock + rocket role roll roof room rope + rose rough roughy round row royal + rub ruby rudder ruin rule run + runner rush rust sacred saddle safe + safety sail salad salami sale salmon + salt sample sand sander sandy sauce + save saving saw scale scampi scene + scheme school score screen script sea + search season seat second secret sector + seemly self sell senate senior sense + series serve set shake shape share + shark shell shift shine shiny ship + shock shoe shoot shop shovel show + side sign signal silk silly silver + simple sing singer single sink site + size skill skin sky slate sleep + sleepy slice slide slip smart smell + smelt smile smoke smooth snap snipe + snow snowy sock socket sodium soft + softly soil sole solid song sorrel + sort soul sound soup source south + space spare speech speed spell spend + sphere spice spider spirit spite split + spoon sport spot spray spread spring + squab square squash stable staff stage + stand staple star start state status + stay steak steel step stern stew + stick still stock stone stop store + storm story strain street stress strike + string stroke strong studio study stuff + style sugar suit sulfur summer sun + sunny sunset super superb surf survey + sweet swim swing switch symbol system + table tackle tail tale talk tan + tank tap tape target task taste + tau tea teach teal team tear + tell ten tender tennis tent term + test tetra text thanks theme theory + thing think thread throat thumb ticket + tidy tie tiger till time timely + tin tip title toast today toe + tomato tone tongue tool tooth top + topic total touch tough tour towel + tower town track trade train trash + travel tray treat tree trick trip + trout trowel truck trupet trust truth + try tube tuna tune turf turkey + turn turnip tutor tux tweet twist + two type union unique unit upbeat + upper use useful user usual valley + value van vase vast veil vein + velvet verse very vessel vest video + view violet visit visual vivid voice + volume vowel voyage waffle wait wake + walk wall warm warmth wasabi wash + watch water wave wax way wealth + wear web wedge week weekly weight + west whale what wheat wheel when + where while who whole why will + win wind window wing winner winter + wire wish witty wolf wonder wood + wool woolly word work worker world + worry worth worthy wrap wrench wrist + writer xenon yak yam yard yarrow + year yearly yellow yew yogurt young + youth zebra zephyr zinc zone zoo +} +set nwordlist [llength $wordlist] + +proc random_char {} { + return [string index \ + "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ" \ + [expr {int(rand()*52)}]] +} +proc random_label {} { + set label [random_char] + while {rand()>0.8} { + append label [random_char] + } + if {rand()>0.9} {append label -} + append label [format %d [expr {int(rand()*100)}]] + return $label +} +proc random_numeric {} { + set n [expr {(rand()*2-1.0)*1e6}] + switch [expr {int(rand()*6)}] { + 0 {set format %.3f} + 1 {set format %.6E} + 2 {set format %.4e} + default {set format %g} + } + return [format $format $n] +} + + +proc random_json {limit indent} { + global nwordlist wordlist + set res {} + if {$indent==0 || ($limit>0 && rand()>0.5)} { + incr limit -1 + incr indent 2 + set n [expr {int(rand()*5)+1}] + if {$n==5} {incr n [expr {int(rand()*10)}]} + if {rand()>0.5} { + set res \173\n + for {set i 0} {$i<$n} {incr i} { + append res [string repeat { } $indent] + if {rand()>0.8} { + if {rand()>0.5} { + set sep ":\n [string repeat { } $indent]" + } else { + set sep " : " + } + } else { + set sep : + } + append res \"[random_label]\"$sep[random_json $limit $indent] + if {$i<$n-1} {append res ,} + append res \n + } + incr indent -2 + append res [string repeat { } $indent] + append res \175 + return $res + } else { + set res \[\n + for {set i 0} {$i<$n} {incr i} { + append res [string repeat { } $indent] + append res [random_json $limit $indent] + if {$i<$n-1} {append res ,} + append res \n + } + incr indent -2 + append res [string repeat { } $indent] + append res \] + return $res + } + } elseif {rand()>0.9} { + if {rand()>0.7} {return "true"} + if {rand()>0.5} {return "false"} + return "null" + } elseif {rand()>0.5} { + return [random_numeric] + } else { + set res \" + set n [expr {int(rand()*4)+1}] + if {$n>=4} {set n [expr {$n+int(rand()*6)}]} + for {set i 0} {$i<$n} {incr i} { + if {rand()<0.05} { + set w [random_numeric] + } else { + set k [expr {int(rand()*$nwordlist)}] + set w [lindex $wordlist $k] + } + if {rand()<0.07} { + set w \\\"$w\\\" + } + if {$i<$n-1} { + switch [expr {int(rand()*9)}] { + 0 {set sp {, }} + 1 {set sp "\\n "} + 2 {set sp "-"} + default {set sp { }} + } + append res $w$sp + } else { + append res $w + if {rand()<0.2} {append res .} + } + } + return $res\" + } +} + +puts "CREATE TABLE IF NOT EXISTS data1(x JSON);" +puts "BEGIN;" +set sz 0 +for {set i 0} {$sz<100000000} {incr i} { + set j [random_json 7 0] + incr sz [string length $j] + puts "INSERT INTO data1(x) VALUES('$j');" +} +puts "COMMIT;" +puts "SELECT sum(length(x)) FROM data1;" diff --git a/test/json/json-q1.txt b/test/json/json-q1.txt new file mode 100644 index 0000000..d122a2d --- /dev/null +++ b/test/json/json-q1.txt @@ -0,0 +1,24 @@ +.mode qbox +.timer on +.param set $label 'q87' +SELECT rowid, x->>$label FROM data1 WHERE x->>$label IS NOT NULL; + +CREATE TEMP TABLE t2(x JSON TEXT); +WITH RECURSIVE + c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<25000), + array1(y) AS ( + SELECT json_group_array( + json_object('x',x,'y',random(),'z',hex(randomblob(50))) + ) + FROM c + ), + c2(n) AS (VALUES(1) UNION ALL SELECT n+1 FROM c2 WHERE n<5) +INSERT INTO t2(x) + SELECT json_object('a',n,'b',n*2,'c',y,'d',3,'e',5,'f',6) FROM array1, c2; +CREATE INDEX t2x1 ON t2(x->>'a'); +CREATE INDEX t2x2 ON t2(x->>'b'); +CREATE INDEX t2x3 ON t2(x->>'e'); +CREATE INDEX t2x4 ON t2(x->>'f'); +UPDATE t2 SET x=json_replace(x,'$.f',(x->>'f')+1); +UPDATE t2 SET x=json_set(x,'$.e',(x->>'f')-1); +UPDATE t2 SET x=json_remove(x,'$.d'); diff --git a/test/json/json-speed-check.sh b/test/json/json-speed-check.sh new file mode 100755 index 0000000..682a7ae --- /dev/null +++ b/test/json/json-speed-check.sh @@ -0,0 +1,87 @@ +#!/bin/bash +# +# This is a template for a script used for day-to-day size and +# performance monitoring of SQLite. Typical usage: +# +# sh speed-check.sh trunk # Baseline measurement of trunk +# sh speed-check.sh x1 # Measure some experimental change +# fossil xdiff --tk jout-trunk.txt jout-x1.txt # View chanages +# +# There are multiple output files, all with a base name given by +# the first argument: +# +# summary-$BASE.txt # Copy of standard output +# jout-$BASE.txt # cachegrind output +# explain-$BASE.txt # EXPLAIN listings (only with --explain) +# +if test "$1" = "" +then + echo "Usage: $0 OUTPUTFILE [OPTIONS]" + exit +fi +NAME=$1 +shift +#CC_OPTS="-DSQLITE_ENABLE_RTREE -DSQLITE_ENABLE_MEMSYS5" +CC_OPTS="-DSQLITE_ENABLE_MEMSYS5" +CC=gcc +LEAN_OPTS="-DSQLITE_THREADSAFE=0" +LEAN_OPTS="$LEAN_OPTS -DSQLITE_DEFAULT_MEMSTATUS=0" +LEAN_OPTS="$LEAN_OPTS -DSQLITE_DEFAULT_WAL_SYNCHRONOUS=1" +LEAN_OPTS="$LEAN_OPTS -DSQLITE_LIKE_DOESNT_MATCH_BLOBS" +LEAN_OPTS="$LEAN_OPTS -DSQLITE_MAX_EXPR_DEPTH=0" +LEAN_OPTS="$LEAN_OPTS -DSQLITE_OMIT_DECLTYPE" +LEAN_OPTS="$LEAN_OPTS -DSQLITE_OMIT_DEPRECATED" +LEAN_OPTS="$LEAN_OPTS -DSQLITE_OMIT_PROGRESS_CALLBACK" +LEAN_OPTS="$LEAN_OPTS -DSQLITE_OMIT_SHARED_CACHE" +LEAN_OPTS="$LEAN_OPTS -DSQLITE_USE_ALLOCA" +BASELINE="trunk" +TYPE="json" +doExplain=0 +doCachegrind=1 +doVdbeProfile=0 +doWal=1 +doDiff=1 +doJsonB=0 +while test "$1" != ""; do + case $1 in + --nodiff) + doDiff=0 + ;; + --lean) + CC_OPTS="$CC_OPTS $LEAN_OPTS" + ;; + --clang) + CC=clang + ;; + --gcc7) + CC=gcc-7 + ;; + --jsonb) + doJsonB=1 + TYPE="jsonb" + ;; + -*) + CC_OPTS="$CC_OPTS $1" + ;; + *) + BASELINE=$1 + ;; + esac + shift +done +echo "NAME = $NAME" | tee summary-$NAME.txt +echo "CC_OPTS = $CC_OPTS" | tee -a summary-$NAME.txt +rm -f cachegrind.out.* jsonshell +$CC -g -Os -Wall -I. $CC_OPTS ./shell.c ./sqlite3.c -o jsonshell -ldl -lpthread +ls -l jsonshell | tee -a summary-$NAME.txt +home=`echo $0 | sed -e 's,/[^/]*$,,'` +DB=$TYPE''100mb.db +echo ./jsonshell $DB "<$home/$TYPE-q1.txt" +valgrind --tool=cachegrind ./jsonshell json100mb_b.db <$home/$TYPE-q1.txt \ + 2>&1 | tee -a summary-$NAME.txt +cg_anno.tcl cachegrind.out.* >$TYPE-$NAME.txt +echo '*****************************************************' >>$TYPE-$NAME.txt +sed 's/^[0-9=-]\{9\}/==00000==/' summary-$NAME.txt >>$TYPE-$NAME.txt +if test "$NAME" != "$BASELINE" -a $doDiff -ne 0; then + fossil xdiff --tk -c 20 $TYPE-$BASELINE.txt $TYPE-$NAME.txt +fi diff --git a/test/json/jsonb-q1.txt b/test/json/jsonb-q1.txt new file mode 100644 index 0000000..e78c636 --- /dev/null +++ b/test/json/jsonb-q1.txt @@ -0,0 +1,24 @@ +.mode qbox +.timer on +.param set $label 'q87' +SELECT rowid, x->>$label FROM data1 WHERE x->>$label IS NOT NULL; + +CREATE TEMP TABLE t2(x JSON TEXT); +WITH RECURSIVE + c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<25000), + array1(y) AS ( + SELECT json_group_array( + json_object('x',x,'y',random(),'z',hex(randomblob(50))) + ) + FROM c + ), + c2(n) AS (VALUES(1) UNION ALL SELECT n+1 FROM c2 WHERE n<5) +INSERT INTO t2(x) + SELECT jsonb_object('a',n,'b',n*2,'c',y,'d',3,'e',5,'f',6) FROM array1, c2; +CREATE INDEX t2x1 ON t2(x->>'a'); +CREATE INDEX t2x2 ON t2(x->>'b'); +CREATE INDEX t2x3 ON t2(x->>'e'); +CREATE INDEX t2x4 ON t2(x->>'f'); +UPDATE t2 SET x=jsonb_replace(x,'$.f',(x->>'f')+1); +UPDATE t2 SET x=jsonb_set(x,'$.e',(x->>'f')-1); +UPDATE t2 SET x=jsonb_remove(x,'$.d'); diff --git a/test/json101.test b/test/json101.test new file mode 100644 index 0000000..bae68e7 --- /dev/null +++ b/test/json101.test @@ -0,0 +1,1167 @@ +# 2015-08-12 +# +# The author disclaims copyright to this source code. In place of +# a legal notice, here is a blessing: +# +# May you do good and not evil. +# May you find forgiveness for yourself and forgive others. +# May you share freely, never taking more than you give. +# +#*********************************************************************** +# This file implements tests for JSON SQL functions extension to the +# SQLite library. +# + +set testdir [file dirname $argv0] +source $testdir/tester.tcl + +do_execsql_test json101-1.1.00 { + SELECT json_array(1,2.5,null,'hello'); +} {[1,2.5,null,"hello"]} +do_execsql_test json101-1.1.01 { + SELECT json_array(1,'{"abc":2.5,"def":null,"ghi":hello}',99); + -- the second term goes in as a string: +} {[1,"{\\"abc\\":2.5,\\"def\\":null,\\"ghi\\":hello}",99]} +do_execsql_test json101-1.1.02 { + SELECT json_array(1,json('{"abc":2.5,"def":null,"ghi":"hello"}'),99); + -- the second term goes in as JSON +} {[1,{"abc":2.5,"def":null,"ghi":"hello"},99]} +do_execsql_test json101-1.1.03 { + SELECT json_array(1,json_object('abc',2.5,'def',null,'ghi','hello'),99); + -- the second term goes in as JSON +} {[1,{"abc":2.5,"def":null,"ghi":"hello"},99]} +do_execsql_test json101-1.2 { + SELECT hex(json_array('String "\ Test')); +} {5B22537472696E67205C225C5C2054657374225D} +do_catchsql_test json101-1.3 { + SELECT json_array(1,printf('%.1000c','x'),x'abcd',3); +} {1 {JSON cannot hold BLOB values}} +do_catchsql_test json101-1.3b { + SELECT jsonb_array(1,printf('%.1000c','x'),x'abcd',3); +} {1 {JSON cannot hold BLOB values}} +do_execsql_test json101-1.4 { + SELECT json_array(-9223372036854775808,9223372036854775807,0,1,-1, + 0.0, 1.0, -1.0, -1e99, +2e100, + '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); +} {[-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]} +do_execsql_test json101-1.4b { + SELECT json(jsonb_array(-9223372036854775808,9223372036854775807,0,1,-1, + 0.0, 1.0, -1.0, -1e99, +2e100, + '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)); +} {[-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]} + +do_execsql_test json101-2.1 { + SELECT json_object('a',1,'b',2.5,'c',null,'d','String Test'); +} {{{"a":1,"b":2.5,"c":null,"d":"String Test"}}} +do_execsql_test json101-2.1b { + SELECT json(jsonb_object('a',1,'b',2.5,'c',null,'d','String Test')); +} {{{"a":1,"b":2.5,"c":null,"d":"String Test"}}} +do_catchsql_test json101-2.2 { + SELECT json_object('a',printf('%.1000c','x'),2,2.5); +} {1 {json_object() labels must be TEXT}} +do_catchsql_test json101-2.2b { + SELECT jsonb_object('a',printf('%.1000c','x'),2,2.5); +} {1 {json_object() labels must be TEXT}} +do_execsql_test json101-2.2.2 { + SELECT json_object('a',json_array('xyx',77,4.5),'x',2.5); +} {{{"a":["xyx",77,4.5],"x":2.5}}} +do_execsql_test json101-2.2.2b { + SELECT json(jsonb_object('a',json_array('xyx',77,4.5),'x',2.5)); +} {{{"a":["xyx",77,4.5],"x":2.5}}} +do_execsql_test json101-2.2.3 { + SELECT json_object('a',jsonb_array('xyx',77,4.5),'x',2.5); +} {{{"a":["xyx",77,4.5],"x":2.5}}} +do_execsql_test json101-2.2.3b { + SELECT json(jsonb_object('a',jsonb_array('xyx',77,4.5),'x',2.5)); +} {{{"a":["xyx",77,4.5],"x":2.5}}} +do_catchsql_test json101-2.3 { + SELECT json_object('a',1,'b'); +} {1 {json_object() requires an even number of arguments}} +do_catchsql_test json101-2.4 { + SELECT json_object('a',printf('%.1000c','x'),'b',x'abcd'); +} {1 {JSON cannot hold BLOB values}} +do_execsql_test json101-2.5 { + SELECT json_object('a',printf('%.10c','x'),'b',jsonb_array(1,2,3)); +} {{{"a":"xxxxxxxxxx","b":[1,2,3]}}} + +do_execsql_test json101-3.1 { + SELECT json_replace('{"a":1,"b":2}','$.a','[3,4,5]'); +} {{{"a":"[3,4,5]","b":2}}} +do_execsql_test json101-3.1b { + SELECT json(jsonb_replace('{"a":1,"b":2}','$.a','[3,4,5]')); +} {{{"a":"[3,4,5]","b":2}}} +do_execsql_test json101-3.2 { + SELECT json_replace('{"a":1,"b":2}','$.a',json('[3,4,5]')); +} {{{"a":[3,4,5],"b":2}}} +do_execsql_test json101-3.2b { + SELECT json_replace('{"a":1,"b":2}','$.a',jsonb('[3,4,5]')); +} {{{"a":[3,4,5],"b":2}}} +do_execsql_test json101-3.3 { + SELECT json_type(json_set('{"a":1,"b":2}','$.b','{"x":3,"y":4}'),'$.b'); +} {text} +do_execsql_test json101-3.3b { + SELECT json_type(jsonb_set('{"a":1,"b":2}','$.b','{"x":3,"y":4}'),'$.b'); +} {text} +do_execsql_test json101-3.4 { + SELECT json_type(json_set('{"a":1,"b":2}','$.b',json('{"x":3,"y":4}')),'$.b'); +} {object} +do_execsql_test json101-3.4b { + SELECT json_type(jsonb_set('{"a":1,"b":2}','$.b',jsonb('{"x":3,"y":4}')),'$.b'); +} {object} +ifcapable vtab { + do_execsql_test json101-3.5 { + SELECT fullkey, atom, '|' FROM json_tree(json_set('{}','$.x',123,'$.x',456)); + } {{$} {} | {$.x} 456 |} + do_execsql_test json101-3.5b { + SELECT fullkey, atom, '|' FROM json_tree(jsonb_set('{}','$.x',123,'$.x',456)); + } {{$} {} | {$.x} 456 |} +} + +# Per rfc7159, any JSON value is allowed at the top level, and whitespace +# is permitting before and/or after that value. +# +do_execsql_test json101-4.1 { + CREATE TABLE j1(x); + INSERT INTO j1(x) + VALUES('true'),('false'),('null'),('123'),('-234'),('34.5e+6'), + ('""'),('"\""'),('"\\"'),('"abcdefghijlmnopqrstuvwxyz"'), + ('[]'),('{}'),('[true,false,null,123,-234,34.5e+6,{},[]]'), + ('{"a":true,"b":{"c":false}}'); + SELECT * FROM j1 WHERE NOT json_valid(x); +} {} +do_execsql_test json101-4.2 { + SELECT * FROM j1 WHERE NOT json_valid(char(0x20,0x09,0x0a,0x0d)||x); +} {} +do_execsql_test json101-4.3 { + SELECT * FROM j1 WHERE NOT json_valid(x||char(0x20,0x09,0x0a,0x0d)); +} {} + +# But an empty string, or a string of pure whitespace is not valid JSON. +# +do_execsql_test json101-4.4 { + SELECT json_valid(''), json_valid(char(0x20,0x09,0x0a,0x0d)); +} {0 0} + +# json_remove() and similar functions with no edit operations return their +# input unchanged. +# +do_execsql_test json101-4.5 { + SELECT x FROM j1 WHERE json_remove(x)<>x; +} {} +do_execsql_test json101-4.6 { + SELECT x FROM j1 WHERE json_replace(x)<>x; +} {} +do_execsql_test json101-4.7 { + SELECT x FROM j1 WHERE json_set(x)<>x; +} {} +do_execsql_test json101-4.8 { + SELECT x FROM j1 WHERE json_insert(x)<>x; +} {} +do_execsql_test json101-4.9 { + SELECT json_insert('{"a":1}','$.b',CAST(x'0000' AS text)); +} {{{"a":1,"b":"\u0000\u0000"}}} + +# json_extract(JSON,'$') will return objects and arrays without change. +# +do_execsql_test json101-4.10 { + SELECT count(*) FROM j1 WHERE json_type(x) IN ('object','array'); + SELECT x FROM j1 + WHERE json_extract(x,'$')<>x + AND json_type(x) IN ('object','array'); +} {4} +do_execsql_test json101-4.10b { + CREATE TABLE j1b AS SELECT jsonb(x) AS "x" FROM j1; + SELECT count(*) FROM j1b WHERE json_type(x) IN ('object','array'); + SELECT json(x) FROM j1b + WHERE json_extract(x,'$')<>json(x) + AND json_type(x) IN ('object','array'); +} {4} + +do_execsql_test json101-5.1 { + CREATE TABLE j2(id INTEGER PRIMARY KEY, json, src); + INSERT INTO j2(id,json,src) + VALUES(1,'{ + "firstName": "John", + "lastName": "Smith", + "isAlive": true, + "age": 25, + "address": { + "streetAddress": "21 2nd Street", + "city": "New York", + "state": "NY", + "postalCode": "10021-3100" + }, + "phoneNumbers": [ + { + "type": "home", + "number": "212 555-1234" + }, + { + "type": "office", + "number": "646 555-4567" + } + ], + "children": [], + "spouse": null + }','https://en.wikipedia.org/wiki/JSON'); + INSERT INTO j2(id,json,src) + VALUES(2, '{ + "id": "0001", + "type": "donut", + "name": "Cake", + "ppu": 0.55, + "batters": + { + "batter": + [ + { "id": "1001", "type": "Regular" }, + { "id": "1002", "type": "Chocolate" }, + { "id": "1003", "type": "Blueberry" }, + { "id": "1004", "type": "Devil''s Food" } + ] + }, + "topping": + [ + { "id": "5001", "type": "None" }, + { "id": "5002", "type": "Glazed" }, + { "id": "5005", "type": "Sugar" }, + { "id": "5007", "type": "Powdered Sugar" }, + { "id": "5006", "type": "Chocolate with Sprinkles" }, + { "id": "5003", "type": "Chocolate" }, + { "id": "5004", "type": "Maple" } + ] + }','https://adobe.github.io/Spry/samples/data_region/JSONDataSetSample.html'); + INSERT INTO j2(id,json,src) + VALUES(3,'[ + { + "id": "0001", + "type": "donut", + "name": "Cake", + "ppu": 0.55, + "batters": + { + "batter": + [ + { "id": "1001", "type": "Regular" }, + { "id": "1002", "type": "Chocolate" }, + { "id": "1003", "type": "Blueberry" }, + { "id": "1004", "type": "Devil''s Food" } + ] + }, + "topping": + [ + { "id": "5001", "type": "None" }, + { "id": "5002", "type": "Glazed" }, + { "id": "5005", "type": "Sugar" }, + { "id": "5007", "type": "Powdered Sugar" }, + { "id": "5006", "type": "Chocolate with Sprinkles" }, + { "id": "5003", "type": "Chocolate" }, + { "id": "5004", "type": "Maple" } + ] + }, + { + "id": "0002", + "type": "donut", + "name": "Raised", + "ppu": 0.55, + "batters": + { + "batter": + [ + { "id": "1001", "type": "Regular" } + ] + }, + "topping": + [ + { "id": "5001", "type": "None" }, + { "id": "5002", "type": "Glazed" }, + { "id": "5005", "type": "Sugar" }, + { "id": "5003", "type": "Chocolate" }, + { "id": "5004", "type": "Maple" } + ] + }, + { + "id": "0003", + "type": "donut", + "name": "Old Fashioned", + "ppu": 0.55, + "batters": + { + "batter": + [ + { "id": "1001", "type": "Regular" }, + { "id": "1002", "type": "Chocolate" } + ] + }, + "topping": + [ + { "id": "5001", "type": "None" }, + { "id": "5002", "type": "Glazed" }, + { "id": "5003", "type": "Chocolate" }, + { "id": "5004", "type": "Maple" } + ] + } + ]','https://adobe.github.io/Spry/samples/data_region/JSONDataSetSample.html'); + SELECT count(*) FROM j2; + CREATE TABLE j2b(id INTEGER PRIMARY KEY, json, src); + INSERT INTO J2b(id,json,src) SELECT id, jsonb(json), src FROM j2; + SELECT count(*) FROM j2b; +} {3 3} + +do_execsql_test json101-5.2 { + SELECT id, json_valid(json), json_type(json), '|' FROM j2 ORDER BY id; +} {1 1 object | 2 1 object | 3 1 array |} +do_execsql_test json101-5.2b { + SELECT id, json_valid(json,5), json_type(json), '|' FROM j2b ORDER BY id; +} {1 1 object | 2 1 object | 3 1 array |} + +ifcapable !vtab { + finish_test + return +} + +# fullkey is always the same as path+key (with appropriate formatting) +# +do_execsql_test json101-5.3 { + SELECT j2.rowid, jx.rowid, fullkey, path, key + FROM j2, json_tree(j2.json) AS jx + WHERE fullkey!=(path || CASE WHEN typeof(key)=='integer' THEN '['||key||']' + ELSE '.'||key END); +} {} +do_execsql_test json101-5.3b { + SELECT j2b.rowid, jx.rowid, fullkey, path, key + FROM j2b, json_tree(j2b.json) AS jx + WHERE fullkey!=(path || CASE WHEN typeof(key)=='integer' THEN '['||key||']' + ELSE '.'||key END); +} {} +do_execsql_test json101-5.4 { + SELECT j2.rowid, jx.rowid, fullkey, path, key + FROM j2, json_each(j2.json) AS jx + WHERE fullkey!=(path || CASE WHEN typeof(key)=='integer' THEN '['||key||']' + ELSE '.'||key END); +} {} + + +# Verify that the json_each.json and json_tree.json output is always the +# same as input. +# +do_execsql_test json101-5.5 { + SELECT j2.rowid, jx.rowid, fullkey, path, key + FROM j2, json_each(j2.json) AS jx + WHERE jx.json<>j2.json; +} {} +do_execsql_test json101-5.6 { + SELECT j2.rowid, jx.rowid, fullkey, path, key + FROM j2, json_tree(j2.json) AS jx + WHERE jx.json<>j2.json; +} {} +do_execsql_test json101-5.7 { + SELECT j2.rowid, jx.rowid, fullkey, path, key + FROM j2, json_each(j2.json) AS jx + WHERE jx.value<>jx.atom AND type NOT IN ('array','object'); +} {} +do_execsql_test json101-5.8 { + SELECT j2.rowid, jx.rowid, fullkey, path, key + FROM j2, json_tree(j2.json) AS jx + WHERE jx.value<>jx.atom AND type NOT IN ('array','object'); +} {} + +do_execsql_test json101-6.1 { + SELECT json_valid('{"a":55,"b":72,}'); +} {0} +do_execsql_test json101-6.2 { + SELECT json_error_position('{"a":55,"b":72,}'); +} {0} +do_execsql_test json101-6.3 { + SELECT json_valid(json('{"a":55,"b":72,}')); +} {1} +do_execsql_test json101-6.4 { + SELECT json_valid('{"a":55,"b":72 , }'); +} {0} +do_execsql_test json101-6.5 { + SELECT json_error_position('{"a":55,"b":72 , }'); +} {0} +do_execsql_test json101-6.6 { + SELECT json_error_position('{"a":55,"b":72,,}'); +} {16} +do_execsql_test json101-6.7 { + SELECT json_valid('{"a":55,"b":72}'); +} {1} +do_execsql_test json101-6.8 { + SELECT json_error_position('["a",55,"b",72,]'); +} {0} +do_execsql_test json101-6.9 { + SELECT json_error_position('["a",55,"b",72 , ]'); +} {0} +do_execsql_test json101-6.10 { + SELECT json_error_position('["a",55,"b",72,,]'); +} {16} +do_execsql_test json101-6.11 { + SELECT json_valid('["a",55,"b",72]'); +} {1} + +# White-space tests. Note that form-feed is not white-space in JSON. +# ticket [57eec374ae1d0a1d4a23077a95f4e173fe269113] +# +foreach {tn isvalid ws} { + 7.1 1 char(0x20) + 7.2 1 char(0x09) + 7.3 1 char(0x0A) + 7.4 1 char(0x0D) + 7.5 0 char(0x0C) + 7.6 1 char(0x20,0x09,0x0a,0x0d,0x20) + 7.7 0 char(0x20,0x09,0x0a,0x0c,0x0d,0x20) +} { + do_execsql_test json101-$tn.1 \ + "SELECT json_valid(printf('%s{%s\"x\"%s:%s9%s}%s', + $::ws,$::ws,$::ws,$::ws,$::ws,$::ws));" \ + $isvalid +} + +# Ticket https://www.sqlite.org/src/info/ad2559db380abf8e +# Control characters must be escaped in JSON strings. +# +do_execsql_test json101-8.1 { + DROP TABLE IF EXISTS t8; + CREATE TABLE t8(a,b); + 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'); + UPDATE t8 SET b=json_array(a); + SELECT b FROM t8; +} {{["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"]}} +do_execsql_test json101-8.1b { + DROP TABLE IF EXISTS t8; + CREATE TABLE t8(a,b); + 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'); + UPDATE t8 SET b=jsonb_array(a); + SELECT json(b) FROM t8; +} {{["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"]}} +do_execsql_test json101-8.2 { + SELECT a=json_extract(b,'$[0]') FROM t8; +} {1} + +# 2017-04-12. Regression reported on the mailing list by Rolf Ade +# +do_execsql_test json101-8.3 { + SELECT json_valid(char(0x22,0xe4,0x22)); +} {1} +do_execsql_test json101-8.4 { + SELECT unicode(json_extract(char(0x22,228,0x22),'$')); +} {228} + +# The json_quote() function transforms an SQL value into a JSON value. +# String values are quoted and interior quotes are escaped. NULL values +# are rendered as the unquoted string "null". +# +do_execsql_test json101-9.1 { + SELECT json_quote('abc"xyz'); +} {{"abc\"xyz"}} +do_execsql_test json101-9.2 { + SELECT json_quote(3.14159); +} {3.14159} +do_execsql_test json101-9.3 { + SELECT json_quote(12345); +} {12345} +do_execsql_test json101-9.4 { + SELECT json_quote(null); +} {"null"} +do_catchsql_test json101-9.5 { + SELECT json_quote(x'3031323334'); +} {1 {JSON cannot hold BLOB values}} +do_catchsql_test json101-9.6 { + SELECT json_quote(123,456) +} {1 {wrong number of arguments to function json_quote()}} +do_catchsql_test json101-9.7 { + SELECT json_quote() +} {1 {wrong number of arguments to function json_quote()}} + +# Make sure only valid backslash-escapes are accepted. +# +do_execsql_test json101-10.1 { + SELECT json_valid('" \ "'); +} {0} +do_execsql_test json101-10.2 { + SELECT json_valid('" \! "'); +} {0} +do_execsql_test json101-10.3 { + SELECT json_valid('" \" "'); +} {1} +do_execsql_test json101-10.4 { + SELECT json_valid('" \# "'); +} {0} +do_execsql_test json101-10.5 { + SELECT json_valid('" \$ "'); +} {0} +do_execsql_test json101-10.6 { + SELECT json_valid('" \% "'); +} {0} +do_execsql_test json101-10.7 { + SELECT json_valid('" \& "'); +} {0} +do_execsql_test json101-10.8 { + SELECT json_valid('" \'' "'); +} {0} +do_execsql_test json101-10.9 { + SELECT json_valid('" \( "'); +} {0} +do_execsql_test json101-10.10 { + SELECT json_valid('" \) "'); +} {0} +do_execsql_test json101-10.11 { + SELECT json_valid('" \* "'); +} {0} +do_execsql_test json101-10.12 { + SELECT json_valid('" \+ "'); +} {0} +do_execsql_test json101-10.13 { + SELECT json_valid('" \, "'); +} {0} +do_execsql_test json101-10.14 { + SELECT json_valid('" \- "'); +} {0} +do_execsql_test json101-10.15 { + SELECT json_valid('" \. "'); +} {0} +do_execsql_test json101-10.16 { + SELECT json_valid('" \/ "'); +} {1} +do_execsql_test json101-10.17 { + SELECT json_valid('" \0 "'); +} {0} +do_execsql_test json101-10.18 { + SELECT json_valid('" \1 "'); +} {0} +do_execsql_test json101-10.19 { + SELECT json_valid('" \2 "'); +} {0} +do_execsql_test json101-10.20 { + SELECT json_valid('" \3 "'); +} {0} +do_execsql_test json101-10.21 { + SELECT json_valid('" \4 "'); +} {0} +do_execsql_test json101-10.22 { + SELECT json_valid('" \5 "'); +} {0} +do_execsql_test json101-10.23 { + SELECT json_valid('" \6 "'); +} {0} +do_execsql_test json101-10.24 { + SELECT json_valid('" \7 "'); +} {0} +do_execsql_test json101-10.25 { + SELECT json_valid('" \8 "'); +} {0} +do_execsql_test json101-10.26 { + SELECT json_valid('" \9 "'); +} {0} +do_execsql_test json101-10.27 { + SELECT json_valid('" \: "'); +} {0} +do_execsql_test json101-10.28 { + SELECT json_valid('" \; "'); +} {0} +do_execsql_test json101-10.29 { + SELECT json_valid('" \< "'); +} {0} +do_execsql_test json101-10.30 { + SELECT json_valid('" \= "'); +} {0} +do_execsql_test json101-10.31 { + SELECT json_valid('" \> "'); +} {0} +do_execsql_test json101-10.32 { + SELECT json_valid('" \? "'); +} {0} +do_execsql_test json101-10.33 { + SELECT json_valid('" \@ "'); +} {0} +do_execsql_test json101-10.34 { + SELECT json_valid('" \A "'); +} {0} +do_execsql_test json101-10.35 { + SELECT json_valid('" \B "'); +} {0} +do_execsql_test json101-10.36 { + SELECT json_valid('" \C "'); +} {0} +do_execsql_test json101-10.37 { + SELECT json_valid('" \D "'); +} {0} +do_execsql_test json101-10.38 { + SELECT json_valid('" \E "'); +} {0} +do_execsql_test json101-10.39 { + SELECT json_valid('" \F "'); +} {0} +do_execsql_test json101-10.40 { + SELECT json_valid('" \G "'); +} {0} +do_execsql_test json101-10.41 { + SELECT json_valid('" \H "'); +} {0} +do_execsql_test json101-10.42 { + SELECT json_valid('" \I "'); +} {0} +do_execsql_test json101-10.43 { + SELECT json_valid('" \J "'); +} {0} +do_execsql_test json101-10.44 { + SELECT json_valid('" \K "'); +} {0} +do_execsql_test json101-10.45 { + SELECT json_valid('" \L "'); +} {0} +do_execsql_test json101-10.46 { + SELECT json_valid('" \M "'); +} {0} +do_execsql_test json101-10.47 { + SELECT json_valid('" \N "'); +} {0} +do_execsql_test json101-10.48 { + SELECT json_valid('" \O "'); +} {0} +do_execsql_test json101-10.49 { + SELECT json_valid('" \P "'); +} {0} +do_execsql_test json101-10.50 { + SELECT json_valid('" \Q "'); +} {0} +do_execsql_test json101-10.51 { + SELECT json_valid('" \R "'); +} {0} +do_execsql_test json101-10.52 { + SELECT json_valid('" \S "'); +} {0} +do_execsql_test json101-10.53 { + SELECT json_valid('" \T "'); +} {0} +do_execsql_test json101-10.54 { + SELECT json_valid('" \U "'); +} {0} +do_execsql_test json101-10.55 { + SELECT json_valid('" \V "'); +} {0} +do_execsql_test json101-10.56 { + SELECT json_valid('" \W "'); +} {0} +do_execsql_test json101-10.57 { + SELECT json_valid('" \X "'); +} {0} +do_execsql_test json101-10.58 { + SELECT json_valid('" \Y "'); +} {0} +do_execsql_test json101-10.59 { + SELECT json_valid('" \Z "'); +} {0} +do_execsql_test json101-10.60 { + SELECT json_valid('" \[ "'); +} {0} +do_execsql_test json101-10.61 { + SELECT json_valid('" \\ "'); +} {1} +do_execsql_test json101-10.62 { + SELECT json_valid('" \] "'); +} {0} +do_execsql_test json101-10.63 { + SELECT json_valid('" \^ "'); +} {0} +do_execsql_test json101-10.64 { + SELECT json_valid('" \_ "'); +} {0} +do_execsql_test json101-10.65 { + SELECT json_valid('" \` "'); +} {0} +do_execsql_test json101-10.66 { + SELECT json_valid('" \a "'); +} {0} +do_execsql_test json101-10.67 { + SELECT json_valid('" \b "'); +} {1} +do_execsql_test json101-10.68 { + SELECT json_valid('" \c "'); +} {0} +do_execsql_test json101-10.69 { + SELECT json_valid('" \d "'); +} {0} +do_execsql_test json101-10.70 { + SELECT json_valid('" \e "'); +} {0} +do_execsql_test json101-10.71 { + SELECT json_valid('" \f "'); +} {1} +do_execsql_test json101-10.72 { + SELECT json_valid('" \g "'); +} {0} +do_execsql_test json101-10.73 { + SELECT json_valid('" \h "'); +} {0} +do_execsql_test json101-10.74 { + SELECT json_valid('" \i "'); +} {0} +do_execsql_test json101-10.75 { + SELECT json_valid('" \j "'); +} {0} +do_execsql_test json101-10.76 { + SELECT json_valid('" \k "'); +} {0} +do_execsql_test json101-10.77 { + SELECT json_valid('" \l "'); +} {0} +do_execsql_test json101-10.78 { + SELECT json_valid('" \m "'); +} {0} +do_execsql_test json101-10.79 { + SELECT json_valid('" \n "'); +} {1} +do_execsql_test json101-10.80 { + SELECT json_valid('" \o "'); +} {0} +do_execsql_test json101-10.81 { + SELECT json_valid('" \p "'); +} {0} +do_execsql_test json101-10.82 { + SELECT json_valid('" \q "'); +} {0} +do_execsql_test json101-10.83 { + SELECT json_valid('" \r "'); +} {1} +do_execsql_test json101-10.84 { + SELECT json_valid('" \s "'); +} {0} +do_execsql_test json101-10.85 { + SELECT json_valid('" \t "'); +} {1} +do_execsql_test json101-10.86.0 { + SELECT json_valid('" \u "'); +} {0} +do_execsql_test json101-10.86.1 { + SELECT json_valid('" \ua "'); +} {0} +do_execsql_test json101-10.86.2 { + SELECT json_valid('" \uab "'); +} {0} +do_execsql_test json101-10.86.3 { + SELECT json_valid('" \uabc "'); +} {0} +do_execsql_test json101-10.86.4 { + SELECT json_valid('" \uabcd "'); +} {1} +do_execsql_test json101-10.86.5 { + SELECT json_valid('" \uFEDC "'); +} {1} +do_execsql_test json101-10.86.6 { + SELECT json_valid('" \u1234 "'); +} {1} +do_execsql_test json101-10.87 { + SELECT json_valid('" \v "'); +} {0} +do_execsql_test json101-10.88 { + SELECT json_valid('" \w "'); +} {0} +do_execsql_test json101-10.89 { + SELECT json_valid('" \x "'); +} {0} +do_execsql_test json101-10.90 { + SELECT json_valid('" \y "'); +} {0} +do_execsql_test json101-10.91 { + SELECT json_valid('" \z "'); +} {0} +do_execsql_test json101-10.92 { + SELECT json_valid('" \{ "'); +} {0} +do_execsql_test json101-10.93 { + SELECT json_valid('" \| "'); +} {0} +do_execsql_test json101-10.94 { + SELECT json_valid('" \} "'); +} {0} +do_execsql_test json101-10.95 { + SELECT json_valid('" \~ "'); +} {0} + +#-------------------------------------------------------------------------- +# 2017-04-11. https://www.sqlite.org/src/info/981329adeef51011 +# Stack overflow on deeply nested JSON. +# +# The following tests confirm that deeply nested JSON is considered invalid. +# +do_execsql_test json101-11.0 { + /* Shallow enough to be parsed */ + SELECT json_valid(printf('%.1000c0%.1000c','[',']')); +} {1} +do_execsql_test json101-11.1 { + /* Too deep by one */ + SELECT json_valid(printf('%.1001c0%.1001c','[',']')); +} {0} +do_execsql_test json101-11.2 { + /* Shallow enough to be parsed { */ + SELECT json_valid(replace(printf('%.1000c0%.1000c','[','}'),'[','{"a":')); + /* } */ +} {1} +do_execsql_test json101-11.3 { + /* Too deep by one { */ + SELECT json_valid(replace(printf('%.1001c0%.1001c','[','}'),'[','{"a":')); + /* } */ +} {0} + +# 2017-10-27. Demonstrate the ability to access an element from +# a json structure even though the element name constains a "." +# character, by quoting the element name in the path. +# +do_execsql_test json101-12.100 { + CREATE TABLE t12(x); + INSERT INTO t12(x) VALUES( + '{"settings": + {"layer2": + {"hapax.legomenon": + {"forceDisplay":true, + "transliterate":true, + "add.footnote":true, + "summary.report":true}, + "dis.legomenon": + {"forceDisplay":true, + "transliterate":false, + "add.footnote":false, + "summary.report":true}, + "tris.legomenon": + {"forceDisplay":true, + "transliterate":false, + "add.footnote":false, + "summary.report":false} + } + } + }'); +} {} + +do_execsql_test json101-12.110 { + SELECT json_remove(x, '$.settings.layer2."dis.legomenon".forceDisplay') + FROM t12; +} {{{"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}}}}}} +do_execsql_test json101-12.110b { + SELECT json_remove(jsonb(x), '$.settings.layer2."dis.legomenon".forceDisplay') + FROM t12; +} {{{"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}}}}}} +do_execsql_test json101-12.120 { + SELECT json_extract(x, '$.settings.layer2."tris.legomenon"."summary.report"') + FROM t12; +} {0} +do_execsql_test json101-12.120b { + SELECT json_extract(jsonb(x), '$.settings.layer2."tris.legomenon"."summary.report"') + FROM t12; +} {0} + +# 2018-01-26 +# ticket https://www.sqlite.org/src/tktview/80177f0c226ff54f6ddd41 +# Make sure the query planner knows about the arguments to table-valued functions. +# +do_execsql_test json101-13.100 { + DROP TABLE IF EXISTS t1; + DROP TABLE IF EXISTS t2; + CREATE TABLE t1(id, json); + INSERT INTO t1(id,json) VALUES(1,'{"items":[3,5]}'); + CREATE TABLE t2(id, json); + INSERT INTO t2(id,json) VALUES(2,'{"value":2}'); + INSERT INTO t2(id,json) VALUES(3,'{"value":3}'); + INSERT INTO t2(id,json) VALUES(4,'{"value":4}'); + INSERT INTO t2(id,json) VALUES(5,'{"value":5}'); + INSERT INTO t2(id,json) VALUES(6,'{"value":6}'); + SELECT * FROM t1 CROSS JOIN t2 + WHERE EXISTS(SELECT 1 FROM json_each(t1.json,'$.items') AS Z + WHERE Z.value==t2.id); +} {1 {{"items":[3,5]}} 3 {{"value":3}} 1 {{"items":[3,5]}} 5 {{"value":5}}} +do_execsql_test json101-13.110 { + SELECT * FROM t2 CROSS JOIN t1 + WHERE EXISTS(SELECT 1 FROM json_each(t1.json,'$.items') AS Z + WHERE Z.value==t2.id); +} {3 {{"value":3}} 1 {{"items":[3,5]}} 5 {{"value":5}} 1 {{"items":[3,5]}}} + +# 2018-05-16 +# Incorrect fullkey output from json_each() +# when the input JSON is not an array or object. +# +do_execsql_test json101-14.100 { + SELECT fullkey FROM json_each('123'); +} {$} +do_execsql_test json101-14.110 { + SELECT fullkey FROM json_each('123.56'); +} {$} +do_execsql_test json101-14.120 { + SELECT fullkey FROM json_each('"hello"'); +} {$} +do_execsql_test json101-14.130 { + SELECT fullkey FROM json_each('null'); +} {$} +do_execsql_test json101-14.140 { + SELECT fullkey FROM json_tree('123'); +} {$} +do_execsql_test json101-14.150 { + SELECT fullkey FROM json_tree('123.56'); +} {$} +do_execsql_test json101-14.160 { + SELECT fullkey FROM json_tree('"hello"'); +} {$} +do_execsql_test json101-14.170 { + SELECT fullkey FROM json_tree('null'); +} {$} + +# 2018-12-03 +# Make sure the table-valued functions contained within parentheses +# work correctly. +# +# Bug reported via private email. See TH3 for more information. +# +do_execsql_test json101-15.100 { + SELECT * FROM JSON_EACH('{"a":1, "b":2}'); +} {a 1 integer 1 1 {} {$.a} {$} b 2 integer 2 5 {} {$.b} {$}} +do_execsql_test json101-15.110 { + SELECT xyz.* FROM JSON_EACH('{"a":1, "b":2}') AS xyz; +} {a 1 integer 1 1 {} {$.a} {$} b 2 integer 2 5 {} {$.b} {$}} +do_execsql_test json101-15.120 { + SELECT * FROM (JSON_EACH('{"a":1, "b":2}')); +} {a 1 integer 1 1 {} {$.a} {$} b 2 integer 2 5 {} {$.b} {$}} +do_execsql_test json101-15.130 { + SELECT xyz.* FROM (JSON_EACH('{"a":1, "b":2}')) AS xyz; +} {a 1 integer 1 1 {} {$.a} {$} b 2 integer 2 5 {} {$.b} {$}} + +# 2019-11-10 +# Mailing list bug report on the handling of surrogate pairs +# in JSON. +# +do_execsql_test json101-16.10 { + SELECT length(json_extract('"abc\uD834\uDD1Exyz"','$')); +} {7} +do_execsql_test json101-16.20 { + SELECT length(json_extract('"\uD834\uDD1E"','$')); +} {1} +do_execsql_test json101-16.30 { + SELECT unicode(json_extract('"\uD834\uDD1E"','$')); +} {119070} + +# 2022-01-30 dbsqlfuzz 4678cf825d27f87c9b8343720121e12cf944b71a +do_execsql_test json101-17.1 { + DROP TABLE IF EXISTS t1; + DROP TABLE IF EXISTS t2; + CREATE TABLE t1(a,b,c); + CREATE TABLE t2(d); + SELECT * FROM t1 LEFT JOIN t2 ON (SELECT b FROM json_each ORDER BY 1); +} {} + +# 2022-04-04 forum post https://sqlite.org/forum/forumpost/c082aeab43 +do_execsql_test json101-18.1 { + SELECT json_valid('{"":5}'); +} {1} +do_execsql_test json101-18.2 { + SELECT json_extract('{"":5}', '$.""'); +} {5} +do_execsql_test json101-18.3 { + SELECT json_extract('[3,{"a":4,"":[5,{"hi":6},7]},8]', '$[1].""[1].hi'); +} {6} +do_execsql_test json101-18.4 { + SELECT json_extract('[3,{"a":4,"":[5,{"hi":6},7]},8]', '$[1].""[1]."hi"'); +} {6} +do_catchsql_test json101-18.5 { + SELECT json_extract('{"":8}', '$.'); +} {1 {bad JSON path: '$.'}} + +# 2022-08-29 https://sqlite.org/forum/forumpost/9b9e4716c0d7bbd1 +# This is not a problem specifically with JSON functions. It is +# a problem with transaction control. But the json() function makes +# the problem more easily accessible, so it is tested here. +# +do_execsql_test json101-19.1 { + DROP TABLE IF EXISTS t1; + CREATE TABLE t1(x); +} {} +do_catchsql_test json101-19.2 { + BEGIN; + INSERT INTO t1 VALUES(0), (json('not-valid-json')); +} {1 {malformed JSON}} +do_execsql_test json101-19.3 { + COMMIT; + SELECT * FROM t1; +} {} + +# 2023-03-17 positive and negative infinities +# +do_execsql_test json101-20.1 { + SELECT json_object('a',2e370,'b',-3e380); +} {{{"a":9.0e+999,"b":-9.0e+999}}} +do_execsql_test json101-20.2 { + SELECT json_object('a',2e370,'b',-3e380)->>'a'; +} Inf +do_execsql_test json101-20.3 { + SELECT json_object('a',2e370,'b',-3e380)->>'b'; +} {-Inf} + +# 2023-05-02 https://sqlite.org/forum/forumpost/06c6334412 +# JSON functions should normally return NULL when given +# a NULL value as the JSON input. +# +db null NULL +if {[db exists {SELECT * FROM pragma_compile_options WHERE compile_options LIKE '%legacy_json_valid%'}]} { + do_execsql_test json101-21.1-legacy { + SELECT json_valid(NULL); + } 0 +} else { + do_execsql_test json101-21.1-correct { + SELECT json_valid(NULL); + } NULL +} +do_execsql_test json101-21.2 { + SELECT json_error_position(NULL); +} NULL +do_execsql_test json101-21.3 { + SELECT json(NULL); +} NULL +do_execsql_test json101-21.4 { + SELECT json_array(NULL); +} {[null]} +do_execsql_test json101-21.5 { + SELECT json_extract(NULL); +} NULL +do_execsql_test json101-21.6 { + SELECT json_insert(NULL,'$',123); +} NULL +do_execsql_test json101-21.7 { + SELECT NULL->0; +} NULL +do_execsql_test json101-21.8 { + SELECT NULL->>0; +} NULL +do_execsql_test json101-21.9 { + SELECT '{a:5}'->NULL; +} NULL +do_execsql_test json101-21.10 { + SELECT '{a:5}'->>NULL; +} NULL +do_catchsql_test json101-21.11 { + SELECT json_object(NULL,5); +} {1 {json_object() labels must be TEXT}} +do_execsql_test json101-21.12 { + SELECT json_patch(NULL,'{a:5}'); +} NULL +do_execsql_test json101-21.13 { + SELECT json_patch('{a:5}',NULL); +} NULL +do_execsql_test json101-21.14 { + SELECT json_patch(NULL,NULL); +} NULL +do_execsql_test json101-21.15 { + SELECT json_remove(NULL,'$'); +} NULL +do_execsql_test json101-21.16 { + SELECT json_remove('{a:5,b:7}',NULL); +} NULL +do_execsql_test json101-21.17 { + SELECT json_replace(NULL,'$.a',123); +} NULL +do_execsql_test json101-21.18 { + SELECT json_replace('{a:5,b:7}',NULL,NULL); +} {{{"a":5,"b":7}}} +do_execsql_test json101-21.19 { + SELECT json_set(NULL,'$.a',123); +} NULL +do_execsql_test json101-21.20 { + SELECT json_set('{a:5,b:7}',NULL,NULL); +} {{{"a":5,"b":7}}} +do_execsql_test json101-21.21 { + SELECT json_type(NULL); +} NULL +do_execsql_test json101-21.22 { + SELECT json_type('{a:5,b:7}',NULL); +} NULL +do_execsql_test json101-21.23 { + SELECT json_quote(NULL); +} null +do_execsql_test json101-21.24 { + SELECT count(*) FROM json_each(NULL); +} 0 +do_execsql_test json101-21.25 { + SELECT count(*) FROM json_tree(NULL); +} 0 +do_execsql_test json101-21.26 { + WITH c(x) AS (VALUES(1),(2.0),(NULL),('three')) + SELECT json_group_array(x) FROM c; +} {[1,2.0,null,"three"]} +do_execsql_test json101-21.27 { + WITH c(x,y) AS (VALUES('a',1),('b',2.0),('c',NULL),(NULL,'three'),('e','four')) + SELECT json_group_object(x,y) FROM c; +} {{{"a":1,"b":2.0,"c":null,:"three","e":"four"}}} + +# 2023-10-09 https://sqlite.org/forum/forumpost/b25edc1d46 +# UAF due to JSON cache overflow +# +do_execsql_test json101-22.1 { + SELECT json_set( + '{}', + '$.a', json('1'), + '$.a', json('2'), + '$.b', json('3'), + '$.b', json('4'), + '$.c', json('5'), + '$.c', json('6') + ); +} {{{"a":2,"b":4,"c":6}}} +do_execsql_test json101-22.2 { + SELECT json_replace( + '{"a":7,"b":8,"c":9}', + '$.a', json('1'), + '$.a', json('2'), + '$.b', json('3'), + '$.b', json('4'), + '$.c', json('5'), + '$.c', json('6') + ); +} {{{"a":2,"b":4,"c":6}}} + +# 2023-10-17 https://sqlite.org/forum/forumpost/fc0e3f1e2a +# Incorrect accesss to '$[0]' in parsed + edited JSON. +# +do_execsql_test json101-23.1 { + SELECT j, j->>0, j->>1 + FROM (SELECT json_set(json_set('[]','$[#]',0), '$[#]',1) AS j); +} {{[0,1]} 0 1} +do_execsql_test json101-23.2 { + SELECT j, j->>0, j->>1 + FROM (SELECT json_set('[]','$[#]',0,'$[#]',1) AS j); +} {{[0,1]} 0 1} + +# Insert/Set/Replace where the path specifies substructure that +# does not yet exist +# +proc tx x {return [string map [list ( \173 ) \175 ' \042 < \133 > \135] $x]} +foreach {id start path ins set repl} { + 1 {{}} {$.a.b.c} ('a':('b':('c':9))) ('a':('b':('c':9))) () + 2 {{a:4}} {$.a.b.c} ('a':4) ('a':4) ('a':4) + 3 {{a:{}}} {$.a.b.c} ('a':('b':('c':9))) ('a':('b':('c':9))) ('a':()) + 4 {[0,1,2]} {$[3].a[0].b} <0,1,2,('a':<('b':9)>)> <0,1,2,('a':<('b':9)>)> <0,1,2> + 5 {[0,1,2]} {$[1].a[0].b} <0,1,2> <0,1,2> <0,1,2> + 6 {[0,{},2]} {$[1].a[0].b} <0,('a':<('b':9)>),2> <0,('a':<('b':9)>),2> <0,(),2> + 7 {[0,1,2]} {$[3][0].b} <0,1,2,<('b':9)>> <0,1,2,<('b':9)>> <0,1,2> + 8 {[0,1,2]} {$[1][0].b} <0,1,2> <0,1,2> <0,1,2> +} { + do_execsql_test json101-24.$id.insert { + SELECT json_insert($start,$path,9); + } [list [tx $ins]] + do_execsql_test json101-24.$id.set { + SELECT json_set($start,$path,9); + } [list [tx $set]] + do_execsql_test json101-24.$id.replace { + SELECT json_replace($start,$path,9); + } [list [tx $repl]] +} + +finish_test diff --git a/test/json102.test b/test/json102.test new file mode 100644 index 0000000..15a54b4 --- /dev/null +++ b/test/json102.test @@ -0,0 +1,767 @@ +# 2015-08-12 +# +# The author disclaims copyright to this source code. In place of +# a legal notice, here is a blessing: +# +# May you do good and not evil. +# May you find forgiveness for yourself and forgive others. +# May you share freely, never taking more than you give. +# +#*********************************************************************** +# This file implements tests for JSON SQL functions extension to the +# SQLite library. +# +# This file contains tests automatically generated from the json1 +# documentation. +# + +set testdir [file dirname $argv0] +source $testdir/tester.tcl + +do_execsql_test json102-100 { + SELECT json_object('ex','[52,3.14159]'); +} {{{"ex":"[52,3.14159]"}}} +do_execsql_test json102-100b { + SELECT json(jsonb_object('ex','[52,3.14159]')); +} {{{"ex":"[52,3.14159]"}}} +do_execsql_test json102-110 { + SELECT json_object('ex',json('[52,3.14159]')); +} {{{"ex":[52,3.14159]}}} +do_execsql_test json102-110-2 { + SELECT json(jsonb_object('ex',json('[52,3.14159]'))); +} {{{"ex":[52,3.14159]}}} +do_execsql_test json102-110-3 { + SELECT json_object('ex',jsonb('[52,3.14159]')); +} {{{"ex":[52,3.14159]}}} +do_execsql_test json102-110-3 { + SELECT json(jsonb_object('ex',jsonb('[52,3.14159]'))); +} {{{"ex":[52,3.14159]}}} +do_execsql_test json102-120 { + SELECT json_object('ex',json_array(52,3.14159)); +} {{{"ex":[52,3.14159]}}} +do_execsql_test json102-120-2 { + SELECT json(jsonb_object('ex',json_array(52,3.14159))); +} {{{"ex":[52,3.14159]}}} +do_execsql_test json102-120-3 { + SELECT json_object('ex',jsonb_array(52,3.14159)); +} {{{"ex":[52,3.14159]}}} +do_execsql_test json102-120-4 { + SELECT json(jsonb_object('ex',jsonb_array(52,3.14159))); +} {{{"ex":[52,3.14159]}}} +do_execsql_test json102-130 { + SELECT json(' { "this" : "is", "a": [ "test" ] } '); +} {{{"this":"is","a":["test"]}}} +do_execsql_test json102-130b { + SELECT json(jsonb(' { "this" : "is", "a": [ "test" ] } ')); +} {{{"this":"is","a":["test"]}}} +do_execsql_test json102-140 { + SELECT json_array(1,2,'3',4); +} {{[1,2,"3",4]}} +do_execsql_test json102-140b { + SELECT json(jsonb_array(1,2,'3',4)); +} {{[1,2,"3",4]}} +do_execsql_test json102-150 { + SELECT json_array('[1,2]'); +} {{["[1,2]"]}} +do_execsql_test json102-150b { + SELECT json(jsonb_array('[1,2]')); +} {{["[1,2]"]}} +do_execsql_test json102-160 { + SELECT json_array(json_array(1,2)); +} {{[[1,2]]}} +do_execsql_test json102-160-2 { + SELECT json_array(jsonb_array(1,2)); +} {{[[1,2]]}} +do_execsql_test json102-160-3 { + SELECT json(jsonb_array(json_array(1,2))); +} {{[[1,2]]}} +do_execsql_test json102-160-4 { + SELECT json(jsonb_array(jsonb_array(1,2))); +} {{[[1,2]]}} +do_execsql_test json102-170 { + SELECT json_array(1,null,'3','[4,5]','{"six":7.7}'); +} {{[1,null,"3","[4,5]","{\"six\":7.7}"]}} +do_execsql_test json102-170b { + SELECT json(jsonb_array(1,null,'3','[4,5]','{"six":7.7}')); +} {{[1,null,"3","[4,5]","{\"six\":7.7}"]}} +do_execsql_test json102-180 { + SELECT json_array(1,null,'3',json('[4,5]'),json('{"six":7.7}')); +} {{[1,null,"3",[4,5],{"six":7.7}]}} +do_execsql_test json102-180-2 { + SELECT json_array(1,null,'3',jsonb('[4,5]'),json('{"six":7.7}')); +} {{[1,null,"3",[4,5],{"six":7.7}]}} +do_execsql_test json102-180-3 { + SELECT json(jsonb_array(1,null,'3',json('[4,5]'),json('{"six":7.7}'))); +} {{[1,null,"3",[4,5],{"six":7.7}]}} +do_execsql_test json102-180-4 { + SELECT json(jsonb_array(1,null,'3',jsonb('[4,5]'),jsonb('{"six":7.7}'))); +} {{[1,null,"3",[4,5],{"six":7.7}]}} +do_execsql_test json102-190 { + SELECT json_array_length('[1,2,3,4]'); +} {{4}} +do_execsql_test json102-190b { + SELECT json_array_length(jsonb('[1,2,3,4]')); +} {{4}} +do_execsql_test json102-191 { + SELECT json_array_length( json_remove('[1,2,3,4]','$[2]') ); +} {{3}} +do_execsql_test json102-191b { + SELECT json_array_length( jsonb_remove('[1,2,3,4]','$[2]') ); +} {{3}} +do_execsql_test json102-200 { + SELECT json_array_length('[1,2,3,4]', '$'); +} {{4}} +do_execsql_test json102-200b { + SELECT json_array_length(jsonb('[1,2,3,4]'), '$'); +} {{4}} +do_execsql_test json102-210 { + SELECT json_array_length('[1,2,3,4]', '$[2]'); +} {{0}} +do_execsql_test json102-210b { + SELECT json_array_length(jsonb('[1,2,3,4]'), '$[2]'); +} {{0}} +do_execsql_test json102-220 { + SELECT json_array_length('{"one":[1,2,3]}'); +} {{0}} +do_execsql_test json102-220 { + SELECT json_array_length('{"one":[1,2,3]}'); +} {{0}} +do_execsql_test json102-230b { + SELECT json_array_length(jsonb('{"one":[1,2,3]}'), '$.one'); +} {{3}} +do_execsql_test json102-240 { + SELECT json_array_length('{"one":[1,2,3]}', '$.two'); +} {{}} +do_execsql_test json102-240b { + SELECT json_array_length(jsonb('{"one":[1,2,3]}'), '$.two'); +} {{}} +do_execsql_test json102-250 { + SELECT json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$'); +} {{{"a":2,"c":[4,5,{"f":7}]}}} +do_execsql_test json102-250-2 { + SELECT json_extract(jsonb('{"a":2,"c":[4,5,{"f":7}]}'), '$'); +} {{{"a":2,"c":[4,5,{"f":7}]}}} +do_execsql_test json102-250-3 { + SELECT json(jsonb_extract('{"a":2,"c":[4,5,{"f":7}]}', '$')); +} {{{"a":2,"c":[4,5,{"f":7}]}}} +do_execsql_test json102-250-4 { + SELECT json(jsonb_extract(jsonb('{"a":2,"c":[4,5,{"f":7}]}'), '$')); +} {{{"a":2,"c":[4,5,{"f":7}]}}} +do_execsql_test json102-260 { + SELECT json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.c'); +} {{[4,5,{"f":7}]}} +do_execsql_test json102-260-2 { + SELECT json_extract(jsonb('{"a":2,"c":[4,5,{"f":7}]}'), '$.c'); +} {{[4,5,{"f":7}]}} +do_execsql_test json102-260-3 { + SELECT json(jsonb_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.c')); +} {{[4,5,{"f":7}]}} +do_execsql_test json102-260-4 { + SELECT json(jsonb_extract(jsonb('{"a":2,"c":[4,5,{"f":7}]}'), '$.c')); +} {{[4,5,{"f":7}]}} +do_execsql_test json102-270 { + SELECT json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.c[2]'); +} {{{"f":7}}} +do_execsql_test json102-270-2 { + SELECT json_extract(jsonb('{"a":2,"c":[4,5,{"f":7}]}'), '$.c[2]'); +} {{{"f":7}}} +do_execsql_test json102-270-3 { + SELECT json(jsonb_extract(jsonb('{"a":2,"c":[4,5,{"f":7}]}'), '$.c[2]')); +} {{{"f":7}}} +do_execsql_test json102-270-4 { + SELECT json(jsonb_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.c[2]')); +} {{{"f":7}}} +do_execsql_test json102-280 { + SELECT json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.c[2].f'); +} {{7}} +do_execsql_test json102-280b { + SELECT jsonb_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.c[2].f'); +} {{7}} +do_execsql_test json102-290 { + SELECT json_extract('{"a":2,"c":[4,5],"f":7}','$.c','$.a'); +} {{[[4,5],2]}} +do_execsql_test json102-290-2 { + SELECT json_extract(jsonb('{"a":2,"c":[4,5],"f":7}'),'$.c','$.a'); +} {{[[4,5],2]}} +do_execsql_test json102-290-3 { + SELECT json(jsonb_extract('{"a":2,"c":[4,5],"f":7}','$.c','$.a')); +} {{[[4,5],2]}} +do_execsql_test json102-290-4 { + SELECT json(jsonb_extract(jsonb('{"a":2,"c":[4,5],"f":7}'),'$.c','$.a')); +} {{[[4,5],2]}} +do_execsql_test json102-300 { + SELECT json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.x'); +} {{}} +do_execsql_test json102-300b { + SELECT jsonb_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.x'); +} {{}} +do_execsql_test json102-310 { + SELECT json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.x', '$.a'); +} {{[null,2]}} +do_execsql_test json102-310-2 { + SELECT json_extract(jsonb('{"a":2,"c":[4,5,{"f":7}]}'), '$.x', '$.a'); +} {{[null,2]}} +do_execsql_test json102-310-3 { + SELECT json(jsonb_extract(jsonb('{"a":2,"c":[4,5,{"f":7}]}'), '$.x', '$.a')); +} {{[null,2]}} +do_execsql_test json102-310-43 { + SELECT json(jsonb_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.x', '$.a')); +} {{[null,2]}} +do_execsql_test json102-320 { + SELECT json_insert('{"a":2,"c":4}', '$.a', 99); +} {{{"a":2,"c":4}}} +do_execsql_test json102-320-2 { + SELECT json_insert(jsonb('{"a":2,"c":4}'), '$.a', 99); +} {{{"a":2,"c":4}}} +do_execsql_test json102-320-3 { + SELECT json(jsonb_insert('{"a":2,"c":4}', '$.a', 99)); +} {{{"a":2,"c":4}}} +do_execsql_test json102-320-4 { + SELECT json(jsonb_insert(jsonb('{"a":2,"c":4}'), '$.a', 99)); +} {{{"a":2,"c":4}}} +do_execsql_test json102-330 { + SELECT json_insert('{"a":2,"c":4}', '$.e', 99); +} {{{"a":2,"c":4,"e":99}}} +do_execsql_test json102-330-2 { + SELECT json_insert(jsonb('{"a":2,"c":4}'), '$.e', 99); +} {{{"a":2,"c":4,"e":99}}} +do_execsql_test json102-330-3 { + SELECT json(jsonb_insert('{"a":2,"c":4}', '$.e', 99)); +} {{{"a":2,"c":4,"e":99}}} +do_execsql_test json102-330-4 { + SELECT json(jsonb_insert(jsonb('{"a":2,"c":4}'), '$.e', 99)); +} {{{"a":2,"c":4,"e":99}}} +do_execsql_test json102-340 { + SELECT json_replace('{"a":2,"c":4}', '$.a', 99); +} {{{"a":99,"c":4}}} +do_execsql_test json102-340-2 { + SELECT json_replace(jsonb('{"a":2,"c":4}'), '$.a', 99); +} {{{"a":99,"c":4}}} +do_execsql_test json102-340-3 { + SELECT json(jsonb_replace('{"a":2,"c":4}', '$.a', 99)); +} {{{"a":99,"c":4}}} +do_execsql_test json102-340-4 { + SELECT json(jsonb_replace(jsonb('{"a":2,"c":4}'), '$.a', 99)); +} {{{"a":99,"c":4}}} +do_execsql_test json102-350 { + SELECT json_replace('{"a":2,"c":4}', '$.e', 99); +} {{{"a":2,"c":4}}} +do_execsql_test json102-350-2 { + SELECT json_replace(jsonb('{"a":2,"c":4}'), '$.e', 99); +} {{{"a":2,"c":4}}} +do_execsql_test json102-350-3 { + SELECT json(jsonb_replace('{"a":2,"c":4}', '$.e', 99)); +} {{{"a":2,"c":4}}} +do_execsql_test json102-350-4 { + SELECT json(jsonb_replace(jsonb('{"a":2,"c":4}'), '$.e', 99)); +} {{{"a":2,"c":4}}} +do_execsql_test json102-360 { + SELECT json_set('{"a":2,"c":4}', '$.a', 99); +} {{{"a":99,"c":4}}} +do_execsql_test json102-360-2 { + SELECT json_set(jsonb('{"a":2,"c":4}'), '$.a', 99); +} {{{"a":99,"c":4}}} +do_execsql_test json102-360-3 { + SELECT json(jsonb_set('{"a":2,"c":4}', '$.a', 99)); +} {{{"a":99,"c":4}}} +do_execsql_test json102-360-4 { + SELECT json(jsonb_set(jsonb('{"a":2,"c":4}'), '$.a', 99)); +} {{{"a":99,"c":4}}} +do_execsql_test json102-370 { + SELECT json_set('{"a":2,"c":4}', '$.e', 99); +} {{{"a":2,"c":4,"e":99}}} +do_execsql_test json102-370-2 { + SELECT json_set(jsonb('{"a":2,"c":4}'), '$.e', 99); +} {{{"a":2,"c":4,"e":99}}} +do_execsql_test json102-370-3 { + SELECT json(jsonb_set('{"a":2,"c":4}', '$.e', 99)); +} {{{"a":2,"c":4,"e":99}}} +do_execsql_test json102-370-4 { + SELECT json(jsonb_set(jsonb('{"a":2,"c":4}'), '$.e', 99)); +} {{{"a":2,"c":4,"e":99}}} +do_execsql_test json102-380 { + SELECT json_set('{"a":2,"c":4}', '$.c', '[97,96]'); +} {{{"a":2,"c":"[97,96]"}}} +do_execsql_test json102-380-2 { + SELECT json_set(jsonb('{"a":2,"c":4}'), '$.c', '[97,96]'); +} {{{"a":2,"c":"[97,96]"}}} +do_execsql_test json102-380-3 { + SELECT json(jsonb_set('{"a":2,"c":4}', '$.c', '[97,96]')); +} {{{"a":2,"c":"[97,96]"}}} +do_execsql_test json102-380-4 { + SELECT json(jsonb_set(jsonb('{"a":2,"c":4}'), '$.c', '[97,96]')); +} {{{"a":2,"c":"[97,96]"}}} +do_execsql_test json102-390 { + SELECT json_set('{"a":2,"c":4}', '$.c', json('[97,96]')); +} {{{"a":2,"c":[97,96]}}} +do_execsql_test json102-390-2 { + SELECT json_set(jsonb('{"a":2,"c":4}'), '$.c', json('[97,96]')); +} {{{"a":2,"c":[97,96]}}} +do_execsql_test json102-390-3 { + SELECT json(jsonb_set('{"a":2,"c":4}', '$.c', json('[97,96]'))); +} {{{"a":2,"c":[97,96]}}} +do_execsql_test json102-390-4 { + SELECT json(jsonb_set(jsonb('{"a":2,"c":4}'), '$.c', json('[97,96]'))); +} {{{"a":2,"c":[97,96]}}} +do_execsql_test json102-390-5 { + SELECT json_set('{"a":2,"c":4}', '$.c', jsonb('[97,96]')); +} {{{"a":2,"c":[97,96]}}} +do_execsql_test json102-390-6 { + SELECT json_set(jsonb('{"a":2,"c":4}'), '$.c', jsonb('[97,96]')); +} {{{"a":2,"c":[97,96]}}} +do_execsql_test json102-390-7 { + SELECT json(jsonb_set('{"a":2,"c":4}', '$.c', jsonb('[97,96]'))); +} {{{"a":2,"c":[97,96]}}} +do_execsql_test json102-390-8 { + SELECT json(jsonb_set(jsonb('{"a":2,"c":4}'), '$.c', jsonb('[97,96]'))); +} {{{"a":2,"c":[97,96]}}} +do_execsql_test json102-400 { + SELECT json_set('{"a":2,"c":4}', '$.c', json_array(97,96)); +} {{{"a":2,"c":[97,96]}}} +do_execsql_test json102-400-2 { + SELECT json_set(jsonb('{"a":2,"c":4}'), '$.c', json_array(97,96)); +} {{{"a":2,"c":[97,96]}}} +do_execsql_test json102-400-3 { + SELECT json(jsonb_set('{"a":2,"c":4}', '$.c', json_array(97,96))); +} {{{"a":2,"c":[97,96]}}} +do_execsql_test json102-400-4 { + SELECT json(jsonb_set(jsonb('{"a":2,"c":4}'), '$.c', json_array(97,96))); +} {{{"a":2,"c":[97,96]}}} +do_execsql_test json102-400-5 { + SELECT json_set('{"a":2,"c":4}', '$.c', jsonb_array(97,96)); +} {{{"a":2,"c":[97,96]}}} +do_execsql_test json102-400-6 { + SELECT json_set(jsonb('{"a":2,"c":4}'), '$.c', jsonb_array(97,96)); +} {{{"a":2,"c":[97,96]}}} +do_execsql_test json102-400-7 { + SELECT json(jsonb_set('{"a":2,"c":4}', '$.c', jsonb_array(97,96))); +} {{{"a":2,"c":[97,96]}}} +do_execsql_test json102-400-8 { + SELECT json(jsonb_set(jsonb('{"a":2,"c":4}'), '$.c', jsonb_array(97,96))); +} {{{"a":2,"c":[97,96]}}} +do_execsql_test json102-410 { + SELECT json_object('a',2,'c',4); +} {{{"a":2,"c":4}}} +do_execsql_test json102-410b { + SELECT json(jsonb_object('a',2,'c',4)); +} {{{"a":2,"c":4}}} +do_execsql_test json102-420 { + SELECT json_object('a',2,'c','{e:5}'); +} {{{"a":2,"c":"{e:5}"}}} +do_execsql_test json102-420b { + SELECT json(jsonb_object('a',2,'c','{e:5}')); +} {{{"a":2,"c":"{e:5}"}}} +do_execsql_test json102-430 { + SELECT json_object('a',2,'c',json_object('e',5)); +} {{{"a":2,"c":{"e":5}}}} +do_execsql_test json102-430-2 { + SELECT json(jsonb_object('a',2,'c',json_object('e',5))); +} {{{"a":2,"c":{"e":5}}}} +do_execsql_test json102-430-3 { + SELECT json_object('a',2,'c',jsonb_object('e',5)); +} {{{"a":2,"c":{"e":5}}}} +do_execsql_test json102-430-4 { + SELECT json(jsonb_object('a',2,'c',jsonb_object('e',5))); +} {{{"a":2,"c":{"e":5}}}} +do_execsql_test json102-440 { + SELECT json_remove('[0,1,2,3,4]','$[2]'); +} {{[0,1,3,4]}} +do_execsql_test json102-440-2 { + SELECT json_remove(jsonb('[0,1,2,3,4]'),'$[2]'); +} {{[0,1,3,4]}} +do_execsql_test json102-440-3 { + SELECT json(jsonb_remove('[0,1,2,3,4]','$[2]')); +} {{[0,1,3,4]}} +do_execsql_test json102-440-4 { + SELECT json(jsonb_remove(jsonb('[0,1,2,3,4]'),'$[2]')); +} {{[0,1,3,4]}} +do_execsql_test json102-450 { + SELECT json_remove('[0,1,2,3,4]','$[2]','$[0]'); +} {{[1,3,4]}} +do_execsql_test json102-450-2 { + SELECT json_remove(jsonb('[0,1,2,3,4]'),'$[2]','$[0]'); +} {{[1,3,4]}} +do_execsql_test json102-450-3 { + SELECT json(jsonb_remove('[0,1,2,3,4]','$[2]','$[0]')); +} {{[1,3,4]}} +do_execsql_test json102-450-4 { + SELECT json(jsonb_remove(jsonb('[0,1,2,3,4]'),'$[2]','$[0]')); +} {{[1,3,4]}} +do_execsql_test json102-460 { + SELECT json_remove('[0,1,2,3,4]','$[0]','$[2]'); +} {{[1,2,4]}} +do_execsql_test json102-460-2 { + SELECT json_remove(jsonb('[0,1,2,3,4]'),'$[0]','$[2]'); +} {{[1,2,4]}} +do_execsql_test json102-460-3 { + SELECT json(jsonb_remove('[0,1,2,3,4]','$[0]','$[2]')); +} {{[1,2,4]}} +do_execsql_test json102-460-4 { + SELECT json(jsonb_remove(jsonb('[0,1,2,3,4]'),'$[0]','$[2]')); +} {{[1,2,4]}} +do_execsql_test json102-470 { + SELECT json_remove('{"x":25,"y":42}'); +} {{{"x":25,"y":42}}} +do_execsql_test json102-470-2 { + SELECT json_remove(jsonb('{"x":25,"y":42}')); +} {{{"x":25,"y":42}}} +do_execsql_test json102-470-3 { + SELECT json(jsonb_remove('{"x":25,"y":42}')); +} {{{"x":25,"y":42}}} +do_execsql_test json102-470-4 { + SELECT json(jsonb_remove(jsonb('{"x":25,"y":42}'))); +} {{{"x":25,"y":42}}} +do_execsql_test json102-480 { + SELECT json_remove('{"x":25,"y":42}','$.z'); +} {{{"x":25,"y":42}}} +do_execsql_test json102-480-2 { + SELECT json_remove(jsonb('{"x":25,"y":42}'),'$.z'); +} {{{"x":25,"y":42}}} +do_execsql_test json102-480-3 { + SELECT json(jsonb_remove('{"x":25,"y":42}','$.z')); +} {{{"x":25,"y":42}}} +do_execsql_test json102-480-4 { + SELECT json(jsonb_remove(jsonb('{"x":25,"y":42}'),'$.z')); +} {{{"x":25,"y":42}}} +do_execsql_test json102-490 { + SELECT json_remove('{"x":25,"y":42}','$.y'); +} {{{"x":25}}} +do_execsql_test json102-490-2 { + SELECT json_remove(jsonb('{"x":25,"y":42}'),'$.y'); +} {{{"x":25}}} +do_execsql_test json102-490-3 { + SELECT json(jsonb_remove('{"x":25,"y":42}','$.y')); +} {{{"x":25}}} +do_execsql_test json102-490-4 { + SELECT json(jsonb_remove(jsonb('{"x":25,"y":42}'),'$.y')); +} {{{"x":25}}} +do_execsql_test json102-500 { + SELECT json_remove('{"x":25,"y":42}','$'); +} {{}} +do_execsql_test json102-500-2 { + SELECT json_remove(jsonb('{"x":25,"y":42}'),'$'); +} {{}} +do_execsql_test json102-500-3 { + SELECT json(jsonb_remove('{"x":25,"y":42}','$')); +} {{}} +do_execsql_test json102-500-4 { + SELECT json(jsonb_remove(jsonb('{"x":25,"y":42}'),'$')); +} {{}} +do_execsql_test json102-510 { + SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}'); +} {{object}} +do_execsql_test json102-510b { + SELECT json_type(x'cc0f1761cb0b133235332e350102001778'); +} {{object}} +do_execsql_test json102-520 { + SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}','$'); +} {{object}} +do_execsql_test json102-520b { + SELECT json_type(x'cc0f1761cb0b133235332e350102001778','$'); +} {{object}} +do_execsql_test json102-530 { + SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a'); +} {{array}} +do_execsql_test json102-530b { + SELECT json_type(x'cc0f1761cb0b133235332e350102001778','$.a'); +} {{array}} +do_execsql_test json102-540 { + SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[0]'); +} {{integer}} +do_execsql_test json102-540b { + SELECT json_type(x'cc0f1761cb0b133235332e350102001778','$.a[0]'); +} {{integer}} +do_execsql_test json102-550 { + SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[1]'); +} {{real}} +do_execsql_test json102-550b { + SELECT json_type(x'cc0f1761cb0b133235332e350102001778','$.a[1]'); +} {{real}} +do_execsql_test json102-560 { + SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[2]'); +} {{true}} +do_execsql_test json102-560b { + SELECT json_type(x'cc0f1761cb0b133235332e350102001778','$.a[2]'); +} {{true}} +do_execsql_test json102-570 { + SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[3]'); +} {{false}} +do_execsql_test json102-570b { + SELECT json_type(x'cc0f1761cb0b133235332e350102001778','$.a[3]'); +} {{false}} +do_execsql_test json102-580 { + SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[4]'); +} {{null}} +do_execsql_test json102-580b { + SELECT json_type(x'cc0f1761cb0b133235332e350102001778','$.a[4]'); +} {{null}} +do_execsql_test json102-590 { + SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[5]'); +} {{text}} +do_execsql_test json102-590b { + SELECT json_type(x'cc0f1761cb0b133235332e350102001778','$.a[5]'); +} {{text}} +do_execsql_test json102-600 { + SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[6]'); +} {{}} +do_execsql_test json102-600b { + SELECT json_type(x'cc0f1761cb0b133235332e350102001778','$.a[6]'); +} {{}} +do_execsql_test json102-610 { + SELECT json_valid(char(123)||'"x":35'||char(125)); +} {{1}} +do_execsql_test json102-620 { + SELECT json_valid(char(123)||'"x":35'); +} {{0}} + +ifcapable vtab { +do_execsql_test json102-1000 { + CREATE TABLE user(name,phone,phoneb); + INSERT INTO user(name,phone) VALUES + ('Alice','["919-555-2345","804-555-3621"]'), + ('Bob','["201-555-8872"]'), + ('Cindy','["704-555-9983"]'), + ('Dave','["336-555-8421","704-555-4321","803-911-4421"]'); + UPDATE user SET phoneb=jsonb(phone); + SELECT DISTINCT user.name + FROM user, json_each(user.phone) + WHERE json_each.value LIKE '704-%' + ORDER BY 1; +} {Cindy Dave} +do_execsql_test json102-1000b { + SELECT DISTINCT user.name + FROM user, json_each(user.phoneb) + WHERE json_each.value LIKE '704-%' + ORDER BY 1; +} {Cindy Dave} + +do_execsql_test json102-1010 { + UPDATE user + SET phone=json_extract(phone,'$[0]') + WHERE json_array_length(phone)<2; + SELECT name, substr(phone,1,5) FROM user ORDER BY name; +} {Alice {["919} Bob 201-5 Cindy 704-5 Dave {["336}} +do_execsql_test json102-1011 { + SELECT name FROM user WHERE phone LIKE '704-%' + UNION + SELECT user.name + FROM user, json_each(user.phone) + WHERE json_valid(user.phone) + AND json_each.value LIKE '704-%'; +} {Cindy Dave} + +do_execsql_test json102-1100 { + CREATE TABLE big(json JSON); + INSERT INTO big(json) VALUES('{ + "id":123, + "stuff":[1,2,3,4], + "partlist":[ + {"uuid":"bb108722-572e-11e5-9320-7f3b63a4ca74"}, + {"uuid":"c690dc14-572e-11e5-95f9-dfc8861fd535"}, + {"subassembly":[ + {"uuid":"6fa5181e-5721-11e5-a04e-57f3d7b32808"} + ]} + ] + }'); + INSERT INTO big(json) VALUES('{ + "id":456, + "stuff":["hello","world","xyzzy"], + "partlist":[ + {"uuid":false}, + {"uuid":"c690dc14-572e-11e5-95f9-dfc8861fd535"} + ] + }'); +} {} +set correct_answer [list \ + 1 {$.id} 123 \ + 1 {$.stuff[0]} 1 \ + 1 {$.stuff[1]} 2 \ + 1 {$.stuff[2]} 3 \ + 1 {$.stuff[3]} 4 \ + 1 {$.partlist[0].uuid} bb108722-572e-11e5-9320-7f3b63a4ca74 \ + 1 {$.partlist[1].uuid} c690dc14-572e-11e5-95f9-dfc8861fd535 \ + 1 {$.partlist[2].subassembly[0].uuid} 6fa5181e-5721-11e5-a04e-57f3d7b32808 \ + 2 {$.id} 456 \ + 2 {$.stuff[0]} hello \ + 2 {$.stuff[1]} world \ + 2 {$.stuff[2]} xyzzy \ + 2 {$.partlist[0].uuid} 0 \ + 2 {$.partlist[1].uuid} c690dc14-572e-11e5-95f9-dfc8861fd535] +do_execsql_test json102-1110 { + SELECT big.rowid, fullkey, value + FROM big, json_tree(big.json) + WHERE json_tree.type NOT IN ('object','array') + ORDER BY +big.rowid, +json_tree.id +} $correct_answer +do_execsql_test json102-1110b { + SELECT big.rowid, fullkey, value + FROM big, json_tree(jsonb(big.json)) + WHERE json_tree.type NOT IN ('object','array') + ORDER BY +big.rowid, +json_tree.id +} $correct_answer +do_execsql_test json102-1120 { + SELECT big.rowid, fullkey, atom + FROM big, json_tree(big.json) + WHERE atom IS NOT NULL + ORDER BY +big.rowid, +json_tree.id +} $correct_answer + +do_execsql_test json102-1130 { + SELECT DISTINCT json_extract(big.json,'$.id') + FROM big, json_tree(big.json,'$.partlist') + WHERE json_tree.key='uuid' + AND json_tree.value='6fa5181e-5721-11e5-a04e-57f3d7b32808'; +} {123} +do_execsql_test json102-1131 { + SELECT DISTINCT json_extract(big.json,'$.id') + FROM big, json_tree(big.json,'$') + WHERE json_tree.key='uuid' + AND json_tree.value='6fa5181e-5721-11e5-a04e-57f3d7b32808'; +} {123} +do_execsql_test json102-1132 { + SELECT DISTINCT json_extract(big.json,'$.id') + FROM big, json_tree(big.json) + WHERE json_tree.key='uuid' + AND json_tree.value='6fa5181e-5721-11e5-a04e-57f3d7b32808'; +} {123} +} ;# end ifcapable vtab + +#------------------------------------------------------------------------- +# Test that json_valid() correctly identifies non-ascii range +# characters as non-whitespace. +# +do_execsql_test json102-1201 { SELECT json_valid(char(32) || '"xyz"') } 1 +do_execsql_test json102-1202 { SELECT json_valid(char(200) || '"xyz"') } 0 + +# Off-by-one error in jsonAppendString() +# +for {set i 0} {$i<100} {incr i} { + set str abcdef[string repeat \" [expr {$i+50}]]uvwxyz + do_test json102-[format %d [expr {$i+1300}]] { + db eval {SELECT json_extract(json_array($::str),'$[0]')==$::str} + } {1} +} + +#------------------------------------------------------------------------- +# 2017-04-08 ticket b93be8729a895a528e2849fca99f7 +# JSON extension accepts invalid numeric values +# +# JSON does not allow leading zeros. But the JSON extension was +# allowing them. The following tests verify that the problem is now +# fixed. +# +foreach {id j x0 x5} { + 1401 {'{"x":01}'} 0 0 + 1402 {'{"x":-01}'} 0 0 + 1403 {'{"x":0}'} 1 1 + 1404 {'{"x":-0}'} 1 1 + 1405 {'{"x":0.1}'} 1 1 + 1406 {'{"x":-0.1}'} 1 1 + 1407 {'{"x":0.0000}'} 1 1 + 1408 {'{"x":-0.0000}'} 1 1 + 1409 {'{"x":01.5}'} 0 0 + 1410 {'{"x":-01.5}'} 0 0 + 1411 {'{"x":00}'} 0 0 + 1412 {'{"x":-00}'} 0 0 + 1413 {'{"x":+0}'} 0 1 + 1414 {'{"x":+5}'} 0 1 + 1415 {'{"x":+5.5}'} 0 1 +} { + do_execsql_test json102-$id " + SELECT json_valid($j), NOT json_error_position($j); + " [list $x0 $x5] +} + +#------------------------------------------------------------------------ +# 2017-04-10 ticket 6c9b5514077fed34551f98e64c09a10dc2fc8e16 +# JSON extension accepts strings containing control characters. +# +# The JSON spec requires that all control characters be escaped. +# +do_execsql_test json102-1500 { + WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<0x20) + SELECT x FROM c WHERE json_valid(printf('{"a":"x%sz"}', char(x))) ORDER BY x; +} {32} + +# All control characters are escaped +# +do_execsql_test json102-1501 { + WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<0x1f) + SELECT sum(json_valid(json_quote('a'||char(x)||'z'))) FROM c ORDER BY x; +} {31} + +# 2022-01-10 tests for -> and ->> operators +# +reset_db +do_execsql_test json102-1600 { + CREATE TABLE t1(id INTEGER PRIMARY KEY, x JSON); + INSERT INTO t1(id,x) VALUES + (1, '{"a":null}'), + (2, '{"a":123}'), + (3, '{"a":4.5}'), + (4, '{"a":"six"}'), + (5, '{"a":[7,8]}'), + (6, '{"a":{"b":9}}'), + (7, '{"b":999}'); + SELECT + id, + x->'a' AS '->', + CASE WHEN subtype(x->'a') THEN 'json' ELSE typeof(x->'a') END AS 'type', + x->>'a' AS '->>', + CASE WHEN subtype(x->>'a') THEN 'json' ELSE typeof(x->>'a') END AS 'type', + json_extract(x,'$.a') AS 'json_extract', + CASE WHEN subtype(json_extract(x,'$.a')) + THEN 'json' ELSE typeof(json_extract(x,'$.a')) END AS 'type' + FROM t1 ORDER BY id; +} [list \ + 1 null json {} null {} null \ + 2 123 json 123 integer 123 integer \ + 3 4.5 json 4.5 real 4.5 real \ + 4 {"six"} json six text six text \ + 5 {[7,8]} json {[7,8]} text {[7,8]} json \ + 6 {{"b":9}} json {{"b":9}} text {{"b":9}} json \ + 7 {} null {} null {} null +] +do_execsql_test json102-1610 { + DELETE FROM t1; + INSERT INTO t1(x) VALUES('[null,123,4.5,"six",[7,8],{"b":9}]'); + WITH c(y) AS (VALUES(0),(1),(2),(3),(4),(5),(6)) + SELECT + y, + x->y AS '->', + CASE WHEN subtype(x->y) THEN 'json' ELSE typeof(x->y) END AS 'type', + x->>y AS '->>', + CASE WHEN subtype(x->>y) THEN 'json' ELSE typeof(x->>y) END AS 'type', + json_extract(x,format('$[%d]',y)) AS 'json_extract', + CASE WHEN subtype(json_extract(x,format('$[%d]',y))) + THEN 'json' ELSE typeof(json_extract(x,format('$[%d]',y))) END AS 'type' + FROM c, t1 ORDER BY y; +} [list \ + 0 null json {} null {} null \ + 1 123 json 123 integer 123 integer \ + 2 4.5 json 4.5 real 4.5 real \ + 3 {"six"} json six text six text \ + 4 {[7,8]} json {[7,8]} text {[7,8]} json \ + 5 {{"b":9}} json {{"b":9}} text {{"b":9}} json \ + 6 {} null {} null {} null +] + +reset_db +do_execsql_test json102-1700 { + CREATE TABLE t1(a1 DATE, a2 INTEGER PRIMARY KEY, a3 INTEGER, memo TEXT); + CREATE INDEX t1x1 ON t1(a3, a1, memo->>'y'); + INSERT INTO t1(a2,a1,a3,memo) VALUES (876, '2023-08-03', 5, '{"x":77,"y":4}'); +} +do_execsql_test json102-1710 { + UPDATE t1 SET memo = JSON_REMOVE(memo, '$.y'); + PRAGMA integrity_check; + SELECT * FROM t1; +} {ok 2023-08-03 876 5 {{"x":77}}} +do_execsql_test json102-1720 { + UPDATE t1 SET memo = JSON_SET(memo, '$.y', 6) + WHERE a2 IN (876) AND JSON_TYPE(memo, '$.y') IS NULL; + PRAGMA integrity_check; + SELECT * FROM t1; +} {ok 2023-08-03 876 5 {{"x":77,"y":6}}} + +finish_test diff --git a/test/json103.test b/test/json103.test new file mode 100644 index 0000000..e748307 --- /dev/null +++ b/test/json103.test @@ -0,0 +1,93 @@ +# 2015-12-30 +# +# The author disclaims copyright to this source code. In place of +# a legal notice, here is a blessing: +# +# May you do good and not evil. +# May you find forgiveness for yourself and forgive others. +# May you share freely, never taking more than you give. +# +#*********************************************************************** +# This file implements tests for JSON aggregate SQL functions +# + +set testdir [file dirname $argv0] +source $testdir/tester.tcl + +do_execsql_test json103-100 { + CREATE TABLE t1(a,b,c); + WITH RECURSIVE c(x) AS (VALUES(1) UNION SELECT x+1 FROM c WHERE x<100) + INSERT INTO t1(a,b,c) SELECT x, x%3, printf('n%d',x) FROM c; + UPDATE t1 SET a='orange' WHERE rowid=39; + UPDATE t1 SET a=32.5 WHERE rowid=31; + UPDATE t1 SET a=x'303132' WHERE rowid=29; + UPDATE t1 SET a=NULL WHERE rowid=37; + SELECT json_group_array(a) FROM t1 WHERE a<0 AND typeof(a)!='blob'; +} {{[]}} +do_catchsql_test json103-101 { + SELECT json_group_array(a) FROM t1; +} {1 {JSON cannot hold BLOB values}} +do_execsql_test json103-110 { + SELECT json_group_array(a) FROM t1 + WHERE rowid BETWEEN 31 AND 39; +} {{[32.5,32,33,34,35,36,null,38,"orange"]}} +do_execsql_test json103-111 { + SELECT json_array_length(json_group_array(a)) FROM t1 + WHERE rowid BETWEEN 31 AND 39; +} {9} +do_execsql_test json103-120 { + SELECT b, json_group_array(a) FROM t1 WHERE rowid<10 GROUP BY b ORDER BY b; +} {0 {[3,6,9]} 1 {[1,4,7]} 2 {[2,5,8]}} + +do_execsql_test json103-200 { + SELECT json_group_object(c,a) FROM t1 WHERE a<0 AND typeof(a)!='blob'; +} {{{}}} +do_catchsql_test json103-201 { + SELECT json_group_object(c,a) FROM t1; +} {1 {JSON cannot hold BLOB values}} + +do_execsql_test json103-210 { + SELECT json_group_object(c,a) FROM t1 + WHERE rowid BETWEEN 31 AND 39 AND rowid%2==1; +} {{{"n31":32.5,"n33":33,"n35":35,"n37":null,"n39":"orange"}}} +do_execsql_test json103-220 { + SELECT b, json_group_object(c,a) FROM t1 + WHERE rowid<7 GROUP BY b ORDER BY b; +} {0 {{"n3":3,"n6":6}} 1 {{"n1":1,"n4":4}} 2 {{"n2":2,"n5":5}}} + +# ticket https://www.sqlite.org/src/info/f45ac567eaa9f93c 2016-01-30 +# Invalid JSON generated by json_group_array() +# +# The underlying problem is a failure to reset Mem.eSubtype +# +do_execsql_test json103-300 { + DROP TABLE IF EXISTS t1; + CREATE TABLE t1(x); + INSERT INTO t1 VALUES(1),('abc'); + SELECT + json_group_array(x), + json_group_array(json_object('x',x)) + FROM t1; +} {{[1,"abc"]} {[{"x":1},{"x":"abc"}]}} + +# json_group_array() and json_group_object() work as window functions. +# +ifcapable windowfunc { + do_execsql_test json103-400 { + CREATE TABLE t4(x); + INSERT INTO t4 VALUES + (1), + ('a,b'), + (3), + ('x"y'), + (5), + (6), + (7); + SELECT json_group_array(x) OVER (ROWS 2 PRECEDING) FROM t4; + } {{[1]} {[1,"a,b"]} {[1,"a,b",3]} {["a,b",3,"x\"y"]} {[3,"x\"y",5]} {["x\"y",5,6]} {[5,6,7]}} + do_execsql_test json103-410 { + SELECT json_group_object(rowid, x) OVER (ROWS 2 PRECEDING) FROM t4; + } {{{"1":1}} {{"1":1,"2":"a,b"}} {{"1":1,"2":"a,b","3":3}} {{"2":"a,b","3":3,"4":"x\"y"}} {{"3":3,"4":"x\"y","5":5}} {{"4":"x\"y","5":5,"6":6}} {{"5":5,"6":6,"7":7}}} +} + +finish_test diff --git a/test/json104.test b/test/json104.test new file mode 100644 index 0000000..c3c43d1 --- /dev/null +++ b/test/json104.test @@ -0,0 +1,195 @@ +# 2017-03-22 +# +# The author disclaims copyright to this source code. In place of +# a legal notice, here is a blessing: +# +# May you do good and not evil. +# May you find forgiveness for yourself and forgive others. +# May you share freely, never taking more than you give. +# +#*********************************************************************** +# This file implements tests for json_patch(A,B) SQL function. +# + +set testdir [file dirname $argv0] +source $testdir/tester.tcl +set testprefix json104 + +# This is the example from pages 2 and 3 of RFC-7396 +do_execsql_test json104-100 { + SELECT json_patch('{ + "a": "b", + "c": { + "d": "e", + "f": "g" + } + }','{ + "a":"z", + "c": { + "f": null + } + }'); +} {{{"a":"z","c":{"d":"e"}}}} +do_execsql_test json104-101 { + SELECT json_patch('{ + "a": "b", + "c": { + "d": "e", + "f": "g" + } + }','{ + a:"z", + c: { + f: null + } + }'); +} {{{"a":"z","c":{"d":"e"}}}} +do_execsql_test json104-102 { + SELECT json_patch('{ + a: "b", + c: { + d: "e", + f: "g" + } + }','{ + "a":"z", + "c": { + "f": null + } + }'); +} {{{"a":"z","c":{"d":"e"}}}} +do_execsql_test json104-103 { + SELECT json_patch('{ + a: "b", + c: { + d: "e", + f: "g" + } + }','{ + a:"z", + c: { + f: null + } + }'); +} {{{"a":"z","c":{"d":"e"}}}} + + +# This is the example from pages 4 and 5 of RFC-7396 +do_execsql_test json104-110 { + SELECT json_patch('{ + "title": "Goodbye!", + "author" : { + "givenName" : "John", + "familyName" : "Doe" + }, + "tags":[ "example", "sample" ], + "content": "This will be unchanged" + }','{ + "title": "Hello!", + "phoneNumber": "+01-123-456-7890", + "author": { + "familyName": null + }, + "tags": [ "example" ] + }'); +} {{{"title":"Hello!","author":{"givenName":"John"},"tags":["example"],"content":"This will be unchanged","phoneNumber":"+01-123-456-7890"}}} + +do_execsql_test json104-200 { + SELECT json_patch('[1,2,3]','{"x":null}'); +} {{{}}} +do_execsql_test json104-210 { + SELECT json_patch('[1,2,3]','{"x":null,"y":1,"z":null}'); +} {{{"y":1}}} +do_execsql_test json104-220 { + SELECT json_patch('{}','{"a":{"bb":{"ccc":null}}}'); +} {{{"a":{"bb":{}}}}} +do_execsql_test json104-221 { + SELECT json_patch('{}','{"a":{"bb":{"ccc":[1,null,3]}}}'); +} {{{"a":{"bb":{"ccc":[1,null,3]}}}}} +do_execsql_test json104-222 { + SELECT json_patch('{}','{"a":{"bb":{"ccc":[1,{"dddd":null},3]}}}'); +} {{{"a":{"bb":{"ccc":[1,{"dddd":null},3]}}}}} + +# Example test cases at the end of the RFC-7396 document +do_execsql_test json104-300 { + SELECT json_patch('{"a":"b"}','{"a":"c"}'); +} {{{"a":"c"}}} +do_execsql_test json104-300a { + SELECT coalesce(json_patch(null,'{"a":"c"}'), 'real-null'); +} {{real-null}} +do_execsql_test json104-301 { + SELECT json_patch('{"a":"b"}','{"b":"c"}'); +} {{{"a":"b","b":"c"}}} +do_execsql_test json104-302 { + SELECT json_patch('{"a":"b"}','{"a":null}'); +} {{{}}} +do_execsql_test json104-303 { + SELECT json_patch('{"a":"b","b":"c"}','{"a":null}'); +} {{{"b":"c"}}} +do_execsql_test json104-304 { + SELECT json_patch('{"a":["b"]}','{"a":"c"}'); +} {{{"a":"c"}}} +do_execsql_test json104-305 { + SELECT json_patch('{"a":"c"}','{"a":["b"]}'); +} {{{"a":["b"]}}} +do_execsql_test json104-306 { + SELECT json_patch('{"a":{"b":"c"}}','{"a":{"b":"d","c":null}}'); +} {{{"a":{"b":"d"}}}} +do_execsql_test json104-307 { + SELECT json_patch('{"a":[{"b":"c"}]}','{"a":[1]}'); +} {{{"a":[1]}}} +do_execsql_test json104-308 { + SELECT json_patch('["a","b"]','["c","d"]'); +} {{["c","d"]}} +do_execsql_test json104-309 { + SELECT json_patch('{"a":"b"}','["c"]'); +} {{["c"]}} +do_execsql_test json104-310 { + SELECT json_patch('{"a":"foo"}','null'); +} {{null}} +do_execsql_test json104-310a { + SELECT coalesce(json_patch('{"a":"foo"}',null), 'real-null'); +} {{real-null}} +do_execsql_test json104-311 { + SELECT json_patch('{"a":"foo"}','"bar"'); +} {{"bar"}} +do_execsql_test json104-312 { + SELECT json_patch('{"e":null}','{"a":1}'); +} {{{"e":null,"a":1}}} +do_execsql_test json104-313 { + SELECT json_patch('[1,2]','{"a":"b","c":null}'); +} {{{"a":"b"}}} +do_execsql_test json104-314 { + SELECT json_patch('{}','{"a":{"bb":{"ccc":null}}}'); +} {{{"a":{"bb":{}}}}} +do_execsql_test json104-320 { + SELECT json_patch('{"x":{"one":1}}','{"x":{"two":2},"x":"three"}'); +} {{{"x":"three"}}} + +#------------------------------------------------------------------------- + +do_execsql_test 401 { + CREATE TABLE obj(x); + INSERT INTO obj VALUES('{"a":1,"b":2}'); + SELECT * FROM obj; +} {{{"a":1,"b":2}}} +do_execsql_test 402 { + UPDATE obj SET x = json_insert(x, '$.c', 3); + SELECT * FROM obj; +} {{{"a":1,"b":2,"c":3}}} +do_execsql_test 403 { + SELECT json_extract(x, '$.b') FROM obj; + SELECT json_extract(x, '$."b"') FROM obj; +} {2 2} +do_execsql_test 404 { + UPDATE obj SET x = json_set(x, '$."b"', 555); + SELECT json_extract(x, '$.b') FROM obj; + SELECT json_extract(x, '$."b"') FROM obj; +} {555 555} +do_execsql_test 405 { + UPDATE obj SET x = json_set(x, '$."d"', 4); + SELECT json_extract(x, '$."d"') FROM obj; +} {4} + + +finish_test diff --git a/test/json105.test b/test/json105.test new file mode 100644 index 0000000..509db94 --- /dev/null +++ b/test/json105.test @@ -0,0 +1,113 @@ +# 2019-11-22 +# +# The author disclaims copyright to this source code. In place of +# a legal notice, here is a blessing: +# +# May you do good and not evil. +# May you find forgiveness for yourself and forgive others. +# May you share freely, never taking more than you give. +# +#*********************************************************************** +# This file implements tests for "[#]" extension to json-path +# + +set testdir [file dirname $argv0] +source $testdir/tester.tcl +set testprefix json105 + +# This is the example from pages 2 and 3 of RFC-7396 +db eval { + CREATE TABLE t1(j); + INSERT INTO t1(j) VALUES('{"a":1,"b":[1,[2,3],4],"c":99}'); +} +proc json_extract_test {testnum path result} { + do_execsql_test json105-1.$testnum "SELECT quote(json_extract(j,$path)) FROM t1" $result +} +json_extract_test 10 {'$.b[#]'} NULL +json_extract_test 20 {'$.b[#-1]'} 4 +json_extract_test 30 {'$.b[#-2]'} {'[2,3]'} +json_extract_test 31 {'$.b[#-02]'} {'[2,3]'} +json_extract_test 40 {'$.b[#-3]'} 1 +json_extract_test 50 {'$.b[#-4]'} NULL +json_extract_test 60 {'$.b[#-2][#-1]'} 3 +json_extract_test 70 {'$.b[0]','$.b[#-1]'} {'[1,4]'} + +json_extract_test 100 {'$.a[#-1]'} NULL +json_extract_test 110 {'$.b[#-000001]'} 4 + +proc json_remove_test {testnum path result} { + do_execsql_test json105-2.$testnum "SELECT quote(json_remove(j,$path)) FROM t1" $result +} +json_remove_test 10 {'$.b[#]'} {'{"a":1,"b":[1,[2,3],4],"c":99}'} +json_remove_test 20 {'$.b[#-0]'} {'{"a":1,"b":[1,[2,3],4],"c":99}'} +json_remove_test 30 {'$.b[#-1]'} {'{"a":1,"b":[1,[2,3]],"c":99}'} +json_remove_test 40 {'$.b[#-2]'} {'{"a":1,"b":[1,4],"c":99}'} +json_remove_test 50 {'$.b[#-3]'} {'{"a":1,"b":[[2,3],4],"c":99}'} +json_remove_test 60 {'$.b[#-4]'} {'{"a":1,"b":[1,[2,3],4],"c":99}'} +json_remove_test 70 {'$.b[#-2][#-1]'} {'{"a":1,"b":[1,[2],4],"c":99}'} + +json_remove_test 100 {'$.b[0]','$.b[#-1]'} {'{"a":1,"b":[[2,3]],"c":99}'} +json_remove_test 110 {'$.b[#-1]','$.b[0]'} {'{"a":1,"b":[[2,3]],"c":99}'} +json_remove_test 120 {'$.b[#-1]','$.b[#-2]'} {'{"a":1,"b":[[2,3]],"c":99}'} +json_remove_test 130 {'$.b[#-1]','$.b[#-1]'} {'{"a":1,"b":[1],"c":99}'} +json_remove_test 140 {'$.b[#-2]','$.b[#-1]'} {'{"a":1,"b":[1],"c":99}'} + +proc json_insert_test {testnum x result} { + do_execsql_test json105-3.$testnum "SELECT quote(json_insert(j,$x)) FROM t1" $result +} +json_insert_test 10 {'$.b[#]','AAA'} {'{"a":1,"b":[1,[2,3],4,"AAA"],"c":99}'} +json_insert_test 20 {'$.b[1][#]','AAA'} {'{"a":1,"b":[1,[2,3,"AAA"],4],"c":99}'} +json_insert_test 30 {'$.b[1][#]','AAA','$.b[#]','BBB'} \ + {'{"a":1,"b":[1,[2,3,"AAA"],4,"BBB"],"c":99}'} +json_insert_test 40 {'$.b[#]','AAA','$.b[#]','BBB'} \ + {'{"a":1,"b":[1,[2,3],4,"AAA","BBB"],"c":99}'} + +proc json_set_test {testnum x result} { + do_execsql_test json105-4.$testnum "SELECT quote(json_set(j,$x)) FROM t1" $result +} +json_set_test 10 {'$.b[#]','AAA'} {'{"a":1,"b":[1,[2,3],4,"AAA"],"c":99}'} +json_set_test 20 {'$.b[1][#]','AAA'} {'{"a":1,"b":[1,[2,3,"AAA"],4],"c":99}'} +json_set_test 30 {'$.b[1][#]','AAA','$.b[#]','BBB'} \ + {'{"a":1,"b":[1,[2,3,"AAA"],4,"BBB"],"c":99}'} +json_set_test 40 {'$.b[#]','AAA','$.b[#]','BBB'} \ + {'{"a":1,"b":[1,[2,3],4,"AAA","BBB"],"c":99}'} +json_set_test 50 {'$.b[#-1]','AAA'} {'{"a":1,"b":[1,[2,3],"AAA"],"c":99}'} +json_set_test 60 {'$.b[1][#-1]','AAA'} {'{"a":1,"b":[1,[2,"AAA"],4],"c":99}'} +json_set_test 70 {'$.b[1][#-1]','AAA','$.b[#-1]','BBB'} \ + {'{"a":1,"b":[1,[2,"AAA"],"BBB"],"c":99}'} +json_set_test 80 {'$.b[#-1]','AAA','$.b[#-1]','BBB'} \ + {'{"a":1,"b":[1,[2,3],"BBB"],"c":99}'} + +proc json_replace_test {testnum x result} { + do_execsql_test json105-5.$testnum "SELECT quote(json_replace(j,$x)) FROM t1" $result +} +json_replace_test 10 {'$.b[#]','AAA'} {'{"a":1,"b":[1,[2,3],4],"c":99}'} +json_replace_test 20 {'$.b[1][#]','AAA'} {'{"a":1,"b":[1,[2,3],4],"c":99}'} +json_replace_test 30 {'$.b[1][#]','AAA','$.b[#]','BBB'} \ + {'{"a":1,"b":[1,[2,3],4],"c":99}'} +json_replace_test 40 {'$.b[#]','AAA','$.b[#]','BBB'} \ + {'{"a":1,"b":[1,[2,3],4],"c":99}'} +json_replace_test 50 {'$.b[#-1]','AAA'} {'{"a":1,"b":[1,[2,3],"AAA"],"c":99}'} +json_replace_test 60 {'$.b[1][#-1]','AAA'} {'{"a":1,"b":[1,[2,"AAA"],4],"c":99}'} +json_replace_test 70 {'$.b[1][#-1]','AAA','$.b[#-1]','BBB'} \ + {'{"a":1,"b":[1,[2,"AAA"],"BBB"],"c":99}'} +json_replace_test 80 {'$.b[#-1]','AAA','$.b[#-1]','BBB'} \ + {'{"a":1,"b":[1,[2,3],"BBB"],"c":99}'} + +do_catchsql_test json105-6.10 { + SELECT json_extract(j, '$.b[#-]') FROM t1; +} {1 {bad JSON path: '$.b[#-]'}} +do_catchsql_test json105-6.20 { + SELECT json_extract(j, '$.b[#9]') FROM t1; +} {1 {bad JSON path: '$.b[#9]'}} +do_catchsql_test json105-6.30 { + SELECT json_extract(j, '$.b[#+2]') FROM t1; +} {1 {bad JSON path: '$.b[#+2]'}} +do_catchsql_test json105-6.40 { + SELECT json_extract(j, '$.b[#-1') FROM t1; +} {1 {bad JSON path: '$.b[#-1'}} +do_catchsql_test json105-6.50 { + SELECT json_extract(j, '$.b[#-1x]') FROM t1; +} {1 {bad JSON path: '$.b[#-1x]'}} + +finish_test diff --git a/test/json106.test b/test/json106.test new file mode 100644 index 0000000..23fa028 --- /dev/null +++ b/test/json106.test @@ -0,0 +1,73 @@ +# 2023-12-18 +# +# The author disclaims copyright to this source code. In place of +# a legal notice, here is a blessing: +# +# May you do good and not evil. +# May you find forgiveness for yourself and forgive others. +# May you share freely, never taking more than you give. +# +#*********************************************************************** +# Invariant tests for JSON built around the randomjson extension +# + +set testdir [file dirname $argv0] +source $testdir/tester.tcl +set testprefix json106 + +# These tests require virtual table "json_tree" to run. +ifcapable !vtab { finish_test ; return } + +load_static_extension db randomjson +db eval { + CREATE TEMP TABLE t1(j0,j5,p); + CREATE TEMP TABLE kv(n,key,val); +} +unset -nocomplain ii +for {set ii 1} {$ii<=5000} {incr ii} { + do_execsql_test $ii.1 { + DELETE FROM t1; + INSERT INTO t1(j0,j5) VALUES(random_json($ii),random_json5($ii)); + SELECT json_valid(j0), json_valid(j5,2) FROM t1; + } {1 1} + do_execsql_test $ii.2 { + SELECT count(*) + FROM t1, json_tree(j0) AS rt + WHERE rt.type NOT IN ('object','array') + AND rt.atom IS NOT (j0 ->> rt.fullkey); + } 0 + do_execsql_test $ii.3 { + SELECT count(*) + FROM t1, json_tree(j5) AS rt + WHERE rt.type NOT IN ('object','array') + AND rt.atom IS NOT (j0 ->> rt.fullkey); + } 0 + do_execsql_test $ii.4 { + DELETE FROM kv; + INSERT INTO kv + SELECT rt.rowid, rt.fullkey, rt.atom + FROM t1, json_tree(j0) AS rt + WHERE rt.type NOT IN ('object','array'); + } + do_execsql_test $ii.5 { + SELECT count(*) + FROM t1, kv + WHERE key NOT LIKE '%]' + AND json_remove(j5,key)->>key IS NOT NULL + } 0 + do_execsql_test $ii.6 { + SELECT count(*) + FROM t1, kv + WHERE key NOT LIKE '%]' + AND json_insert(json_remove(j5,key),key,val)->>key IS NOT val + } 0 + do_execsql_test $ii.7 { + UPDATE t1 SET p=json_patch(j0,j5); + SELECT count(*) + FROM t1, kv + WHERE p->>key IS NOT val + } 0 +} + + +finish_test diff --git a/test/json107.test b/test/json107.test new file mode 100644 index 0000000..779b557 --- /dev/null +++ b/test/json107.test @@ -0,0 +1,86 @@ +# 2024-01-23 +# +# The author disclaims copyright to this source code. In place of +# a legal notice, here is a blessing: +# +# May you do good and not evil. +# May you find forgiveness for yourself and forgive others. +# May you share freely, never taking more than you give. +# +#*********************************************************************** +# +# Legacy JSON bug: If the input is a BLOB that when cast into TEXT looks +# like valid JSON, then treat it as valid JSON. +# +# The original intent of the JSON functions was to raise an error on any +# BLOB input. That intent was clearly documented, but the code failed to +# to implement it. Subsequently, many applications began to depend on the +# incorrect behavior, especially apps that used readfile() to read JSON +# content, since readfile() returns a BLOB. So we need to support the +# bug moving forward. +# +# The tests in this fail verify that the original buggy behavior is +# preserved. +# + +set testdir [file dirname $argv0] +source $testdir/tester.tcl +set testprefix json107 + +if {[db one {PRAGMA encoding}]!="UTF-8"} { + # These tests only work for a UTF-8 encoding. + finish_test + return +} + +do_execsql_test 1.1 { + SELECT json_valid( CAST('{"a":1}' AS BLOB) ); +} 1 +do_execsql_test 1.1.1 { + SELECT json_valid( CAST('{"a":1}' AS BLOB), 1); +} 1 +do_execsql_test 1.1.2 { + SELECT json_valid( CAST('{"a":1}' AS BLOB), 2); +} 1 +do_execsql_test 1.1.4 { + SELECT json_valid( CAST('{"a":1}' AS BLOB), 4); +} 0 +do_execsql_test 1.1.8 { + SELECT json_valid( CAST('{"a":1}' AS BLOB), 8); +} 0 + +do_execsql_test 1.2.1 { + SELECT CAST('{"a":123}' AS blob) -> 'a'; +} 123 +do_execsql_test 1.2.2 { + SELECT CAST('{"a":123}' AS blob) ->> 'a'; +} 123 +do_execsql_test 1.2.3 { + SELECT json_extract(CAST('{"a":123}' AS blob), '$.a'); +} 123 +do_execsql_test 1.3 { + SELECT json_insert(CAST('{"a":123}' AS blob),'$.b',456); +} {{{"a":123,"b":456}}} +do_execsql_test 1.4 { + SELECT json_remove(CAST('{"a":123,"b":456}' AS blob),'$.a'); +} {{{"b":456}}} +do_execsql_test 1.5 { + SELECT json_set(CAST('{"a":123,"b":456}' AS blob),'$.a',789); +} {{{"a":789,"b":456}}} +do_execsql_test 1.6 { + SELECT json_replace(CAST('{"a":123,"b":456}' AS blob),'$.a',789); +} {{{"a":789,"b":456}}} +do_execsql_test 1.7 { + SELECT json_type(CAST('{"a":123,"b":456}' AS blob)); +} object +do_execsql_test 1.8 { + SELECT json(CAST('{"a":123,"b":456}' AS blob)); +} {{{"a":123,"b":456}}} + +ifcapable vtab { + do_execsql_test 2.1 { + SELECT key, value FROM json_tree( CAST('{"a":123,"b":456}' AS blob) ) + WHERE atom; + } {a 123 b 456} +} +finish_test diff --git a/test/json501.test b/test/json501.test new file mode 100644 index 0000000..40b3b56 --- /dev/null +++ b/test/json501.test @@ -0,0 +1,309 @@ +# 2023-04-27 +# +# The author disclaims copyright to this source code. In place of +# a legal notice, here is a blessing: +# +# May you do good and not evil. +# May you find forgiveness for yourself and forgive others. +# May you share freely, never taking more than you give. +# +#*********************************************************************** +# This file implements tests for the JSON5 enhancements to the +# JSON SQL functions extension to the SQLite library. +# + +set testdir [file dirname $argv0] +source $testdir/tester.tcl +set testprefix json501 + +# From https://spec.json5.org/#introduction +# +#----------------------------------------------------------------------------- +# Summary of Features +# +# The following ECMAScript 5.1 features, which are not supported in JSON, have +# been extended to JSON5. +# +# Objects +# +# 1) Object keys may be an ECMAScript 5.1 IdentifierName. +# 2) Objects may have a single trailing comma. +# +# Arrays +# +# 3) Arrays may have a single trailing comma. +# +# Strings +# +# 4) Strings may be single quoted. +# 5) Strings may span multiple lines by escaping new line characters. +# 6) Strings may include character escapes. +# +# Numbers +# +# 7) Numbers may be hexadecimal. +# 8) Numbers may have a leading or trailing decimal point. +# 9) Numbers may be IEEE 754 positive infinity, negative infinity, and NaN. +# 10) Numbers may begin with an explicit plus sign. +# +# Comments +# +# 11) Single and multi-line comments are allowed. +# +# White Space +# +# 12) Additional white space characters are allowed. +#----------------------------------------------------------------------------- +# +# Test number in this file are of the form X.Y where X is one of the item +# numbers in the feature list above and Y is the test sequence number. +# + +############################################################################### +# 1) Object keys may be an ECMAScript 5.1 IdentifierName. +do_execsql_test 1.1 { + WITH c(x) AS (VALUES('{a:5,b:6}')) + SELECT x->>'a', json(x), json_valid(x), NOT json_error_position(x) FROM c; +} {5 {{"a":5,"b":6}} 0 1} +do_execsql_test 1.2 { + SELECT '[7,null,{a:5,b:6},[8,9]]'->>'$[2].b'; +} {6} +do_execsql_test 1.3 { + SELECT '{ $123 : 789 }'->>'$."$123"'; +} 789 +do_execsql_test 1.4 { + SELECT '{ _123$xyz : 789 }'->>'$."_123$xyz"'; +} 789 +do_execsql_test 1.5 { + SELECT '{ MNO_123$xyz : 789 }'->>'$."MNO_123$xyz"'; +} 789 + +do_execsql_test 1.6 { + SELECT json('{ MNO_123$xyz : 789 }'); +} [list {{"MNO_123$xyz":789}}] + +do_catchsql_test 1.10 { + SELECT json('{ MNO_123/xyz : 789 }'); +} {1 {malformed JSON}} + +do_execsql_test 1.11 { + SELECT '{ MNO_123æxyz : 789 }'->>'MNO_123æxyz'; +} {789} + +############################################################################### +# 2) Objects may have a single trailing comma. + +do_execsql_test 2.1 { + WITH c(x) AS (VALUES('{"a":5, "b":6, }')) + SELECT x->>'b', json(x), json_valid(x), NOT json_error_position(x) FROM c; +} {6 {{"a":5,"b":6}} 0 1} +do_execsql_test 2.2 { + SELECT '{a:5, b:6 , }'->>'b'; +} 6 +do_catchsql_test 2.3 { + SELECT '{a:5, b:6 ,, }'->>'b'; +} {1 {malformed JSON}} +do_catchsql_test 2.4 { + SELECT '{a:5, b:6, ,}'->>'b'; +} {1 {malformed JSON}} + +############################################################################### +# 3) Arrays may have a single trailing comma. + +do_execsql_test 3.1 { + WITH c(x) AS (VALUES('[5, 6,]')) + SELECT x->>1, json(x), json_valid(x), NOT json_error_position(x) FROM c; +} {6 {[5,6]} 0 1} +do_execsql_test 3.2 { + SELECT '[5, 6 , ]'->>1; +} 6 +do_catchsql_test 3.3 { + SELECT '[5, 6,,]'->>1; +} {1 {malformed JSON}} +do_catchsql_test 3.4 { + SELECT '[5, 6 , , ]'->>1; +} {1 {malformed JSON}} + +############################################################################### +# 4) Strings may be single quoted. + +do_execsql_test 4.1 { + WITH c(x) AS (VALUES('{"a": ''abcd''}')) + SELECT x->>'a', json(x), json_valid(x), NOT json_error_position(x) FROM c; +} {abcd {{"a":"abcd"}} 0 1} +do_execsql_test 4.2 { + SELECT '{b: 123, ''a'': ''ab\''cd''}'->>'a'; +} {ab'cd} + +############################################################################### +# 5) Strings may span multiple lines by escaping new line characters. + +do_execsql_test 5.1 { + WITH c(x) AS (VALUES('{a: "abc'||char(0x5c,0x0a)||'xyz"}')) + SELECT x->>'a', json(x), json_valid(x), NOT json_error_position(x) FROM c; +} {abcxyz {{"a":"abcxyz"}} 0 1} +do_execsql_test 5.2 { + SELECT ('{a: "abc'||char(0x5c,0x0d)||'xyz"}')->>'a'; +} {abcxyz} +do_execsql_test 5.3 { + SELECT ('{a: "abc'||char(0x5c,0x0d,0x0a)||'xyz"}')->>'a'; +} {abcxyz} +do_execsql_test 5.4 { + SELECT ('{a: "abc'||char(0x5c,0x2028)||'xyz"}')->>'a'; +} {abcxyz} +do_execsql_test 5.5 { + SELECT ('{a: "abc'||char(0x5c,0x2029)||'xyz"}')->>'a'; +} {abcxyz} + + +############################################################################### +# 6) Strings may include character escapes. + +do_execsql_test 6.1 { + SELECT ('{a: "abc'||char(0x5c,0x27)||'xyz"}')->>'a'; +} {abc'xyz} +do_execsql_test 6.2 { + SELECT ('{a: "abc'||char(0x5c,0x22)||'xyz"}')->>'a'; +} {abc"xyz} +do_execsql_test 6.3 { + SELECT ('{a: "abc'||char(0x5c,0x5c)||'xyz"}')->>'a'; +} {{abc\xyz}} +do_execsql_test 6.4 { + SELECT hex(('{a: "abc\bxyz"}')->>'a'); +} {6162630878797A} +do_execsql_test 6.5 { + SELECT hex(('{a: "abc\f\n\r\t\vxyz"}')->>'a'); +} {6162630C0A0D090B78797A} +do_execsql_test 6.6 { + SELECT hex(('{a: "abc\0xyz"}')->>'a'); +} {6162630078797A} +do_execsql_test 6.7 { + SELECT '{a: "abc\x35\x4f\x6Exyz"}'->>'a'; +} {abc5Onxyz} +do_execsql_test 6.8 { + SELECT '{a: "\x6a\x6A\x6b\x6B\x6c\x6C\x6d\x6D\x6e\x6E\x6f\x6F"}'->>'a'; +} {jjkkllmmnnoo} + +############################################################################### +# 7) Numbers may be hexadecimal. + +do_execsql_test 7.1 { + SELECT '{a: 0x0}'->>'a'; +} 0 +do_execsql_test 7.2 { + SELECT '{a: -0x0}'->>'a'; +} 0 +do_execsql_test 7.3 { + SELECT '{a: +0x0}'->>'a'; +} 0 +do_execsql_test 7.4 { + SELECT '{a: 0xabcdef}'->>'a'; +} 11259375 +do_execsql_test 7.5 { + SELECT '{a: -0xaBcDeF}'->>'a'; +} -11259375 +do_execsql_test 7.6 { + SELECT '{a: +0xABCDEF}'->>'a'; +} 11259375 + +############################################################################### +# 8) Numbers may have a leading or trailing decimal point. + +do_execsql_test 8.1 { + WITH c(x) AS (VALUES('{x: 4.}')) SELECT x->>'x', json(x) FROM c; +} {4.0 {{"x":4.0}}} +do_execsql_test 8.2 { + WITH c(x) AS (VALUES('{x: +4.}')) SELECT x->>'x', json(x) FROM c; +} {4.0 {{"x":4.0}}} +do_execsql_test 8.3 { + WITH c(x) AS (VALUES('{x: -4.}')) SELECT x->>'x', json(x) FROM c; +} {-4.0 {{"x":-4.0}}} +do_execsql_test 8.3 { + WITH c(x) AS (VALUES('{x: .5}')) SELECT x->>'x', json(x) FROM c; +} {0.5 {{"x":0.5}}} +do_execsql_test 8.4 { + WITH c(x) AS (VALUES('{x: -.5}')) SELECT x->>'x', json(x) FROM c; +} {-0.5 {{"x":-0.5}}} +do_execsql_test 8.5 { + WITH c(x) AS (VALUES('{x: +.5}')) SELECT x->>'x', json(x) FROM c; +} {0.5 {{"x":0.5}}} +do_execsql_test 8.6 { + WITH c(x) AS (VALUES('{x: 4.e0}')) SELECT x->>'x', json(x) FROM c; +} {4.0 {{"x":4.0e0}}} +do_execsql_test 8.7 { + WITH c(x) AS (VALUES('{x: +4.e1}')) SELECT x->>'x', json(x) FROM c; +} {40.0 {{"x":4.0e1}}} +do_execsql_test 8.8 { + WITH c(x) AS (VALUES('{x: -4.e2}')) SELECT x->>'x', json(x) FROM c; +} {-400.0 {{"x":-4.0e2}}} +do_execsql_test 8.9 { + WITH c(x) AS (VALUES('{x: .5e3}')) SELECT x->>'x', json(x) FROM c; +} {500.0 {{"x":0.5e3}}} +do_execsql_test 8.10 { + WITH c(x) AS (VALUES('{x: -.5e-1}')) SELECT x->>'x', json(x) FROM c; +} {-0.05 {{"x":-0.5e-1}}} +do_execsql_test 8.11 { + WITH c(x) AS (VALUES('{x: +.5e-2}')) SELECT x->>'x', json(x) FROM c; +} {0.005 {{"x":0.5e-2}}} + + +############################################################################### +# 9) Numbers may be IEEE 754 positive infinity, negative infinity, and NaN. + +do_execsql_test 9.1 { + WITH c(x) AS (VALUES('{x: +Infinity}')) SELECT x->>'x', json(x) FROM c; +} {Inf {{"x":9e999}}} +do_execsql_test 9.2 { + WITH c(x) AS (VALUES('{x: -Infinity}')) SELECT x->>'x', json(x) FROM c; +} {-Inf {{"x":-9e999}}} +do_execsql_test 9.3 { + WITH c(x) AS (VALUES('{x: Infinity}')) SELECT x->>'x', json(x) FROM c; +} {Inf {{"x":9e999}}} +do_execsql_test 9.4 { + WITH c(x) AS (VALUES('{x: NaN}')) SELECT x->>'x', json(x) FROM c; +} {{} {{"x":null}}} + +############################################################################### +# 10) Numbers may begin with an explicit plus sign. + +do_execsql_test 10.1 { + SELECT '{a: +123}'->'a'; +} 123 + +############################################################################### +# 11) Single and multi-line comments are allowed. + +do_execsql_test 11.1 { + SELECT ' /* abc */ { /*def*/ aaa /* xyz */ : // to the end of line + 123 /* xyz */ , /* 123 */ }'->>'aaa'; +} 123 + +############################################################################### +# 12) Additional white space characters are allowed. + +do_execsql_test 12.1 { + SELECT (char(0x09,0x0a,0x0b,0x0c,0x0d,0x20,0xa0,0x2028,0x2029) + || '{a: "xyz"}')->>'a'; +} xyz +do_execsql_test 12.2 { + SELECT ('{a:' || char(0x09,0x0a,0x0b,0x0c,0x0d,0x20,0xa0,0x2028,0x2029) + || '"xyz"}')->>'a'; +} xyz +do_execsql_test 12.3 { + SELECT (char(0x1680,0x2000,0x2001,0x2002,0x2003,0x2004,0x2005, + 0x2006,0x2007,0x2008,0x2009,0x200a,0x3000,0xfeff) + || '{a: "xyz"}')->>'a'; +} xyz +do_execsql_test 12.4 { + SELECT ('{a: ' ||char(0x1680,0x2000,0x2001,0x2002,0x2003,0x2004,0x2005, + 0x2006,0x2007,0x2008,0x2009,0x200a,0x3000,0xfeff) + || ' "xyz"}')->>'a'; +} xyz + +# 2023-11-08 forum/forumpost/ddcad3e884 +# +do_execsql_test 13.1 { + SELECT json('{x:''a "b" c''}'); +} {{{"x":"a \"b\" c"}}} + +finish_test diff --git a/test/json502.test b/test/json502.test new file mode 100644 index 0000000..cc14b8c --- /dev/null +++ b/test/json502.test @@ -0,0 +1,67 @@ +# 2023-04-28 +# +# The author disclaims copyright to this source code. In place of +# a legal notice, here is a blessing: +# +# May you do good and not evil. +# May you find forgiveness for yourself and forgive others. +# May you share freely, never taking more than you give. +# +#*********************************************************************** +# This file implements tests for the JSON5 enhancements to the +# JSON SQL functions extension to the SQLite library. +# + +set testdir [file dirname $argv0] +source $testdir/tester.tcl +set testprefix json502 + +ifcapable vtab { + +do_execsql_test 1.1 { + CREATE TABLE t1(x JSON); + INSERT INTO t1(x) VALUES('{a:{b:{c:"hello",},},}'); + SELECT fullkey FROM t1, json_tree(x); +} {{$} {$.a} {$.a.b} {$.a.b.c}} + +} + +do_execsql_test 2.1 { + SELECT json_error_position('{a:null,{"h":[1,[1,2,3]],"j":"abc"}:true}'); +} 9 +do_catchsql_test 2.2 { + SELECT json('{a:null,{"h":[1,[1,2,3]],"j":"abc"}:true}'); +} {1 {malformed JSON}} +do_catchsql_test 2.3 { + SELECT '{a:null,{"h":[1,[1,2,3]],"j":"abc"}:true}'->'$h[#-1]'; +} {1 {malformed JSON}} + +# Verify that escaped label names are compared correctly. +# +do_execsql_test 3.1 { + SELECT '{"a\x62c":123}' ->> 'abc'; +} 123 +do_execsql_test 3.2 { + SELECT '{"abc":123}' ->> 'a\x62c'; +} 123 + +db null null +do_execsql_test 3.3 { + DROP TABLE IF EXISTS t1; + CREATE TABLE t1(x); + INSERT INTO t1 VALUES(json_insert('{}','$.a\',111,'$."b\\"',222)); + INSERT INTO t1 VALUES(jsonb_insert('{}','$.a\',111,'$."b\\"',222)); + SELECT x->'$.a\', x->'$.a\\', x->'$."a\\"', x->'$."b\\"' FROM t1; +} {111 null 111 222 111 null 111 222} + +do_execsql_test 3.4 { + SELECT json_patch('{"a\x62c":123}','{"ab\x63":456}') ->> 'abc'; +} 456 + +ifcapable vtab { + do_execsql_test 4.1 { + SELECT * FROM json_tree('{"\u0017":1}','$."\x17"'); + } {{\x17} 1 integer 1 1 null {$."\x17"} {$}} +} + +finish_test diff --git a/test/jsonb01.test b/test/jsonb01.test new file mode 100644 index 0000000..8f16428 --- /dev/null +++ b/test/jsonb01.test @@ -0,0 +1,53 @@ +# 2023-11-15 +# +# The author disclaims copyright to this source code. In place of +# a legal notice, here is a blessing: +# +# May you do good and not evil. +# May you find forgiveness for yourself and forgive others. +# May you share freely, never taking more than you give. +# +#*********************************************************************** +# Test cases for JSONB +# + +set testdir [file dirname $argv0] +source $testdir/tester.tcl + +do_execsql_test jsonb01-1.1 { + CREATE TABLE t1(x JSON BLOB); + INSERT INTO t1 VALUES(jsonb('{a:5,b:{x:10,y:11},c:[1,2,3,4]}')); +} +foreach {id path res} { + 1 {$.a} {{{"b":{"x":10,"y":11},"c":[1,2,3,4]}}} + 2 {$.b} {{{"a":5,"c":[1,2,3,4]}}} + 3 {$.c} {{{"a":5,"b":{"x":10,"y":11}}}} + 4 {$.d} {{{"a":5,"b":{"x":10,"y":11},"c":[1,2,3,4]}}} + 5 {$.b.x} {{{"a":5,"b":{"y":11},"c":[1,2,3,4]}}} + 6 {$.b.y} {{{"a":5,"b":{"x":10},"c":[1,2,3,4]}}} + 7 {$.c[0]} {{{"a":5,"b":{"x":10,"y":11},"c":[2,3,4]}}} + 8 {$.c[1]} {{{"a":5,"b":{"x":10,"y":11},"c":[1,3,4]}}} + 9 {$.c[2]} {{{"a":5,"b":{"x":10,"y":11},"c":[1,2,4]}}} + 10 {$.c[3]} {{{"a":5,"b":{"x":10,"y":11},"c":[1,2,3]}}} + 11 {$.c[4]} {{{"a":5,"b":{"x":10,"y":11},"c":[1,2,3,4]}}} + 12 {$.c[#]} {{{"a":5,"b":{"x":10,"y":11},"c":[1,2,3,4]}}} + 13 {$.c[#-1]} {{{"a":5,"b":{"x":10,"y":11},"c":[1,2,3]}}} + 14 {$.c[#-2]} {{{"a":5,"b":{"x":10,"y":11},"c":[1,2,4]}}} + 15 {$.c[#-3]} {{{"a":5,"b":{"x":10,"y":11},"c":[1,3,4]}}} + 16 {$.c[#-4]} {{{"a":5,"b":{"x":10,"y":11},"c":[2,3,4]}}} + 17 {$.c[#-5]} {{{"a":5,"b":{"x":10,"y":11},"c":[1,2,3,4]}}} + 18 {$.c[#-6]} {{{"a":5,"b":{"x":10,"y":11},"c":[1,2,3,4]}}} +} { + do_execsql_test jsonb01-1.2.$id.1 { + SELECT json(jsonb_remove(x,$path)) FROM t1; + } $res + do_execsql_test jsonb01-1.2.$id.2 { + SELECT json_remove(x,$path) FROM t1; + } $res +} + +do_catchsql_test jsonb01-2.0 { + SELECT x'8ce6ffffffff171333' -> '$'; +} {1 {malformed JSON}} + +finish_test |