Materialize/test/sqllogictest/cockroach/postgres_jsonb.slt

# Copyright 2015 - 2019 The Cockroach Authors. All rights reserved.
# Copyright Materialize, Inc. and contributors. All rights reserved.
#
# Use of this software is governed by the Business Source License
# included in the LICENSE file at the root of this repository.
#
# As of the Change Date specified in that file, in accordance with
# the Business Source License, use of this software will be governed
# by the Apache License, Version 2.0.
#
# This file is derived from the logic test suite in CockroachDB. The
# original file was retrieved on June 10, 2019 from:
#
#     https://github.com/cockroachdb/cockroach/blob/d2f7fbf5dd1fc1a099bbad790a2e1f7c60a66cc3/pkg/sql/logictest/testdata/logic_test/postgres_jsonb
#
# The original source code is subject to the terms of the Apache
# 2.0 license, a copy of which can be found in the LICENSE file at the
# root of this repository.

mode cockroach

# This file is an incomplete porting of
# https://github.com/postgres/postgres/blob/11e264517dff7a911d9e6494de86049cab42cde3/src/test/regress/sql/jsonb.sql
# to CockroachDB logic tests.

query T
SELECT '[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]'::jsonb
----
[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]

statement ok
CREATE TABLE test_jsonb (
    json_type text,
    test_json jsonb
)

statement ok
INSERT INTO test_jsonb VALUES
 ('scalar','"a scalar"'),
 ('array','["zero", "one","two",null,"four","five", [1,2,3],{"f1":9}]'),
 ('object','{"field1":"val1","field2":"val2","field3":null, "field4": 4, "field5": [1,2,3], "field6": {"f1":9}}')

query T
SELECT test_json -> 'x' FROM test_jsonb WHERE json_type = 'scalar'
----
NULL

query T
SELECT test_json -> 'x' FROM test_jsonb WHERE json_type = 'array'
----
NULL

query T
SELECT test_json -> 'x' FROM test_jsonb WHERE json_type = 'object'
----
NULL

query T
SELECT test_json -> 'field2' FROM test_jsonb WHERE json_type = 'object'
----
"val2"

query T
SELECT test_json ->> 'field2' FROM test_jsonb WHERE json_type = 'scalar'
----
NULL

query T
SELECT test_json ->> 'field2' FROM test_jsonb WHERE json_type = 'array'
----
NULL

query T
SELECT test_json ->> 'field2' FROM test_jsonb WHERE json_type = 'object'
----
val2

query T
SELECT test_json -> 2 FROM test_jsonb WHERE json_type = 'scalar'
----
NULL

query T
SELECT test_json -> 2 FROM test_jsonb WHERE json_type = 'array'
----
"two"

query T
SELECT test_json -> 9 FROM test_jsonb WHERE json_type = 'array'
----
NULL

query T
SELECT test_json -> 2 FROM test_jsonb WHERE json_type = 'object'
----
NULL

query T
SELECT test_json ->> 6 FROM test_jsonb WHERE json_type = 'array'
----
[1,2,3]

query T
SELECT test_json ->> 7 FROM test_jsonb WHERE json_type = 'array'
----
{"f1":9}

query T
SELECT test_json ->> 'field4' FROM test_jsonb WHERE json_type = 'object'
----
4

query T
SELECT test_json ->> 'field5' FROM test_jsonb WHERE json_type = 'object'
----
[1,2,3]

query T
SELECT test_json ->> 'field6' FROM test_jsonb WHERE json_type = 'object'
----
{"f1":9}

query T
SELECT test_json ->> 2 FROM test_jsonb WHERE json_type = 'scalar'
----
NULL

query T
SELECT test_json ->> 2 FROM test_jsonb WHERE json_type = 'array'
----
two

query T
SELECT test_json ->> 2 FROM test_jsonb WHERE json_type = 'object'
----
NULL

# nulls

