json

## Basic creation

query TT
SELECT '1'::JSONB, '2'::JSON
----
1 2

query T
SELECT pg_typeof(JSON '1')
----
jsonb

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

query T
SELECT '1.00'::JSON
----
1.00

statement error unexpected EOF
SELECT '{'::JSON

query T
SELECT '"hello"'::JSON
----
"hello"

query T
SELECT '"abc\n123"'::JSON
----
"abc\n123"

query TTT
SELECT 'true'::JSON, 'false'::JSON, 'null'::JSON
----
true false null

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

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

query T
SELECT '[1, "hello", [[[true, false]]]]'::JSON
----
[1, "hello", [[[true, false]]]]

query T
SELECT '[1, "hello", {"a": ["foo", {"b": 3}]}]'::JSON
----
[1, "hello", {"a": ["foo", {"b": 3}]}]

query T
SELECT '{}'::JSON
----
{}

query T
SELECT '{"a": "b", "c": "d"}'::JSON
----
{"a": "b", "c": "d"}

query T
SELECT '{"a": 1, "c": {"foo": "bar"}}'::JSON
----
{"a": 1, "c": {"foo": "bar"}}

# Only the final occurrence of a key in an object is kept.
query T
SELECT '{"a": 1, "a": 2}'::JSON
----
{"a": 2}

query T
SELECT NULL::JSON
----
NULL

statement error arrays of JSON unsupported as column type.*\nHINT:.*\n.*23468
CREATE TABLE x (y JSONB[])

statement ok
CREATE TABLE foo (pk INT DEFAULT unique_rowid(), bar JSON)

statement error arrays of JSON unsupported as column type.*\nHINT:.*\n.*23468
CREATE VIEW x AS SELECT array_agg(bar) FROM foo

statement ok
INSERT INTO foo(bar) VALUES
  ('{"a": "b"}'),
  ('[1, 2, 3]'),
  ('"hello"'),
  ('1.000'),
  ('true'),
  ('false'),
  (NULL),
  ('{"x": [1, 2, 3]}'),
  ('{"x": {"y": "z"}}')

query T rowsort
SELECT bar FROM foo
----
{"a": "b"}
[1, 2, 3]
"hello"
1.000
true
false
NULL
{"x": [1, 2, 3]}
{"x": {"y": "z"}}

query T
SELECT bar FROM foo WHERE bar->>'a' = 'b'
----
{"a": "b"}

query T
SELECT bar FROM foo WHERE bar ? 'a'
----
{"a": "b"}

query BBBBBBB
VALUES (
  '"hello"'::JSONB   ? 'hello',
  '"hello"'::JSONB   ? 'goodbye',
  '"hello"'::JSONB   ? 'ello',
  '"hello"'::JSONB   ? 'h',
  'true'::JSONB      ? 'true',
  '1'::JSONB         ? '1',
  'null'::JSONB      ? 'null'
)
----
true  false  false  false  false  false  false

query T
SELECT bar FROM foo WHERE bar ? 'hello'
----
"hello"

query T
SELECT bar FROM foo WHERE bar ? 'goodbye'
----

query T
SELECT bar FROM foo WHERE bar ?| ARRAY['a','b']
----
{"a": "b"}

query T
SELECT bar FROM foo WHERE bar ?& ARRAY['a','b']
----

# ?| and ?& ignore NULLs.
query T
SELECT bar FROM foo WHERE bar ?| ARRAY['a', null]
----
{"a": "b"}

query T
SELECT bar FROM foo WHERE bar ?| ARRAY[null, null]::STRING[]
----

query T
SELECT bar FROM foo WHERE bar ?& ARRAY['a', null]
----
{"a": "b"}

query T
SELECT bar FROM foo WHERE bar->'a' = '"b"'::JSON
----
{"a": "b"}

statement error pgcode 0A000 can't order by column type jsonb.*\nHINT.*\n.*35706
SELECT bar FROM foo ORDER BY bar

statement error pgcode 0A000 column k is of type jsonb and thus is not indexable
CREATE TABLE pk (k JSON PRIMARY KEY)

query T rowsort
SELECT bar->'a' FROM foo
----
"b"
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL

query IT
SELECT * from foo where bar->'x' = '[1]'
----

query IT
SELECT * from foo where bar->'x' = '{}'
----

query T
SELECT array_agg(bar ORDER BY pk) FROM foo
----
{"'{\"a\": \"b\"}'","'[1, 2, 3]'","\"hello\"",1.000,true,false,NULL,"'{\"x\": [1, 2, 3]}'","'{\"x\": {\"y\": \"z\"}}'"}

