Materialize/test/sqllogictest/jsonb.slt

# 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/json_builtins
#
# 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.

# These tests came from cockroack/postgres_jsonb.slt, cockroach/json.slt and cockroach/json_builtins.slt
# but are modified for the subset of json we support

mode cockroach

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

query T
SELECT '{"a": 1, "a": 2, "a": 3}'::jsonb
----
{"a":3}

# Regression test for database-issues#1824, in which 64-bit integers could not be represented
# in the jsonb type.
query T
SELECT '1614259308542846100'::jsonb
----
1614259308542846100

query T
SELECT '9223372036854775807'::jsonb
----
9223372036854775807

query T
SELECT '999999999999999999999999999999999999999'::jsonb
----
999999999999999999999999999999999999999

query error invalid input syntax for type jsonb: "1000000000000000000000000000000000000000" is out of range for type numeric: exceeds maximum precision 39 at line 1
SELECT '1000000000000000000000000000000000000000'::jsonb

# "NaN" is not a valid JSON number, but it is a valid numeric value. Validate
# that the conversion to JSON converts NaN-valued numerics to strings.
query T
SELECT jsonb_typeof(to_jsonb('NaN'::numeric))
----
string

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

query T
SELECT '1'::JSONB
----
1

query T
SELECT pg_typeof('1'::JSONB)
----
jsonb

query error EOF while parsing an object at line 1
SELECT '{'::JSONB

query T
SELECT '{"a":1}'::JSONB->'a'
----
1

query T
SELECT pg_typeof('{"a":1}'::JSONB->'a')
----
jsonb

query T
SELECT '{"a":1,"b":2}'::JSONB->'b'
----
2

query T
SELECT '{"a":1,"b":{"c":3}}'::JSONB->'b'->'c'
----
3

query TT
SELECT '{"a":1,"b":2}'::JSONB->'c','{"c":1}'::JSONB->'a'
----
NULL NULL

query TT
SELECT '2'::JSONB->'b','[1,2,3]'::JSONB->'0'
----
NULL NULL

query T
SELECT '[1,2,3]'::JSONB->0
----
1

query T
SELECT '[1,2,3]'::JSONB->3
----
NULL

query T
SELECT '{"a":"b"}'::JSONB->>'a'
----
b

query T
SELECT '[null]'::JSONB->>0
----
NULL

query T
SELECT '{"a":null}'::JSONB->>'a'
----
NULL

query T
SELECT pg_typeof('{"a":1}'::JSONB->>'a')
----
text

query T
SELECT '{"a":1,"b":2}'::JSONB->>'b'
----
2

query TT
SELECT '{"a":1,"b":2}'::JSONB->>'c','{"c":1}'::JSONB->>'a'
----
NULL NULL

query TT
SELECT '2'::JSONB->>'b','[1,2,3]'::JSONB->>'0'
----
NULL NULL

query T
SELECT '[1,2,3]'::JSONB->>0
----
1

query T
SELECT '[1,2,3]'::JSONB->>3
----
NULL

query T
SELECT '{"a":1}'::JSONB#>'{a}'::STRING[]
----
1

query T
SELECT '{"a":{"b":"c"}}'::JSONB#>'{a,b}'::STRING[]
----
"c"

query T
SELECT '{"a":["b"]}'::JSONB#>'{a,b}'::STRING[]
----
NULL

query T
SELECT '{"a":["b"]}'::JSONB#>'{a,0}'::STRING[]
----
"b"

query T
SELECT '{"a":1}'::JSONB#>>ARRAY['foo',null]
----
NULL

query T
SELECT '{"a":1}'::JSONB#>>'{a}'::STRING[]
----
1

query T
SELECT '{"a":{"b":"c"}}'::JSONB#>>'{a,b}'::STRING[]
----
c

query T
SELECT '{"a":["b"]}'::JSONB#>>'{a,b}'::STRING[]
----
NULL

query T
SELECT '{"a":["b"]}'::JSONB#>>'{a,0}'::STRING[]
----
b

query T
SELECT '{"a":[null]}'::JSONB#>>'{a,0}'::STRING[]
----
NULL

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

query BB
SELECT '{"a":1}'::JSONB ? 'a','{"a":1}'::JSONB ? 'b'
----
true false

query BB
SELECT '{"a":1,"b":1}'::JSONB ? 'a','{"a":1,"b":1}'::JSONB ? 'b'
----
true true

# query BB
# SELECT '{"a":1}'::JSONB ?| ARRAY['a','b'],'{"b":1}'::JSONB ?| ARRAY['a','b']
# ----
# true true

# query B
# SELECT '{"c":1}'::JSONB ?| ARRAY['a','b']
# ----
# false

# query BB
# SELECT '{"a":1}'::JSONB ?& ARRAY['a','b'],'{"b":1}'::JSONB ?& ARRAY['a','b']
# ----
# false false

# query B
# SELECT '{"a":1,"b":1,"c":1}'::JSONB ?& ARRAY['a','b']
# ----
# true

## Arrays do not `?` their stringified indices.
query B
SELECT '[1,2,3]'::JSONB ? '0'
----
false

## Arrays `?` string elements.
query B
SELECT '["foo","bar","baz"]'::JSONB ? 'foo'
----
true

query B
SELECT '["foo","bar","baz"]'::JSONB ? 'baz'
----
true

query B
SELECT '["foo","bar","baz"]'::JSONB ? 'gup'
----
false