query B
SELECT (test_json->'field3') IS NULL AS expect_false FROM test_jsonb WHERE json_type = 'object'
----
false

query T
SELECT (test_json->>'field3') FROM test_jsonb WHERE json_type = 'object';
----
NULL

query B
SELECT (test_json->>'field3') IS NULL AS expect_true FROM test_jsonb WHERE json_type = 'object';
----
true

query B
SELECT (test_json->3) IS NULL AS expect_false FROM test_jsonb WHERE json_type = 'array';
----
false

query B
SELECT (test_json->>3) IS NULL AS expect_true FROM test_jsonb WHERE json_type = 'array';
----
true

# corner cases
query T
SELECT '{"a": [{"b": "c"}, {"b": "cc"}]}'::jsonb -> null::text;
----
NULL

query T
SELECT '{"a": [{"b": "c"}, {"b": "cc"}]}'::jsonb -> null::int;
----
NULL

query T
SELECT '{"a": [{"b": "c"}, {"b": "cc"}]}'::jsonb -> 1;
----
NULL

query T
SELECT '{"a": [{"b": "c"}, {"b": "cc"}]}'::jsonb -> 'z';
----
NULL

query T
SELECT '{"a": [{"b": "c"}, {"b": "cc"}]}'::jsonb -> '';
----
NULL

query T
SELECT '[{"b": "c"}, {"b": "cc"}]'::jsonb -> 1;
----
{"b":"cc"}

query T
SELECT '[{"b": "c"}, {"b": "cc"}]'::jsonb -> 3;
----
NULL

query T
SELECT '[{"b": "c"}, {"b": "cc"}]'::jsonb -> 'z';
----
NULL

query T
SELECT '{"a": "c", "b": null}'::jsonb -> 'b';
----
null

query T
SELECT '"foo"'::jsonb -> 1;
----
NULL

query T
SELECT '"foo"'::jsonb -> 'z';
----
NULL

query T
SELECT '{"a": [{"b": "c"}, {"b": "cc"}]}'::jsonb ->> null::text;
----
NULL

query T
SELECT '{"a": [{"b": "c"}, {"b": "cc"}]}'::jsonb ->> null::int;
----
NULL

query T
SELECT '{"a": [{"b": "c"}, {"b": "cc"}]}'::jsonb ->> 1;
----
NULL

query T
SELECT '{"a": [{"b": "c"}, {"b": "cc"}]}'::jsonb ->> 'z';
----
NULL

query T
SELECT '{"a": [{"b": "c"}, {"b": "cc"}]}'::jsonb ->> '';
----
NULL

query T
SELECT '[{"b": "c"}, {"b": "cc"}]'::jsonb ->> 1;
----
{"b":"cc"}

query T
SELECT '[{"b": "c"}, {"b": "cc"}]'::jsonb ->> 3;
----
NULL

query T
SELECT '[{"b": "c"}, {"b": "cc"}]'::jsonb ->> 'z';
----
NULL

query T
SELECT '{"a": "c", "b": null}'::jsonb ->> 'b';
----
NULL

query T
SELECT '"foo"'::jsonb ->> 1;
----
NULL

query T
SELECT '"foo"'::jsonb ->> 'z';
----
NULL

# equality and inequality

query B
SELECT '{"x":"y"}'::jsonb = '{"x":"y"}'::jsonb;
----
true

query B
SELECT '{"x":"y"}'::jsonb = '{"x":"z"}'::jsonb;
----
false

query B
SELECT '{"x":"y"}'::jsonb <> '{"x":"y"}'::jsonb;
----
false

query B
SELECT '{"x":"y"}'::jsonb <> '{"x":"z"}'::jsonb;
----
true

# containment

query B
SELECT '{"a":"b", "b":1, "c":null}'::JSONB @> '{"a":"b"}'
----
true

query B
SELECT '{"a":"b", "b":1, "c":null}'::JSONB @> '{"a":"b", "c":null}'
----
true

