github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/logictest/testdata/logic_test/postgres_jsonb (about) 1 # This file is an incomplete porting of 2 # https://github.com/postgres/postgres/blob/11e264517dff7a911d9e6494de86049cab42cde3/src/test/regress/sql/jsonb.sql 3 # to CockroachDB logic tests. 4 5 query T 6 SELECT '[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]'::jsonb 7 ---- 8 [[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]] 9 10 statement ok 11 CREATE TABLE test_jsonb ( 12 json_type text, 13 test_json jsonb 14 ) 15 16 statement ok 17 INSERT INTO test_jsonb VALUES 18 ('scalar','"a scalar"'), 19 ('array','["zero", "one","two",null,"four","five", [1,2,3],{"f1":9}]'), 20 ('object','{"field1":"val1","field2":"val2","field3":null, "field4": 4, "field5": [1,2,3], "field6": {"f1":9}}') 21 22 query T 23 SELECT test_json -> 'x' FROM test_jsonb WHERE json_type = 'scalar' 24 ---- 25 NULL 26 27 query T 28 SELECT test_json -> 'x' FROM test_jsonb WHERE json_type = 'array' 29 ---- 30 NULL 31 32 query T 33 SELECT test_json -> 'x' FROM test_jsonb WHERE json_type = 'object' 34 ---- 35 NULL 36 37 query T 38 SELECT test_json -> 'field2' FROM test_jsonb WHERE json_type = 'object' 39 ---- 40 "val2" 41 42 query T 43 SELECT test_json ->> 'field2' FROM test_jsonb WHERE json_type = 'scalar' 44 ---- 45 NULL 46 47 query T 48 SELECT test_json ->> 'field2' FROM test_jsonb WHERE json_type = 'array' 49 ---- 50 NULL 51 52 query T 53 SELECT test_json ->> 'field2' FROM test_jsonb WHERE json_type = 'object' 54 ---- 55 val2 56 57 query T 58 SELECT test_json -> 2 FROM test_jsonb WHERE json_type = 'scalar' 59 ---- 60 NULL 61 62 query T 63 SELECT test_json -> 2 FROM test_jsonb WHERE json_type = 'array' 64 ---- 65 "two" 66 67 query T 68 SELECT test_json -> 9 FROM test_jsonb WHERE json_type = 'array' 69 ---- 70 NULL 71 72 query T 73 SELECT test_json -> 2 FROM test_jsonb WHERE json_type = 'object' 74 ---- 75 NULL 76 77 query T 78 SELECT test_json ->> 6 FROM test_jsonb WHERE json_type = 'array' 79 ---- 80 [1, 2, 3] 81 82 query T 83 SELECT test_json ->> 7 FROM test_jsonb WHERE json_type = 'array' 84 ---- 85 {"f1": 9} 86 87 query T 88 SELECT test_json ->> 'field4' FROM test_jsonb WHERE json_type = 'object' 89 ---- 90 4 91 92 query T 93 SELECT test_json ->> 'field5' FROM test_jsonb WHERE json_type = 'object' 94 ---- 95 [1, 2, 3] 96 97 query T 98 SELECT test_json ->> 'field6' FROM test_jsonb WHERE json_type = 'object' 99 ---- 100 {"f1": 9} 101 102 query T 103 SELECT test_json ->> 2 FROM test_jsonb WHERE json_type = 'scalar' 104 ---- 105 NULL 106 107 query T 108 SELECT test_json ->> 2 FROM test_jsonb WHERE json_type = 'array' 109 ---- 110 two 111 112 query T 113 SELECT test_json ->> 2 FROM test_jsonb WHERE json_type = 'object' 114 ---- 115 NULL 116 117 # nulls 118 119 query B 120 SELECT (test_json->'field3') IS NULL AS expect_false FROM test_jsonb WHERE json_type = 'object' 121 ---- 122 false 123 124 query T 125 SELECT (test_json->>'field3') FROM test_jsonb WHERE json_type = 'object'; 126 ---- 127 NULL 128 129 query B 130 SELECT (test_json->>'field3') IS NULL AS expect_true FROM test_jsonb WHERE json_type = 'object'; 131 ---- 132 true 133 134 query B 135 SELECT (test_json->3) IS NULL AS expect_false FROM test_jsonb WHERE json_type = 'array'; 136 ---- 137 false 138 139 query B 140 SELECT (test_json->>3) IS NULL AS expect_true FROM test_jsonb WHERE json_type = 'array'; 141 ---- 142 true 143 144 # corner cases 145 query T 146 SELECT '{"a": [{"b": "c"}, {"b": "cc"}]}'::jsonb -> null::text; 147 ---- 148 NULL 149 150 query T 151 SELECT '{"a": [{"b": "c"}, {"b": "cc"}]}'::jsonb -> null::int; 152 ---- 153 NULL 154 155 query T 156 SELECT '{"a": [{"b": "c"}, {"b": "cc"}]}'::jsonb -> 1; 157 ---- 158 NULL 159 160 query T 161 SELECT '{"a": [{"b": "c"}, {"b": "cc"}]}'::jsonb -> 'z'; 162 ---- 163 NULL 164 165 query T 166 SELECT '{"a": [{"b": "c"}, {"b": "cc"}]}'::jsonb -> ''; 167 ---- 168 NULL 169 170 query T 171 SELECT '[{"b": "c"}, {"b": "cc"}]'::jsonb -> 1; 172 ---- 173 {"b": "cc"} 174 175 query T 176 SELECT '[{"b": "c"}, {"b": "cc"}]'::jsonb -> 3; 177 ---- 178 NULL 179 180 query T 181 SELECT '[{"b": "c"}, {"b": "cc"}]'::jsonb -> 'z'; 182 ---- 183 NULL 184 185 query T 186 SELECT '{"a": "c", "b": null}'::jsonb -> 'b'; 187 ---- 188 null 189 190 query T 191 SELECT '"foo"'::jsonb -> 1; 192 ---- 193 NULL 194 195 query T 196 SELECT '"foo"'::jsonb -> 'z'; 197 ---- 198 NULL 199 200 query T 201 SELECT '{"a": [{"b": "c"}, {"b": "cc"}]}'::jsonb ->> null::text; 202 ---- 203 NULL 204 205 query T 206 SELECT '{"a": [{"b": "c"}, {"b": "cc"}]}'::jsonb ->> null::int; 207 ---- 208 NULL 209 210 query T 211 SELECT '{"a": [{"b": "c"}, {"b": "cc"}]}'::jsonb ->> 1; 212 ---- 213 NULL 214 215 query T 216 SELECT '{"a": [{"b": "c"}, {"b": "cc"}]}'::jsonb ->> 'z'; 217 ---- 218 NULL 219 220 query T 221 SELECT '{"a": [{"b": "c"}, {"b": "cc"}]}'::jsonb ->> ''; 222 ---- 223 NULL 224 225 query T 226 SELECT '[{"b": "c"}, {"b": "cc"}]'::jsonb ->> 1; 227 ---- 228 {"b": "cc"} 229 230 query T 231 SELECT '[{"b": "c"}, {"b": "cc"}]'::jsonb ->> 3; 232 ---- 233 NULL 234 235 query T 236 SELECT '[{"b": "c"}, {"b": "cc"}]'::jsonb ->> 'z'; 237 ---- 238 NULL 239 240 query T 241 SELECT '{"a": "c", "b": null}'::jsonb ->> 'b'; 242 ---- 243 NULL 244 245 query T 246 SELECT '"foo"'::jsonb ->> 1; 247 ---- 248 NULL 249 250 query T 251 SELECT '"foo"'::jsonb ->> 'z'; 252 ---- 253 NULL 254 255 # equality and inequality 256 257 query B 258 SELECT '{"x":"y"}'::jsonb = '{"x":"y"}'::jsonb; 259 ---- 260 true 261 262 query B 263 SELECT '{"x":"y"}'::jsonb = '{"x":"z"}'::jsonb; 264 ---- 265 false 266 267 query B 268 SELECT '{"x":"y"}'::jsonb <> '{"x":"y"}'::jsonb; 269 ---- 270 false 271 272 query B 273 SELECT '{"x":"y"}'::jsonb <> '{"x":"z"}'::jsonb; 274 ---- 275 true 276 277 # containment 278 279 query B 280 SELECT '{"a":"b", "b":1, "c":null}'::JSONB @> '{"a":"b"}' 281 ---- 282 true 283 284 query B 285 SELECT '{"a":"b", "b":1, "c":null}'::JSONB @> '{"a":"b", "c":null}' 286 ---- 287 true 288 289 query B 290 SELECT '{"a":"b", "b":1, "c":null}'::JSONB @> '{"a":"b", "g":null}' 291 ---- 292 false 293 294 query B 295 SELECT '{"a":"b", "b":1, "c":null}'::JSONB @> '{"g":null}' 296 ---- 297 false 298 299 query B 300 SELECT '{"a":"b", "b":1, "c":null}'::JSONB @> '{"a":"c"}' 301 ---- 302 false 303 304 query B 305 SELECT '{"a":"b", "b":1, "c":null}'::JSONB @> '{"a":"b"}' 306 ---- 307 true 308 309 query B 310 SELECT '{"a":"b", "b":1, "c":null}'::JSONB @> '{"a":"b", "c":"q"}' 311 ---- 312 false 313 314 query B 315 SELECT '{"a":"b", "b":1, "c":null}'::JSONB @> '{"a":"b"}' 316 ---- 317 true 318 319 query B 320 SELECT '{"a":"b", "b":1, "c":null}'::JSONB @> '{"a":"b", "c":null}' 321 ---- 322 true 323 324 query B 325 SELECT '{"a":"b", "b":1, "c":null}'::JSONB @> '{"a":"b", "g":null}' 326 ---- 327 false 328 329 query B 330 SELECT '{"a":"b", "b":1, "c":null}'::JSONB @> '{"g":null}' 331 ---- 332 false 333 334 query B 335 SELECT '{"a":"b", "b":1, "c":null}'::JSONB @> '{"a":"c"}' 336 ---- 337 false 338 339 query B 340 SELECT '{"a":"b", "b":1, "c":null}'::JSONB @> '{"a":"b"}' 341 ---- 342 true 343 344 query B 345 SELECT '{"a":"b", "b":1, "c":null}'::JSONB @> '{"a":"b", "c":"q"}' 346 ---- 347 false 348 349 query B 350 SELECT '[1,2]'::JSONB @> '[1,2,2]'::jsonb 351 ---- 352 true 353 354 query B 355 SELECT '[1,1,2]'::JSONB @> '[1,2,2]'::jsonb 356 ---- 357 true 358 359 query B 360 SELECT '[[1,2]]'::JSONB @> '[[1,2,2]]'::jsonb 361 ---- 362 true 363 364 query B 365 SELECT '[1,2,2]'::JSONB <@ '[1,2]'::jsonb 366 ---- 367 true 368 369 query B 370 SELECT '[1,2,2]'::JSONB <@ '[1,1,2]'::jsonb 371 ---- 372 true 373 374 query B 375 SELECT '[[1,2,2]]'::JSONB <@ '[[1,2]]'::jsonb 376 ---- 377 true 378 379 query B 380 SELECT '{"a":"b"}'::JSONB <@ '{"a":"b", "b":1, "c":null}' 381 ---- 382 true 383 384 query B 385 SELECT '{"a":"b", "c":null}'::JSONB <@ '{"a":"b", "b":1, "c":null}' 386 ---- 387 true 388 389 query B 390 SELECT '{"a":"b", "g":null}'::JSONB <@ '{"a":"b", "b":1, "c":null}' 391 ---- 392 false 393 394 query B 395 SELECT '{"g":null}'::JSONB <@ '{"a":"b", "b":1, "c":null}' 396 ---- 397 false 398 399 query B 400 SELECT '{"a":"c"}'::JSONB <@ '{"a":"b", "b":1, "c":null}' 401 ---- 402 false 403 404 query B 405 SELECT '{"a":"b"}'::JSONB <@ '{"a":"b", "b":1, "c":null}' 406 ---- 407 true 408 409 query B 410 SELECT '{"a":"b", "c":"q"}'::JSONB <@ '{"a":"b", "b":1, "c":null}' 411 ---- 412 false 413 414 # Raw scalar may contain another raw scalar, array may contain a raw scalar 415 query B 416 SELECT '[5]'::JSONB @> '[5]' 417 ---- 418 true 419 420 query B 421 SELECT '5'::JSONB @> '5' 422 ---- 423 true 424 425 query B 426 SELECT '[5]'::JSONB @> '5' 427 ---- 428 true 429 430 # -- But a raw scalar cannot contain an array 431 query B 432 SELECT '5'::JSONB @> '[5]' 433 ---- 434 false 435 436 # -- In general, one thing should always contain itself. Test array containment: 437 query B 438 SELECT '["9", ["7", "3"], 1]'::JSONB @> '["9", ["7", "3"], 1]'::jsonb 439 ---- 440 true 441 442 query B 443 SELECT '["9", ["7", "3"], ["1"]]'::JSONB @> '["9", ["7", "3"], ["1"]]'::jsonb 444 ---- 445 true 446 447 # -- array containment string matching confusion bug 448 query B 449 SELECT '{ "name": "Bob", "tags": [ "enim", "qui"]}'::JSONB @> '{"tags":["qu"]}' 450 ---- 451 false