# query B
# SELECT '["foo","bar","baz"]'::JSONB ?| ARRAY['foo','gup']
# ----
# true

# query B
# SELECT '["foo","bar","baz"]'::JSONB ?| ARRAY['buh','gup']
# ----
# false

# query B
# SELECT '["foo","bar","baz"]'::JSONB ?& ARRAY['foo','bar']
# ----
# true

# query B
# SELECT '["foo","bar","baz"]'::JSONB ?& ARRAY['foo','buh']
# ----
# false

query T
SELECT '{"a":1}'::JSONB - 'a'
----
{}

query T
SELECT '{"a":1}'::JSONB - 'b'
----
{"a":1}

# `-` is one of the very few cases that PG errors in a JSON type mismatch with operators.
query T
SELECT '[1,2,3]'::JSONB - 0
----
[2,3]

query T
SELECT '[1,2,3]'::JSONB - 1
----
[1,3]

query T
SELECT '3'::JSONB - 'b'
----
NULL

query T
SELECT '{}'::JSONB - 1
----
NULL

query B
SELECT '[1,2,3]'::JSONB <@ '[1,2]'::JSONB
----
false

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

query B
SELECT '[1,2]'::JSONB @> '[1,2,3]'::JSONB
----
false

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

query B
SELECT '{"a":[1,2,3]}'::JSONB->'a' @> '2'::JSONB
----
true

statement ok
CREATE TABLE x (j JSONB)

statement ok
INSERT INTO x VALUES ('{"a":[1,2,3]}')

query B
SELECT true FROM x WHERE j->'a' @> '2'::JSONB
----
true

query B
SELECT true FROM x WHERE j->'a' @> '2'::JSONB
----
true

# query T
# SELECT '{"foo":{"bar":1}}'::JSONB #- ARRAY['foo','bar']
# ----
# {"foo":{}}

# statement error path element at position 1 is null
# SELECT '{"foo":{"bar":1}}'::JSONB #- ARRAY[null,'foo']

# statement error path element at position 2 is null
# SELECT '{"foo":{"bar":1}}'::JSONB #- ARRAY['foo',null]

# query T
# SELECT '{"foo":{"bar":1}}'::JSONB #- ARRAY['foo']
# ----
# {}

# query T
# SELECT '{"foo":{"bar":1}}'::JSONB #- ARRAY['bar']
# ----
# {"foo":{"bar":1.0}}

# query T
# SELECT '{"foo":{"bar":1},"one":1,"two":2}'::JSONB #- ARRAY['one']
# ----
# {"foo":{"bar":1.0},"two":2.0}

# query T
# SELECT '{}'::JSONB #- ARRAY['foo']
# ----
# {}

# query T
# SELECT '{"foo":{"bar":1}}'::JSONB #- ARRAY['']
# ----
# {"foo":{"bar":1.0}}

query T
SELECT '{"a":"b"}'::JSONB::STRING
----
{"a":"b"}

query T
SELECT CAST('{"a":"b"}'::JSONB AS STRING)
----
{"a":"b"}

query T
SELECT '["1","2","3"]'::JSONB - '1'
----
["2","3"]

query T
SELECT '["1","2","1","2","3"]'::JSONB - '2'
----
["1","1","3"]

query T
SELECT '["1","2","3"]'::JSONB - '4'
----
["1","2","3"]

query T
SELECT '[]'::JSONB - '1'
----
[]

query T
SELECT '["1","2","3"]'::JSONB - ''
----
["1","2","3"]

query T
SELECT '[1,"1",1.0]'::JSONB - '1'
----
[1,1]

# query T
# SELECT '[1,2,3]'::JSONB #- ARRAY['0']
# ----
# [2.0,3.0]

# query T
# SELECT '[1,2,3]'::JSONB #- ARRAY['3']
# ----
# [1.0,2.0,3.0]

# query T
# SELECT '[]'::JSONB #- ARRAY['0']
# ----
# []

# statement error pgcode 22P02 a path element is not an integer:foo
# SELECT '["foo"]'::JSONB #- ARRAY['foo']

# query T
# SELECT '{"a":["foo"]}'::JSONB #- ARRAY['a','0']
# ----
# {"a":[]}

# query T
# SELECT '{"a":["foo","bar"]}'::JSONB #- ARRAY['a','1']
# ----
# {"a":["foo"]}

# query T
# SELECT '{"a":[]}'::JSONB #- ARRAY['a','0']
# ----
# {"a":[]}

# query T
# SELECT '{"a":123,"b":456,"c":567}'::JSONB - array[]:::text[];
# ----
# {"a":123.0,"b":456.0,"c":567.0}

# query T
# SELECT '{"a":123,"b":456,"c":567}'::JSONB - array['a','c'];
# ----
# {"b":456.0}

# query T
# SELECT '{"a":123,"c":"asdf"}'::JSONB - array['a','c'];
# ----
# {}

# query T
# SELECT '{}'::JSONB - array['a','c'];
# ----
# {}

# query T
# SELECT '{"b":[],"c":{"a":"b"}}'::JSONB - array['a'];
# ----
# {"b":[],"c":{"a":"b"}}

# # Regression test for cockroach#34756.
# query T
# SELECT '{"b":[],"c":{"a":"b"}}'::JSONB - array['foo',NULL]
# ----
# {"b":[],"c":{"a":"b"}}

# statement error pgcode 22P02 a path element is not an integer:foo
# SELECT '{"a":{"b":["foo"]}}'::JSONB #- ARRAY['a','b','foo']