statement ok
DELETE FROM foo

statement ok
INSERT INTO foo(bar) VALUES ('{"a": {"c": "d"}}');

query TT
SELECT bar->'a'->'c', bar->'a'->>'c' FROM foo
----
"d" d

statement ok
CREATE TABLE multiple (a JSON, b JSON)

statement ok
INSERT INTO multiple VALUES ('{"a":"b"}', '[1,2,3,4,"foo"]')

query T
SELECT a FROM multiple
----
{"a": "b"}

query T
SELECT b FROM multiple
----
[1, 2, 3, 4, "foo"]

## Comparisons

# We opt to not expose <, >, <=, >= at this time, to avoid having to commit to
# an ordering.
query B
SELECT '1'::JSON = '1'::JSON
----
true

query B
SELECT '1'::JSON = '1'
----
true

query B
SELECT '1'::JSON = '2'::JSON
----
false

query B
SELECT '1.00'::JSON = '1'::JSON
----
true

query BB
SELECT '"hello"'::JSON = '"hello"'::JSON, '"hello"'::JSON = '"goodbye"'::JSON
----
true false

query B
SELECT '"hello"'::JSON IN ('"hello"'::JSON, '1'::JSON, '[]'::JSON)
----
true

query B
SELECT 'false'::JSON IN ('"hello"'::JSON, '1'::JSON, '[]'::JSON)
----
false

## Operators

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 TTTT
SELECT 'null'::jsonb->-2, 'null'::jsonb->-1, 'null'::jsonb->0, 'null'::jsonb->1
----
NULL  null  null  NULL

query TTTT
SELECT 'true'::jsonb->-2, 'true'::jsonb->-1, 'true'::jsonb->0, 'true'::jsonb->1
----
NULL  true  true  NULL

query TTTT
SELECT 'false'::jsonb->-2, 'false'::jsonb->-1, 'false'::jsonb->0, 'false'::jsonb->1
----
NULL  false  false  NULL

query TTTT
SELECT '"foo"'::jsonb->-2, '"foo"'::jsonb->-1, '"foo"'::jsonb->0, '"foo"'::jsonb->1
----
NULL  "foo"  "foo"  NULL

query TTTT
SELECT '123'::jsonb->-2, '123'::jsonb->-1, '123'::jsonb->0, '123'::jsonb->1
----
NULL  123  123  NULL

query TTTT
SELECT 'null'::jsonb->>-2, 'null'::jsonb->>-1, 'null'::jsonb->>0, 'null'::jsonb->>1
----
NULL  NULL  NULL  NULL

query TTTT
SELECT 'true'::jsonb->>-2, 'true'::jsonb->>-1, 'true'::jsonb->>0, 'true'::jsonb->>1
----
NULL  true  true  NULL

query TTTT
SELECT 'false'::jsonb->>-2, 'false'::jsonb->>-1, 'false'::jsonb->>0, 'false'::jsonb->>1
----
NULL  false  false  NULL

query TTTT
SELECT '"foo"'::jsonb->>-2, '"foo"'::jsonb->>-1, '"foo"'::jsonb->>0, '"foo"'::jsonb->>1
----
NULL  foo  foo  NULL

query TTTT
SELECT '123'::jsonb->>-2, '123'::jsonb->>-1, '123'::jsonb->>0, '123'::jsonb->>1
----
NULL  123  123  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 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]

statement error pgcode 22023 cannot delete from scalar
SELECT '3'::JSONB - 'b'

statement error pgcode 22023 cannot delete from object using integer index
SELECT '{}'::JSONB - 1

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

statement ok
CREATE INVERTED INDEX ON x (j)

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}}

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

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

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

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.0]

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

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

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, "b": 456, "c": 567}

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

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 #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']

subtest single_family_jsonb

statement ok
CREATE TABLE json_family (a INT PRIMARY KEY, b JSONB, FAMILY fam0(a), FAMILY fam1(b))

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

# Regression tests for #49143. Correctly handle cases where the -> operator
# results in NULL when both arguments are non-NULL.
subtest regression_49143

statement ok
CREATE TABLE t49143 (k INT PRIMARY KEY, j JSON);
INSERT INTO t49143 VALUES
  (0, '[]'),
  (1, '[1]'),
  (2, '[2]'),
  (3, '[[1, 2], [3, 4]]'),
  (4, '[[5, 6], [7, 8]]'),
  (5, '{}'),
  (6, '{"a": 1}'),
  (7, '{"b": 1}'),
  (8, '{"b": 2}'),
  (9, '{"b": [1, 2]}'),
  (10, '{"b": [3, 4]}');

