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