statement ok
CREATE TABLE json_family (a INT,b JSONB)

statement ok
INSERT INTO json_family VALUES(0,'{}')

statement ok
INSERT INTO json_family VALUES(1,'{"a":123,"c":"asdf"}')

query IT colnames
SELECT a,b FROM json_family ORDER BY a
----
a    b
0  {}
1  {"a":123,"c":"asdf"}

statement ok
DROP TABLE json_family

query T
SELECT jsonb_typeof('-123'::JSON)
----
number

query T
SELECT jsonb_typeof('-123.4'::JSON)
----
number

query T
SELECT jsonb_typeof('"-123.4"'::JSON)
----
string

query T
SELECT jsonb_typeof('{"1":{"2":3}}'::JSON)
----
object

query T
SELECT jsonb_typeof('[1, 2, [3]]'::JSON)
----
array

query T
SELECT jsonb_typeof('true'::JSON)
----
boolean

query T
SELECT jsonb_typeof('false'::JSON)
----
boolean

query T
SELECT jsonb_typeof('null'::JSON)
----
null

## to_jsonb

query T
SELECT to_jsonb(123::INT)
----
123

query T
SELECT to_jsonb('\a'::TEXT)
----
"\\a"

# query T
# SELECT to_jsonb('\a'::TEXT COLLATE "fr_FR")
# ----
# "\\a"

# query T
# SELECT to_jsonb(3::OID::INT::OID)
# ----
# "3"

# query T
# SELECT to_jsonb('a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'::UUID);
# ----
# "a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11"

query T
SELECT to_jsonb('\x0001'::BYTEA)
----
"\\x0001"

query T
SELECT to_jsonb(true::BOOL)
----
true

query T
SELECT to_jsonb(false::BOOL)
----
false

query T
SELECT to_jsonb('"a"'::JSON)
----
"a"

query T
SELECT to_jsonb(1.234::FLOAT)
----
1.234

query T
SELECT to_jsonb(null::int)
----
NULL

query T
SELECT to_jsonb(null::int[])
----
NULL

query T
SELECT to_jsonb(array[1]::int[])
----
[1]

query T
SELECT to_jsonb(array[1, 2]::int[])
----
[1,2]

query T
SELECT to_jsonb(array[1, null]::int[])
----
[1,null]

query T
SELECT to_jsonb(array[null]::int[])
----
[null]

query T
SELECT to_jsonb(array[array[1, 2], array[3, 4]]::int[])
----
[[1,2],[3,4]]

query T
SELECT to_jsonb(array[array[array[1, 2], array[3, 4]], array[array[5, 6], array[7, 8]]]::int[])
----
[[[1,2],[3,4]],[[5,6],[7,8]]]

query T
SELECT to_jsonb(array[1.2, .3]::float[])
----
[1.2,0.3]

query T
SELECT to_jsonb(array[row(1, 2), row(3, 4)])
----
[{"f1":1,"f2":2},{"f1":3,"f2":4}]

query T
SELECT to_jsonb(null::int list)
----
NULL

query T
SELECT to_jsonb(list[1]::int list)
----
[1]

query T
SELECT to_jsonb(list[1, 2]::int list)
----
[1,2]

query T
SELECT to_jsonb(list[list[1], list[3, 4]]::int list list)
----
[[1],[3,4]]

query T
SELECT to_jsonb(list[list[list[1, 2], list[3]], list[list[7, 8]]]::int list list list)
----
[[[1,2],[3]],[[7,8]]]

query T
SELECT to_jsonb(list[1.2, .3]::float list)
----
[1.2,0.3]

query T
SELECT to_jsonb(list[row(1, 2), row(3, 4)])
----
[{"f1":1,"f2":2},{"f1":3,"f2":4}]


query T
SELECT to_jsonb(1.234::DECIMAL)
----
1.234

# query T
# SELECT to_jsonb('10.1.0.0/16'::INET)
# ----
# "10.1.0.0/16"

# query T
# SELECT to_jsonb(ARRAY[[1,2],[3,4]])
# ----
# [[1.0,2.0],[3.0,4.0]]

query T
SELECT to_jsonb('2014-05-28 12:22:35.614298'::TIMESTAMP)
----
"2014-05-28 12:22:35.614298"

query T
SELECT to_jsonb('2014-05-28 12:22:35.614298-04'::TIMESTAMPTZ)
----
"2014-05-28 16:22:35.614298+00"

query T
SELECT to_jsonb('2014-05-28 12:22:35.614298-04'::TIMESTAMP)
----
"2014-05-28 12:22:35.614298"

query T
SELECT to_jsonb('2014-05-28'::DATE)
----
"2014-05-28"

query T
SELECT to_jsonb('00:00:00'::TIME)
----
"00:00:00"

query T
SELECT to_jsonb('02:45:02.234'::INTERVAL)
----
"02:45:02.234"

query T
SELECT to_jsonb((1,2,'hello',NULL,NULL))
----
{"f1":1,"f2":2,"f3":"hello","f4":null,"f5":null}

## jsonb_array_elements

query T rowsort
SELECT * FROM jsonb_array_elements('[1,2,3]')
----
1
2
3

query T rowsort
SELECT * FROM jsonb_array_elements('[1,true,null,"text",-1.234,{"2":3,"4":"5"},[1,2,3]]')
----
"text"
-1.234
1
[1,2,3]
null
true
{"2":3,"4":"5"}