query T
SELECT j FROM t49143 WHERE NOT (j->0 = '2') ORDER BY k
----
[1]
[[1, 2], [3, 4]]
[[5, 6], [7, 8]]

query T
SELECT j FROM t49143 WHERE NOT (j->'b' = '1') ORDER BY k
----
{"b": 2}
{"b": [1, 2]}
{"b": [3, 4]}

query T
SELECT j FROM t49143 WHERE NOT (j -> 0 @> '[1]') ORDER BY k
----
[1]
[2]
[[5, 6], [7, 8]]

query T
SELECT j FROM t49143 WHERE NOT (j -> 'b' @> '[1]') ORDER BY k
----
{"b": 1}
{"b": 2}
{"b": [3, 4]}

# Regression tests for #57165 (wrong type conversion in the vectorized engine).
subtest regression_57165

statement ok
CREATE TABLE t57165(j JSON, s STRING);
INSERT INTO t57165 VALUES ('{"foo": "bar"}', 'foo'), ('{"bar": "foo"}', 'bar')

query TT
SELECT j - 'foo' AS a, j - 'bar' AS b FROM t57165 ORDER BY rowid
----
{}              {"foo": "bar"}
{"bar": "foo"}  {}

query TT
SELECT '{"foo": "bar"}' - s AS a, '{"bar": "foo"}' - s AS b FROM t57165 ORDER BY rowid
----
{}              {"bar": "foo"}
{"foo": "bar"}  {}

query T
SELECT j - s FROM t57165
----
{}
{}

query T
SELECT ARRAY['"hello"'::JSON]
----
{"\"hello\""}

query T
SELECT '{}'::JSONB[]
----
{}

query B
SELECT json_valid('{"hello": {}}')
----
true

query B
SELECT json_valid('{"foo": {"bar": 1, "one": 1, "two": 2')
----
false

query B
SELECT json_valid('[{"bar": 1}, {"bar": 2}]')
----
true

query B
SELECT json_valid(NULL)
----
NULL

# Regression tests for #81647 (false negatives for ? operator in some cases).
subtest regression_81647

statement ok
CREATE TABLE t81647(j JSON);
INSERT INTO t81647 VALUES ('["a", "b"]')

query TBTB
SELECT j, j ? 'a', j-1, (j-1) ? 'a' FROM t81647
----
["a", "b"]  true  ["a"]  true

#
# Test JSONB subscripting.
#

# Constant folding.
query TTT
SELECT
  ('{"a": {"b": {"c": 1}}}'::jsonb)['a'],
  ('{"a": {"b": {"c": 1}}}'::jsonb)['a']['b']['c'],
  ('[1, "2", null]'::jsonb)[1]
----
{"b": {"c": 1}}  1  "2"

# Referencing subscript which does not exist.
query TTTT
SELECT
  ('{"a": 1}'::jsonb)['b'],
  ('{"a": {"b": {"c": 1}}}'::jsonb)['c']['b']['c'],
  ('[1, "2", null]'::jsonb)[4],
  ('{"a": 1}'::jsonb)[NULL]
----
NULL  NULL  NULL  NULL

# Error cases.
statement error unexpected JSON subscript type: TIMESTAMPTZ
SELECT ('{"a": 1}'::jsonb)[now()]

# Check it works from a JSON table.
statement ok
CREATE TABLE json_subscript_test (
  id SERIAL PRIMARY KEY,
  j JSONB,
  extract_field TEXT,
  extract_int_field INT
);
INSERT INTO json_subscript_test (j, extract_field, extract_int_field) VALUES
  ('{"other_field": 2}', 'other_field', 1),
  ('{"field": {"field": 2}}', 'field', 0),
  ('[1, 2, 3]', 'nothing_to_fetch', 1)

# Test subscripts with fields using other columns.
query TTITTTT
SELECT j, extract_field, extract_int_field, j['field'], j[extract_field], j[extract_field][extract_field], j[extract_int_field]
FROM json_subscript_test ORDER BY id
----
{"other_field": 2}       other_field       1  NULL          2             NULL  NULL
{"field": {"field": 2}}  field             0  {"field": 2}  {"field": 2}  2     NULL
[1, 2, 3]                nothing_to_fetch  1  NULL          NULL          NULL  2

# Test use in a WHERE clause.
query T
SELECT j FROM json_subscript_test WHERE j['other_field'] = '2' ORDER BY id
----
{"other_field": 2}