query B
SELECT '{"a":"b", "b":1, "c":null}'::JSONB @> '{"a":"b", "g":null}'
----
false

query B
SELECT '{"a":"b", "b":1, "c":null}'::JSONB @> '{"g":null}'
----
false

query B
SELECT '{"a":"b", "b":1, "c":null}'::JSONB @> '{"a":"c"}'
----
false

query B
SELECT '{"a":"b", "b":1, "c":null}'::JSONB @> '{"a":"b"}'
----
true

query B
SELECT '{"a":"b", "b":1, "c":null}'::JSONB @> '{"a":"b", "c":"q"}'
----
false

query B
SELECT '{"a":"b", "b":1, "c":null}'::JSONB @> '{"a":"b"}'
----
true

query B
SELECT '{"a":"b", "b":1, "c":null}'::JSONB @> '{"a":"b", "c":null}'
----
true

query B
SELECT '{"a":"b", "b":1, "c":null}'::JSONB @> '{"a":"b", "g":null}'
----
false

query B
SELECT '{"a":"b", "b":1, "c":null}'::JSONB @> '{"g":null}'
----
false

query B
SELECT '{"a":"b", "b":1, "c":null}'::JSONB @> '{"a":"c"}'
----
false

query B
SELECT '{"a":"b", "b":1, "c":null}'::JSONB @> '{"a":"b"}'
----
true

query B
SELECT '{"a":"b", "b":1, "c":null}'::JSONB @> '{"a":"b", "c":"q"}'
----
false

query B
SELECT '[1,2]'::JSONB @> '[1,2,2]'::jsonb
----
true

query B
SELECT '[1,1,2]'::JSONB @> '[1,2,2]'::jsonb
----
true

query B
SELECT '[[1,2]]'::JSONB @> '[[1,2,2]]'::jsonb
----
true

query B
SELECT '[1,2,2]'::JSONB <@ '[1,2]'::jsonb
----
true

query B
SELECT '[1,2,2]'::JSONB <@ '[1,1,2]'::jsonb
----
true

query B
SELECT '[[1,2,2]]'::JSONB <@ '[[1,2]]'::jsonb
----
true

query B
SELECT '{"a":"b"}'::JSONB <@ '{"a":"b", "b":1, "c":null}'
----
true

query B
SELECT '{"a":"b", "c":null}'::JSONB <@ '{"a":"b", "b":1, "c":null}'
----
true

query B
SELECT '{"a":"b", "g":null}'::JSONB <@ '{"a":"b", "b":1, "c":null}'
----
false

query B
SELECT '{"g":null}'::JSONB <@ '{"a":"b", "b":1, "c":null}'
----
false

query B
SELECT '{"a":"c"}'::JSONB <@ '{"a":"b", "b":1, "c":null}'
----
false

query B
SELECT '{"a":"b"}'::JSONB <@ '{"a":"b", "b":1, "c":null}'
----
true

query B
SELECT '{"a":"b", "c":"q"}'::JSONB <@ '{"a":"b", "b":1, "c":null}'
----
false

# Raw scalar may contain another raw scalar, array may contain a raw scalar
query B
SELECT '[5]'::JSONB @> '[5]'
----
true

query B
SELECT '5'::JSONB @> '5'
----
true

query B
SELECT '[5]'::JSONB @> '5'
----
true

# -- But a raw scalar cannot contain an array
query B
SELECT '5'::JSONB @> '[5]'
----
false

# -- In general, one thing should always contain itself. Test array containment:
query B
SELECT '["9", ["7", "3"], 1]'::JSONB @> '["9", ["7", "3"], 1]'::jsonb
----
true

query B
SELECT '["9", ["7", "3"], ["1"]]'::JSONB @> '["9", ["7", "3"], ["1"]]'::jsonb
----
true

# -- array containment string matching confusion bug
query B
SELECT '{ "name": "Bob", "tags": [ "enim", "qui"]}'::JSONB @> '{"tags":["qu"]}'
----
false