query T rowsort
SELECT js.value->>'a' FROM jsonb_array_elements('[{"a":1},{"a":2},{"a":3}]') js
----
1
2
3

query T
SELECT * FROM jsonb_array_elements('[]')
----

query T
SELECT * FROM jsonb_array_elements('{"1":2}')
----

## jsonb_array_elements_text

query T rowsort
SELECT * FROM jsonb_array_elements_text('[1,2,3]')
----
1
2
3

query T rowsort
SELECT * FROM jsonb_array_elements_text('[1,2,3]')
----
1
2
3

query T rowsort
SELECT * FROM jsonb_array_elements_text('[1,true,null,"text",-1.234,{"2":3,"4":"5"},[1,2,3]]')
----
-1.234
1
NULL
[1,2,3]
text
true
{"2":3,"4":"5"}

query T
SELECT * FROM jsonb_array_elements('[]')
----

query T
SELECT * FROM jsonb_array_elements_text('{"1":2}')
----

query T
SELECT * FROM jsonb_array_elements_text('{"1":2}')
----

## jsonb_object_keys

query T
SELECT * FROM jsonb_object_keys('{"1":2,"3":4}')
----
1
3

query T
SELECT * FROM jsonb_object_keys('{"1":2,"3":4}')
----
1
3

query T
SELECT * FROM jsonb_object_keys('{}')
----

query T
SELECT * FROM jsonb_object_keys('{"\"1\"":2}')
----
"1"

# Keys are sorted.
query T
SELECT * FROM jsonb_object_keys('{"a":1,"1":2,"3":{"4":5,"6":7}}')
----
1
3
a

query T
SELECT * FROM jsonb_object_keys('null')
----

query T
SELECT * FROM jsonb_object_keys('[1,2,3]')
----

## jsonb_build_object

query T
SELECT jsonb_build_object()
----
{}

query T
SELECT jsonb_build_object('a',2,'b',4)
----
{"a":2,"b":4}

query T
SELECT jsonb_build_object('a',null,'b',4)
----
{"a":null,"b":4}

query error key cannot be null
SELECT jsonb_build_object(null, null)

query error key cannot be null
SELECT jsonb_build_object(null, 1)

query T
SELECT jsonb_build_object(true,'val',1,0,1.3,2,date '2019-02-03' - date '2019-01-01',4)
----
{"1":0,"1.3":2,"33":4,"true":"val"}

query T
SELECT jsonb_build_object('a',1,'b',1.2::float,'c',true,'d',null,'e','{"x":3,"y":[1,2,3]}'::JSONB)
----
{"a":1,"b":1.2,"c":true,"d":null,"e":{"x":3,"y":[1,2,3]}}

query T
SELECT jsonb_build_object(
       'a',jsonb_build_object('b',false,'c',99),
       'd',jsonb_build_object('e',jsonb_build_array(9,8,7))
)
----
{"a":{"b":false,"c":99},"d":{"e":[9,8,7]}}

query T
SELECT jsonb_build_object(a,3) FROM (SELECT 1 AS a,2 AS b) r
----
{"1":3}

# query T
# SELECT jsonb_build_object('\a'::TEXT COLLATE "fr_FR",1)
# ----
# {"\\a":1.0}

query T
SELECT jsonb_build_object('\a',1)
----
{"\\a":1}

# query T
# SELECT jsonb_build_object(jsonb_object_keys('{"x":3,"y":4}'::JSONB),2)
# ----
# {"x":2.0}
# {"y":2.0}

# # Regression for panic when bit array is passed as argument.
# query T
# SELECT jsonb_build_object('a','0100110'::varbit)
# ----
# {"a":"0100110"}

# even number of arguments
query error
SELECT jsonb_build_object(1,2,3)

# # keys must be scalar and not null
# query error
# SELECT jsonb_build_object(null,2)

# query error
# SELECT jsonb_build_object((1,2),3)

# query error
# SELECT jsonb_build_object('{"a":1,"b":2}'::JSON,3)

# query T
# SELECT jsonb_build_object('{1,2,3}'::int[],3)

# query T
# SELECT json_extract_path('{"a":1}','a')
# ----
# 1

# query T
# SELECT json_extract_path('{"a":1}','a',NULL)
# ----
# NULL

# query T
# SELECT json_extract_path('{"a":1}')
# ----
# {"a":1}

# query T
# SELECT json_extract_path('{"a":{"b":2}}','a')
# ----
# {"b":2}

# query T
# SELECT json_extract_path('{"a":{"b":2}}','a','b')
# ----
# 2

# query T
# SELECT jsonb_extract_path('{"a":{"b":2}}','a','b')
# ----
# 2

# query T
# SELECT json_extract_path('{"a":{"b":2}}','a','b','c')
# ----
# NULL

# query T
# SELECT jsonb_pretty('{"a":1}'::JSONB)
# ----
# {
#     "a":1.0
# }

query T
SELECT '[1,2,3]'::JSONB || '[4,5,6]'::JSONB
----
[1,2,3,4,5,6]

query T
SELECT '{"a":1,"b":2}'::JSONB || '{"b":3,"c":4}'::JSONB
----
{"a":1,"b":3,"c":4}

query T
SELECT '{"a":1,"b":2}'::JSONB || '"c"'::JSONB
----
NULL

# Test that concatenating a jsonb value with a string literal uses
# jsonb-specific concatenation.
query T
SELECT '[1,2,3]'::jsonb || '[4,5,6]'
----
[1,2,3,4,5,6]

query T
SELECT jsonb_build_array()
----
[]

query T
SELECT jsonb_build_array(1, 2, 3)
----
[1,2,3]

query T
SELECT jsonb_build_array(1, 2, NULL)
----
[1,2,null]

query T
SELECT jsonb_build_array(NULL)
----
[null]

query T
SELECT jsonb_build_array('\x0001'::BYTEA)
----
["\\x0001"]

# query T
# SELECT jsonb_build_array(1,'1'::JSON,1.2::FLOAT,NULL,ARRAY['x','y'])
# ----
# [1,1,1.2,null,["x","y"]]

# # Regression for cockroach#37318
# query T
# SELECT jsonb_build_array('+Inf'::FLOAT,'-Inf'::FLOAT,'NaN'::FLOAT)::STRING::JSONB
# ----
# ["Infinity","-Infinity","NaN"]

# query error pq:json_object\(\):array must have even number of elements
# SELECT json_object('{a,b,c}'::TEXT[])

# query error pq:json_object\(\):null value not allowed for object key
# SELECT json_object('{NULL,a}'::TEXT[])

# query error pq:json_object\(\):null value not allowed for object key
# SELECT json_object('{a,b,NULL,"d e f"}'::TEXT[],'{1,2,3,"a b c"}'::TEXT[])

# query error pq:json_object\(\):mismatched array dimensions
# SELECT json_object('{a,b,c,"d e f",g}'::TEXT[],'{1,2,3,"a b c"}'::TEXT[])

# query error pq:json_object\(\):mismatched array dimensions
# SELECT json_object('{a,b,c,"d e f"}'::TEXT[],'{1,2,3,"a b c",g}'::TEXT[])

# query error pq:unknown signature:json_object\(collatedstring\{fr_FR\}\[\]\)
# SELECT json_object(ARRAY['a'::TEXT COLLATE "fr_FR"])

# query T
# SELECT json_object('{}'::TEXT[])
# ----
# {}

# query T
# SELECT json_object('{}'::TEXT[],'{}'::TEXT[])
# ----
# {}

# query T
# SELECT json_object('{b,3,a,1,b,4,a,2}'::TEXT[])
# ----
# {"a":"2","b":"4"}

# query T
# SELECT json_object('{b,b,a,a}'::TEXT[],'{1,2,3,4}'::TEXT[])
# ----
# {"a":"4","b":"2"}

# query T
# SELECT json_object('{a,1,b,2,3,NULL,"d e f","a b c"}'::TEXT[])
# ----
# {"3":null,"a":"1","b":"2","d e f":"a b c"}

# query T
# SELECT json_object('{a,b,"","d e f"}'::TEXT[],'{1,2,3,"a b c"}'::TEXT[])
# ----
# {"":"3","a":"1","b":"2","d e f":"a b c"}

# query T
# SELECT json_object('{a,b,c,"d e f"}'::TEXT[],'{1,2,3,"a b c"}'::TEXT[])
# ----
# {"a":"1","b":"2","c":"3","d e f":"a b c"}

# query error pq:jsonb_object\(\):array must have even number of elements
# SELECT jsonb_object('{a,b,c}'::TEXT[])

# query error pq:jsonb_object\(\):null value not allowed for object key
# SELECT jsonb_object('{NULL,a}'::TEXT[])

# query error pq:jsonb_object\(\):null value not allowed for object key
# SELECT jsonb_object('{a,b,NULL,"d e f"}'::TEXT[],'{1,2,3,"a b c"}'::TEXT[])

# query error pq:jsonb_object\(\):mismatched array dimensions
# SELECT jsonb_object('{a,b,c,"d e f",g}'::TEXT[],'{1,2,3,"a b c"}'::TEXT[])

# query error pq:jsonb_object\(\):mismatched array dimensions
# SELECT jsonb_object('{a,b,c,"d e f"}'::TEXT[],'{1,2,3,"a b c",g}'::TEXT[])

# query error pq:unknown signature:jsonb_object\(collatedstring\{fr_FR\}\[\]\)
# SELECT jsonb_object(ARRAY['a'::TEXT COLLATE "fr_FR"])

# query T
# SELECT jsonb_object('{}'::TEXT[])
# ----
# {}

# query T
# SELECT jsonb_object('{}'::TEXT[],'{}'::TEXT[])
# ----
# {}

# query T
# SELECT jsonb_object('{b,3,a,1,b,4,a,2}'::TEXT[])
# ----
# {"a":"2","b":"4"}

# query T
# SELECT jsonb_object('{b,b,a,a}'::TEXT[],'{1,2,3,4}'::TEXT[])
# ----
# {"a":"4","b":"2"}

# query T
# SELECT jsonb_object('{a,1,b,2,3,NULL,"d e f","a b c"}'::TEXT[])
# ----
# {"3":null,"a":"1","b":"2","d e f":"a b c"}

# query T
# SELECT jsonb_object('{a,b,"","d e f"}'::TEXT[],'{1,2,3,"a b c"}'::TEXT[])
# ----
# {"":"3","a":"1","b":"2","d e f":"a b c"}

# query T
# SELECT jsonb_object('{a,b,c,"d e f"}'::TEXT[],'{1,2,3,"a b c"}'::TEXT[])
# ----
# {"a":"1","b":"2","c":"3","d e f":"a b c"}

query TT
SELECT * FROM jsonb_each('[1]'::JSONB)
----

query TT
SELECT * FROM jsonb_each('null'::JSONB)
----

query TT
SELECT * from jsonb_each('{}'::JSONB) q
----

query TT colnames,rowsort
SELECT * from jsonb_each('{"f1":[1,2,3],"f2":{"f3":1},"f4":null,"f5":99,"f6":"stringy"}'::JSONB) q
----
key  value
f1  [1,2,3]
f2  {"f3":1}
f4  null
f5  99
f6  "stringy"

query TT
SELECT * FROM jsonb_each('[1]')
----

query TT
SELECT * FROM jsonb_each('null')
----

query TT
SELECT * from jsonb_each('{}') q
----

query TT colnames,rowsort
SELECT * from jsonb_each('{"f1":[1,2,3],"f2":{"f3":1},"f4":null,"f5":99,"f6":"stringy"}'::JSONB) q
----
key  value
f1  [1,2,3]
f2  {"f3":1}
f4  null
f5  99
f6  "stringy"

query TT
SELECT * FROM jsonb_each_text('[1]')
----

query TT
SELECT * FROM jsonb_each_text('null')
----

query TT
SELECT * from jsonb_each_text('{}') q
----

query TT
SELECT * from jsonb_each_text('{}') q
----

query TT colnames,rowsort
SELECT * from jsonb_each_text('{"f1":[1,2,3],"f2":{"f3":1},"f4":null,"f5":99,"f6":"stringy"}') q
----
key  value
f1  [1,2,3]
f2  {"f3":1}
f4  NULL
f5  99
f6  stringy

query TT
SELECT * FROM jsonb_each_text('[1]')
----

query TT
SELECT * FROM jsonb_each_text('null')
----

query TT
SELECT * from jsonb_each_text('{}') q
----

query TT
SELECT * from jsonb_each_text('{}') q
----

query TT colnames,rowsort
SELECT * from jsonb_each_text('{"f1":[1,2,3],"f2":{"f3":1},"f4":null,"f5":99,"f6":"stringy"}') q
----
key  value
f1  [1,2,3]
f2  {"f3":1}
f4  NULL
f5  99
f6  stringy

# query T
# SELECT json_set('{"a":1}','{a}'::STRING[],'2')
# ----
# {"a":2}

# query T
# SELECT jsonb_set('{"a":1}','{b}'::STRING[],'2')
# ----
# {"a":1,"b":2}

# statement error path element at position 1 is null
# SELECT jsonb_set('{"a":1}',ARRAY[null,'foo']::STRING[],'2')

# statement error path element at position 1 is null
# SELECT jsonb_set('{"a":1}','{null,foo}'::STRING[],'2',true)

# statement error path element at position 2 is null
# SELECT jsonb_set('{"a":1}','{foo,null}'::STRING[],'2',true)

# query T
# SELECT jsonb_set('{"a":1}','{b}'::STRING[],'2',true)
# ----
# {"a":1,"b":2}

# query T
# SELECT jsonb_set('{"a":1}','{b}'::STRING[],'2',false)
# ----
# {"a":1}

# query T
# SELECT jsonb_set('[{"f1":1,"f2":null},2,null,3]','{0,f1}'::STRING[],'[2,3,4]',false)
# ----
# [{"f1":[2,3,4],"f2":null},2,null,3]

# query T
# SELECT jsonb_set('[{"f1":1,"f2":null},2]','{0,f3}'::STRING[],'[2,3,4]')
# ----
# [{"f1":1,"f2":null,"f3":[2,3,4]},2]

# query T
# SELECT jsonb_insert('{"a":[0,1,2]}','{a,1}'::STRING[],'"new_value"');
# ----
# {"a":[0,"new_value",1,2]}

# query T
# SELECT jsonb_insert('[0,1,2,{"a":["a","b","d"]},4]','{3,a,2}'::STRING[],'"c"')
# ----
# [0,1,2,{"a":["a","b","c","d"]},4]

# query T
# SELECT jsonb_insert('{"a":"foo"}','{b}'::STRING[],'"bar"')
# ----
# {"a":"foo","b":"bar"}

# query T
# SELECT jsonb_insert(NULL,'{a}',NULL,false)
# ----
# NULL

# query T
# SELECT jsonb_insert('{"a":[0,1,2]}','{a,1}'::STRING[],'"new_value"',true)
# ----
# {"a":[0,1,"new_value",2]}

# query T
# SELECT jsonb_insert('{"a":[0,1,2]}','{a,-1}'::STRING[],'"new_value"',true)
# ----
# {"a":[0,1,2,"new_value"]}

# query error pq:jsonb_insert\(\):cannot replace existing key
# SELECT jsonb_insert('{"a":"foo"}','{a}'::STRING[],'"new_value"',false)

# query T
# SELECT jsonb_insert('{"a":"foo"}','{a,0}'::STRING[],'"new_value"',false)
# ----
# {"a":"foo"}

# query T
# SELECT jsonb_insert('[0,1,2,3]','{3}'::STRING[],'10',true)
# ----
# [0,1,2,3,10]

# statement error cannot set path in scalar
# SELECT jsonb_insert('1','{a}'::STRING[],'10',true)

# query T
# SELECT jsonb_insert('1',NULL,'10')
# ----
# NULL

# statement error path element at position 1 is null
# SELECT jsonb_insert('{"a":[0,1,2],"b":"hello","c":"world"}','{NULL,a,0}'::STRING[],'"new_val"')

# statement error path element at position 2 is null
# SELECT jsonb_insert('{"a":[0,1,2],"b":"hello","c":"world"}','{a,NULL,0}'::STRING[],'"new_val"')

query T
SELECT jsonb_strip_nulls(NULL)
----
NULL

query T
SELECT jsonb_strip_nulls('1'::JSONB)
----
1

query T
SELECT jsonb_strip_nulls('"a string"'::JSONB)
----
"a string"

query T
SELECT jsonb_strip_nulls('null'::JSONB)
----
null

query T
SELECT jsonb_strip_nulls('[1,2,null,3,4]'::JSONB)
----
[1,2,null,3,4]

query T
SELECT jsonb_strip_nulls('{"a":1,"b":null,"c":[2,null,3],"d":{"e":4,"f":null}}'::JSONB)
----
{"a":1,"c":[2,null,3],"d":{"e":4}}

query T
SELECT jsonb_strip_nulls('[1,{"a":1,"b":null,"c":2},3]'::JSONB)
----
[1,{"a":1,"c":2},3]

query T
SELECT jsonb_strip_nulls('{"a":{"b":null,"c":null},"d":{}}'::JSONB)
----
{"a":{},"d":{}}

query T
SELECT jsonb_strip_nulls(NULL)
----
NULL

query T
SELECT jsonb_strip_nulls('1'::JSONB)
----
1

query T
SELECT jsonb_strip_nulls('"a string"'::JSONB)
----
"a string"

query T
SELECT jsonb_strip_nulls('null'::JSONB)
----
null

query T
SELECT jsonb_strip_nulls('[1,2,null,3,4]'::JSONB)
----
[1,2,null,3,4]

query T
SELECT jsonb_strip_nulls('{"a":1,"b":null,"c":[2,null,3],"d":{"e":4,"f":null}}'::JSONB)
----
{"a":1,"c":[2,null,3],"d":{"e":4}}

query T
SELECT jsonb_strip_nulls('[1,{"a":1,"b":null,"c":2},3]'::JSONB)
----
[1,{"a":1,"c":2},3]

query T
SELECT jsonb_strip_nulls('{"a":{"b":null,"c":null},"d":{}}'::JSONB)
----
{"a":{},"d":{}}

query T
SELECT jsonb_array_length('{"f1":1,"f2":[5,6]}'::JSONB)
----
NULL

query T
SELECT jsonb_array_length('4'::JSONB)
----
NULL

query I
SELECT jsonb_array_length('[1,2,3,{"f1":1,"f2":[5,6]},4]'::JSONB)
----
5

query I
SELECT jsonb_array_length('[]'::JSONB)
----
0

query T
SELECT jsonb_array_length('{"f1":1,"f2":[5,6]}'::JSONB)
----
NULL

query T
SELECT jsonb_array_length('4'::JSONB)
----
NULL

query I
SELECT jsonb_array_length('[1,2,3,{"f1":1,"f2":[5,6]},4]'::JSONB)
----
5

# implicit conversion from string to JSONB
query I
SELECT jsonb_array_length('[]')
----
0

# Casts

query T
SELECT ('1'::jsonb)::float;
----
1.000

query error cannot cast jsonb string to type double precision
SELECT ('"Infinity"'::jsonb)::float;

query error cannot cast jsonb string to type double precision
SELECT ('"-Infinity"'::jsonb)::float;

query error cannot cast jsonb string to type double precision
SELECT ('"NaN"'::jsonb)::float;

# not a number
query error cannot cast jsonb array to type double precision
SELECT ('[1]'::jsonb)::float;

# not a number
query error cannot cast jsonb string to type double precision
SELECT ('"1"'::jsonb)::float;

query T
SELECT ('1'::jsonb)::int;
----
1

query error "9999999999999999999" integer out of range
SELECT ('9999999999999999999'::jsonb)::int;

# not a number
query error cannot cast jsonb array to type integer
SELECT ('[1]'::jsonb)::int;

query error CAST does not support casting from jsonb to timestamp
SELECT ('"1969-06-01 10:10:10.410"'::jsonb)::timestamp;

query error CAST does not support casting from jsonb to interval
SELECT ('"1-2 3 4:5:6.7"'::jsonb)::interval;

query error CAST does not support casting from jsonb to date
SELECT ('["2019-12-31"]'::jsonb)::date;

query T
SELECT (null::jsonb)::text;
----
NULL

query T
SELECT (null::jsonb)->>0;
----
NULL

query T
SELECT ('null'::jsonb)::text;
----
null

query T
SELECT ('null'::jsonb)->>0;
----
NULL

query T
SELECT to_jsonb(DATE '2019-12-31');
----
"2019-12-31"

query T
SELECT to_jsonb(TIMESTAMP '1969-06-01 10:10:10.41');
----
"1969-06-01 10:10:10.41"

query T
SELECT to_jsonb(LIST[null]::int[] list)
----
[null]

query T
SELECT to_jsonb(LIST[ARRAY[]::int[]]::int[] list)
----
[[]]

query T
SELECT to_jsonb(LIST[ARRAY[]::int[], NULL]::int[] list)
----
[[],null]

query T
SELECT to_jsonb(LIST[ARRAY[]::int[], NULL, ARRAY[2, 3]]::int[] list)
----
[[],null,[2,3]]

query T
SELECT to_jsonb(LIST[ARRAY[ARRAY[1, 2], ARRAY[3, 4]]]::int[] list)
----
[[[1,2],[3,4]]]

# jsonb_agg

query T
SELECT jsonb_agg(js) FROM (SELECT '1'::jsonb AS js WHERE false)
----
NULL

query T
SELECT jsonb_agg(1)
----
[1]

statement ok
CREATE TABLE t1 (a int)

statement ok
INSERT INTO t1 VALUES (1), (2), (3), (NULL)

query T
SELECT jsonb_agg(a) FROM (select a from t1 where a IS NOT NULL)
----
[1,2,3]

query T
SELECT jsonb_agg(a) FROM t1
----
[1,2,3,null]

query T
SELECT jsonb_agg(a::text::jsonb) FROM t1
----
[1,2,3,null]

query T
SELECT jsonb_agg(a) FILTER (WHERE a IS NOT NULL) FROM t1
----
[1,2,3]

query error db error: ERROR: function jsonb_agg\(integer, integer\) does not exist
SELECT jsonb_agg(1, 2)

statement ok
CREATE TABLE t2 (a int, b date)

statement ok
INSERT INTO t2 VALUES (1, date '2020-01-01'), (NULL, date '2020-01-02')

query T
SELECT jsonb_agg((a, b)) FROM t2
----
[{"f1":null,"f2":"2020-01-02"},{"f1":1,"f2":"2020-01-01"}]

query TTT
SELECT jsonb_agg((a, b)), jsonb_agg(a), jsonb_agg(b) FROM t2
----
[{"f1":null,"f2":"2020-01-02"},{"f1":1,"f2":"2020-01-01"}]  [1,null]  ["2020-01-01","2020-01-02"]

# jsonb_object_agg

query T
SELECT jsonb_object_agg(k, v) FROM (SELECT 1 AS k, 2 AS V WHERE false)
----
NULL

query T
SELECT jsonb_object_agg('one', 2)
----
{"one":2}

query T
SELECT jsonb_object_agg(1, 2)
----
{"1":2}

query T
SELECT jsonb_object_agg(k, v) FROM (SELECT a - 1 AS k, a AS v from t1 where a IS NOT NULL)
----
{"0":1,"1":2,"2":3}

query T
SELECT jsonb_object_agg(column1, column2) FROM (VALUES ('a', null), ('b', 1))
----
{"a":null,"b":1}

query T
SELECT jsonb_object_agg(column1, column2) FROM (VALUES ('b', 2), ('a', 1))
----
{"a":1,"b":2}

query T
SELECT jsonb_object_agg(column1, column2) FROM (VALUES ('a', 1), ('a', 2))
----
{"a":2}

query T
SELECT jsonb_object_agg(null, null)
----
{}

query T
SELECT jsonb_object_agg(a, a) FILTER (WHERE a IS NOT NULL) FROM t1
----
{"1":1,"2":2,"3":3}

# Null casts. Protects against database-issues#2244.
query TTTTTTT
SELECT
    NULL::jsonb::text,
    NULL::jsonb::int4,
    NULL::jsonb::int8,
    NULL::jsonb::float4,
    NULL::jsonb::float8,
    NULL::jsonb::decimal,
    NULL::jsonb::bool
----
NULL NULL NULL NULL NULL NULL NULL

query T
SELECT '{}'::JSONB ->> -9223372036854775808;
----
NULL

query T
SELECT '{}'::JSONB - -9223372036854775808;
----
NULL

query T
SELECT '{}'::JSONB #> '{-9223372036854775808}';
----
NULL

## Regression test for https://github.com/MaterializeInc/database-issues/issues/9182

statement ok
CREATE TABLE t3(x int, y text, z bool);

# Give inputs with different types to `to_jsonb` in different branches. In this case, the optimization where we pull out
# `to_jsonb` from the CASE should not happen.

query T multiline
EXPLAIN OPTIMIZED PLAN AS VERBOSE TEXT FOR
SELECT
  CASE x
    WHEN 1 THEN to_jsonb(x)
    WHEN 2 THEN to_jsonb(y)
    WHEN 3 THEN to_jsonb(z)
    ELSE to_jsonb(x+x)
  END
FROM t3;
----
Explained Query:
  Project (#3)
    Map (case when (#0{x} = 1) then jsonbable_to_jsonb(integer_to_numeric(#0{x})) else case when (#0{x} = 2) then jsonbable_to_jsonb(#1{y}) else case when (#0{x} = 3) then jsonbable_to_jsonb(#2{z}) else jsonbable_to_jsonb(integer_to_numeric((#0{x} + #0{x}))) end end end)
      ReadStorage materialize.public.t3

Source materialize.public.t3

Target cluster: quickstart

EOF

# Check that we are still doing the optimization when the types do match.
query T multiline
EXPLAIN OPTIMIZED PLAN AS VERBOSE TEXT FOR
SELECT
  CASE x
    WHEN 1 THEN to_jsonb(1*x)
    WHEN 2 THEN to_jsonb(2*x)
    WHEN 3 THEN to_jsonb(3*x)
    ELSE to_jsonb(x+x)
  END
FROM t3;
----
Explained Query:
  Project (#3)
    Map (jsonbable_to_jsonb(integer_to_numeric(case when (#0{x} = 1) then (1 * #0{x}) else case when (#0{x} = 2) then (2 * #0{x}) else case when (#0{x} = 3) then (3 * #0{x}) else (#0{x} + #0{x}) end end end)))
      ReadStorage materialize.public.t3

Source materialize.public.t3

Target cluster: quickstart

EOF