aboutsummaryrefslogtreecommitdiff
path: root/src/test/regress
diff options
context:
space:
mode:
Diffstat (limited to 'src/test/regress')
-rw-r--r--src/test/regress/expected/json_sqljson.out24
-rw-r--r--src/test/regress/expected/jsonb_sqljson.out2135
-rw-r--r--src/test/regress/expected/opr_sanity.out6
-rw-r--r--src/test/regress/expected/sqljson.out1320
-rw-r--r--src/test/regress/parallel_schedule2
-rw-r--r--src/test/regress/sql/json_sqljson.sql15
-rw-r--r--src/test/regress/sql/jsonb_sqljson.sql977
-rw-r--r--src/test/regress/sql/opr_sanity.sql6
-rw-r--r--src/test/regress/sql/sqljson.sql471
9 files changed, 5 insertions, 4951 deletions
diff --git a/src/test/regress/expected/json_sqljson.out b/src/test/regress/expected/json_sqljson.out
deleted file mode 100644
index 995f2674046..00000000000
--- a/src/test/regress/expected/json_sqljson.out
+++ /dev/null
@@ -1,24 +0,0 @@
--- JSON_EXISTS
-SELECT JSON_EXISTS(NULL FORMAT JSON, '$');
-ERROR: JSON_EXISTS() is not yet implemented for the json type
-LINE 1: SELECT JSON_EXISTS(NULL FORMAT JSON, '$');
- ^
-HINT: Try casting the argument to jsonb
--- JSON_VALUE
-SELECT JSON_VALUE(NULL FORMAT JSON, '$');
-ERROR: JSON_VALUE() is not yet implemented for the json type
-LINE 1: SELECT JSON_VALUE(NULL FORMAT JSON, '$');
- ^
-HINT: Try casting the argument to jsonb
--- JSON_QUERY
-SELECT JSON_QUERY(NULL FORMAT JSON, '$');
-ERROR: JSON_QUERY() is not yet implemented for the json type
-LINE 1: SELECT JSON_QUERY(NULL FORMAT JSON, '$');
- ^
-HINT: Try casting the argument to jsonb
--- JSON_TABLE
-SELECT * FROM JSON_TABLE(NULL FORMAT JSON, '$' COLUMNS (foo text));
-ERROR: JSON_TABLE() is not yet implemented for the json type
-LINE 1: SELECT * FROM JSON_TABLE(NULL FORMAT JSON, '$' COLUMNS (foo ...
- ^
-HINT: Try casting the argument to jsonb
diff --git a/src/test/regress/expected/jsonb_sqljson.out b/src/test/regress/expected/jsonb_sqljson.out
deleted file mode 100644
index ef496110af3..00000000000
--- a/src/test/regress/expected/jsonb_sqljson.out
+++ /dev/null
@@ -1,2135 +0,0 @@
--- JSON_EXISTS
-SELECT JSON_EXISTS(NULL::jsonb, '$');
- json_exists
--------------
-
-(1 row)
-
-SELECT JSON_EXISTS(jsonb '[]', '$');
- json_exists
--------------
- t
-(1 row)
-
-SELECT JSON_EXISTS(JSON_OBJECT(RETURNING jsonb), '$');
- json_exists
--------------
- t
-(1 row)
-
-SELECT JSON_EXISTS(jsonb '1', '$');
- json_exists
--------------
- t
-(1 row)
-
-SELECT JSON_EXISTS(jsonb 'null', '$');
- json_exists
--------------
- t
-(1 row)
-
-SELECT JSON_EXISTS(jsonb '[]', '$');
- json_exists
--------------
- t
-(1 row)
-
-SELECT JSON_EXISTS(jsonb '1', '$.a');
- json_exists
--------------
- f
-(1 row)
-
-SELECT JSON_EXISTS(jsonb '1', 'strict $.a');
- json_exists
--------------
- f
-(1 row)
-
-SELECT JSON_EXISTS(jsonb '1', 'strict $.a' ERROR ON ERROR);
-ERROR: jsonpath member accessor can only be applied to an object
-SELECT JSON_EXISTS(jsonb 'null', '$.a');
- json_exists
--------------
- f
-(1 row)
-
-SELECT JSON_EXISTS(jsonb '[]', '$.a');
- json_exists
--------------
- f
-(1 row)
-
-SELECT JSON_EXISTS(jsonb '[1, "aaa", {"a": 1}]', 'strict $.a');
- json_exists
--------------
- f
-(1 row)
-
-SELECT JSON_EXISTS(jsonb '[1, "aaa", {"a": 1}]', 'lax $.a');
- json_exists
--------------
- t
-(1 row)
-
-SELECT JSON_EXISTS(jsonb '{}', '$.a');
- json_exists
--------------
- f
-(1 row)
-
-SELECT JSON_EXISTS(jsonb '{"b": 1, "a": 2}', '$.a');
- json_exists
--------------
- t
-(1 row)
-
-SELECT JSON_EXISTS(jsonb '1', '$.a.b');
- json_exists
--------------
- f
-(1 row)
-
-SELECT JSON_EXISTS(jsonb '{"a": {"b": 1}}', '$.a.b');
- json_exists
--------------
- t
-(1 row)
-
-SELECT JSON_EXISTS(jsonb '{"a": 1, "b": 2}', '$.a.b');
- json_exists
--------------
- f
-(1 row)
-
-SELECT JSON_EXISTS(jsonb '{"a": 1, "b": 2}', '$.* ? (@ > $x)' PASSING 1 AS x);
- json_exists
--------------
- t
-(1 row)
-
-SELECT JSON_EXISTS(jsonb '{"a": 1, "b": 2}', '$.* ? (@ > $x)' PASSING '1' AS x);
- json_exists
--------------
- f
-(1 row)
-
-SELECT JSON_EXISTS(jsonb '{"a": 1, "b": 2}', '$.* ? (@ > $x && @ < $y)' PASSING 0 AS x, 2 AS y);
- json_exists
--------------
- t
-(1 row)
-
-SELECT JSON_EXISTS(jsonb '{"a": 1, "b": 2}', '$.* ? (@ > $x && @ < $y)' PASSING 0 AS x, 1 AS y);
- json_exists
--------------
- f
-(1 row)
-
--- extension: boolean expressions
-SELECT JSON_EXISTS(jsonb '1', '$ > 2');
- json_exists
--------------
- t
-(1 row)
-
-SELECT JSON_EXISTS(jsonb '1', '$.a > 2' ERROR ON ERROR);
- json_exists
--------------
- t
-(1 row)
-
--- extension: RETURNING clause
-SELECT JSON_EXISTS(jsonb '1', '$[0]' RETURNING bool);
- json_exists
--------------
- t
-(1 row)
-
-SELECT JSON_EXISTS(jsonb '1', '$[1]' RETURNING bool);
- json_exists
--------------
- f
-(1 row)
-
-SELECT JSON_EXISTS(jsonb '1', '$[0]' RETURNING int);
- json_exists
--------------
- 1
-(1 row)
-
-SELECT JSON_EXISTS(jsonb '1', '$[1]' RETURNING int);
- json_exists
--------------
- 0
-(1 row)
-
-SELECT JSON_EXISTS(jsonb '1', '$[0]' RETURNING text);
- json_exists
--------------
- true
-(1 row)
-
-SELECT JSON_EXISTS(jsonb '1', '$[1]' RETURNING text);
- json_exists
--------------
- false
-(1 row)
-
-SELECT JSON_EXISTS(jsonb '1', 'strict $[1]' RETURNING text FALSE ON ERROR);
- json_exists
--------------
- false
-(1 row)
-
-SELECT JSON_EXISTS(jsonb '1', '$[0]' RETURNING jsonb);
-ERROR: cannot cast type boolean to jsonb
-LINE 1: SELECT JSON_EXISTS(jsonb '1', '$[0]' RETURNING jsonb);
- ^
-SELECT JSON_EXISTS(jsonb '1', '$[0]' RETURNING float4);
-ERROR: cannot cast type boolean to real
-LINE 1: SELECT JSON_EXISTS(jsonb '1', '$[0]' RETURNING float4);
- ^
--- JSON_VALUE
-SELECT JSON_VALUE(NULL::jsonb, '$');
- json_value
-------------
-
-(1 row)
-
-SELECT JSON_VALUE(jsonb 'null', '$');
- json_value
-------------
-
-(1 row)
-
-SELECT JSON_VALUE(jsonb 'null', '$' RETURNING int);
- json_value
-------------
-
-(1 row)
-
-SELECT JSON_VALUE(jsonb 'true', '$');
- json_value
-------------
- true
-(1 row)
-
-SELECT JSON_VALUE(jsonb 'true', '$' RETURNING bool);
- json_value
-------------
- t
-(1 row)
-
-SELECT JSON_VALUE(jsonb '123', '$');
- json_value
-------------
- 123
-(1 row)
-
-SELECT JSON_VALUE(jsonb '123', '$' RETURNING int) + 234;
- ?column?
-----------
- 357
-(1 row)
-
-SELECT JSON_VALUE(jsonb '123', '$' RETURNING text);
- json_value
-------------
- 123
-(1 row)
-
-/* jsonb bytea ??? */
-SELECT JSON_VALUE(jsonb '123', '$' RETURNING bytea ERROR ON ERROR);
-ERROR: SQL/JSON item cannot be cast to target type
-SELECT JSON_VALUE(jsonb '1.23', '$');
- json_value
-------------
- 1.23
-(1 row)
-
-SELECT JSON_VALUE(jsonb '1.23', '$' RETURNING int);
- json_value
-------------
- 1
-(1 row)
-
-SELECT JSON_VALUE(jsonb '"1.23"', '$' RETURNING numeric);
- json_value
-------------
- 1.23
-(1 row)
-
-SELECT JSON_VALUE(jsonb '"1.23"', '$' RETURNING int ERROR ON ERROR);
-ERROR: invalid input syntax for type integer: "1.23"
-SELECT JSON_VALUE(jsonb '"aaa"', '$');
- json_value
-------------
- aaa
-(1 row)
-
-SELECT JSON_VALUE(jsonb '"aaa"', '$' RETURNING text);
- json_value
-------------
- aaa
-(1 row)
-
-SELECT JSON_VALUE(jsonb '"aaa"', '$' RETURNING char(5));
- json_value
-------------
- aaa
-(1 row)
-
-SELECT JSON_VALUE(jsonb '"aaa"', '$' RETURNING char(2));
- json_value
-------------
- aa
-(1 row)
-
-SELECT JSON_VALUE(jsonb '"aaa"', '$' RETURNING json);
- json_value
-------------
- "aaa"
-(1 row)
-
-SELECT JSON_VALUE(jsonb '"aaa"', '$' RETURNING jsonb);
- json_value
-------------
- "aaa"
-(1 row)
-
-SELECT JSON_VALUE(jsonb '"aaa"', '$' RETURNING json ERROR ON ERROR);
- json_value
-------------
- "aaa"
-(1 row)
-
-SELECT JSON_VALUE(jsonb '"aaa"', '$' RETURNING jsonb ERROR ON ERROR);
- json_value
-------------
- "aaa"
-(1 row)
-
-SELECT JSON_VALUE(jsonb '"\"aaa\""', '$' RETURNING json);
- json_value
-------------
- "\"aaa\""
-(1 row)
-
-SELECT JSON_VALUE(jsonb '"\"aaa\""', '$' RETURNING jsonb);
- json_value
-------------
- "\"aaa\""
-(1 row)
-
-SELECT JSON_VALUE(jsonb '"aaa"', '$' RETURNING int);
- json_value
-------------
-
-(1 row)
-
-SELECT JSON_VALUE(jsonb '"aaa"', '$' RETURNING int ERROR ON ERROR);
-ERROR: invalid input syntax for type integer: "aaa"
-SELECT JSON_VALUE(jsonb '"aaa"', '$' RETURNING int DEFAULT 111 ON ERROR);
- json_value
-------------
- 111
-(1 row)
-
-SELECT JSON_VALUE(jsonb '"123"', '$' RETURNING int) + 234;
- ?column?
-----------
- 357
-(1 row)
-
-SELECT JSON_VALUE(jsonb '"2017-02-20"', '$' RETURNING date) + 9;
- ?column?
-------------
- 03-01-2017
-(1 row)
-
--- Test NULL checks execution in domain types
-CREATE DOMAIN sqljsonb_int_not_null AS int NOT NULL;
-SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_int_not_null);
-ERROR: domain sqljsonb_int_not_null does not allow null values
-SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_int_not_null NULL ON ERROR);
-ERROR: domain sqljsonb_int_not_null does not allow null values
-SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_int_not_null DEFAULT NULL ON ERROR);
-ERROR: domain sqljsonb_int_not_null does not allow null values
-SELECT JSON_VALUE(jsonb '[]', '$');
- json_value
-------------
-
-(1 row)
-
-SELECT JSON_VALUE(jsonb '[]', '$' ERROR ON ERROR);
-ERROR: JSON path expression in JSON_VALUE should return singleton scalar item
-SELECT JSON_VALUE(jsonb '{}', '$');
- json_value
-------------
-
-(1 row)
-
-SELECT JSON_VALUE(jsonb '{}', '$' ERROR ON ERROR);
-ERROR: JSON path expression in JSON_VALUE should return singleton scalar item
-SELECT JSON_VALUE(jsonb '1', '$.a');
- json_value
-------------
-
-(1 row)
-
-SELECT JSON_VALUE(jsonb '1', 'strict $.a' ERROR ON ERROR);
-ERROR: jsonpath member accessor can only be applied to an object
-SELECT JSON_VALUE(jsonb '1', 'strict $.a' DEFAULT 'error' ON ERROR);
- json_value
-------------
- error
-(1 row)
-
-SELECT JSON_VALUE(jsonb '1', 'lax $.a' ERROR ON ERROR);
- json_value
-------------
-
-(1 row)
-
-SELECT JSON_VALUE(jsonb '1', 'lax $.a' ERROR ON EMPTY ERROR ON ERROR);
-ERROR: no SQL/JSON item
-SELECT JSON_VALUE(jsonb '1', 'strict $.a' DEFAULT 2 ON ERROR);
- json_value
-------------
- 2
-(1 row)
-
-SELECT JSON_VALUE(jsonb '1', 'lax $.a' DEFAULT 2 ON ERROR);
- json_value
-------------
-
-(1 row)
-
-SELECT JSON_VALUE(jsonb '1', 'lax $.a' DEFAULT '2' ON ERROR);
- json_value
-------------
-
-(1 row)
-
-SELECT JSON_VALUE(jsonb '1', 'lax $.a' NULL ON EMPTY DEFAULT '2' ON ERROR);
- json_value
-------------
-
-(1 row)
-
-SELECT JSON_VALUE(jsonb '1', 'lax $.a' DEFAULT '2' ON EMPTY DEFAULT '3' ON ERROR);
- json_value
-------------
- 2
-(1 row)
-
-SELECT JSON_VALUE(jsonb '1', 'lax $.a' ERROR ON EMPTY DEFAULT '3' ON ERROR);
- json_value
-------------
- 3
-(1 row)
-
-SELECT JSON_VALUE(jsonb '[1,2]', '$[*]' ERROR ON ERROR);
-ERROR: JSON path expression in JSON_VALUE should return singleton scalar item
-SELECT JSON_VALUE(jsonb '[1,2]', '$[*]' DEFAULT '0' ON ERROR);
- json_value
-------------
- 0
-(1 row)
-
-SELECT JSON_VALUE(jsonb '[" "]', '$[*]' RETURNING int ERROR ON ERROR);
-ERROR: invalid input syntax for type integer: " "
-SELECT JSON_VALUE(jsonb '[" "]', '$[*]' RETURNING int DEFAULT 2 + 3 ON ERROR);
- json_value
-------------
- 5
-(1 row)
-
-SELECT JSON_VALUE(jsonb '["1"]', '$[*]' RETURNING int DEFAULT 2 + 3 ON ERROR);
- json_value
-------------
- 1
-(1 row)
-
-SELECT
- x,
- JSON_VALUE(
- jsonb '{"a": 1, "b": 2}',
- '$.* ? (@ > $x)' PASSING x AS x
- RETURNING int
- DEFAULT -1 ON EMPTY
- DEFAULT -2 ON ERROR
- ) y
-FROM
- generate_series(0, 2) x;
- x | y
----+----
- 0 | -2
- 1 | 2
- 2 | -1
-(3 rows)
-
-SELECT JSON_VALUE(jsonb 'null', '$a' PASSING point ' (1, 2 )' AS a);
- json_value
-------------
- (1,2)
-(1 row)
-
-SELECT JSON_VALUE(jsonb 'null', '$a' PASSING point ' (1, 2 )' AS a RETURNING point);
- json_value
-------------
- (1,2)
-(1 row)
-
--- Test timestamptz passing and output
-SELECT JSON_VALUE(jsonb 'null', '$ts' PASSING timestamptz '2018-02-21 12:34:56 +10' AS ts);
- json_value
-------------------------------
- Tue Feb 20 18:34:56 2018 PST
-(1 row)
-
-SELECT JSON_VALUE(jsonb 'null', '$ts' PASSING timestamptz '2018-02-21 12:34:56 +10' AS ts RETURNING timestamptz);
- json_value
-------------------------------
- Tue Feb 20 18:34:56 2018 PST
-(1 row)
-
-SELECT JSON_VALUE(jsonb 'null', '$ts' PASSING timestamptz '2018-02-21 12:34:56 +10' AS ts RETURNING timestamp);
- json_value
---------------------------
- Tue Feb 20 18:34:56 2018
-(1 row)
-
-SELECT JSON_VALUE(jsonb 'null', '$ts' PASSING timestamptz '2018-02-21 12:34:56 +10' AS ts RETURNING json);
- json_value
------------------------------
- "2018-02-21T02:34:56+00:00"
-(1 row)
-
-SELECT JSON_VALUE(jsonb 'null', '$ts' PASSING timestamptz '2018-02-21 12:34:56 +10' AS ts RETURNING jsonb);
- json_value
------------------------------
- "2018-02-21T02:34:56+00:00"
-(1 row)
-
--- JSON_QUERY
-SELECT
- JSON_QUERY(js, '$'),
- JSON_QUERY(js, '$' WITHOUT WRAPPER),
- JSON_QUERY(js, '$' WITH CONDITIONAL WRAPPER),
- JSON_QUERY(js, '$' WITH UNCONDITIONAL ARRAY WRAPPER),
- JSON_QUERY(js, '$' WITH ARRAY WRAPPER)
-FROM
- (VALUES
- (jsonb 'null'),
- ('12.3'),
- ('true'),
- ('"aaa"'),
- ('[1, null, "2"]'),
- ('{"a": 1, "b": [2]}')
- ) foo(js);
- json_query | json_query | json_query | json_query | json_query
---------------------+--------------------+--------------------+----------------------+----------------------
- null | null | [null] | [null] | [null]
- 12.3 | 12.3 | [12.3] | [12.3] | [12.3]
- true | true | [true] | [true] | [true]
- "aaa" | "aaa" | ["aaa"] | ["aaa"] | ["aaa"]
- [1, null, "2"] | [1, null, "2"] | [1, null, "2"] | [[1, null, "2"]] | [[1, null, "2"]]
- {"a": 1, "b": [2]} | {"a": 1, "b": [2]} | {"a": 1, "b": [2]} | [{"a": 1, "b": [2]}] | [{"a": 1, "b": [2]}]
-(6 rows)
-
-SELECT
- JSON_QUERY(js, 'strict $[*]') AS "unspec",
- JSON_QUERY(js, 'strict $[*]' WITHOUT WRAPPER) AS "without",
- JSON_QUERY(js, 'strict $[*]' WITH CONDITIONAL WRAPPER) AS "with cond",
- JSON_QUERY(js, 'strict $[*]' WITH UNCONDITIONAL ARRAY WRAPPER) AS "with uncond",
- JSON_QUERY(js, 'strict $[*]' WITH ARRAY WRAPPER) AS "with"
-FROM
- (VALUES
- (jsonb '1'),
- ('[]'),
- ('[null]'),
- ('[12.3]'),
- ('[true]'),
- ('["aaa"]'),
- ('[[1, 2, 3]]'),
- ('[{"a": 1, "b": [2]}]'),
- ('[1, "2", null, [3]]')
- ) foo(js);
- unspec | without | with cond | with uncond | with
---------------------+--------------------+---------------------+----------------------+----------------------
- | | | |
- | | | |
- null | null | [null] | [null] | [null]
- 12.3 | 12.3 | [12.3] | [12.3] | [12.3]
- true | true | [true] | [true] | [true]
- "aaa" | "aaa" | ["aaa"] | ["aaa"] | ["aaa"]
- [1, 2, 3] | [1, 2, 3] | [1, 2, 3] | [[1, 2, 3]] | [[1, 2, 3]]
- {"a": 1, "b": [2]} | {"a": 1, "b": [2]} | {"a": 1, "b": [2]} | [{"a": 1, "b": [2]}] | [{"a": 1, "b": [2]}]
- | | [1, "2", null, [3]] | [1, "2", null, [3]] | [1, "2", null, [3]]
-(9 rows)
-
-SELECT JSON_QUERY(jsonb '"aaa"', '$' RETURNING text);
- json_query
-------------
- "aaa"
-(1 row)
-
-SELECT JSON_QUERY(jsonb '"aaa"', '$' RETURNING text KEEP QUOTES);
- json_query
-------------
- "aaa"
-(1 row)
-
-SELECT JSON_QUERY(jsonb '"aaa"', '$' RETURNING text KEEP QUOTES ON SCALAR STRING);
- json_query
-------------
- "aaa"
-(1 row)
-
-SELECT JSON_QUERY(jsonb '"aaa"', '$' RETURNING text OMIT QUOTES);
- json_query
-------------
- aaa
-(1 row)
-
-SELECT JSON_QUERY(jsonb '"aaa"', '$' RETURNING text OMIT QUOTES ON SCALAR STRING);
- json_query
-------------
- aaa
-(1 row)
-
-SELECT JSON_QUERY(jsonb '"aaa"', '$' OMIT QUOTES ERROR ON ERROR);
-ERROR: invalid input syntax for type json
-DETAIL: Token "aaa" is invalid.
-CONTEXT: JSON data, line 1: aaa
-SELECT JSON_QUERY(jsonb '"aaa"', '$' RETURNING json OMIT QUOTES ERROR ON ERROR);
-ERROR: invalid input syntax for type json
-DETAIL: Token "aaa" is invalid.
-CONTEXT: JSON data, line 1: aaa
-SELECT JSON_QUERY(jsonb '"aaa"', '$' RETURNING bytea FORMAT JSON OMIT QUOTES ERROR ON ERROR);
- json_query
-------------
- \x616161
-(1 row)
-
--- QUOTES behavior should not be specified when WITH WRAPPER used:
--- Should fail
-SELECT JSON_QUERY(jsonb '[1]', '$' WITH WRAPPER OMIT QUOTES);
-ERROR: SQL/JSON QUOTES behavior must not be specified when WITH WRAPPER is used
-LINE 1: SELECT JSON_QUERY(jsonb '[1]', '$' WITH WRAPPER OMIT QUOTES)...
- ^
-SELECT JSON_QUERY(jsonb '[1]', '$' WITH WRAPPER KEEP QUOTES);
-ERROR: SQL/JSON QUOTES behavior must not be specified when WITH WRAPPER is used
-LINE 1: SELECT JSON_QUERY(jsonb '[1]', '$' WITH WRAPPER KEEP QUOTES)...
- ^
-SELECT JSON_QUERY(jsonb '[1]', '$' WITH CONDITIONAL WRAPPER KEEP QUOTES);
-ERROR: SQL/JSON QUOTES behavior must not be specified when WITH WRAPPER is used
-LINE 1: ...N_QUERY(jsonb '[1]', '$' WITH CONDITIONAL WRAPPER KEEP QUOTE...
- ^
-SELECT JSON_QUERY(jsonb '[1]', '$' WITH CONDITIONAL WRAPPER OMIT QUOTES);
-ERROR: SQL/JSON QUOTES behavior must not be specified when WITH WRAPPER is used
-LINE 1: ...N_QUERY(jsonb '[1]', '$' WITH CONDITIONAL WRAPPER OMIT QUOTE...
- ^
--- Should succeed
-SELECT JSON_QUERY(jsonb '[1]', '$' WITHOUT WRAPPER OMIT QUOTES);
- json_query
-------------
- [1]
-(1 row)
-
-SELECT JSON_QUERY(jsonb '[1]', '$' WITHOUT WRAPPER KEEP QUOTES);
- json_query
-------------
- [1]
-(1 row)
-
-SELECT JSON_QUERY(jsonb '[]', '$[*]');
- json_query
-------------
-
-(1 row)
-
-SELECT JSON_QUERY(jsonb '[]', '$[*]' NULL ON EMPTY);
- json_query
-------------
-
-(1 row)
-
-SELECT JSON_QUERY(jsonb '[]', '$[*]' EMPTY ON EMPTY);
- json_query
-------------
- []
-(1 row)
-
-SELECT JSON_QUERY(jsonb '[]', '$[*]' EMPTY ARRAY ON EMPTY);
- json_query
-------------
- []
-(1 row)
-
-SELECT JSON_QUERY(jsonb '[]', '$[*]' EMPTY OBJECT ON EMPTY);
- json_query
-------------
- {}
-(1 row)
-
-SELECT JSON_QUERY(jsonb '[]', '$[*]' ERROR ON EMPTY);
- json_query
-------------
-
-(1 row)
-
-SELECT JSON_QUERY(jsonb '[]', '$[*]' DEFAULT '"empty"' ON EMPTY);
- json_query
-------------
- "empty"
-(1 row)
-
-SELECT JSON_QUERY(jsonb '[]', '$[*]' ERROR ON EMPTY NULL ON ERROR);
- json_query
-------------
-
-(1 row)
-
-SELECT JSON_QUERY(jsonb '[]', '$[*]' ERROR ON EMPTY EMPTY ARRAY ON ERROR);
- json_query
-------------
- []
-(1 row)
-
-SELECT JSON_QUERY(jsonb '[]', '$[*]' ERROR ON EMPTY EMPTY OBJECT ON ERROR);
- json_query
-------------
- {}
-(1 row)
-
-SELECT JSON_QUERY(jsonb '[]', '$[*]' ERROR ON EMPTY ERROR ON ERROR);
-ERROR: no SQL/JSON item
-SELECT JSON_QUERY(jsonb '[]', '$[*]' ERROR ON ERROR);
- json_query
-------------
-
-(1 row)
-
-SELECT JSON_QUERY(jsonb '[1,2]', '$[*]' ERROR ON ERROR);
-ERROR: JSON path expression in JSON_QUERY should return singleton item without wrapper
-HINT: Use WITH WRAPPER clause to wrap SQL/JSON item sequence into array.
-SELECT JSON_QUERY(jsonb '[1,2]', '$[*]' DEFAULT '"empty"' ON ERROR);
- json_query
-------------
- "empty"
-(1 row)
-
-SELECT JSON_QUERY(jsonb '[1,2]', '$' RETURNING json);
- json_query
-------------
- [1, 2]
-(1 row)
-
-SELECT JSON_QUERY(jsonb '[1,2]', '$' RETURNING json FORMAT JSON);
- json_query
-------------
- [1, 2]
-(1 row)
-
-SELECT JSON_QUERY(jsonb '[1,2]', '$' RETURNING jsonb);
- json_query
-------------
- [1, 2]
-(1 row)
-
-SELECT JSON_QUERY(jsonb '[1,2]', '$' RETURNING jsonb FORMAT JSON);
- json_query
-------------
- [1, 2]
-(1 row)
-
-SELECT JSON_QUERY(jsonb '[1,2]', '$' RETURNING text);
- json_query
-------------
- [1, 2]
-(1 row)
-
-SELECT JSON_QUERY(jsonb '[1,2]', '$' RETURNING char(10));
- json_query
-------------
- [1, 2]
-(1 row)
-
-SELECT JSON_QUERY(jsonb '[1,2]', '$' RETURNING char(3));
- json_query
-------------
- [1,
-(1 row)
-
-SELECT JSON_QUERY(jsonb '[1,2]', '$' RETURNING text FORMAT JSON);
- json_query
-------------
- [1, 2]
-(1 row)
-
-SELECT JSON_QUERY(jsonb '[1,2]', '$' RETURNING bytea);
- json_query
-----------------
- \x5b312c20325d
-(1 row)
-
-SELECT JSON_QUERY(jsonb '[1,2]', '$' RETURNING bytea FORMAT JSON);
- json_query
-----------------
- \x5b312c20325d
-(1 row)
-
-SELECT JSON_QUERY(jsonb '[1,2]', '$[*]' RETURNING bytea EMPTY OBJECT ON ERROR);
- json_query
-------------
- \x7b7d
-(1 row)
-
-SELECT JSON_QUERY(jsonb '[1,2]', '$[*]' RETURNING bytea FORMAT JSON EMPTY OBJECT ON ERROR);
- json_query
-------------
- \x7b7d
-(1 row)
-
-SELECT JSON_QUERY(jsonb '[1,2]', '$[*]' RETURNING json EMPTY OBJECT ON ERROR);
- json_query
-------------
- {}
-(1 row)
-
-SELECT JSON_QUERY(jsonb '[1,2]', '$[*]' RETURNING jsonb EMPTY OBJECT ON ERROR);
- json_query
-------------
- {}
-(1 row)
-
-SELECT
- x, y,
- JSON_QUERY(
- jsonb '[1,2,3,4,5,null]',
- '$[*] ? (@ >= $x && @ <= $y)'
- PASSING x AS x, y AS y
- WITH CONDITIONAL WRAPPER
- EMPTY ARRAY ON EMPTY
- ) list
-FROM
- generate_series(0, 4) x,
- generate_series(0, 4) y;
- x | y | list
----+---+--------------
- 0 | 0 | []
- 0 | 1 | [1]
- 0 | 2 | [1, 2]
- 0 | 3 | [1, 2, 3]
- 0 | 4 | [1, 2, 3, 4]
- 1 | 0 | []
- 1 | 1 | [1]
- 1 | 2 | [1, 2]
- 1 | 3 | [1, 2, 3]
- 1 | 4 | [1, 2, 3, 4]
- 2 | 0 | []
- 2 | 1 | []
- 2 | 2 | [2]
- 2 | 3 | [2, 3]
- 2 | 4 | [2, 3, 4]
- 3 | 0 | []
- 3 | 1 | []
- 3 | 2 | []
- 3 | 3 | [3]
- 3 | 4 | [3, 4]
- 4 | 0 | []
- 4 | 1 | []
- 4 | 2 | []
- 4 | 3 | []
- 4 | 4 | [4]
-(25 rows)
-
--- Extension: record types returning
-CREATE TYPE sqljsonb_rec AS (a int, t text, js json, jb jsonb, jsa json[]);
-CREATE TYPE sqljsonb_reca AS (reca sqljsonb_rec[]);
-SELECT JSON_QUERY(jsonb '[{"a": 1, "b": "foo", "t": "aaa", "js": [1, "2", {}], "jb": {"x": [1, "2", {}]}}, {"a": 2}]', '$[0]' RETURNING sqljsonb_rec);
- json_query
------------------------------------------------------
- (1,aaa,"[1, ""2"", {}]","{""x"": [1, ""2"", {}]}",)
-(1 row)
-
-SELECT * FROM unnest((JSON_QUERY(jsonb '{"jsa": [{"a": 1, "b": ["foo"]}, {"a": 2, "c": {}}, 123]}', '$' RETURNING sqljsonb_rec)).jsa);
- unnest
-------------------------
- {"a": 1, "b": ["foo"]}
- {"a": 2, "c": {}}
- 123
-(3 rows)
-
-SELECT * FROM unnest((JSON_QUERY(jsonb '{"reca": [{"a": 1, "t": ["foo", []]}, {"a": 2, "jb": [{}, true]}]}', '$' RETURNING sqljsonb_reca)).reca);
- a | t | js | jb | jsa
----+-------------+----+------------+-----
- 1 | ["foo", []] | | |
- 2 | | | [{}, true] |
-(2 rows)
-
--- Extension: array types returning
-SELECT JSON_QUERY(jsonb '[1,2,null,"3"]', '$[*]' RETURNING int[] WITH WRAPPER);
- json_query
---------------
- {1,2,NULL,3}
-(1 row)
-
-SELECT * FROM unnest(JSON_QUERY(jsonb '[{"a": 1, "t": ["foo", []]}, {"a": 2, "jb": [{}, true]}]', '$' RETURNING sqljsonb_rec[]));
- a | t | js | jb | jsa
----+-------------+----+------------+-----
- 1 | ["foo", []] | | |
- 2 | | | [{}, true] |
-(2 rows)
-
--- Extension: domain types returning
-SELECT JSON_QUERY(jsonb '{"a": 1}', '$.a' RETURNING sqljsonb_int_not_null);
- json_query
-------------
- 1
-(1 row)
-
-SELECT JSON_QUERY(jsonb '{"a": 1}', '$.b' RETURNING sqljsonb_int_not_null);
-ERROR: domain sqljsonb_int_not_null does not allow null values
--- Test timestamptz passing and output
-SELECT JSON_QUERY(jsonb 'null', '$ts' PASSING timestamptz '2018-02-21 12:34:56 +10' AS ts);
- json_query
------------------------------
- "2018-02-21T02:34:56+00:00"
-(1 row)
-
-SELECT JSON_QUERY(jsonb 'null', '$ts' PASSING timestamptz '2018-02-21 12:34:56 +10' AS ts RETURNING json);
- json_query
------------------------------
- "2018-02-21T02:34:56+00:00"
-(1 row)
-
-SELECT JSON_QUERY(jsonb 'null', '$ts' PASSING timestamptz '2018-02-21 12:34:56 +10' AS ts RETURNING jsonb);
- json_query
------------------------------
- "2018-02-21T02:34:56+00:00"
-(1 row)
-
--- Test constraints
-CREATE TABLE test_jsonb_constraints (
- js text,
- i int,
- x jsonb DEFAULT JSON_QUERY(jsonb '[1,2]', '$[*]' WITH WRAPPER)
- CONSTRAINT test_jsonb_constraint1
- CHECK (js IS JSON)
- CONSTRAINT test_jsonb_constraint2
- CHECK (JSON_EXISTS(js::jsonb, '$.a' PASSING i + 5 AS int, i::text AS txt, array[1,2,3] as arr))
- CONSTRAINT test_jsonb_constraint3
- CHECK (JSON_VALUE(js::jsonb, '$.a' RETURNING int DEFAULT ('12' || i)::int ON EMPTY ERROR ON ERROR) > i)
- CONSTRAINT test_jsonb_constraint4
- CHECK (JSON_QUERY(js::jsonb, '$.a' WITH CONDITIONAL WRAPPER EMPTY OBJECT ON ERROR) < jsonb '[10]')
- CONSTRAINT test_jsonb_constraint5
- CHECK (JSON_QUERY(js::jsonb, '$.a' RETURNING char(5) OMIT QUOTES EMPTY ARRAY ON EMPTY) > 'a' COLLATE "C")
- CONSTRAINT test_jsonb_constraint6
- CHECK (JSON_EXISTS(js::jsonb, 'strict $.a' RETURNING int TRUE ON ERROR) < 2)
-);
-\d test_jsonb_constraints
- Table "public.test_jsonb_constraints"
- Column | Type | Collation | Nullable | Default
---------+---------+-----------+----------+--------------------------------------------------------------------------------
- js | text | | |
- i | integer | | |
- x | jsonb | | | JSON_QUERY('[1, 2]'::jsonb, '$[*]' RETURNING jsonb WITH UNCONDITIONAL WRAPPER)
-Check constraints:
- "test_jsonb_constraint1" CHECK (js IS JSON)
- "test_jsonb_constraint2" CHECK (JSON_EXISTS(js::jsonb, '$."a"' PASSING i + 5 AS int, i::text AS txt, ARRAY[1, 2, 3] AS arr))
- "test_jsonb_constraint3" CHECK (JSON_VALUE(js::jsonb, '$."a"' RETURNING integer DEFAULT ('12'::text || i)::integer ON EMPTY ERROR ON ERROR) > i)
- "test_jsonb_constraint4" CHECK (JSON_QUERY(js::jsonb, '$."a"' RETURNING jsonb WITH CONDITIONAL WRAPPER EMPTY OBJECT ON ERROR) < '[10]'::jsonb)
- "test_jsonb_constraint5" CHECK (JSON_QUERY(js::jsonb, '$."a"' RETURNING character(5) OMIT QUOTES EMPTY ARRAY ON EMPTY) > ('a'::bpchar COLLATE "C"))
- "test_jsonb_constraint6" CHECK (JSON_EXISTS(js::jsonb, 'strict $."a"' RETURNING integer TRUE ON ERROR) < 2)
-
-SELECT check_clause
-FROM information_schema.check_constraints
-WHERE constraint_name LIKE 'test_jsonb_constraint%'
-ORDER BY 1;
- check_clause
---------------------------------------------------------------------------------------------------------------------------
- ((JSON_EXISTS((js)::jsonb, 'strict $."a"' RETURNING integer TRUE ON ERROR) < 2))
- ((JSON_QUERY((js)::jsonb, '$."a"' RETURNING character(5) OMIT QUOTES EMPTY ARRAY ON EMPTY) > ('a'::bpchar COLLATE "C")))
- ((JSON_QUERY((js)::jsonb, '$."a"' RETURNING jsonb WITH CONDITIONAL WRAPPER EMPTY OBJECT ON ERROR) < '[10]'::jsonb))
- ((JSON_VALUE((js)::jsonb, '$."a"' RETURNING integer DEFAULT (('12'::text || i))::integer ON EMPTY ERROR ON ERROR) > i))
- ((js IS JSON))
- (JSON_EXISTS((js)::jsonb, '$."a"' PASSING (i + 5) AS int, (i)::text AS txt, ARRAY[1, 2, 3] AS arr))
-(6 rows)
-
-SELECT pg_get_expr(adbin, adrelid)
-FROM pg_attrdef
-WHERE adrelid = 'test_jsonb_constraints'::regclass
-ORDER BY 1;
- pg_get_expr
---------------------------------------------------------------------------------
- JSON_QUERY('[1, 2]'::jsonb, '$[*]' RETURNING jsonb WITH UNCONDITIONAL WRAPPER)
-(1 row)
-
-INSERT INTO test_jsonb_constraints VALUES ('', 1);
-ERROR: new row for relation "test_jsonb_constraints" violates check constraint "test_jsonb_constraint1"
-DETAIL: Failing row contains (, 1, [1, 2]).
-INSERT INTO test_jsonb_constraints VALUES ('1', 1);
-ERROR: new row for relation "test_jsonb_constraints" violates check constraint "test_jsonb_constraint2"
-DETAIL: Failing row contains (1, 1, [1, 2]).
-INSERT INTO test_jsonb_constraints VALUES ('[]');
-ERROR: new row for relation "test_jsonb_constraints" violates check constraint "test_jsonb_constraint2"
-DETAIL: Failing row contains ([], null, [1, 2]).
-INSERT INTO test_jsonb_constraints VALUES ('{"b": 1}', 1);
-ERROR: new row for relation "test_jsonb_constraints" violates check constraint "test_jsonb_constraint2"
-DETAIL: Failing row contains ({"b": 1}, 1, [1, 2]).
-INSERT INTO test_jsonb_constraints VALUES ('{"a": 1}', 1);
-ERROR: new row for relation "test_jsonb_constraints" violates check constraint "test_jsonb_constraint3"
-DETAIL: Failing row contains ({"a": 1}, 1, [1, 2]).
-INSERT INTO test_jsonb_constraints VALUES ('{"a": 7}', 1);
-ERROR: new row for relation "test_jsonb_constraints" violates check constraint "test_jsonb_constraint5"
-DETAIL: Failing row contains ({"a": 7}, 1, [1, 2]).
-INSERT INTO test_jsonb_constraints VALUES ('{"a": 10}', 1);
-ERROR: new row for relation "test_jsonb_constraints" violates check constraint "test_jsonb_constraint4"
-DETAIL: Failing row contains ({"a": 10}, 1, [1, 2]).
-DROP TABLE test_jsonb_constraints;
--- Test mutabilily od query functions
-CREATE TABLE test_jsonb_mutability(js jsonb);
-CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$'));
-CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.a[0]'));
-CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.datetime()'));
-CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.a ? (@ < $.datetime())'));
-CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.a ? (@.datetime() < $.datetime())'));
-ERROR: functions in index expression must be marked IMMUTABLE
-CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.a ? (@.datetime() < $.datetime("HH:MI TZH"))'));
-ERROR: functions in index expression must be marked IMMUTABLE
-CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.a ? (@.datetime("HH:MI TZH") < $.datetime("HH:MI TZH"))'));
-CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.a ? (@.datetime("HH:MI") < $.datetime("YY-MM-DD HH:MI"))'));
-CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.a ? (@.datetime("HH:MI TZH") < $.datetime("YY-MM-DD HH:MI"))'));
-ERROR: functions in index expression must be marked IMMUTABLE
-CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.datetime("HH:MI TZH") < $x' PASSING '12:34'::timetz AS x));
-CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.datetime("HH:MI TZH") < $y' PASSING '12:34'::timetz AS x));
-CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.datetime() < $x' PASSING '12:34'::timetz AS x));
-ERROR: functions in index expression must be marked IMMUTABLE
-CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.datetime() < $x' PASSING '1234'::int AS x));
-CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.datetime() ? (@ == $x)' PASSING '12:34'::time AS x));
-ERROR: functions in index expression must be marked IMMUTABLE
-CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.datetime("YY-MM-DD") ? (@ == $x)' PASSING '2020-07-14'::date AS x));
-CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$[1, $.a ? (@.datetime() == $x)]' PASSING '12:34'::time AS x));
-ERROR: functions in index expression must be marked IMMUTABLE
-CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$[1, 0 to $.a ? (@.datetime() == $x)]' PASSING '12:34'::time AS x));
-ERROR: functions in index expression must be marked IMMUTABLE
-CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$[1, $.a ? (@.datetime("HH:MI") == $x)]' PASSING '12:34'::time AS x));
-DROP TABLE test_jsonb_mutability;
--- JSON_TABLE
--- Should fail (JSON_TABLE can be used only in FROM clause)
-SELECT JSON_TABLE('[]', '$');
-ERROR: syntax error at or near "("
-LINE 1: SELECT JSON_TABLE('[]', '$');
- ^
--- Should fail (no columns)
-SELECT * FROM JSON_TABLE(NULL, '$' COLUMNS ());
-ERROR: syntax error at or near ")"
-LINE 1: SELECT * FROM JSON_TABLE(NULL, '$' COLUMNS ());
- ^
-SELECT * FROM JSON_TABLE (NULL::jsonb, '$' COLUMNS (v1 timestamp)) AS f (v1, v2);
-ERROR: JSON_TABLE function has 1 columns available but 2 columns specified
--- NULL => empty table
-SELECT * FROM JSON_TABLE(NULL::jsonb, '$' COLUMNS (foo int)) bar;
- foo
------
-(0 rows)
-
---
-SELECT * FROM JSON_TABLE(jsonb '123', '$'
- COLUMNS (item int PATH '$', foo int)) bar;
- item | foo
-------+-----
- 123 |
-(1 row)
-
--- JSON_TABLE: basic functionality
-CREATE DOMAIN jsonb_test_domain AS text CHECK (value <> 'foo');
-SELECT *
-FROM
- (VALUES
- ('1'),
- ('[]'),
- ('{}'),
- ('[1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""]')
- ) vals(js)
- LEFT OUTER JOIN
--- JSON_TABLE is implicitly lateral
- JSON_TABLE(
- vals.js::jsonb, 'lax $[*]'
- COLUMNS (
- id FOR ORDINALITY,
- id2 FOR ORDINALITY, -- allowed additional ordinality columns
- "int" int PATH '$',
- "text" text PATH '$',
- "char(4)" char(4) PATH '$',
- "bool" bool PATH '$',
- "numeric" numeric PATH '$',
- "domain" jsonb_test_domain PATH '$',
- js json PATH '$',
- jb jsonb PATH '$',
- jst text FORMAT JSON PATH '$',
- jsc char(4) FORMAT JSON PATH '$',
- jsv varchar(4) FORMAT JSON PATH '$',
- jsb jsonb FORMAT JSON PATH '$',
- jsbq jsonb FORMAT JSON PATH '$' OMIT QUOTES,
- aaa int, -- implicit path '$."aaa"',
- aaa1 int PATH '$.aaa',
- exists1 bool EXISTS PATH '$.aaa',
- exists2 int EXISTS PATH '$.aaa',
- exists3 int EXISTS PATH 'strict $.aaa' UNKNOWN ON ERROR,
- exists4 text EXISTS PATH 'strict $.aaa' FALSE ON ERROR,
- js2 json PATH '$',
- jsb2w jsonb PATH '$' WITH WRAPPER,
- jsb2q jsonb PATH '$' OMIT QUOTES,
- ia int[] PATH '$',
- ta text[] PATH '$',
- jba jsonb[] PATH '$'
- )
- ) jt
- ON true;
- js | id | id2 | int | text | char(4) | bool | numeric | domain | js | jb | jst | jsc | jsv | jsb | jsbq | aaa | aaa1 | exists1 | exists2 | exists3 | exists4 | js2 | jsb2w | jsb2q | ia | ta | jba
----------------------------------------------------------------------------------------+----+-----+-----+---------+---------+------+---------+---------+--------------+--------------+--------------+------+------+--------------+--------------+-----+------+---------+---------+---------+---------+--------------+----------------+--------------+----+----+-----
- 1 | 1 | 1 | 1 | 1 | 1 | | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | | | f | 0 | | false | 1 | [1] | 1 | | |
- [] | | | | | | | | | | | | | | | | | | | | | | | | | | |
- {} | 1 | 1 | | | | | | | {} | {} | {} | {} | {} | {} | {} | | | f | 0 | | false | {} | [{}] | {} | | |
- [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 1 | 1 | 1 | 1 | 1 | | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | | | f | 0 | | false | 1 | [1] | 1 | | |
- [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 2 | 2 | 1 | 1.23 | 1.23 | | 1.23 | 1.23 | 1.23 | 1.23 | 1.23 | 1.23 | 1.23 | 1.23 | 1.23 | | | f | 0 | | false | 1.23 | [1.23] | 1.23 | | |
- [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 3 | 3 | 2 | 2 | 2 | | 2 | 2 | "2" | "2" | "2" | "2" | "2" | "2" | 2 | | | f | 0 | | false | "2" | ["2"] | 2 | | |
- [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 4 | 4 | | aaaaaaa | aaaa | | | aaaaaaa | "aaaaaaa" | "aaaaaaa" | "aaaaaaa" | "aaa | "aaa | "aaaaaaa" | | | | f | 0 | | false | "aaaaaaa" | ["aaaaaaa"] | | | |
- [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 5 | 5 | | foo | foo | | | | "foo" | "foo" | "foo" | "foo | "foo | "foo" | | | | f | 0 | | false | "foo" | ["foo"] | | | |
- [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 6 | 6 | | | | | | | null | null | null | null | null | null | null | | | f | 0 | | false | null | [null] | null | | |
- [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 7 | 7 | 0 | false | fals | f | | false | false | false | false | fals | fals | false | false | | | f | 0 | | false | false | [false] | false | | |
- [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 8 | 8 | 1 | true | true | t | | true | true | true | true | true | true | true | true | | | f | 0 | | false | true | [true] | true | | |
- [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 9 | 9 | | | | | | | {"aaa": 123} | {"aaa": 123} | {"aaa": 123} | {"aa | {"aa | {"aaa": 123} | {"aaa": 123} | 123 | 123 | t | 1 | 1 | true | {"aaa": 123} | [{"aaa": 123}] | {"aaa": 123} | | |
- [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 10 | 10 | | [1,2] | [1,2 | | | [1,2] | "[1,2]" | "[1,2]" | "[1,2]" | "[1, | "[1, | "[1,2]" | [1, 2] | | | f | 0 | | false | "[1,2]" | ["[1,2]"] | [1, 2] | | |
- [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 11 | 11 | | "str" | "str | | | "str" | "\"str\"" | "\"str\"" | "\"str\"" | "\"s | "\"s | "\"str\"" | "str" | | | f | 0 | | false | "\"str\"" | ["\"str\""] | "str" | | |
-(14 rows)
-
--- JSON_TABLE: Test backward parsing
-CREATE VIEW jsonb_table_view AS
-SELECT * FROM
- JSON_TABLE(
- jsonb 'null', 'lax $[*]' PASSING 1 + 2 AS a, json '"foo"' AS "b c"
- COLUMNS (
- id FOR ORDINALITY,
- id2 FOR ORDINALITY, -- allowed additional ordinality columns
- "int" int PATH '$',
- "text" text PATH '$',
- "char(4)" char(4) PATH '$',
- "bool" bool PATH '$',
- "numeric" numeric PATH '$',
- "domain" jsonb_test_domain PATH '$',
- js json PATH '$',
- jb jsonb PATH '$',
- jst text FORMAT JSON PATH '$',
- jsc char(4) FORMAT JSON PATH '$',
- jsv varchar(4) FORMAT JSON PATH '$',
- jsb jsonb FORMAT JSON PATH '$',
- jsbq jsonb FORMAT JSON PATH '$' OMIT QUOTES,
- aaa int, -- implicit path '$."aaa"',
- aaa1 int PATH '$.aaa',
- exists1 bool EXISTS PATH '$.aaa',
- exists2 int EXISTS PATH '$.aaa' TRUE ON ERROR,
- exists3 text EXISTS PATH 'strict $.aaa' UNKNOWN ON ERROR,
- js2 json PATH '$',
- jsb2w jsonb PATH '$' WITH WRAPPER,
- jsb2q jsonb PATH '$' OMIT QUOTES,
- ia int[] PATH '$',
- ta text[] PATH '$',
- jba jsonb[] PATH '$',
- NESTED PATH '$[1]' AS p1 COLUMNS (
- a1 int,
- NESTED PATH '$[*]' AS "p1 1" COLUMNS (
- a11 text
- ),
- b1 text
- ),
- NESTED PATH '$[2]' AS p2 COLUMNS (
- NESTED PATH '$[*]' AS "p2:1" COLUMNS (
- a21 text
- ),
- NESTED PATH '$[*]' AS p22 COLUMNS (
- a22 text
- )
- )
- )
- );
-\sv jsonb_table_view
-CREATE OR REPLACE VIEW public.jsonb_table_view AS
- SELECT "json_table".id,
- "json_table".id2,
- "json_table"."int",
- "json_table".text,
- "json_table"."char(4)",
- "json_table".bool,
- "json_table"."numeric",
- "json_table".domain,
- "json_table".js,
- "json_table".jb,
- "json_table".jst,
- "json_table".jsc,
- "json_table".jsv,
- "json_table".jsb,
- "json_table".jsbq,
- "json_table".aaa,
- "json_table".aaa1,
- "json_table".exists1,
- "json_table".exists2,
- "json_table".exists3,
- "json_table".js2,
- "json_table".jsb2w,
- "json_table".jsb2q,
- "json_table".ia,
- "json_table".ta,
- "json_table".jba,
- "json_table".a1,
- "json_table".b1,
- "json_table".a11,
- "json_table".a21,
- "json_table".a22
- FROM JSON_TABLE(
- 'null'::jsonb, '$[*]' AS json_table_path_1
- PASSING
- 1 + 2 AS a,
- '"foo"'::json AS "b c"
- COLUMNS (
- id FOR ORDINALITY,
- id2 FOR ORDINALITY,
- "int" integer PATH '$',
- text text PATH '$',
- "char(4)" character(4) PATH '$',
- bool boolean PATH '$',
- "numeric" numeric PATH '$',
- domain jsonb_test_domain PATH '$',
- js json PATH '$',
- jb jsonb PATH '$',
- jst text FORMAT JSON PATH '$',
- jsc character(4) FORMAT JSON PATH '$',
- jsv character varying(4) FORMAT JSON PATH '$',
- jsb jsonb PATH '$',
- jsbq jsonb PATH '$' OMIT QUOTES,
- aaa integer PATH '$."aaa"',
- aaa1 integer PATH '$."aaa"',
- exists1 boolean EXISTS PATH '$."aaa"',
- exists2 integer EXISTS PATH '$."aaa"' TRUE ON ERROR,
- exists3 text EXISTS PATH 'strict $."aaa"' UNKNOWN ON ERROR,
- js2 json PATH '$',
- jsb2w jsonb PATH '$' WITH UNCONDITIONAL WRAPPER,
- jsb2q jsonb PATH '$' OMIT QUOTES,
- ia integer[] PATH '$',
- ta text[] PATH '$',
- jba jsonb[] PATH '$',
- NESTED PATH '$[1]' AS p1
- COLUMNS (
- a1 integer PATH '$."a1"',
- b1 text PATH '$."b1"',
- NESTED PATH '$[*]' AS "p1 1"
- COLUMNS (
- a11 text PATH '$."a11"'
- )
- ),
- NESTED PATH '$[2]' AS p2
- COLUMNS (
- NESTED PATH '$[*]' AS "p2:1"
- COLUMNS (
- a21 text PATH '$."a21"'
- ),
- NESTED PATH '$[*]' AS p22
- COLUMNS (
- a22 text PATH '$."a22"'
- )
- )
- )
- PLAN (json_table_path_1 OUTER ((p1 OUTER "p1 1") UNION (p2 OUTER ("p2:1" UNION p22))))
- )
-EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM jsonb_table_view;
- QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
- Table Function Scan on "json_table"
- Output: "json_table".id, "json_table".id2, "json_table"."int", "json_table".text, "json_table"."char(4)", "json_table".bool, "json_table"."numeric", "json_table".domain, "json_table".js, "json_table".jb, "json_table".jst, "json_table".jsc, "json_table".jsv, "json_table".jsb, "json_table".jsbq, "json_table".aaa, "json_table".aaa1, "json_table".exists1, "json_table".exists2, "json_table".exists3, "json_table".js2, "json_table".jsb2w, "json_table".jsb2q, "json_table".ia, "json_table".ta, "json_table".jba, "json_table".a1, "json_table".b1, "json_table".a11, "json_table".a21, "json_table".a22
- Table Function Call: JSON_TABLE('null'::jsonb, '$[*]' AS json_table_path_1 PASSING 3 AS a, '"foo"'::jsonb AS "b c" COLUMNS (id FOR ORDINALITY, id2 FOR ORDINALITY, "int" integer PATH '$', text text PATH '$', "char(4)" character(4) PATH '$', bool boolean PATH '$', "numeric" numeric PATH '$', domain jsonb_test_domain PATH '$', js json PATH '$', jb jsonb PATH '$', jst text FORMAT JSON PATH '$', jsc character(4) FORMAT JSON PATH '$', jsv character varying(4) FORMAT JSON PATH '$', jsb jsonb PATH '$', jsbq jsonb PATH '$' OMIT QUOTES, aaa integer PATH '$."aaa"', aaa1 integer PATH '$."aaa"', exists1 boolean EXISTS PATH '$."aaa"', exists2 integer EXISTS PATH '$."aaa"' TRUE ON ERROR, exists3 text EXISTS PATH 'strict $."aaa"' UNKNOWN ON ERROR, js2 json PATH '$', jsb2w jsonb PATH '$' WITH UNCONDITIONAL WRAPPER, jsb2q jsonb PATH '$' OMIT QUOTES, ia integer[] PATH '$', ta text[] PATH '$', jba jsonb[] PATH '$', NESTED PATH '$[1]' AS p1 COLUMNS (a1 integer PATH '$."a1"', b1 text PATH '$."b1"', NESTED PATH '$[*]' AS "p1 1" COLUMNS (a11 text PATH '$."a11"')), NESTED PATH '$[2]' AS p2 COLUMNS ( NESTED PATH '$[*]' AS "p2:1" COLUMNS (a21 text PATH '$."a21"'), NESTED PATH '$[*]' AS p22 COLUMNS (a22 text PATH '$."a22"'))) PLAN (json_table_path_1 OUTER ((p1 OUTER "p1 1") UNION (p2 OUTER ("p2:1" UNION p22)))))
-(3 rows)
-
-DROP VIEW jsonb_table_view;
-DROP DOMAIN jsonb_test_domain;
--- JSON_TABLE: ON EMPTY/ON ERROR behavior
-SELECT *
-FROM
- (VALUES ('1'), ('"err"')) vals(js),
- JSON_TABLE(vals.js::jsonb, '$' COLUMNS (a int PATH '$')) jt;
- js | a
--------+---
- 1 | 1
- "err" |
-(2 rows)
-
-SELECT *
-FROM
- (VALUES ('1'), ('"err"')) vals(js)
- LEFT OUTER JOIN
- JSON_TABLE(vals.js::jsonb, '$' COLUMNS (a int PATH '$') ERROR ON ERROR) jt
- ON true;
-ERROR: invalid input syntax for type integer: "err"
-SELECT *
-FROM
- (VALUES ('1'), ('"err"')) vals(js)
- LEFT OUTER JOIN
- JSON_TABLE(vals.js::jsonb, '$' COLUMNS (a int PATH '$' ERROR ON ERROR)) jt
- ON true;
-ERROR: invalid input syntax for type integer: "err"
-SELECT * FROM JSON_TABLE(jsonb '1', '$' COLUMNS (a int PATH '$.a' ERROR ON EMPTY)) jt;
- a
----
-
-(1 row)
-
-SELECT * FROM JSON_TABLE(jsonb '1', '$' COLUMNS (a int PATH 'strict $.a' ERROR ON EMPTY) ERROR ON ERROR) jt;
-ERROR: jsonpath member accessor can only be applied to an object
-SELECT * FROM JSON_TABLE(jsonb '1', '$' COLUMNS (a int PATH 'lax $.a' ERROR ON EMPTY) ERROR ON ERROR) jt;
-ERROR: no SQL/JSON item
-SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int PATH '$' DEFAULT 1 ON EMPTY DEFAULT 2 ON ERROR)) jt;
- a
----
- 2
-(1 row)
-
-SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int PATH 'strict $.a' DEFAULT 1 ON EMPTY DEFAULT 2 ON ERROR)) jt;
- a
----
- 2
-(1 row)
-
-SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int PATH 'lax $.a' DEFAULT 1 ON EMPTY DEFAULT 2 ON ERROR)) jt;
- a
----
- 1
-(1 row)
-
--- JSON_TABLE: EXISTS PATH types
-SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int4 EXISTS PATH '$.a'));
- a
----
- 0
-(1 row)
-
-SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int2 EXISTS PATH '$.a'));
-ERROR: cannot cast type boolean to smallint
-LINE 1: ...ELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int2 EXI...
- ^
-SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int8 EXISTS PATH '$.a'));
-ERROR: cannot cast type boolean to bigint
-LINE 1: ...ELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int8 EXI...
- ^
-SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a float4 EXISTS PATH '$.a'));
-ERROR: cannot cast type boolean to real
-LINE 1: ...ELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a float4 E...
- ^
-SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a char(3) EXISTS PATH '$.a'));
- a
------
- fal
-(1 row)
-
-SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a json EXISTS PATH '$.a'));
-ERROR: cannot cast type boolean to json
-LINE 1: ...ELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a json EXI...
- ^
-SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a jsonb EXISTS PATH '$.a'));
-ERROR: cannot cast type boolean to jsonb
-LINE 1: ...ELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a jsonb EX...
- ^
--- JSON_TABLE: nested paths and plans
--- Should fail (JSON_TABLE columns must contain explicit AS path
--- specifications if explicit PLAN clause is used)
-SELECT * FROM JSON_TABLE(
- jsonb '[]', '$' -- AS <path name> required here
- COLUMNS (
- foo int PATH '$'
- )
- PLAN DEFAULT (UNION)
-) jt;
-ERROR: invalid JSON_TABLE expression
-LINE 2: jsonb '[]', '$' -- AS <path name> required here
- ^
-DETAIL: JSON_TABLE columns must contain explicit AS pathname specification if explicit PLAN clause is used
-SELECT * FROM JSON_TABLE(
- jsonb '[]', '$' AS path1
- COLUMNS (
- NESTED PATH '$' COLUMNS ( -- AS <path name> required here
- foo int PATH '$'
- )
- )
- PLAN DEFAULT (UNION)
-) jt;
-ERROR: invalid JSON_TABLE expression
-LINE 4: NESTED PATH '$' COLUMNS ( -- AS <path name> required here
- ^
-DETAIL: JSON_TABLE columns must contain explicit AS pathname specification if explicit PLAN clause is used
--- Should fail (column names must be distinct)
-SELECT * FROM JSON_TABLE(
- jsonb '[]', '$' AS a
- COLUMNS (
- a int
- )
-) jt;
-ERROR: duplicate JSON_TABLE column name: a
-HINT: JSON_TABLE column names must be distinct from one another.
-SELECT * FROM JSON_TABLE(
- jsonb '[]', '$' AS a
- COLUMNS (
- b int,
- NESTED PATH '$' AS a
- COLUMNS (
- c int
- )
- )
-) jt;
-ERROR: duplicate JSON_TABLE column name: a
-HINT: JSON_TABLE column names must be distinct from one another.
-SELECT * FROM JSON_TABLE(
- jsonb '[]', '$'
- COLUMNS (
- b int,
- NESTED PATH '$' AS b
- COLUMNS (
- c int
- )
- )
-) jt;
-ERROR: duplicate JSON_TABLE column name: b
-HINT: JSON_TABLE column names must be distinct from one another.
-SELECT * FROM JSON_TABLE(
- jsonb '[]', '$'
- COLUMNS (
- NESTED PATH '$' AS a
- COLUMNS (
- b int
- ),
- NESTED PATH '$'
- COLUMNS (
- NESTED PATH '$' AS a
- COLUMNS (
- c int
- )
- )
- )
-) jt;
-ERROR: duplicate JSON_TABLE column name: a
-HINT: JSON_TABLE column names must be distinct from one another.
--- JSON_TABLE: plan validation
-SELECT * FROM JSON_TABLE(
- jsonb 'null', '$[*]' AS p0
- COLUMNS (
- NESTED PATH '$' AS p1 COLUMNS (
- NESTED PATH '$' AS p11 COLUMNS ( foo int ),
- NESTED PATH '$' AS p12 COLUMNS ( bar int )
- ),
- NESTED PATH '$' AS p2 COLUMNS (
- NESTED PATH '$' AS p21 COLUMNS ( baz int )
- )
- )
- PLAN (p1)
-) jt;
-ERROR: invalid JSON_TABLE plan
-LINE 12: PLAN (p1)
- ^
-DETAIL: Path name mismatch: expected p0 but p1 is given.
-SELECT * FROM JSON_TABLE(
- jsonb 'null', '$[*]' AS p0
- COLUMNS (
- NESTED PATH '$' AS p1 COLUMNS (
- NESTED PATH '$' AS p11 COLUMNS ( foo int ),
- NESTED PATH '$' AS p12 COLUMNS ( bar int )
- ),
- NESTED PATH '$' AS p2 COLUMNS (
- NESTED PATH '$' AS p21 COLUMNS ( baz int )
- )
- )
- PLAN (p0)
-) jt;
-ERROR: invalid JSON_TABLE plan
-LINE 4: NESTED PATH '$' AS p1 COLUMNS (
- ^
-DETAIL: Plan node for nested path p1 was not found in plan.
-SELECT * FROM JSON_TABLE(
- jsonb 'null', '$[*]' AS p0
- COLUMNS (
- NESTED PATH '$' AS p1 COLUMNS (
- NESTED PATH '$' AS p11 COLUMNS ( foo int ),
- NESTED PATH '$' AS p12 COLUMNS ( bar int )
- ),
- NESTED PATH '$' AS p2 COLUMNS (
- NESTED PATH '$' AS p21 COLUMNS ( baz int )
- )
- )
- PLAN (p0 OUTER p3)
-) jt;
-ERROR: invalid JSON_TABLE plan
-LINE 4: NESTED PATH '$' AS p1 COLUMNS (
- ^
-DETAIL: Plan node for nested path p1 was not found in plan.
-SELECT * FROM JSON_TABLE(
- jsonb 'null', '$[*]' AS p0
- COLUMNS (
- NESTED PATH '$' AS p1 COLUMNS (
- NESTED PATH '$' AS p11 COLUMNS ( foo int ),
- NESTED PATH '$' AS p12 COLUMNS ( bar int )
- ),
- NESTED PATH '$' AS p2 COLUMNS (
- NESTED PATH '$' AS p21 COLUMNS ( baz int )
- )
- )
- PLAN (p0 UNION p1 UNION p11)
-) jt;
-ERROR: invalid JSON_TABLE plan
-LINE 12: PLAN (p0 UNION p1 UNION p11)
- ^
-DETAIL: Expected INNER or OUTER JSON_TABLE plan node.
-SELECT * FROM JSON_TABLE(
- jsonb 'null', '$[*]' AS p0
- COLUMNS (
- NESTED PATH '$' AS p1 COLUMNS (
- NESTED PATH '$' AS p11 COLUMNS ( foo int ),
- NESTED PATH '$' AS p12 COLUMNS ( bar int )
- ),
- NESTED PATH '$' AS p2 COLUMNS (
- NESTED PATH '$' AS p21 COLUMNS ( baz int )
- )
- )
- PLAN (p0 OUTER (p1 CROSS p13))
-) jt;
-ERROR: invalid JSON_TABLE plan
-LINE 8: NESTED PATH '$' AS p2 COLUMNS (
- ^
-DETAIL: Plan node for nested path p2 was not found in plan.
-SELECT * FROM JSON_TABLE(
- jsonb 'null', '$[*]' AS p0
- COLUMNS (
- NESTED PATH '$' AS p1 COLUMNS (
- NESTED PATH '$' AS p11 COLUMNS ( foo int ),
- NESTED PATH '$' AS p12 COLUMNS ( bar int )
- ),
- NESTED PATH '$' AS p2 COLUMNS (
- NESTED PATH '$' AS p21 COLUMNS ( baz int )
- )
- )
- PLAN (p0 OUTER (p1 CROSS p2))
-) jt;
-ERROR: invalid JSON_TABLE plan
-LINE 5: NESTED PATH '$' AS p11 COLUMNS ( foo int ),
- ^
-DETAIL: Plan node for nested path p11 was not found in plan.
-SELECT * FROM JSON_TABLE(
- jsonb 'null', '$[*]' AS p0
- COLUMNS (
- NESTED PATH '$' AS p1 COLUMNS (
- NESTED PATH '$' AS p11 COLUMNS ( foo int ),
- NESTED PATH '$' AS p12 COLUMNS ( bar int )
- ),
- NESTED PATH '$' AS p2 COLUMNS (
- NESTED PATH '$' AS p21 COLUMNS ( baz int )
- )
- )
- PLAN (p0 OUTER ((p1 UNION p11) CROSS p2))
-) jt;
-ERROR: invalid JSON_TABLE plan
-LINE 12: PLAN (p0 OUTER ((p1 UNION p11) CROSS p2))
- ^
-DETAIL: Plan node contains some extra or duplicate sibling nodes.
-SELECT * FROM JSON_TABLE(
- jsonb 'null', '$[*]' AS p0
- COLUMNS (
- NESTED PATH '$' AS p1 COLUMNS (
- NESTED PATH '$' AS p11 COLUMNS ( foo int ),
- NESTED PATH '$' AS p12 COLUMNS ( bar int )
- ),
- NESTED PATH '$' AS p2 COLUMNS (
- NESTED PATH '$' AS p21 COLUMNS ( baz int )
- )
- )
- PLAN (p0 OUTER ((p1 INNER p11) CROSS p2))
-) jt;
-ERROR: invalid JSON_TABLE plan
-LINE 6: NESTED PATH '$' AS p12 COLUMNS ( bar int )
- ^
-DETAIL: Plan node for nested path p12 was not found in plan.
-SELECT * FROM JSON_TABLE(
- jsonb 'null', '$[*]' AS p0
- COLUMNS (
- NESTED PATH '$' AS p1 COLUMNS (
- NESTED PATH '$' AS p11 COLUMNS ( foo int ),
- NESTED PATH '$' AS p12 COLUMNS ( bar int )
- ),
- NESTED PATH '$' AS p2 COLUMNS (
- NESTED PATH '$' AS p21 COLUMNS ( baz int )
- )
- )
- PLAN (p0 OUTER ((p1 INNER (p12 CROSS p11)) CROSS p2))
-) jt;
-ERROR: invalid JSON_TABLE plan
-LINE 9: NESTED PATH '$' AS p21 COLUMNS ( baz int )
- ^
-DETAIL: Plan node for nested path p21 was not found in plan.
-SELECT * FROM JSON_TABLE(
- jsonb 'null', 'strict $[*]' AS p0
- COLUMNS (
- NESTED PATH '$' AS p1 COLUMNS (
- NESTED PATH '$' AS p11 COLUMNS ( foo int ),
- NESTED PATH '$' AS p12 COLUMNS ( bar int )
- ),
- NESTED PATH '$' AS p2 COLUMNS (
- NESTED PATH '$' AS p21 COLUMNS ( baz int )
- )
- )
- PLAN (p0 OUTER ((p1 INNER (p12 CROSS p11)) CROSS (p2 INNER p21)))
-) jt;
- bar | foo | baz
------+-----+-----
-(0 rows)
-
-SELECT * FROM JSON_TABLE(
- jsonb 'null', 'strict $[*]' -- without root path name
- COLUMNS (
- NESTED PATH '$' AS p1 COLUMNS (
- NESTED PATH '$' AS p11 COLUMNS ( foo int ),
- NESTED PATH '$' AS p12 COLUMNS ( bar int )
- ),
- NESTED PATH '$' AS p2 COLUMNS (
- NESTED PATH '$' AS p21 COLUMNS ( baz int )
- )
- )
- PLAN ((p1 INNER (p12 CROSS p11)) CROSS (p2 INNER p21))
-) jt;
-ERROR: invalid JSON_TABLE expression
-LINE 2: jsonb 'null', 'strict $[*]' -- without root path name
- ^
-DETAIL: JSON_TABLE columns must contain explicit AS pathname specification if explicit PLAN clause is used
--- JSON_TABLE: plan execution
-CREATE TEMP TABLE jsonb_table_test (js jsonb);
-INSERT INTO jsonb_table_test
-VALUES (
- '[
- {"a": 1, "b": [], "c": []},
- {"a": 2, "b": [1, 2, 3], "c": [10, null, 20]},
- {"a": 3, "b": [1, 2], "c": []},
- {"x": "4", "b": [1, 2], "c": 123}
- ]'
-);
--- unspecified plan (outer, union)
-select
- jt.*
-from
- jsonb_table_test jtt,
- json_table (
- jtt.js,'strict $[*]' as p
- columns (
- n for ordinality,
- a int path 'lax $.a' default -1 on empty,
- nested path 'strict $.b[*]' as pb columns ( b int path '$' ),
- nested path 'strict $.c[*]' as pc columns ( c int path '$' )
- )
- ) jt;
- n | a | b | c
----+----+---+----
- 1 | 1 | |
- 2 | 2 | 1 |
- 2 | 2 | 2 |
- 2 | 2 | 3 |
- 2 | 2 | | 10
- 2 | 2 | |
- 2 | 2 | | 20
- 3 | 3 | 1 |
- 3 | 3 | 2 |
- 4 | -1 | 1 |
- 4 | -1 | 2 |
-(11 rows)
-
--- default plan (outer, union)
-select
- jt.*
-from
- jsonb_table_test jtt,
- json_table (
- jtt.js,'strict $[*]' as p
- columns (
- n for ordinality,
- a int path 'lax $.a' default -1 on empty,
- nested path 'strict $.b[*]' as pb columns ( b int path '$' ),
- nested path 'strict $.c[*]' as pc columns ( c int path '$' )
- )
- plan default (outer, union)
- ) jt;
- n | a | b | c
----+----+---+----
- 1 | 1 | |
- 2 | 2 | 1 |
- 2 | 2 | 2 |
- 2 | 2 | 3 |
- 2 | 2 | | 10
- 2 | 2 | |
- 2 | 2 | | 20
- 3 | 3 | 1 |
- 3 | 3 | 2 |
- 4 | -1 | 1 |
- 4 | -1 | 2 |
-(11 rows)
-
--- specific plan (p outer (pb union pc))
-select
- jt.*
-from
- jsonb_table_test jtt,
- json_table (
- jtt.js,'strict $[*]' as p
- columns (
- n for ordinality,
- a int path 'lax $.a' default -1 on empty,
- nested path 'strict $.b[*]' as pb columns ( b int path '$' ),
- nested path 'strict $.c[*]' as pc columns ( c int path '$' )
- )
- plan (p outer (pb union pc))
- ) jt;
- n | a | b | c
----+----+---+----
- 1 | 1 | |
- 2 | 2 | 1 |
- 2 | 2 | 2 |
- 2 | 2 | 3 |
- 2 | 2 | | 10
- 2 | 2 | |
- 2 | 2 | | 20
- 3 | 3 | 1 |
- 3 | 3 | 2 |
- 4 | -1 | 1 |
- 4 | -1 | 2 |
-(11 rows)
-
--- specific plan (p outer (pc union pb))
-select
- jt.*
-from
- jsonb_table_test jtt,
- json_table (
- jtt.js,'strict $[*]' as p
- columns (
- n for ordinality,
- a int path 'lax $.a' default -1 on empty,
- nested path 'strict $.b[*]' as pb columns ( b int path '$' ),
- nested path 'strict $.c[*]' as pc columns ( c int path '$' )
- )
- plan (p outer (pc union pb))
- ) jt;
- n | a | c | b
----+----+----+---
- 1 | 1 | |
- 2 | 2 | 10 |
- 2 | 2 | |
- 2 | 2 | 20 |
- 2 | 2 | | 1
- 2 | 2 | | 2
- 2 | 2 | | 3
- 3 | 3 | | 1
- 3 | 3 | | 2
- 4 | -1 | | 1
- 4 | -1 | | 2
-(11 rows)
-
--- default plan (inner, union)
-select
- jt.*
-from
- jsonb_table_test jtt,
- json_table (
- jtt.js,'strict $[*]' as p
- columns (
- n for ordinality,
- a int path 'lax $.a' default -1 on empty,
- nested path 'strict $.b[*]' as pb columns ( b int path '$' ),
- nested path 'strict $.c[*]' as pc columns ( c int path '$' )
- )
- plan default (inner)
- ) jt;
- n | a | b | c
----+----+---+----
- 2 | 2 | 1 |
- 2 | 2 | 2 |
- 2 | 2 | 3 |
- 2 | 2 | | 10
- 2 | 2 | |
- 2 | 2 | | 20
- 3 | 3 | 1 |
- 3 | 3 | 2 |
- 4 | -1 | 1 |
- 4 | -1 | 2 |
-(10 rows)
-
--- specific plan (p inner (pb union pc))
-select
- jt.*
-from
- jsonb_table_test jtt,
- json_table (
- jtt.js,'strict $[*]' as p
- columns (
- n for ordinality,
- a int path 'lax $.a' default -1 on empty,
- nested path 'strict $.b[*]' as pb columns ( b int path '$' ),
- nested path 'strict $.c[*]' as pc columns ( c int path '$' )
- )
- plan (p inner (pb union pc))
- ) jt;
- n | a | b | c
----+----+---+----
- 2 | 2 | 1 |
- 2 | 2 | 2 |
- 2 | 2 | 3 |
- 2 | 2 | | 10
- 2 | 2 | |
- 2 | 2 | | 20
- 3 | 3 | 1 |
- 3 | 3 | 2 |
- 4 | -1 | 1 |
- 4 | -1 | 2 |
-(10 rows)
-
--- default plan (inner, cross)
-select
- jt.*
-from
- jsonb_table_test jtt,
- json_table (
- jtt.js,'strict $[*]' as p
- columns (
- n for ordinality,
- a int path 'lax $.a' default -1 on empty,
- nested path 'strict $.b[*]' as pb columns ( b int path '$' ),
- nested path 'strict $.c[*]' as pc columns ( c int path '$' )
- )
- plan default (cross, inner)
- ) jt;
- n | a | b | c
----+---+---+----
- 2 | 2 | 1 | 10
- 2 | 2 | 1 |
- 2 | 2 | 1 | 20
- 2 | 2 | 2 | 10
- 2 | 2 | 2 |
- 2 | 2 | 2 | 20
- 2 | 2 | 3 | 10
- 2 | 2 | 3 |
- 2 | 2 | 3 | 20
-(9 rows)
-
--- specific plan (p inner (pb cross pc))
-select
- jt.*
-from
- jsonb_table_test jtt,
- json_table (
- jtt.js,'strict $[*]' as p
- columns (
- n for ordinality,
- a int path 'lax $.a' default -1 on empty,
- nested path 'strict $.b[*]' as pb columns ( b int path '$' ),
- nested path 'strict $.c[*]' as pc columns ( c int path '$' )
- )
- plan (p inner (pb cross pc))
- ) jt;
- n | a | b | c
----+---+---+----
- 2 | 2 | 1 | 10
- 2 | 2 | 1 |
- 2 | 2 | 1 | 20
- 2 | 2 | 2 | 10
- 2 | 2 | 2 |
- 2 | 2 | 2 | 20
- 2 | 2 | 3 | 10
- 2 | 2 | 3 |
- 2 | 2 | 3 | 20
-(9 rows)
-
--- default plan (outer, cross)
-select
- jt.*
-from
- jsonb_table_test jtt,
- json_table (
- jtt.js,'strict $[*]' as p
- columns (
- n for ordinality,
- a int path 'lax $.a' default -1 on empty,
- nested path 'strict $.b[*]' as pb columns ( b int path '$' ),
- nested path 'strict $.c[*]' as pc columns ( c int path '$' )
- )
- plan default (outer, cross)
- ) jt;
- n | a | b | c
----+----+---+----
- 1 | 1 | |
- 2 | 2 | 1 | 10
- 2 | 2 | 1 |
- 2 | 2 | 1 | 20
- 2 | 2 | 2 | 10
- 2 | 2 | 2 |
- 2 | 2 | 2 | 20
- 2 | 2 | 3 | 10
- 2 | 2 | 3 |
- 2 | 2 | 3 | 20
- 3 | 3 | |
- 4 | -1 | |
-(12 rows)
-
--- specific plan (p outer (pb cross pc))
-select
- jt.*
-from
- jsonb_table_test jtt,
- json_table (
- jtt.js,'strict $[*]' as p
- columns (
- n for ordinality,
- a int path 'lax $.a' default -1 on empty,
- nested path 'strict $.b[*]' as pb columns ( b int path '$' ),
- nested path 'strict $.c[*]' as pc columns ( c int path '$' )
- )
- plan (p outer (pb cross pc))
- ) jt;
- n | a | b | c
----+----+---+----
- 1 | 1 | |
- 2 | 2 | 1 | 10
- 2 | 2 | 1 |
- 2 | 2 | 1 | 20
- 2 | 2 | 2 | 10
- 2 | 2 | 2 |
- 2 | 2 | 2 | 20
- 2 | 2 | 3 | 10
- 2 | 2 | 3 |
- 2 | 2 | 3 | 20
- 3 | 3 | |
- 4 | -1 | |
-(12 rows)
-
-select
- jt.*, b1 + 100 as b
-from
- json_table (jsonb
- '[
- {"a": 1, "b": [[1, 10], [2], [3, 30, 300]], "c": [1, null, 2]},
- {"a": 2, "b": [10, 20], "c": [1, null, 2]},
- {"x": "3", "b": [11, 22, 33, 44]}
- ]',
- '$[*]' as p
- columns (
- n for ordinality,
- a int path 'lax $.a' default -1 on error,
- nested path 'strict $.b[*]' as pb columns (
- b text format json path '$',
- nested path 'strict $[*]' as pb1 columns (
- b1 int path '$'
- )
- ),
- nested path 'strict $.c[*]' as pc columns (
- c text format json path '$',
- nested path 'strict $[*]' as pc1 columns (
- c1 int path '$'
- )
- )
- )
- --plan default(outer, cross)
- plan(p outer ((pb inner pb1) cross (pc outer pc1)))
- ) jt;
- n | a | b | b1 | c | c1 | b
----+---+--------------+-----+------+----+-----
- 1 | 1 | [1, 10] | 1 | 1 | | 101
- 1 | 1 | [1, 10] | 1 | null | | 101
- 1 | 1 | [1, 10] | 1 | 2 | | 101
- 1 | 1 | [1, 10] | 10 | 1 | | 110
- 1 | 1 | [1, 10] | 10 | null | | 110
- 1 | 1 | [1, 10] | 10 | 2 | | 110
- 1 | 1 | [2] | 2 | 1 | | 102
- 1 | 1 | [2] | 2 | null | | 102
- 1 | 1 | [2] | 2 | 2 | | 102
- 1 | 1 | [3, 30, 300] | 3 | 1 | | 103
- 1 | 1 | [3, 30, 300] | 3 | null | | 103
- 1 | 1 | [3, 30, 300] | 3 | 2 | | 103
- 1 | 1 | [3, 30, 300] | 30 | 1 | | 130
- 1 | 1 | [3, 30, 300] | 30 | null | | 130
- 1 | 1 | [3, 30, 300] | 30 | 2 | | 130
- 1 | 1 | [3, 30, 300] | 300 | 1 | | 400
- 1 | 1 | [3, 30, 300] | 300 | null | | 400
- 1 | 1 | [3, 30, 300] | 300 | 2 | | 400
- 2 | 2 | | | | |
- 3 | | | | | |
-(20 rows)
-
--- Should succeed (JSON arguments are passed to root and nested paths)
-SELECT *
-FROM
- generate_series(1, 4) x,
- generate_series(1, 3) y,
- JSON_TABLE(jsonb
- '[[1,2,3],[2,3,4,5],[3,4,5,6]]',
- 'strict $[*] ? (@[*] < $x)'
- PASSING x AS x, y AS y
- COLUMNS (
- y text FORMAT JSON PATH '$',
- NESTED PATH 'strict $[*] ? (@ >= $y)'
- COLUMNS (
- z int PATH '$'
- )
- )
- ) jt;
- x | y | y | z
----+---+--------------+---
- 2 | 1 | [1, 2, 3] | 1
- 2 | 1 | [1, 2, 3] | 2
- 2 | 1 | [1, 2, 3] | 3
- 3 | 1 | [1, 2, 3] | 1
- 3 | 1 | [1, 2, 3] | 2
- 3 | 1 | [1, 2, 3] | 3
- 3 | 1 | [2, 3, 4, 5] | 2
- 3 | 1 | [2, 3, 4, 5] | 3
- 3 | 1 | [2, 3, 4, 5] | 4
- 3 | 1 | [2, 3, 4, 5] | 5
- 4 | 1 | [1, 2, 3] | 1
- 4 | 1 | [1, 2, 3] | 2
- 4 | 1 | [1, 2, 3] | 3
- 4 | 1 | [2, 3, 4, 5] | 2
- 4 | 1 | [2, 3, 4, 5] | 3
- 4 | 1 | [2, 3, 4, 5] | 4
- 4 | 1 | [2, 3, 4, 5] | 5
- 4 | 1 | [3, 4, 5, 6] | 3
- 4 | 1 | [3, 4, 5, 6] | 4
- 4 | 1 | [3, 4, 5, 6] | 5
- 4 | 1 | [3, 4, 5, 6] | 6
- 2 | 2 | [1, 2, 3] | 2
- 2 | 2 | [1, 2, 3] | 3
- 3 | 2 | [1, 2, 3] | 2
- 3 | 2 | [1, 2, 3] | 3
- 3 | 2 | [2, 3, 4, 5] | 2
- 3 | 2 | [2, 3, 4, 5] | 3
- 3 | 2 | [2, 3, 4, 5] | 4
- 3 | 2 | [2, 3, 4, 5] | 5
- 4 | 2 | [1, 2, 3] | 2
- 4 | 2 | [1, 2, 3] | 3
- 4 | 2 | [2, 3, 4, 5] | 2
- 4 | 2 | [2, 3, 4, 5] | 3
- 4 | 2 | [2, 3, 4, 5] | 4
- 4 | 2 | [2, 3, 4, 5] | 5
- 4 | 2 | [3, 4, 5, 6] | 3
- 4 | 2 | [3, 4, 5, 6] | 4
- 4 | 2 | [3, 4, 5, 6] | 5
- 4 | 2 | [3, 4, 5, 6] | 6
- 2 | 3 | [1, 2, 3] | 3
- 3 | 3 | [1, 2, 3] | 3
- 3 | 3 | [2, 3, 4, 5] | 3
- 3 | 3 | [2, 3, 4, 5] | 4
- 3 | 3 | [2, 3, 4, 5] | 5
- 4 | 3 | [1, 2, 3] | 3
- 4 | 3 | [2, 3, 4, 5] | 3
- 4 | 3 | [2, 3, 4, 5] | 4
- 4 | 3 | [2, 3, 4, 5] | 5
- 4 | 3 | [3, 4, 5, 6] | 3
- 4 | 3 | [3, 4, 5, 6] | 4
- 4 | 3 | [3, 4, 5, 6] | 5
- 4 | 3 | [3, 4, 5, 6] | 6
-(52 rows)
-
--- Should fail (JSON arguments are not passed to column paths)
-SELECT *
-FROM JSON_TABLE(
- jsonb '[1,2,3]',
- '$[*] ? (@ < $x)'
- PASSING 10 AS x
- COLUMNS (y text FORMAT JSON PATH '$ ? (@ < $x)')
- ) jt;
-ERROR: could not find jsonpath variable "x"
--- Extension: non-constant JSON path
-SELECT JSON_EXISTS(jsonb '{"a": 123}', '$' || '.' || 'a');
- json_exists
--------------
- t
-(1 row)
-
-SELECT JSON_VALUE(jsonb '{"a": 123}', '$' || '.' || 'a');
- json_value
-------------
- 123
-(1 row)
-
-SELECT JSON_VALUE(jsonb '{"a": 123}', '$' || '.' || 'b' DEFAULT 'foo' ON EMPTY);
- json_value
-------------
- foo
-(1 row)
-
-SELECT JSON_QUERY(jsonb '{"a": 123}', '$' || '.' || 'a');
- json_query
-------------
- 123
-(1 row)
-
-SELECT JSON_QUERY(jsonb '{"a": 123}', '$' || '.' || 'a' WITH WRAPPER);
- json_query
-------------
- [123]
-(1 row)
-
--- Should fail (invalid path)
-SELECT JSON_QUERY(jsonb '{"a": 123}', 'error' || ' ' || 'error');
-ERROR: syntax error at or near " " of jsonpath input
--- Should fail (not supported)
-SELECT * FROM JSON_TABLE(jsonb '{"a": 123}', '$' || '.' || 'a' COLUMNS (foo int));
-ERROR: only string constants supported in JSON_TABLE path specification
-LINE 1: SELECT * FROM JSON_TABLE(jsonb '{"a": 123}', '$' || '.' || '...
- ^
--- Test parallel JSON_VALUE()
-CREATE UNLOGGED TABLE test_parallel_jsonb_value AS
-SELECT i::text::jsonb AS js
-FROM generate_series(1, 50000) i;
--- encourage use of parallel plans
-set parallel_setup_cost=0;
-set parallel_tuple_cost=0;
-set min_parallel_table_scan_size=0;
-set max_parallel_workers_per_gather=4;
-set parallel_leader_participation = off;
--- Should be non-parallel due to subtransactions
-EXPLAIN (COSTS OFF)
-SELECT sum(JSON_VALUE(js, '$' RETURNING numeric)) FROM test_parallel_jsonb_value;
- QUERY PLAN
----------------------------------------------
- Aggregate
- -> Seq Scan on test_parallel_jsonb_value
-(2 rows)
-
-SELECT sum(JSON_VALUE(js, '$' RETURNING numeric)) FROM test_parallel_jsonb_value;
- sum
-------------
- 1250025000
-(1 row)
-
--- Should be parallel
-EXPLAIN (COSTS OFF)
-SELECT sum(JSON_VALUE(js, '$' RETURNING numeric ERROR ON ERROR)) FROM test_parallel_jsonb_value;
- QUERY PLAN
-------------------------------------------------------------------
- Finalize Aggregate
- -> Gather
- Workers Planned: 4
- -> Partial Aggregate
- -> Parallel Seq Scan on test_parallel_jsonb_value
-(5 rows)
-
-SELECT sum(JSON_VALUE(js, '$' RETURNING numeric ERROR ON ERROR)) FROM test_parallel_jsonb_value;
- sum
-------------
- 1250025000
-(1 row)
-
-DROP TABLE test_parallel_jsonb_value;
diff --git a/src/test/regress/expected/opr_sanity.out b/src/test/regress/expected/opr_sanity.out
index 86d755aa443..330eb0f7656 100644
--- a/src/test/regress/expected/opr_sanity.out
+++ b/src/test/regress/expected/opr_sanity.out
@@ -1474,10 +1474,8 @@ WHERE a.aggfnoid = p.oid AND
NOT binary_coercible(p.proargtypes[1], ptr.proargtypes[2]))
OR (p.pronargs > 2 AND
NOT binary_coercible(p.proargtypes[2], ptr.proargtypes[3]))
- OR (p.pronargs > 3 AND
- NOT binary_coercible(p.proargtypes[3], ptr.proargtypes[4]))
- -- we could carry the check further, but 4 args is enough for now
- OR (p.pronargs > 4)
+ -- we could carry the check further, but 3 args is enough for now
+ OR (p.pronargs > 3)
);
aggfnoid | proname | oid | proname
----------+---------+-----+---------
diff --git a/src/test/regress/expected/sqljson.out b/src/test/regress/expected/sqljson.out
deleted file mode 100644
index bdd0969a509..00000000000
--- a/src/test/regress/expected/sqljson.out
+++ /dev/null
@@ -1,1320 +0,0 @@
--- JSON()
-SELECT JSON();
-ERROR: syntax error at or near ")"
-LINE 1: SELECT JSON();
- ^
-SELECT JSON(NULL);
- json
-------
-
-(1 row)
-
-SELECT JSON('{ "a" : 1 } ');
- json
---------------
- { "a" : 1 }
-(1 row)
-
-SELECT JSON('{ "a" : 1 } ' FORMAT JSON);
- json
---------------
- { "a" : 1 }
-(1 row)
-
-SELECT JSON('{ "a" : 1 } ' FORMAT JSON ENCODING UTF8);
-ERROR: JSON ENCODING clause is only allowed for bytea input type
-LINE 1: SELECT JSON('{ "a" : 1 } ' FORMAT JSON ENCODING UTF8);
- ^
-SELECT JSON('{ "a" : 1 } '::bytea FORMAT JSON ENCODING UTF8);
- json
---------------
- { "a" : 1 }
-(1 row)
-
-SELECT pg_typeof(JSON('{ "a" : 1 } '));
- pg_typeof
------------
- json
-(1 row)
-
-SELECT JSON(' 1 '::json);
- json
----------
- 1
-(1 row)
-
-SELECT JSON(' 1 '::jsonb);
- json
-------
- 1
-(1 row)
-
-SELECT JSON(' 1 '::json WITH UNIQUE KEYS);
-ERROR: cannot use non-string types with WITH UNIQUE KEYS clause
-LINE 1: SELECT JSON(' 1 '::json WITH UNIQUE KEYS);
- ^
-SELECT JSON(123);
-ERROR: cannot cast type integer to json
-LINE 1: SELECT JSON(123);
- ^
-SELECT JSON('{"a": 1, "a": 2}');
- json
-------------------
- {"a": 1, "a": 2}
-(1 row)
-
-SELECT JSON('{"a": 1, "a": 2}' WITH UNIQUE KEYS);
-ERROR: duplicate JSON object key value
-SELECT JSON('{"a": 1, "a": 2}' WITHOUT UNIQUE KEYS);
- json
-------------------
- {"a": 1, "a": 2}
-(1 row)
-
-EXPLAIN (VERBOSE, COSTS OFF) SELECT JSON('123');
- QUERY PLAN
------------------------------
- Result
- Output: JSON('123'::json)
-(2 rows)
-
-EXPLAIN (VERBOSE, COSTS OFF) SELECT JSON('123' FORMAT JSON);
- QUERY PLAN
------------------------------
- Result
- Output: JSON('123'::json)
-(2 rows)
-
-EXPLAIN (VERBOSE, COSTS OFF) SELECT JSON('123'::bytea FORMAT JSON);
- QUERY PLAN
------------------------------------------------
- Result
- Output: JSON('\x313233'::bytea FORMAT JSON)
-(2 rows)
-
-EXPLAIN (VERBOSE, COSTS OFF) SELECT JSON('123'::bytea FORMAT JSON ENCODING UTF8);
- QUERY PLAN
--------------------------------------------------------------
- Result
- Output: JSON('\x313233'::bytea FORMAT JSON ENCODING UTF8)
-(2 rows)
-
-EXPLAIN (VERBOSE, COSTS OFF) SELECT JSON('123' WITH UNIQUE KEYS);
- QUERY PLAN
-----------------------------------------------
- Result
- Output: JSON('123'::text WITH UNIQUE KEYS)
-(2 rows)
-
-EXPLAIN (VERBOSE, COSTS OFF) SELECT JSON('123' WITHOUT UNIQUE KEYS);
- QUERY PLAN
------------------------------
- Result
- Output: JSON('123'::json)
-(2 rows)
-
-SELECT JSON('123' RETURNING text);
-ERROR: cannot use RETURNING type text in JSON()
-LINE 1: SELECT JSON('123' RETURNING text);
- ^
-EXPLAIN (VERBOSE, COSTS OFF) SELECT JSON('123');
- QUERY PLAN
------------------------------
- Result
- Output: JSON('123'::json)
-(2 rows)
-
-EXPLAIN (VERBOSE, COSTS OFF) SELECT JSON('123' RETURNING json);
- QUERY PLAN
------------------------------
- Result
- Output: JSON('123'::json)
-(2 rows)
-
-EXPLAIN (VERBOSE, COSTS OFF) SELECT JSON('123' RETURNING jsonb);
- QUERY PLAN
-----------------------------------------------
- Result
- Output: JSON('123'::jsonb RETURNING jsonb)
-(2 rows)
-
-SELECT pg_typeof(JSON('123'));
- pg_typeof
------------
- json
-(1 row)
-
-SELECT pg_typeof(JSON('123' RETURNING json));
- pg_typeof
------------
- json
-(1 row)
-
-SELECT pg_typeof(JSON('123' RETURNING jsonb));
- pg_typeof
------------
- jsonb
-(1 row)
-
--- JSON_SCALAR()
-SELECT JSON_SCALAR();
-ERROR: syntax error at or near ")"
-LINE 1: SELECT JSON_SCALAR();
- ^
-SELECT JSON_SCALAR(NULL);
- json_scalar
--------------
-
-(1 row)
-
-SELECT JSON_SCALAR(NULL::int);
- json_scalar
--------------
-
-(1 row)
-
-SELECT JSON_SCALAR(123);
- json_scalar
--------------
- 123
-(1 row)
-
-SELECT JSON_SCALAR(123.45);
- json_scalar
--------------
- 123.45
-(1 row)
-
-SELECT JSON_SCALAR(123.45::numeric);
- json_scalar
--------------
- 123.45
-(1 row)
-
-SELECT JSON_SCALAR(true);
- json_scalar
--------------
- true
-(1 row)
-
-SELECT JSON_SCALAR(false);
- json_scalar
--------------
- false
-(1 row)
-
-SELECT JSON_SCALAR(' 123.45');
- json_scalar
--------------
- " 123.45"
-(1 row)
-
-SELECT JSON_SCALAR('2020-06-07'::date);
- json_scalar
---------------
- "2020-06-07"
-(1 row)
-
-SELECT JSON_SCALAR('2020-06-07 01:02:03'::timestamp);
- json_scalar
------------------------
- "2020-06-07T01:02:03"
-(1 row)
-
-SELECT JSON_SCALAR('{}'::json);
- json_scalar
--------------
- {}
-(1 row)
-
-SELECT JSON_SCALAR('{}'::jsonb);
- json_scalar
--------------
- {}
-(1 row)
-
-EXPLAIN (VERBOSE, COSTS OFF) SELECT JSON_SCALAR(123);
- QUERY PLAN
-----------------------------
- Result
- Output: JSON_SCALAR(123)
-(2 rows)
-
-EXPLAIN (VERBOSE, COSTS OFF) SELECT JSON_SCALAR('123');
- QUERY PLAN
-------------------------------------
- Result
- Output: JSON_SCALAR('123'::text)
-(2 rows)
-
-EXPLAIN (VERBOSE, COSTS OFF) SELECT JSON_SCALAR(123 RETURNING json);
- QUERY PLAN
-----------------------------
- Result
- Output: JSON_SCALAR(123)
-(2 rows)
-
-EXPLAIN (VERBOSE, COSTS OFF) SELECT JSON_SCALAR(123 RETURNING jsonb);
- QUERY PLAN
---------------------------------------------
- Result
- Output: JSON_SCALAR(123 RETURNING jsonb)
-(2 rows)
-
--- JSON_SERIALIZE()
-SELECT JSON_SERIALIZE();
-ERROR: syntax error at or near ")"
-LINE 1: SELECT JSON_SERIALIZE();
- ^
-SELECT JSON_SERIALIZE(NULL);
- json_serialize
-----------------
-
-(1 row)
-
-SELECT JSON_SERIALIZE(JSON('{ "a" : 1 } '));
- json_serialize
-----------------
- { "a" : 1 }
-(1 row)
-
-SELECT JSON_SERIALIZE('{ "a" : 1 } ');
- json_serialize
-----------------
- { "a" : 1 }
-(1 row)
-
-SELECT JSON_SERIALIZE('1');
- json_serialize
-----------------
- 1
-(1 row)
-
-SELECT JSON_SERIALIZE('1' FORMAT JSON);
- json_serialize
-----------------
- 1
-(1 row)
-
-SELECT JSON_SERIALIZE('{ "a" : 1 } ' RETURNING bytea);
- json_serialize
-----------------------------
- \x7b20226122203a2031207d20
-(1 row)
-
-SELECT JSON_SERIALIZE('{ "a" : 1 } ' RETURNING varchar);
- json_serialize
-----------------
- { "a" : 1 }
-(1 row)
-
-SELECT pg_typeof(JSON_SERIALIZE(NULL));
- pg_typeof
------------
- text
-(1 row)
-
--- only string types or bytea allowed
-SELECT JSON_SERIALIZE('{ "a" : 1 } ' RETURNING jsonb);
-ERROR: cannot use RETURNING type jsonb in JSON_SERIALIZE()
-HINT: Try returning a string type or bytea.
-EXPLAIN (VERBOSE, COSTS OFF) SELECT JSON_SERIALIZE('{}');
- QUERY PLAN
------------------------------------------------------
- Result
- Output: JSON_SERIALIZE('{}'::json RETURNING text)
-(2 rows)
-
-EXPLAIN (VERBOSE, COSTS OFF) SELECT JSON_SERIALIZE('{}' RETURNING bytea);
- QUERY PLAN
-------------------------------------------------------
- Result
- Output: JSON_SERIALIZE('{}'::json RETURNING bytea)
-(2 rows)
-
--- JSON_OBJECT()
-SELECT JSON_OBJECT();
- json_object
--------------
- {}
-(1 row)
-
-SELECT JSON_OBJECT(RETURNING json);
- json_object
--------------
- {}
-(1 row)
-
-SELECT JSON_OBJECT(RETURNING json FORMAT JSON);
- json_object
--------------
- {}
-(1 row)
-
-SELECT JSON_OBJECT(RETURNING jsonb);
- json_object
--------------
- {}
-(1 row)
-
-SELECT JSON_OBJECT(RETURNING jsonb FORMAT JSON);
- json_object
--------------
- {}
-(1 row)
-
-SELECT JSON_OBJECT(RETURNING text);
- json_object
--------------
- {}
-(1 row)
-
-SELECT JSON_OBJECT(RETURNING text FORMAT JSON);
- json_object
--------------
- {}
-(1 row)
-
-SELECT JSON_OBJECT(RETURNING text FORMAT JSON ENCODING UTF8);
-ERROR: cannot set JSON encoding for non-bytea output types
-LINE 1: SELECT JSON_OBJECT(RETURNING text FORMAT JSON ENCODING UTF8)...
- ^
-SELECT JSON_OBJECT(RETURNING text FORMAT JSON ENCODING INVALID_ENCODING);
-ERROR: unrecognized JSON encoding: invalid_encoding
-SELECT JSON_OBJECT(RETURNING bytea);
- json_object
--------------
- \x7b7d
-(1 row)
-
-SELECT JSON_OBJECT(RETURNING bytea FORMAT JSON);
- json_object
--------------
- \x7b7d
-(1 row)
-
-SELECT JSON_OBJECT(RETURNING bytea FORMAT JSON ENCODING UTF8);
- json_object
--------------
- \x7b7d
-(1 row)
-
-SELECT JSON_OBJECT(RETURNING bytea FORMAT JSON ENCODING UTF16);
-ERROR: unsupported JSON encoding
-LINE 1: SELECT JSON_OBJECT(RETURNING bytea FORMAT JSON ENCODING UTF1...
- ^
-HINT: Only UTF8 JSON encoding is supported.
-SELECT JSON_OBJECT(RETURNING bytea FORMAT JSON ENCODING UTF32);
-ERROR: unsupported JSON encoding
-LINE 1: SELECT JSON_OBJECT(RETURNING bytea FORMAT JSON ENCODING UTF3...
- ^
-HINT: Only UTF8 JSON encoding is supported.
-SELECT JSON_OBJECT('foo': NULL::int FORMAT JSON);
-ERROR: cannot use non-string types with explicit FORMAT JSON clause
-LINE 1: SELECT JSON_OBJECT('foo': NULL::int FORMAT JSON);
- ^
-SELECT JSON_OBJECT('foo': NULL::int FORMAT JSON ENCODING UTF8);
-ERROR: JSON ENCODING clause is only allowed for bytea input type
-LINE 1: SELECT JSON_OBJECT('foo': NULL::int FORMAT JSON ENCODING UTF...
- ^
-SELECT JSON_OBJECT('foo': NULL::json FORMAT JSON);
-WARNING: FORMAT JSON has no effect for json and jsonb types
-LINE 1: SELECT JSON_OBJECT('foo': NULL::json FORMAT JSON);
- ^
- json_object
-----------------
- {"foo" : null}
-(1 row)
-
-SELECT JSON_OBJECT('foo': NULL::json FORMAT JSON ENCODING UTF8);
-ERROR: JSON ENCODING clause is only allowed for bytea input type
-LINE 1: SELECT JSON_OBJECT('foo': NULL::json FORMAT JSON ENCODING UT...
- ^
-SELECT JSON_OBJECT('foo': NULL::jsonb FORMAT JSON);
-WARNING: FORMAT JSON has no effect for json and jsonb types
-LINE 1: SELECT JSON_OBJECT('foo': NULL::jsonb FORMAT JSON);
- ^
- json_object
----------------
- {"foo": null}
-(1 row)
-
-SELECT JSON_OBJECT('foo': NULL::jsonb FORMAT JSON ENCODING UTF8);
-ERROR: JSON ENCODING clause is only allowed for bytea input type
-LINE 1: SELECT JSON_OBJECT('foo': NULL::jsonb FORMAT JSON ENCODING U...
- ^
-SELECT JSON_OBJECT(NULL: 1);
-ERROR: argument 1 cannot be null
-HINT: Object keys should be text.
-SELECT JSON_OBJECT('a': 2 + 3);
- json_object
--------------
- {"a" : 5}
-(1 row)
-
-SELECT JSON_OBJECT('a' VALUE 2 + 3);
- json_object
--------------
- {"a" : 5}
-(1 row)
-
---SELECT JSON_OBJECT(KEY 'a' VALUE 2 + 3);
-SELECT JSON_OBJECT('a' || 2: 1);
- json_object
--------------
- {"a2" : 1}
-(1 row)
-
-SELECT JSON_OBJECT(('a' || 2) VALUE 1);
- json_object
--------------
- {"a2" : 1}
-(1 row)
-
---SELECT JSON_OBJECT('a' || 2 VALUE 1);
---SELECT JSON_OBJECT(KEY 'a' || 2 VALUE 1);
-SELECT JSON_OBJECT('a': 2::text);
- json_object
--------------
- {"a" : "2"}
-(1 row)
-
-SELECT JSON_OBJECT('a' VALUE 2::text);
- json_object
--------------
- {"a" : "2"}
-(1 row)
-
---SELECT JSON_OBJECT(KEY 'a' VALUE 2::text);
-SELECT JSON_OBJECT(1::text: 2);
- json_object
--------------
- {"1" : 2}
-(1 row)
-
-SELECT JSON_OBJECT((1::text) VALUE 2);
- json_object
--------------
- {"1" : 2}
-(1 row)
-
---SELECT JSON_OBJECT(1::text VALUE 2);
---SELECT JSON_OBJECT(KEY 1::text VALUE 2);
-SELECT JSON_OBJECT(json '[1]': 123);
-ERROR: key value must be scalar, not array, composite, or json
-SELECT JSON_OBJECT(ARRAY[1,2,3]: 'aaa');
-ERROR: key value must be scalar, not array, composite, or json
-SELECT JSON_OBJECT(
- 'a': '123',
- 1.23: 123,
- 'c': json '[ 1,true,{ } ]',
- 'd': jsonb '{ "x" : 123.45 }'
-);
- json_object
--------------------------------------------------------------------
- {"a": "123", "c": [1, true, {}], "d": {"x": 123.45}, "1.23": 123}
-(1 row)
-
-SELECT JSON_OBJECT(
- 'a': '123',
- 1.23: 123,
- 'c': json '[ 1,true,{ } ]',
- 'd': jsonb '{ "x" : 123.45 }'
- RETURNING jsonb
-);
- json_object
--------------------------------------------------------------------
- {"a": "123", "c": [1, true, {}], "d": {"x": 123.45}, "1.23": 123}
-(1 row)
-
-/*
-SELECT JSON_OBJECT(
- 'a': '123',
- KEY 1.23 VALUE 123,
- 'c' VALUE json '[1, true, {}]'
-);
-*/
-SELECT JSON_OBJECT('a': '123', 'b': JSON_OBJECT('a': 111, 'b': 'aaa'));
- json_object
------------------------------------------------
- {"a" : "123", "b" : {"a" : 111, "b" : "aaa"}}
-(1 row)
-
-SELECT JSON_OBJECT('a': '123', 'b': JSON_OBJECT('a': 111, 'b': 'aaa' RETURNING jsonb));
- json_object
--------------------------------------------
- {"a": "123", "b": {"a": 111, "b": "aaa"}}
-(1 row)
-
-SELECT JSON_OBJECT('a': JSON_OBJECT('b': 1 RETURNING text));
- json_object
------------------------
- {"a" : "{\"b\" : 1}"}
-(1 row)
-
-SELECT JSON_OBJECT('a': JSON_OBJECT('b': 1 RETURNING text) FORMAT JSON);
- json_object
--------------------
- {"a" : {"b" : 1}}
-(1 row)
-
-SELECT JSON_OBJECT('a': JSON_OBJECT('b': 1 RETURNING bytea));
- json_object
----------------------------------
- {"a" : "\\x7b226222203a20317d"}
-(1 row)
-
-SELECT JSON_OBJECT('a': JSON_OBJECT('b': 1 RETURNING bytea) FORMAT JSON);
- json_object
--------------------
- {"a" : {"b" : 1}}
-(1 row)
-
-SELECT JSON_OBJECT('a': '1', 'b': NULL, 'c': 2);
- json_object
-----------------------------------
- {"a" : "1", "b" : null, "c" : 2}
-(1 row)
-
-SELECT JSON_OBJECT('a': '1', 'b': NULL, 'c': 2 NULL ON NULL);
- json_object
-----------------------------------
- {"a" : "1", "b" : null, "c" : 2}
-(1 row)
-
-SELECT JSON_OBJECT('a': '1', 'b': NULL, 'c': 2 ABSENT ON NULL);
- json_object
-----------------------
- {"a" : "1", "c" : 2}
-(1 row)
-
-SELECT JSON_OBJECT(1: 1, '1': NULL WITH UNIQUE);
-ERROR: duplicate JSON key "1"
-SELECT JSON_OBJECT(1: 1, '1': NULL ABSENT ON NULL WITH UNIQUE);
-ERROR: duplicate JSON key "1"
-SELECT JSON_OBJECT(1: 1, '1': NULL NULL ON NULL WITH UNIQUE RETURNING jsonb);
-ERROR: duplicate JSON object key value
-SELECT JSON_OBJECT(1: 1, '1': NULL ABSENT ON NULL WITH UNIQUE RETURNING jsonb);
-ERROR: duplicate JSON object key value
-SELECT JSON_OBJECT(1: 1, '2': NULL, '1': 1 NULL ON NULL WITH UNIQUE);
-ERROR: duplicate JSON key "1"
-SELECT JSON_OBJECT(1: 1, '2': NULL, '1': 1 ABSENT ON NULL WITH UNIQUE);
-ERROR: duplicate JSON key "1"
-SELECT JSON_OBJECT(1: 1, '2': NULL, '1': 1 ABSENT ON NULL WITHOUT UNIQUE);
- json_object
---------------------
- {"1" : 1, "1" : 1}
-(1 row)
-
-SELECT JSON_OBJECT(1: 1, '2': NULL, '1': 1 ABSENT ON NULL WITH UNIQUE RETURNING jsonb);
-ERROR: duplicate JSON object key value
-SELECT JSON_OBJECT(1: 1, '2': NULL, '1': 1 ABSENT ON NULL WITHOUT UNIQUE RETURNING jsonb);
- json_object
--------------
- {"1": 1}
-(1 row)
-
-SELECT JSON_OBJECT(1: 1, '2': NULL, '3': 1, 4: NULL, '5': 'a' ABSENT ON NULL WITH UNIQUE RETURNING jsonb);
- json_object
-----------------------------
- {"1": 1, "3": 1, "5": "a"}
-(1 row)
-
--- JSON_ARRAY()
-SELECT JSON_ARRAY();
- json_array
-------------
- []
-(1 row)
-
-SELECT JSON_ARRAY(RETURNING json);
- json_array
-------------
- []
-(1 row)
-
-SELECT JSON_ARRAY(RETURNING json FORMAT JSON);
- json_array
-------------
- []
-(1 row)
-
-SELECT JSON_ARRAY(RETURNING jsonb);
- json_array
-------------
- []
-(1 row)
-
-SELECT JSON_ARRAY(RETURNING jsonb FORMAT JSON);
- json_array
-------------
- []
-(1 row)
-
-SELECT JSON_ARRAY(RETURNING text);
- json_array
-------------
- []
-(1 row)
-
-SELECT JSON_ARRAY(RETURNING text FORMAT JSON);
- json_array
-------------
- []
-(1 row)
-
-SELECT JSON_ARRAY(RETURNING text FORMAT JSON ENCODING UTF8);
-ERROR: cannot set JSON encoding for non-bytea output types
-LINE 1: SELECT JSON_ARRAY(RETURNING text FORMAT JSON ENCODING UTF8);
- ^
-SELECT JSON_ARRAY(RETURNING text FORMAT JSON ENCODING INVALID_ENCODING);
-ERROR: unrecognized JSON encoding: invalid_encoding
-SELECT JSON_ARRAY(RETURNING bytea);
- json_array
-------------
- \x5b5d
-(1 row)
-
-SELECT JSON_ARRAY(RETURNING bytea FORMAT JSON);
- json_array
-------------
- \x5b5d
-(1 row)
-
-SELECT JSON_ARRAY(RETURNING bytea FORMAT JSON ENCODING UTF8);
- json_array
-------------
- \x5b5d
-(1 row)
-
-SELECT JSON_ARRAY(RETURNING bytea FORMAT JSON ENCODING UTF16);
-ERROR: unsupported JSON encoding
-LINE 1: SELECT JSON_ARRAY(RETURNING bytea FORMAT JSON ENCODING UTF16...
- ^
-HINT: Only UTF8 JSON encoding is supported.
-SELECT JSON_ARRAY(RETURNING bytea FORMAT JSON ENCODING UTF32);
-ERROR: unsupported JSON encoding
-LINE 1: SELECT JSON_ARRAY(RETURNING bytea FORMAT JSON ENCODING UTF32...
- ^
-HINT: Only UTF8 JSON encoding is supported.
-SELECT JSON_ARRAY('aaa', 111, true, array[1,2,3], NULL, json '{"a": [1]}', jsonb '["a",3]');
- json_array
------------------------------------------------------
- ["aaa", 111, true, [1, 2, 3], {"a": [1]}, ["a", 3]]
-(1 row)
-
-SELECT JSON_ARRAY('a', NULL, 'b' NULL ON NULL);
- json_array
-------------------
- ["a", null, "b"]
-(1 row)
-
-SELECT JSON_ARRAY('a', NULL, 'b' ABSENT ON NULL);
- json_array
-------------
- ["a", "b"]
-(1 row)
-
-SELECT JSON_ARRAY(NULL, NULL, 'b' ABSENT ON NULL);
- json_array
-------------
- ["b"]
-(1 row)
-
-SELECT JSON_ARRAY('a', NULL, 'b' NULL ON NULL RETURNING jsonb);
- json_array
-------------------
- ["a", null, "b"]
-(1 row)
-
-SELECT JSON_ARRAY('a', NULL, 'b' ABSENT ON NULL RETURNING jsonb);
- json_array
-------------
- ["a", "b"]
-(1 row)
-
-SELECT JSON_ARRAY(NULL, NULL, 'b' ABSENT ON NULL RETURNING jsonb);
- json_array
-------------
- ["b"]
-(1 row)
-
-SELECT JSON_ARRAY(JSON_ARRAY('{ "a" : 123 }' RETURNING text));
- json_array
--------------------------------
- ["[\"{ \\\"a\\\" : 123 }\"]"]
-(1 row)
-
-SELECT JSON_ARRAY(JSON_ARRAY('{ "a" : 123 }' FORMAT JSON RETURNING text));
- json_array
------------------------
- ["[{ \"a\" : 123 }]"]
-(1 row)
-
-SELECT JSON_ARRAY(JSON_ARRAY('{ "a" : 123 }' FORMAT JSON RETURNING text) FORMAT JSON);
- json_array
--------------------
- [[{ "a" : 123 }]]
-(1 row)
-
-SELECT JSON_ARRAY(SELECT i FROM (VALUES (1), (2), (NULL), (4)) foo(i));
- json_array
-------------
- [1, 2, 4]
-(1 row)
-
-SELECT JSON_ARRAY(SELECT i FROM (VALUES (NULL::int[]), ('{1,2}'), (NULL), (NULL), ('{3,4}'), (NULL)) foo(i));
- json_array
-------------
- [[1,2], +
- [3,4]]
-(1 row)
-
-SELECT JSON_ARRAY(SELECT i FROM (VALUES (NULL::int[]), ('{1,2}'), (NULL), (NULL), ('{3,4}'), (NULL)) foo(i) RETURNING jsonb);
- json_array
-------------------
- [[1, 2], [3, 4]]
-(1 row)
-
---SELECT JSON_ARRAY(SELECT i FROM (VALUES (NULL::int[]), ('{1,2}'), (NULL), (NULL), ('{3,4}'), (NULL)) foo(i) NULL ON NULL);
---SELECT JSON_ARRAY(SELECT i FROM (VALUES (NULL::int[]), ('{1,2}'), (NULL), (NULL), ('{3,4}'), (NULL)) foo(i) NULL ON NULL RETURNING jsonb);
-SELECT JSON_ARRAY(SELECT i FROM (VALUES (3), (1), (NULL), (2)) foo(i) ORDER BY i);
- json_array
-------------
- [1, 2, 3]
-(1 row)
-
--- Should fail
-SELECT JSON_ARRAY(SELECT FROM (VALUES (1)) foo(i));
-ERROR: subquery must return only one column
-LINE 1: SELECT JSON_ARRAY(SELECT FROM (VALUES (1)) foo(i));
- ^
-SELECT JSON_ARRAY(SELECT i, i FROM (VALUES (1)) foo(i));
-ERROR: subquery must return only one column
-LINE 1: SELECT JSON_ARRAY(SELECT i, i FROM (VALUES (1)) foo(i));
- ^
-SELECT JSON_ARRAY(SELECT * FROM (VALUES (1, 2)) foo(i, j));
-ERROR: subquery must return only one column
-LINE 1: SELECT JSON_ARRAY(SELECT * FROM (VALUES (1, 2)) foo(i, j));
- ^
--- JSON_ARRAYAGG()
-SELECT JSON_ARRAYAGG(i) IS NULL,
- JSON_ARRAYAGG(i RETURNING jsonb) IS NULL
-FROM generate_series(1, 0) i;
- ?column? | ?column?
-----------+----------
- t | t
-(1 row)
-
-SELECT JSON_ARRAYAGG(i),
- JSON_ARRAYAGG(i RETURNING jsonb)
-FROM generate_series(1, 5) i;
- json_arrayagg | json_arrayagg
------------------+-----------------
- [1, 2, 3, 4, 5] | [1, 2, 3, 4, 5]
-(1 row)
-
-SELECT JSON_ARRAYAGG(i ORDER BY i DESC)
-FROM generate_series(1, 5) i;
- json_arrayagg
------------------
- [5, 4, 3, 2, 1]
-(1 row)
-
-SELECT JSON_ARRAYAGG(i::text::json)
-FROM generate_series(1, 5) i;
- json_arrayagg
------------------
- [1, 2, 3, 4, 5]
-(1 row)
-
-SELECT JSON_ARRAYAGG(JSON_ARRAY(i, i + 1 RETURNING text) FORMAT JSON)
-FROM generate_series(1, 5) i;
- json_arrayagg
-------------------------------------------
- [[1, 2], [2, 3], [3, 4], [4, 5], [5, 6]]
-(1 row)
-
-SELECT JSON_ARRAYAGG(NULL),
- JSON_ARRAYAGG(NULL RETURNING jsonb)
-FROM generate_series(1, 5);
- json_arrayagg | json_arrayagg
----------------+---------------
- [] | []
-(1 row)
-
-SELECT JSON_ARRAYAGG(NULL NULL ON NULL),
- JSON_ARRAYAGG(NULL NULL ON NULL RETURNING jsonb)
-FROM generate_series(1, 5);
- json_arrayagg | json_arrayagg
---------------------------------+--------------------------------
- [null, null, null, null, null] | [null, null, null, null, null]
-(1 row)
-
-SELECT
- JSON_ARRAYAGG(bar),
- JSON_ARRAYAGG(bar RETURNING jsonb),
- JSON_ARRAYAGG(bar ABSENT ON NULL),
- JSON_ARRAYAGG(bar ABSENT ON NULL RETURNING jsonb),
- JSON_ARRAYAGG(bar NULL ON NULL),
- JSON_ARRAYAGG(bar NULL ON NULL RETURNING jsonb),
- JSON_ARRAYAGG(foo),
- JSON_ARRAYAGG(foo RETURNING jsonb),
- JSON_ARRAYAGG(foo ORDER BY bar) FILTER (WHERE bar > 2),
- JSON_ARRAYAGG(foo ORDER BY bar RETURNING jsonb) FILTER (WHERE bar > 2)
-FROM
- (VALUES (NULL), (3), (1), (NULL), (NULL), (5), (2), (4), (NULL)) foo(bar);
- json_arrayagg | json_arrayagg | json_arrayagg | json_arrayagg | json_arrayagg | json_arrayagg | json_arrayagg | json_arrayagg | json_arrayagg | json_arrayagg
------------------+-----------------+-----------------+-----------------+-----------------------------------------+-----------------------------------------+-----------------+--------------------------------------------------------------------------------------------------------------------------+---------------+--------------------------------------
- [3, 1, 5, 2, 4] | [3, 1, 5, 2, 4] | [3, 1, 5, 2, 4] | [3, 1, 5, 2, 4] | [null, 3, 1, null, null, 5, 2, 4, null] | [null, 3, 1, null, null, 5, 2, 4, null] | [{"bar":null}, +| [{"bar": null}, {"bar": 3}, {"bar": 1}, {"bar": null}, {"bar": null}, {"bar": 5}, {"bar": 2}, {"bar": 4}, {"bar": null}] | [{"bar":3}, +| [{"bar": 3}, {"bar": 4}, {"bar": 5}]
- | | | | | | {"bar":3}, +| | {"bar":4}, +|
- | | | | | | {"bar":1}, +| | {"bar":5}] |
- | | | | | | {"bar":null}, +| | |
- | | | | | | {"bar":null}, +| | |
- | | | | | | {"bar":5}, +| | |
- | | | | | | {"bar":2}, +| | |
- | | | | | | {"bar":4}, +| | |
- | | | | | | {"bar":null}] | | |
-(1 row)
-
-SELECT
- bar, JSON_ARRAYAGG(bar) FILTER (WHERE bar > 2) OVER (PARTITION BY foo.bar % 2)
-FROM
- (VALUES (NULL), (3), (1), (NULL), (NULL), (5), (2), (4), (NULL), (5), (4)) foo(bar);
- bar | json_arrayagg
------+---------------
- 4 | [4, 4]
- 4 | [4, 4]
- 2 | [4, 4]
- 5 | [5, 3, 5]
- 3 | [5, 3, 5]
- 1 | [5, 3, 5]
- 5 | [5, 3, 5]
- |
- |
- |
- |
-(11 rows)
-
--- JSON_OBJECTAGG()
-SELECT JSON_OBJECTAGG('key': 1) IS NULL,
- JSON_OBJECTAGG('key': 1 RETURNING jsonb) IS NULL
-WHERE FALSE;
- ?column? | ?column?
-----------+----------
- t | t
-(1 row)
-
-SELECT JSON_OBJECTAGG(NULL: 1);
-ERROR: field name must not be null
-SELECT JSON_OBJECTAGG(NULL: 1 RETURNING jsonb);
-ERROR: field name must not be null
-SELECT
- JSON_OBJECTAGG(i: i),
--- JSON_OBJECTAGG(i VALUE i),
--- JSON_OBJECTAGG(KEY i VALUE i),
- JSON_OBJECTAGG(i: i RETURNING jsonb)
-FROM
- generate_series(1, 5) i;
- json_objectagg | json_objectagg
--------------------------------------------------+------------------------------------------
- { "1" : 1, "2" : 2, "3" : 3, "4" : 4, "5" : 5 } | {"1": 1, "2": 2, "3": 3, "4": 4, "5": 5}
-(1 row)
-
-SELECT
- JSON_OBJECTAGG(k: v),
- JSON_OBJECTAGG(k: v NULL ON NULL),
- JSON_OBJECTAGG(k: v ABSENT ON NULL),
- JSON_OBJECTAGG(k: v RETURNING jsonb),
- JSON_OBJECTAGG(k: v NULL ON NULL RETURNING jsonb),
- JSON_OBJECTAGG(k: v ABSENT ON NULL RETURNING jsonb)
-FROM
- (VALUES (1, 1), (1, NULL), (2, NULL), (3, 3)) foo(k, v);
- json_objectagg | json_objectagg | json_objectagg | json_objectagg | json_objectagg | json_objectagg
-----------------------------------------------+----------------------------------------------+----------------------+--------------------------------+--------------------------------+------------------
- { "1" : 1, "1" : null, "2" : null, "3" : 3 } | { "1" : 1, "1" : null, "2" : null, "3" : 3 } | { "1" : 1, "3" : 3 } | {"1": null, "2": null, "3": 3} | {"1": null, "2": null, "3": 3} | {"1": 1, "3": 3}
-(1 row)
-
-SELECT JSON_OBJECTAGG(k: v WITH UNIQUE KEYS)
-FROM (VALUES (1, 1), (1, NULL), (2, 2)) foo(k, v);
-ERROR: duplicate JSON key "1"
-SELECT JSON_OBJECTAGG(k: v ABSENT ON NULL WITH UNIQUE KEYS)
-FROM (VALUES (1, 1), (1, NULL), (2, 2)) foo(k, v);
-ERROR: duplicate JSON key "1"
-SELECT JSON_OBJECTAGG(k: v ABSENT ON NULL WITH UNIQUE KEYS)
-FROM (VALUES (1, 1), (0, NULL), (3, NULL), (2, 2), (4, NULL)) foo(k, v);
- json_objectagg
-----------------------
- { "1" : 1, "2" : 2 }
-(1 row)
-
-SELECT JSON_OBJECTAGG(k: v WITH UNIQUE KEYS RETURNING jsonb)
-FROM (VALUES (1, 1), (1, NULL), (2, 2)) foo(k, v);
-ERROR: duplicate JSON object key value
-SELECT JSON_OBJECTAGG(k: v ABSENT ON NULL WITH UNIQUE KEYS RETURNING jsonb)
-FROM (VALUES (1, 1), (1, NULL), (2, 2)) foo(k, v);
-ERROR: duplicate JSON object key value
-SELECT JSON_OBJECTAGG(k: v ABSENT ON NULL WITH UNIQUE KEYS RETURNING jsonb)
-FROM (VALUES (1, 1), (0, NULL),(4, null), (5, null),(6, null),(2, 2)) foo(k, v);
- json_objectagg
-------------------
- {"1": 1, "2": 2}
-(1 row)
-
--- Test JSON_OBJECT deparsing
-EXPLAIN (VERBOSE, COSTS OFF)
-SELECT JSON_OBJECT('foo' : '1' FORMAT JSON, 'bar' : 'baz' RETURNING json);
- QUERY PLAN
-------------------------------------------------------------------------------
- Result
- Output: JSON_OBJECT('foo' : '1'::json, 'bar' : 'baz'::text RETURNING json)
-(2 rows)
-
-CREATE VIEW json_object_view AS
-SELECT JSON_OBJECT('foo' : '1' FORMAT JSON, 'bar' : 'baz' RETURNING json);
-\sv json_object_view
-CREATE OR REPLACE VIEW public.json_object_view AS
- SELECT JSON_OBJECT('foo' : '1'::text FORMAT JSON, 'bar' : 'baz'::text RETURNING json) AS "json_object"
-DROP VIEW json_object_view;
-SELECT to_json(a) AS a, JSON_OBJECTAGG(k : v WITH UNIQUE KEYS) OVER (ORDER BY k)
-FROM (VALUES (1,1), (2,2)) a(k,v);
- a | json_objectagg
----------------+----------------------
- {"k":1,"v":1} | { "1" : 1 }
- {"k":2,"v":2} | { "1" : 1, "2" : 2 }
-(2 rows)
-
-SELECT to_json(a) AS a, JSON_OBJECTAGG(k : v WITH UNIQUE KEYS) OVER (ORDER BY k)
-FROM (VALUES (1,1), (1,2), (2,2)) a(k,v);
-ERROR: duplicate JSON key "1"
-SELECT to_json(a) AS a, JSON_OBJECTAGG(k : v ABSENT ON NULL WITH UNIQUE KEYS)
- OVER (ORDER BY k)
-FROM (VALUES (1,1), (1,null), (2,2)) a(k,v);
-ERROR: duplicate JSON key "1"
-SELECT to_json(a) AS a, JSON_OBJECTAGG(k : v ABSENT ON NULL)
-OVER (ORDER BY k)
-FROM (VALUES (1,1), (1,null), (2,2)) a(k,v);
- a | json_objectagg
-------------------+----------------------
- {"k":1,"v":1} | { "1" : 1 }
- {"k":1,"v":null} | { "1" : 1 }
- {"k":2,"v":2} | { "1" : 1, "2" : 2 }
-(3 rows)
-
-SELECT to_json(a) AS a, JSON_OBJECTAGG(k : v ABSENT ON NULL)
-OVER (ORDER BY k RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
-FROM (VALUES (1,1), (1,null), (2,2)) a(k,v);
- a | json_objectagg
-------------------+----------------------
- {"k":1,"v":1} | { "1" : 1, "2" : 2 }
- {"k":1,"v":null} | { "1" : 1, "2" : 2 }
- {"k":2,"v":2} | { "1" : 1, "2" : 2 }
-(3 rows)
-
--- Test JSON_ARRAY deparsing
-EXPLAIN (VERBOSE, COSTS OFF)
-SELECT JSON_ARRAY('1' FORMAT JSON, 2 RETURNING json);
- QUERY PLAN
----------------------------------------------------
- Result
- Output: JSON_ARRAY('1'::json, 2 RETURNING json)
-(2 rows)
-
-CREATE VIEW json_array_view AS
-SELECT JSON_ARRAY('1' FORMAT JSON, 2 RETURNING json);
-\sv json_array_view
-CREATE OR REPLACE VIEW public.json_array_view AS
- SELECT JSON_ARRAY('1'::text FORMAT JSON, 2 RETURNING json) AS "json_array"
-DROP VIEW json_array_view;
--- Test JSON_OBJECTAGG deparsing
-EXPLAIN (VERBOSE, COSTS OFF)
-SELECT JSON_OBJECTAGG(i: ('111' || i)::bytea FORMAT JSON WITH UNIQUE RETURNING text) FILTER (WHERE i > 3)
-FROM generate_series(1,5) i;
- QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------
- Aggregate
- Output: JSON_OBJECTAGG(i : (('111'::text || (i)::text))::bytea FORMAT JSON WITH UNIQUE KEYS RETURNING text) FILTER (WHERE (i > 3))
- -> Function Scan on pg_catalog.generate_series i
- Output: i
- Function Call: generate_series(1, 5)
-(5 rows)
-
-EXPLAIN (VERBOSE, COSTS OFF)
-SELECT JSON_OBJECTAGG(i: ('111' || i)::bytea FORMAT JSON WITH UNIQUE RETURNING text) OVER (PARTITION BY i % 2)
-FROM generate_series(1,5) i;
- QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
- WindowAgg
- Output: JSON_OBJECTAGG(i : (('111'::text || (i)::text))::bytea FORMAT JSON WITH UNIQUE KEYS RETURNING text) OVER (?), ((i % 2))
- -> Sort
- Output: ((i % 2)), i
- Sort Key: ((i.i % 2))
- -> Function Scan on pg_catalog.generate_series i
- Output: (i % 2), i
- Function Call: generate_series(1, 5)
-(8 rows)
-
-CREATE VIEW json_objectagg_view AS
-SELECT JSON_OBJECTAGG(i: ('111' || i)::bytea FORMAT JSON WITH UNIQUE RETURNING text) FILTER (WHERE i > 3)
-FROM generate_series(1,5) i;
-\sv json_objectagg_view
-CREATE OR REPLACE VIEW public.json_objectagg_view AS
- SELECT JSON_OBJECTAGG(i.i : ('111'::text || i.i)::bytea FORMAT JSON WITH UNIQUE KEYS RETURNING text) FILTER (WHERE i.i > 3) AS "json_objectagg"
- FROM generate_series(1, 5) i(i)
-DROP VIEW json_objectagg_view;
--- Test JSON_ARRAYAGG deparsing
-EXPLAIN (VERBOSE, COSTS OFF)
-SELECT JSON_ARRAYAGG(('111' || i)::bytea FORMAT JSON NULL ON NULL RETURNING text) FILTER (WHERE i > 3)
-FROM generate_series(1,5) i;
- QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
- Aggregate
- Output: JSON_ARRAYAGG((('111'::text || (i)::text))::bytea FORMAT JSON NULL ON NULL RETURNING text) FILTER (WHERE (i > 3))
- -> Function Scan on pg_catalog.generate_series i
- Output: i
- Function Call: generate_series(1, 5)
-(5 rows)
-
-EXPLAIN (VERBOSE, COSTS OFF)
-SELECT JSON_ARRAYAGG(('111' || i)::bytea FORMAT JSON NULL ON NULL RETURNING text) OVER (PARTITION BY i % 2)
-FROM generate_series(1,5) i;
- QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
- WindowAgg
- Output: JSON_ARRAYAGG((('111'::text || (i)::text))::bytea FORMAT JSON NULL ON NULL RETURNING text) OVER (?), ((i % 2))
- -> Sort
- Output: ((i % 2)), i
- Sort Key: ((i.i % 2))
- -> Function Scan on pg_catalog.generate_series i
- Output: (i % 2), i
- Function Call: generate_series(1, 5)
-(8 rows)
-
-CREATE VIEW json_arrayagg_view AS
-SELECT JSON_ARRAYAGG(('111' || i)::bytea FORMAT JSON NULL ON NULL RETURNING text) FILTER (WHERE i > 3)
-FROM generate_series(1,5) i;
-\sv json_arrayagg_view
-CREATE OR REPLACE VIEW public.json_arrayagg_view AS
- SELECT JSON_ARRAYAGG(('111'::text || i.i)::bytea FORMAT JSON NULL ON NULL RETURNING text) FILTER (WHERE i.i > 3) AS "json_arrayagg"
- FROM generate_series(1, 5) i(i)
-DROP VIEW json_arrayagg_view;
--- Test JSON_ARRAY(subquery) deparsing
-EXPLAIN (VERBOSE, COSTS OFF)
-SELECT JSON_ARRAY(SELECT i FROM (VALUES (1), (2), (NULL), (4)) foo(i) RETURNING jsonb);
- QUERY PLAN
----------------------------------------------------------------------
- Result
- Output: $0
- InitPlan 1 (returns $0)
- -> Aggregate
- Output: JSON_ARRAYAGG("*VALUES*".column1 RETURNING jsonb)
- -> Values Scan on "*VALUES*"
- Output: "*VALUES*".column1
-(7 rows)
-
-CREATE VIEW json_array_subquery_view AS
-SELECT JSON_ARRAY(SELECT i FROM (VALUES (1), (2), (NULL), (4)) foo(i) RETURNING jsonb);
-\sv json_array_subquery_view
-CREATE OR REPLACE VIEW public.json_array_subquery_view AS
- SELECT ( SELECT JSON_ARRAYAGG(q.a RETURNING jsonb) AS "json_arrayagg"
- FROM ( SELECT foo.i
- FROM ( VALUES (1), (2), (NULL::integer), (4)) foo(i)) q(a)) AS "json_array"
-DROP VIEW json_array_subquery_view;
--- IS JSON predicate
-SELECT NULL IS JSON;
- ?column?
-----------
-
-(1 row)
-
-SELECT NULL IS NOT JSON;
- ?column?
-----------
-
-(1 row)
-
-SELECT NULL::json IS JSON;
- ?column?
-----------
-
-(1 row)
-
-SELECT NULL::jsonb IS JSON;
- ?column?
-----------
-
-(1 row)
-
-SELECT NULL::text IS JSON;
- ?column?
-----------
-
-(1 row)
-
-SELECT NULL::bytea IS JSON;
- ?column?
-----------
-
-(1 row)
-
-SELECT NULL::int IS JSON;
-ERROR: cannot use type integer in IS JSON predicate
-SELECT '' IS JSON;
- ?column?
-----------
- f
-(1 row)
-
-SELECT bytea '\x00' IS JSON;
-ERROR: invalid byte sequence for encoding "UTF8": 0x00
-CREATE TABLE test_is_json (js text);
-INSERT INTO test_is_json VALUES
- (NULL),
- (''),
- ('123'),
- ('"aaa "'),
- ('true'),
- ('null'),
- ('[]'),
- ('[1, "2", {}]'),
- ('{}'),
- ('{ "a": 1, "b": null }'),
- ('{ "a": 1, "a": null }'),
- ('{ "a": 1, "b": [{ "a": 1 }, { "a": 2 }] }'),
- ('{ "a": 1, "b": [{ "a": 1, "b": 0, "a": 2 }] }'),
- ('aaa'),
- ('{a:1}'),
- ('["a",]');
-SELECT
- js,
- js IS JSON "IS JSON",
- js IS NOT JSON "IS NOT JSON",
- js IS JSON VALUE "IS VALUE",
- js IS JSON OBJECT "IS OBJECT",
- js IS JSON ARRAY "IS ARRAY",
- js IS JSON SCALAR "IS SCALAR",
- js IS JSON WITHOUT UNIQUE KEYS "WITHOUT UNIQUE",
- js IS JSON WITH UNIQUE KEYS "WITH UNIQUE"
-FROM
- test_is_json;
- js | IS JSON | IS NOT JSON | IS VALUE | IS OBJECT | IS ARRAY | IS SCALAR | WITHOUT UNIQUE | WITH UNIQUE
------------------------------------------------+---------+-------------+----------+-----------+----------+-----------+----------------+-------------
- | | | | | | | |
- | f | t | f | f | f | f | f | f
- 123 | t | f | t | f | f | t | t | t
- "aaa " | t | f | t | f | f | t | t | t
- true | t | f | t | f | f | t | t | t
- null | t | f | t | f | f | t | t | t
- [] | t | f | t | f | t | f | t | t
- [1, "2", {}] | t | f | t | f | t | f | t | t
- {} | t | f | t | t | f | f | t | t
- { "a": 1, "b": null } | t | f | t | t | f | f | t | t
- { "a": 1, "a": null } | t | f | t | t | f | f | t | f
- { "a": 1, "b": [{ "a": 1 }, { "a": 2 }] } | t | f | t | t | f | f | t | t
- { "a": 1, "b": [{ "a": 1, "b": 0, "a": 2 }] } | t | f | t | t | f | f | t | f
- aaa | f | t | f | f | f | f | f | f
- {a:1} | f | t | f | f | f | f | f | f
- ["a",] | f | t | f | f | f | f | f | f
-(16 rows)
-
-SELECT
- js,
- js IS JSON "IS JSON",
- js IS NOT JSON "IS NOT JSON",
- js IS JSON VALUE "IS VALUE",
- js IS JSON OBJECT "IS OBJECT",
- js IS JSON ARRAY "IS ARRAY",
- js IS JSON SCALAR "IS SCALAR",
- js IS JSON WITHOUT UNIQUE KEYS "WITHOUT UNIQUE",
- js IS JSON WITH UNIQUE KEYS "WITH UNIQUE"
-FROM
- (SELECT js::json FROM test_is_json WHERE js IS JSON) foo(js);
- js | IS JSON | IS NOT JSON | IS VALUE | IS OBJECT | IS ARRAY | IS SCALAR | WITHOUT UNIQUE | WITH UNIQUE
------------------------------------------------+---------+-------------+----------+-----------+----------+-----------+----------------+-------------
- 123 | t | f | t | f | f | t | t | t
- "aaa " | t | f | t | f | f | t | t | t
- true | t | f | t | f | f | t | t | t
- null | t | f | t | f | f | t | t | t
- [] | t | f | t | f | t | f | t | t
- [1, "2", {}] | t | f | t | f | t | f | t | t
- {} | t | f | t | t | f | f | t | t
- { "a": 1, "b": null } | t | f | t | t | f | f | t | t
- { "a": 1, "a": null } | t | f | t | t | f | f | t | f
- { "a": 1, "b": [{ "a": 1 }, { "a": 2 }] } | t | f | t | t | f | f | t | t
- { "a": 1, "b": [{ "a": 1, "b": 0, "a": 2 }] } | t | f | t | t | f | f | t | f
-(11 rows)
-
-SELECT
- js0,
- js IS JSON "IS JSON",
- js IS NOT JSON "IS NOT JSON",
- js IS JSON VALUE "IS VALUE",
- js IS JSON OBJECT "IS OBJECT",
- js IS JSON ARRAY "IS ARRAY",
- js IS JSON SCALAR "IS SCALAR",
- js IS JSON WITHOUT UNIQUE KEYS "WITHOUT UNIQUE",
- js IS JSON WITH UNIQUE KEYS "WITH UNIQUE"
-FROM
- (SELECT js, js::bytea FROM test_is_json WHERE js IS JSON) foo(js0, js);
- js0 | IS JSON | IS NOT JSON | IS VALUE | IS OBJECT | IS ARRAY | IS SCALAR | WITHOUT UNIQUE | WITH UNIQUE
------------------------------------------------+---------+-------------+----------+-----------+----------+-----------+----------------+-------------
- 123 | t | f | t | f | f | t | t | t
- "aaa " | t | f | t | f | f | t | t | t
- true | t | f | t | f | f | t | t | t
- null | t | f | t | f | f | t | t | t
- [] | t | f | t | f | t | f | t | t
- [1, "2", {}] | t | f | t | f | t | f | t | t
- {} | t | f | t | t | f | f | t | t
- { "a": 1, "b": null } | t | f | t | t | f | f | t | t
- { "a": 1, "a": null } | t | f | t | t | f | f | t | f
- { "a": 1, "b": [{ "a": 1 }, { "a": 2 }] } | t | f | t | t | f | f | t | t
- { "a": 1, "b": [{ "a": 1, "b": 0, "a": 2 }] } | t | f | t | t | f | f | t | f
-(11 rows)
-
-SELECT
- js,
- js IS JSON "IS JSON",
- js IS NOT JSON "IS NOT JSON",
- js IS JSON VALUE "IS VALUE",
- js IS JSON OBJECT "IS OBJECT",
- js IS JSON ARRAY "IS ARRAY",
- js IS JSON SCALAR "IS SCALAR",
- js IS JSON WITHOUT UNIQUE KEYS "WITHOUT UNIQUE",
- js IS JSON WITH UNIQUE KEYS "WITH UNIQUE"
-FROM
- (SELECT js::jsonb FROM test_is_json WHERE js IS JSON) foo(js);
- js | IS JSON | IS NOT JSON | IS VALUE | IS OBJECT | IS ARRAY | IS SCALAR | WITHOUT UNIQUE | WITH UNIQUE
--------------------------------------+---------+-------------+----------+-----------+----------+-----------+----------------+-------------
- 123 | t | f | t | f | f | t | t | t
- "aaa " | t | f | t | f | f | t | t | t
- true | t | f | t | f | f | t | t | t
- null | t | f | t | f | f | t | t | t
- [] | t | f | t | f | t | f | t | t
- [1, "2", {}] | t | f | t | f | t | f | t | t
- {} | t | f | t | t | f | f | t | t
- {"a": 1, "b": null} | t | f | t | t | f | f | t | t
- {"a": null} | t | f | t | t | f | f | t | t
- {"a": 1, "b": [{"a": 1}, {"a": 2}]} | t | f | t | t | f | f | t | t
- {"a": 1, "b": [{"a": 2, "b": 0}]} | t | f | t | t | f | f | t | t
-(11 rows)
-
--- Test IS JSON deparsing
-EXPLAIN (VERBOSE, COSTS OFF)
-SELECT '1' IS JSON AS "any", ('1' || i) IS JSON SCALAR AS "scalar", '[]' IS NOT JSON ARRAY AS "array", '{}' IS JSON OBJECT WITH UNIQUE AS "object" FROM generate_series(1, 3) i;
- QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------
- Function Scan on pg_catalog.generate_series i
- Output: ('1'::text IS JSON), (('1'::text || (i)::text) IS JSON SCALAR), (NOT ('[]'::text IS JSON ARRAY)), ('{}'::text IS JSON OBJECT WITH UNIQUE KEYS)
- Function Call: generate_series(1, 3)
-(3 rows)
-
-CREATE VIEW is_json_view AS
-SELECT '1' IS JSON AS "any", ('1' || i) IS JSON SCALAR AS "scalar", '[]' IS NOT JSON ARRAY AS "array", '{}' IS JSON OBJECT WITH UNIQUE AS "object" FROM generate_series(1, 3) i;
-\sv is_json_view
-CREATE OR REPLACE VIEW public.is_json_view AS
- SELECT '1'::text IS JSON AS "any",
- ('1'::text || i.i) IS JSON SCALAR AS scalar,
- NOT '[]'::text IS JSON ARRAY AS "array",
- '{}'::text IS JSON OBJECT WITH UNIQUE KEYS AS object
- FROM generate_series(1, 3) i(i)
-DROP VIEW is_json_view;
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index 103e11483d2..9f644a0c1b2 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -111,7 +111,7 @@ test: select_views portals_p2 foreign_key cluster dependency guc bitmapops combo
# ----------
# Another group of parallel tests (JSON related)
# ----------
-test: json jsonb json_encoding jsonpath jsonpath_encoding jsonb_jsonpath sqljson json_sqljson jsonb_sqljson
+test: json jsonb json_encoding jsonpath jsonpath_encoding jsonb_jsonpath
# ----------
# Another group of parallel tests
diff --git a/src/test/regress/sql/json_sqljson.sql b/src/test/regress/sql/json_sqljson.sql
deleted file mode 100644
index df4a430d885..00000000000
--- a/src/test/regress/sql/json_sqljson.sql
+++ /dev/null
@@ -1,15 +0,0 @@
--- JSON_EXISTS
-
-SELECT JSON_EXISTS(NULL FORMAT JSON, '$');
-
--- JSON_VALUE
-
-SELECT JSON_VALUE(NULL FORMAT JSON, '$');
-
--- JSON_QUERY
-
-SELECT JSON_QUERY(NULL FORMAT JSON, '$');
-
--- JSON_TABLE
-
-SELECT * FROM JSON_TABLE(NULL FORMAT JSON, '$' COLUMNS (foo text));
diff --git a/src/test/regress/sql/jsonb_sqljson.sql b/src/test/regress/sql/jsonb_sqljson.sql
deleted file mode 100644
index fff25374808..00000000000
--- a/src/test/regress/sql/jsonb_sqljson.sql
+++ /dev/null
@@ -1,977 +0,0 @@
--- JSON_EXISTS
-
-SELECT JSON_EXISTS(NULL::jsonb, '$');
-
-SELECT JSON_EXISTS(jsonb '[]', '$');
-SELECT JSON_EXISTS(JSON_OBJECT(RETURNING jsonb), '$');
-
-SELECT JSON_EXISTS(jsonb '1', '$');
-SELECT JSON_EXISTS(jsonb 'null', '$');
-SELECT JSON_EXISTS(jsonb '[]', '$');
-
-SELECT JSON_EXISTS(jsonb '1', '$.a');
-SELECT JSON_EXISTS(jsonb '1', 'strict $.a');
-SELECT JSON_EXISTS(jsonb '1', 'strict $.a' ERROR ON ERROR);
-SELECT JSON_EXISTS(jsonb 'null', '$.a');
-SELECT JSON_EXISTS(jsonb '[]', '$.a');
-SELECT JSON_EXISTS(jsonb '[1, "aaa", {"a": 1}]', 'strict $.a');
-SELECT JSON_EXISTS(jsonb '[1, "aaa", {"a": 1}]', 'lax $.a');
-SELECT JSON_EXISTS(jsonb '{}', '$.a');
-SELECT JSON_EXISTS(jsonb '{"b": 1, "a": 2}', '$.a');
-
-SELECT JSON_EXISTS(jsonb '1', '$.a.b');
-SELECT JSON_EXISTS(jsonb '{"a": {"b": 1}}', '$.a.b');
-SELECT JSON_EXISTS(jsonb '{"a": 1, "b": 2}', '$.a.b');
-
-SELECT JSON_EXISTS(jsonb '{"a": 1, "b": 2}', '$.* ? (@ > $x)' PASSING 1 AS x);
-SELECT JSON_EXISTS(jsonb '{"a": 1, "b": 2}', '$.* ? (@ > $x)' PASSING '1' AS x);
-SELECT JSON_EXISTS(jsonb '{"a": 1, "b": 2}', '$.* ? (@ > $x && @ < $y)' PASSING 0 AS x, 2 AS y);
-SELECT JSON_EXISTS(jsonb '{"a": 1, "b": 2}', '$.* ? (@ > $x && @ < $y)' PASSING 0 AS x, 1 AS y);
-
--- extension: boolean expressions
-SELECT JSON_EXISTS(jsonb '1', '$ > 2');
-SELECT JSON_EXISTS(jsonb '1', '$.a > 2' ERROR ON ERROR);
-
--- extension: RETURNING clause
-SELECT JSON_EXISTS(jsonb '1', '$[0]' RETURNING bool);
-SELECT JSON_EXISTS(jsonb '1', '$[1]' RETURNING bool);
-SELECT JSON_EXISTS(jsonb '1', '$[0]' RETURNING int);
-SELECT JSON_EXISTS(jsonb '1', '$[1]' RETURNING int);
-SELECT JSON_EXISTS(jsonb '1', '$[0]' RETURNING text);
-SELECT JSON_EXISTS(jsonb '1', '$[1]' RETURNING text);
-SELECT JSON_EXISTS(jsonb '1', 'strict $[1]' RETURNING text FALSE ON ERROR);
-SELECT JSON_EXISTS(jsonb '1', '$[0]' RETURNING jsonb);
-SELECT JSON_EXISTS(jsonb '1', '$[0]' RETURNING float4);
-
-
--- JSON_VALUE
-
-SELECT JSON_VALUE(NULL::jsonb, '$');
-
-SELECT JSON_VALUE(jsonb 'null', '$');
-SELECT JSON_VALUE(jsonb 'null', '$' RETURNING int);
-
-SELECT JSON_VALUE(jsonb 'true', '$');
-SELECT JSON_VALUE(jsonb 'true', '$' RETURNING bool);
-
-SELECT JSON_VALUE(jsonb '123', '$');
-SELECT JSON_VALUE(jsonb '123', '$' RETURNING int) + 234;
-SELECT JSON_VALUE(jsonb '123', '$' RETURNING text);
-/* jsonb bytea ??? */
-SELECT JSON_VALUE(jsonb '123', '$' RETURNING bytea ERROR ON ERROR);
-
-SELECT JSON_VALUE(jsonb '1.23', '$');
-SELECT JSON_VALUE(jsonb '1.23', '$' RETURNING int);
-SELECT JSON_VALUE(jsonb '"1.23"', '$' RETURNING numeric);
-SELECT JSON_VALUE(jsonb '"1.23"', '$' RETURNING int ERROR ON ERROR);
-
-SELECT JSON_VALUE(jsonb '"aaa"', '$');
-SELECT JSON_VALUE(jsonb '"aaa"', '$' RETURNING text);
-SELECT JSON_VALUE(jsonb '"aaa"', '$' RETURNING char(5));
-SELECT JSON_VALUE(jsonb '"aaa"', '$' RETURNING char(2));
-SELECT JSON_VALUE(jsonb '"aaa"', '$' RETURNING json);
-SELECT JSON_VALUE(jsonb '"aaa"', '$' RETURNING jsonb);
-SELECT JSON_VALUE(jsonb '"aaa"', '$' RETURNING json ERROR ON ERROR);
-SELECT JSON_VALUE(jsonb '"aaa"', '$' RETURNING jsonb ERROR ON ERROR);
-SELECT JSON_VALUE(jsonb '"\"aaa\""', '$' RETURNING json);
-SELECT JSON_VALUE(jsonb '"\"aaa\""', '$' RETURNING jsonb);
-SELECT JSON_VALUE(jsonb '"aaa"', '$' RETURNING int);
-SELECT JSON_VALUE(jsonb '"aaa"', '$' RETURNING int ERROR ON ERROR);
-SELECT JSON_VALUE(jsonb '"aaa"', '$' RETURNING int DEFAULT 111 ON ERROR);
-SELECT JSON_VALUE(jsonb '"123"', '$' RETURNING int) + 234;
-
-SELECT JSON_VALUE(jsonb '"2017-02-20"', '$' RETURNING date) + 9;
-
--- Test NULL checks execution in domain types
-CREATE DOMAIN sqljsonb_int_not_null AS int NOT NULL;
-SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_int_not_null);
-SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_int_not_null NULL ON ERROR);
-SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_int_not_null DEFAULT NULL ON ERROR);
-
-SELECT JSON_VALUE(jsonb '[]', '$');
-SELECT JSON_VALUE(jsonb '[]', '$' ERROR ON ERROR);
-SELECT JSON_VALUE(jsonb '{}', '$');
-SELECT JSON_VALUE(jsonb '{}', '$' ERROR ON ERROR);
-
-SELECT JSON_VALUE(jsonb '1', '$.a');
-SELECT JSON_VALUE(jsonb '1', 'strict $.a' ERROR ON ERROR);
-SELECT JSON_VALUE(jsonb '1', 'strict $.a' DEFAULT 'error' ON ERROR);
-SELECT JSON_VALUE(jsonb '1', 'lax $.a' ERROR ON ERROR);
-SELECT JSON_VALUE(jsonb '1', 'lax $.a' ERROR ON EMPTY ERROR ON ERROR);
-SELECT JSON_VALUE(jsonb '1', 'strict $.a' DEFAULT 2 ON ERROR);
-SELECT JSON_VALUE(jsonb '1', 'lax $.a' DEFAULT 2 ON ERROR);
-SELECT JSON_VALUE(jsonb '1', 'lax $.a' DEFAULT '2' ON ERROR);
-SELECT JSON_VALUE(jsonb '1', 'lax $.a' NULL ON EMPTY DEFAULT '2' ON ERROR);
-SELECT JSON_VALUE(jsonb '1', 'lax $.a' DEFAULT '2' ON EMPTY DEFAULT '3' ON ERROR);
-SELECT JSON_VALUE(jsonb '1', 'lax $.a' ERROR ON EMPTY DEFAULT '3' ON ERROR);
-
-SELECT JSON_VALUE(jsonb '[1,2]', '$[*]' ERROR ON ERROR);
-SELECT JSON_VALUE(jsonb '[1,2]', '$[*]' DEFAULT '0' ON ERROR);
-SELECT JSON_VALUE(jsonb '[" "]', '$[*]' RETURNING int ERROR ON ERROR);
-SELECT JSON_VALUE(jsonb '[" "]', '$[*]' RETURNING int DEFAULT 2 + 3 ON ERROR);
-SELECT JSON_VALUE(jsonb '["1"]', '$[*]' RETURNING int DEFAULT 2 + 3 ON ERROR);
-
-SELECT
- x,
- JSON_VALUE(
- jsonb '{"a": 1, "b": 2}',
- '$.* ? (@ > $x)' PASSING x AS x
- RETURNING int
- DEFAULT -1 ON EMPTY
- DEFAULT -2 ON ERROR
- ) y
-FROM
- generate_series(0, 2) x;
-
-SELECT JSON_VALUE(jsonb 'null', '$a' PASSING point ' (1, 2 )' AS a);
-SELECT JSON_VALUE(jsonb 'null', '$a' PASSING point ' (1, 2 )' AS a RETURNING point);
-
--- Test timestamptz passing and output
-SELECT JSON_VALUE(jsonb 'null', '$ts' PASSING timestamptz '2018-02-21 12:34:56 +10' AS ts);
-SELECT JSON_VALUE(jsonb 'null', '$ts' PASSING timestamptz '2018-02-21 12:34:56 +10' AS ts RETURNING timestamptz);
-SELECT JSON_VALUE(jsonb 'null', '$ts' PASSING timestamptz '2018-02-21 12:34:56 +10' AS ts RETURNING timestamp);
-SELECT JSON_VALUE(jsonb 'null', '$ts' PASSING timestamptz '2018-02-21 12:34:56 +10' AS ts RETURNING json);
-SELECT JSON_VALUE(jsonb 'null', '$ts' PASSING timestamptz '2018-02-21 12:34:56 +10' AS ts RETURNING jsonb);
-
--- JSON_QUERY
-
-SELECT
- JSON_QUERY(js, '$'),
- JSON_QUERY(js, '$' WITHOUT WRAPPER),
- JSON_QUERY(js, '$' WITH CONDITIONAL WRAPPER),
- JSON_QUERY(js, '$' WITH UNCONDITIONAL ARRAY WRAPPER),
- JSON_QUERY(js, '$' WITH ARRAY WRAPPER)
-FROM
- (VALUES
- (jsonb 'null'),
- ('12.3'),
- ('true'),
- ('"aaa"'),
- ('[1, null, "2"]'),
- ('{"a": 1, "b": [2]}')
- ) foo(js);
-
-SELECT
- JSON_QUERY(js, 'strict $[*]') AS "unspec",
- JSON_QUERY(js, 'strict $[*]' WITHOUT WRAPPER) AS "without",
- JSON_QUERY(js, 'strict $[*]' WITH CONDITIONAL WRAPPER) AS "with cond",
- JSON_QUERY(js, 'strict $[*]' WITH UNCONDITIONAL ARRAY WRAPPER) AS "with uncond",
- JSON_QUERY(js, 'strict $[*]' WITH ARRAY WRAPPER) AS "with"
-FROM
- (VALUES
- (jsonb '1'),
- ('[]'),
- ('[null]'),
- ('[12.3]'),
- ('[true]'),
- ('["aaa"]'),
- ('[[1, 2, 3]]'),
- ('[{"a": 1, "b": [2]}]'),
- ('[1, "2", null, [3]]')
- ) foo(js);
-
-SELECT JSON_QUERY(jsonb '"aaa"', '$' RETURNING text);
-SELECT JSON_QUERY(jsonb '"aaa"', '$' RETURNING text KEEP QUOTES);
-SELECT JSON_QUERY(jsonb '"aaa"', '$' RETURNING text KEEP QUOTES ON SCALAR STRING);
-SELECT JSON_QUERY(jsonb '"aaa"', '$' RETURNING text OMIT QUOTES);
-SELECT JSON_QUERY(jsonb '"aaa"', '$' RETURNING text OMIT QUOTES ON SCALAR STRING);
-SELECT JSON_QUERY(jsonb '"aaa"', '$' OMIT QUOTES ERROR ON ERROR);
-SELECT JSON_QUERY(jsonb '"aaa"', '$' RETURNING json OMIT QUOTES ERROR ON ERROR);
-SELECT JSON_QUERY(jsonb '"aaa"', '$' RETURNING bytea FORMAT JSON OMIT QUOTES ERROR ON ERROR);
-
--- QUOTES behavior should not be specified when WITH WRAPPER used:
--- Should fail
-SELECT JSON_QUERY(jsonb '[1]', '$' WITH WRAPPER OMIT QUOTES);
-SELECT JSON_QUERY(jsonb '[1]', '$' WITH WRAPPER KEEP QUOTES);
-SELECT JSON_QUERY(jsonb '[1]', '$' WITH CONDITIONAL WRAPPER KEEP QUOTES);
-SELECT JSON_QUERY(jsonb '[1]', '$' WITH CONDITIONAL WRAPPER OMIT QUOTES);
--- Should succeed
-SELECT JSON_QUERY(jsonb '[1]', '$' WITHOUT WRAPPER OMIT QUOTES);
-SELECT JSON_QUERY(jsonb '[1]', '$' WITHOUT WRAPPER KEEP QUOTES);
-
-SELECT JSON_QUERY(jsonb '[]', '$[*]');
-SELECT JSON_QUERY(jsonb '[]', '$[*]' NULL ON EMPTY);
-SELECT JSON_QUERY(jsonb '[]', '$[*]' EMPTY ON EMPTY);
-SELECT JSON_QUERY(jsonb '[]', '$[*]' EMPTY ARRAY ON EMPTY);
-SELECT JSON_QUERY(jsonb '[]', '$[*]' EMPTY OBJECT ON EMPTY);
-SELECT JSON_QUERY(jsonb '[]', '$[*]' ERROR ON EMPTY);
-SELECT JSON_QUERY(jsonb '[]', '$[*]' DEFAULT '"empty"' ON EMPTY);
-
-SELECT JSON_QUERY(jsonb '[]', '$[*]' ERROR ON EMPTY NULL ON ERROR);
-SELECT JSON_QUERY(jsonb '[]', '$[*]' ERROR ON EMPTY EMPTY ARRAY ON ERROR);
-SELECT JSON_QUERY(jsonb '[]', '$[*]' ERROR ON EMPTY EMPTY OBJECT ON ERROR);
-SELECT JSON_QUERY(jsonb '[]', '$[*]' ERROR ON EMPTY ERROR ON ERROR);
-SELECT JSON_QUERY(jsonb '[]', '$[*]' ERROR ON ERROR);
-
-SELECT JSON_QUERY(jsonb '[1,2]', '$[*]' ERROR ON ERROR);
-SELECT JSON_QUERY(jsonb '[1,2]', '$[*]' DEFAULT '"empty"' ON ERROR);
-
-SELECT JSON_QUERY(jsonb '[1,2]', '$' RETURNING json);
-SELECT JSON_QUERY(jsonb '[1,2]', '$' RETURNING json FORMAT JSON);
-SELECT JSON_QUERY(jsonb '[1,2]', '$' RETURNING jsonb);
-SELECT JSON_QUERY(jsonb '[1,2]', '$' RETURNING jsonb FORMAT JSON);
-SELECT JSON_QUERY(jsonb '[1,2]', '$' RETURNING text);
-SELECT JSON_QUERY(jsonb '[1,2]', '$' RETURNING char(10));
-SELECT JSON_QUERY(jsonb '[1,2]', '$' RETURNING char(3));
-SELECT JSON_QUERY(jsonb '[1,2]', '$' RETURNING text FORMAT JSON);
-SELECT JSON_QUERY(jsonb '[1,2]', '$' RETURNING bytea);
-SELECT JSON_QUERY(jsonb '[1,2]', '$' RETURNING bytea FORMAT JSON);
-
-SELECT JSON_QUERY(jsonb '[1,2]', '$[*]' RETURNING bytea EMPTY OBJECT ON ERROR);
-SELECT JSON_QUERY(jsonb '[1,2]', '$[*]' RETURNING bytea FORMAT JSON EMPTY OBJECT ON ERROR);
-SELECT JSON_QUERY(jsonb '[1,2]', '$[*]' RETURNING json EMPTY OBJECT ON ERROR);
-SELECT JSON_QUERY(jsonb '[1,2]', '$[*]' RETURNING jsonb EMPTY OBJECT ON ERROR);
-
-SELECT
- x, y,
- JSON_QUERY(
- jsonb '[1,2,3,4,5,null]',
- '$[*] ? (@ >= $x && @ <= $y)'
- PASSING x AS x, y AS y
- WITH CONDITIONAL WRAPPER
- EMPTY ARRAY ON EMPTY
- ) list
-FROM
- generate_series(0, 4) x,
- generate_series(0, 4) y;
-
--- Extension: record types returning
-CREATE TYPE sqljsonb_rec AS (a int, t text, js json, jb jsonb, jsa json[]);
-CREATE TYPE sqljsonb_reca AS (reca sqljsonb_rec[]);
-
-SELECT JSON_QUERY(jsonb '[{"a": 1, "b": "foo", "t": "aaa", "js": [1, "2", {}], "jb": {"x": [1, "2", {}]}}, {"a": 2}]', '$[0]' RETURNING sqljsonb_rec);
-SELECT * FROM unnest((JSON_QUERY(jsonb '{"jsa": [{"a": 1, "b": ["foo"]}, {"a": 2, "c": {}}, 123]}', '$' RETURNING sqljsonb_rec)).jsa);
-SELECT * FROM unnest((JSON_QUERY(jsonb '{"reca": [{"a": 1, "t": ["foo", []]}, {"a": 2, "jb": [{}, true]}]}', '$' RETURNING sqljsonb_reca)).reca);
-
--- Extension: array types returning
-SELECT JSON_QUERY(jsonb '[1,2,null,"3"]', '$[*]' RETURNING int[] WITH WRAPPER);
-SELECT * FROM unnest(JSON_QUERY(jsonb '[{"a": 1, "t": ["foo", []]}, {"a": 2, "jb": [{}, true]}]', '$' RETURNING sqljsonb_rec[]));
-
--- Extension: domain types returning
-SELECT JSON_QUERY(jsonb '{"a": 1}', '$.a' RETURNING sqljsonb_int_not_null);
-SELECT JSON_QUERY(jsonb '{"a": 1}', '$.b' RETURNING sqljsonb_int_not_null);
-
--- Test timestamptz passing and output
-SELECT JSON_QUERY(jsonb 'null', '$ts' PASSING timestamptz '2018-02-21 12:34:56 +10' AS ts);
-SELECT JSON_QUERY(jsonb 'null', '$ts' PASSING timestamptz '2018-02-21 12:34:56 +10' AS ts RETURNING json);
-SELECT JSON_QUERY(jsonb 'null', '$ts' PASSING timestamptz '2018-02-21 12:34:56 +10' AS ts RETURNING jsonb);
-
--- Test constraints
-
-CREATE TABLE test_jsonb_constraints (
- js text,
- i int,
- x jsonb DEFAULT JSON_QUERY(jsonb '[1,2]', '$[*]' WITH WRAPPER)
- CONSTRAINT test_jsonb_constraint1
- CHECK (js IS JSON)
- CONSTRAINT test_jsonb_constraint2
- CHECK (JSON_EXISTS(js::jsonb, '$.a' PASSING i + 5 AS int, i::text AS txt, array[1,2,3] as arr))
- CONSTRAINT test_jsonb_constraint3
- CHECK (JSON_VALUE(js::jsonb, '$.a' RETURNING int DEFAULT ('12' || i)::int ON EMPTY ERROR ON ERROR) > i)
- CONSTRAINT test_jsonb_constraint4
- CHECK (JSON_QUERY(js::jsonb, '$.a' WITH CONDITIONAL WRAPPER EMPTY OBJECT ON ERROR) < jsonb '[10]')
- CONSTRAINT test_jsonb_constraint5
- CHECK (JSON_QUERY(js::jsonb, '$.a' RETURNING char(5) OMIT QUOTES EMPTY ARRAY ON EMPTY) > 'a' COLLATE "C")
- CONSTRAINT test_jsonb_constraint6
- CHECK (JSON_EXISTS(js::jsonb, 'strict $.a' RETURNING int TRUE ON ERROR) < 2)
-);
-
-\d test_jsonb_constraints
-
-SELECT check_clause
-FROM information_schema.check_constraints
-WHERE constraint_name LIKE 'test_jsonb_constraint%'
-ORDER BY 1;
-
-SELECT pg_get_expr(adbin, adrelid)
-FROM pg_attrdef
-WHERE adrelid = 'test_jsonb_constraints'::regclass
-ORDER BY 1;
-
-INSERT INTO test_jsonb_constraints VALUES ('', 1);
-INSERT INTO test_jsonb_constraints VALUES ('1', 1);
-INSERT INTO test_jsonb_constraints VALUES ('[]');
-INSERT INTO test_jsonb_constraints VALUES ('{"b": 1}', 1);
-INSERT INTO test_jsonb_constraints VALUES ('{"a": 1}', 1);
-INSERT INTO test_jsonb_constraints VALUES ('{"a": 7}', 1);
-INSERT INTO test_jsonb_constraints VALUES ('{"a": 10}', 1);
-
-DROP TABLE test_jsonb_constraints;
-
--- Test mutabilily od query functions
-CREATE TABLE test_jsonb_mutability(js jsonb);
-CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$'));
-CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.a[0]'));
-CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.datetime()'));
-CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.a ? (@ < $.datetime())'));
-CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.a ? (@.datetime() < $.datetime())'));
-CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.a ? (@.datetime() < $.datetime("HH:MI TZH"))'));
-CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.a ? (@.datetime("HH:MI TZH") < $.datetime("HH:MI TZH"))'));
-CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.a ? (@.datetime("HH:MI") < $.datetime("YY-MM-DD HH:MI"))'));
-CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.a ? (@.datetime("HH:MI TZH") < $.datetime("YY-MM-DD HH:MI"))'));
-CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.datetime("HH:MI TZH") < $x' PASSING '12:34'::timetz AS x));
-CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.datetime("HH:MI TZH") < $y' PASSING '12:34'::timetz AS x));
-CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.datetime() < $x' PASSING '12:34'::timetz AS x));
-CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.datetime() < $x' PASSING '1234'::int AS x));
-CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.datetime() ? (@ == $x)' PASSING '12:34'::time AS x));
-CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$.datetime("YY-MM-DD") ? (@ == $x)' PASSING '2020-07-14'::date AS x));
-CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$[1, $.a ? (@.datetime() == $x)]' PASSING '12:34'::time AS x));
-CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$[1, 0 to $.a ? (@.datetime() == $x)]' PASSING '12:34'::time AS x));
-CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$[1, $.a ? (@.datetime("HH:MI") == $x)]' PASSING '12:34'::time AS x));
-DROP TABLE test_jsonb_mutability;
-
--- JSON_TABLE
-
--- Should fail (JSON_TABLE can be used only in FROM clause)
-SELECT JSON_TABLE('[]', '$');
-
--- Should fail (no columns)
-SELECT * FROM JSON_TABLE(NULL, '$' COLUMNS ());
-
-SELECT * FROM JSON_TABLE (NULL::jsonb, '$' COLUMNS (v1 timestamp)) AS f (v1, v2);
-
--- NULL => empty table
-SELECT * FROM JSON_TABLE(NULL::jsonb, '$' COLUMNS (foo int)) bar;
-
---
-SELECT * FROM JSON_TABLE(jsonb '123', '$'
- COLUMNS (item int PATH '$', foo int)) bar;
-
--- JSON_TABLE: basic functionality
-CREATE DOMAIN jsonb_test_domain AS text CHECK (value <> 'foo');
-
-SELECT *
-FROM
- (VALUES
- ('1'),
- ('[]'),
- ('{}'),
- ('[1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""]')
- ) vals(js)
- LEFT OUTER JOIN
--- JSON_TABLE is implicitly lateral
- JSON_TABLE(
- vals.js::jsonb, 'lax $[*]'
- COLUMNS (
- id FOR ORDINALITY,
- id2 FOR ORDINALITY, -- allowed additional ordinality columns
- "int" int PATH '$',
- "text" text PATH '$',
- "char(4)" char(4) PATH '$',
- "bool" bool PATH '$',
- "numeric" numeric PATH '$',
- "domain" jsonb_test_domain PATH '$',
- js json PATH '$',
- jb jsonb PATH '$',
- jst text FORMAT JSON PATH '$',
- jsc char(4) FORMAT JSON PATH '$',
- jsv varchar(4) FORMAT JSON PATH '$',
- jsb jsonb FORMAT JSON PATH '$',
- jsbq jsonb FORMAT JSON PATH '$' OMIT QUOTES,
- aaa int, -- implicit path '$."aaa"',
- aaa1 int PATH '$.aaa',
- exists1 bool EXISTS PATH '$.aaa',
- exists2 int EXISTS PATH '$.aaa',
- exists3 int EXISTS PATH 'strict $.aaa' UNKNOWN ON ERROR,
- exists4 text EXISTS PATH 'strict $.aaa' FALSE ON ERROR,
-
- js2 json PATH '$',
- jsb2w jsonb PATH '$' WITH WRAPPER,
- jsb2q jsonb PATH '$' OMIT QUOTES,
- ia int[] PATH '$',
- ta text[] PATH '$',
- jba jsonb[] PATH '$'
- )
- ) jt
- ON true;
-
--- JSON_TABLE: Test backward parsing
-
-CREATE VIEW jsonb_table_view AS
-SELECT * FROM
- JSON_TABLE(
- jsonb 'null', 'lax $[*]' PASSING 1 + 2 AS a, json '"foo"' AS "b c"
- COLUMNS (
- id FOR ORDINALITY,
- id2 FOR ORDINALITY, -- allowed additional ordinality columns
- "int" int PATH '$',
- "text" text PATH '$',
- "char(4)" char(4) PATH '$',
- "bool" bool PATH '$',
- "numeric" numeric PATH '$',
- "domain" jsonb_test_domain PATH '$',
- js json PATH '$',
- jb jsonb PATH '$',
- jst text FORMAT JSON PATH '$',
- jsc char(4) FORMAT JSON PATH '$',
- jsv varchar(4) FORMAT JSON PATH '$',
- jsb jsonb FORMAT JSON PATH '$',
- jsbq jsonb FORMAT JSON PATH '$' OMIT QUOTES,
- aaa int, -- implicit path '$."aaa"',
- aaa1 int PATH '$.aaa',
- exists1 bool EXISTS PATH '$.aaa',
- exists2 int EXISTS PATH '$.aaa' TRUE ON ERROR,
- exists3 text EXISTS PATH 'strict $.aaa' UNKNOWN ON ERROR,
-
- js2 json PATH '$',
- jsb2w jsonb PATH '$' WITH WRAPPER,
- jsb2q jsonb PATH '$' OMIT QUOTES,
- ia int[] PATH '$',
- ta text[] PATH '$',
- jba jsonb[] PATH '$',
-
- NESTED PATH '$[1]' AS p1 COLUMNS (
- a1 int,
- NESTED PATH '$[*]' AS "p1 1" COLUMNS (
- a11 text
- ),
- b1 text
- ),
- NESTED PATH '$[2]' AS p2 COLUMNS (
- NESTED PATH '$[*]' AS "p2:1" COLUMNS (
- a21 text
- ),
- NESTED PATH '$[*]' AS p22 COLUMNS (
- a22 text
- )
- )
- )
- );
-
-\sv jsonb_table_view
-
-EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM jsonb_table_view;
-
-DROP VIEW jsonb_table_view;
-DROP DOMAIN jsonb_test_domain;
-
--- JSON_TABLE: ON EMPTY/ON ERROR behavior
-SELECT *
-FROM
- (VALUES ('1'), ('"err"')) vals(js),
- JSON_TABLE(vals.js::jsonb, '$' COLUMNS (a int PATH '$')) jt;
-
-SELECT *
-FROM
- (VALUES ('1'), ('"err"')) vals(js)
- LEFT OUTER JOIN
- JSON_TABLE(vals.js::jsonb, '$' COLUMNS (a int PATH '$') ERROR ON ERROR) jt
- ON true;
-
-SELECT *
-FROM
- (VALUES ('1'), ('"err"')) vals(js)
- LEFT OUTER JOIN
- JSON_TABLE(vals.js::jsonb, '$' COLUMNS (a int PATH '$' ERROR ON ERROR)) jt
- ON true;
-
-SELECT * FROM JSON_TABLE(jsonb '1', '$' COLUMNS (a int PATH '$.a' ERROR ON EMPTY)) jt;
-SELECT * FROM JSON_TABLE(jsonb '1', '$' COLUMNS (a int PATH 'strict $.a' ERROR ON EMPTY) ERROR ON ERROR) jt;
-SELECT * FROM JSON_TABLE(jsonb '1', '$' COLUMNS (a int PATH 'lax $.a' ERROR ON EMPTY) ERROR ON ERROR) jt;
-
-SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int PATH '$' DEFAULT 1 ON EMPTY DEFAULT 2 ON ERROR)) jt;
-SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int PATH 'strict $.a' DEFAULT 1 ON EMPTY DEFAULT 2 ON ERROR)) jt;
-SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int PATH 'lax $.a' DEFAULT 1 ON EMPTY DEFAULT 2 ON ERROR)) jt;
-
--- JSON_TABLE: EXISTS PATH types
-SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int4 EXISTS PATH '$.a'));
-SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int2 EXISTS PATH '$.a'));
-SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int8 EXISTS PATH '$.a'));
-SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a float4 EXISTS PATH '$.a'));
-SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a char(3) EXISTS PATH '$.a'));
-SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a json EXISTS PATH '$.a'));
-SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a jsonb EXISTS PATH '$.a'));
-
--- JSON_TABLE: nested paths and plans
-
--- Should fail (JSON_TABLE columns must contain explicit AS path
--- specifications if explicit PLAN clause is used)
-SELECT * FROM JSON_TABLE(
- jsonb '[]', '$' -- AS <path name> required here
- COLUMNS (
- foo int PATH '$'
- )
- PLAN DEFAULT (UNION)
-) jt;
-
-SELECT * FROM JSON_TABLE(
- jsonb '[]', '$' AS path1
- COLUMNS (
- NESTED PATH '$' COLUMNS ( -- AS <path name> required here
- foo int PATH '$'
- )
- )
- PLAN DEFAULT (UNION)
-) jt;
-
--- Should fail (column names must be distinct)
-SELECT * FROM JSON_TABLE(
- jsonb '[]', '$' AS a
- COLUMNS (
- a int
- )
-) jt;
-
-SELECT * FROM JSON_TABLE(
- jsonb '[]', '$' AS a
- COLUMNS (
- b int,
- NESTED PATH '$' AS a
- COLUMNS (
- c int
- )
- )
-) jt;
-
-SELECT * FROM JSON_TABLE(
- jsonb '[]', '$'
- COLUMNS (
- b int,
- NESTED PATH '$' AS b
- COLUMNS (
- c int
- )
- )
-) jt;
-
-SELECT * FROM JSON_TABLE(
- jsonb '[]', '$'
- COLUMNS (
- NESTED PATH '$' AS a
- COLUMNS (
- b int
- ),
- NESTED PATH '$'
- COLUMNS (
- NESTED PATH '$' AS a
- COLUMNS (
- c int
- )
- )
- )
-) jt;
-
--- JSON_TABLE: plan validation
-
-SELECT * FROM JSON_TABLE(
- jsonb 'null', '$[*]' AS p0
- COLUMNS (
- NESTED PATH '$' AS p1 COLUMNS (
- NESTED PATH '$' AS p11 COLUMNS ( foo int ),
- NESTED PATH '$' AS p12 COLUMNS ( bar int )
- ),
- NESTED PATH '$' AS p2 COLUMNS (
- NESTED PATH '$' AS p21 COLUMNS ( baz int )
- )
- )
- PLAN (p1)
-) jt;
-
-SELECT * FROM JSON_TABLE(
- jsonb 'null', '$[*]' AS p0
- COLUMNS (
- NESTED PATH '$' AS p1 COLUMNS (
- NESTED PATH '$' AS p11 COLUMNS ( foo int ),
- NESTED PATH '$' AS p12 COLUMNS ( bar int )
- ),
- NESTED PATH '$' AS p2 COLUMNS (
- NESTED PATH '$' AS p21 COLUMNS ( baz int )
- )
- )
- PLAN (p0)
-) jt;
-
-SELECT * FROM JSON_TABLE(
- jsonb 'null', '$[*]' AS p0
- COLUMNS (
- NESTED PATH '$' AS p1 COLUMNS (
- NESTED PATH '$' AS p11 COLUMNS ( foo int ),
- NESTED PATH '$' AS p12 COLUMNS ( bar int )
- ),
- NESTED PATH '$' AS p2 COLUMNS (
- NESTED PATH '$' AS p21 COLUMNS ( baz int )
- )
- )
- PLAN (p0 OUTER p3)
-) jt;
-
-SELECT * FROM JSON_TABLE(
- jsonb 'null', '$[*]' AS p0
- COLUMNS (
- NESTED PATH '$' AS p1 COLUMNS (
- NESTED PATH '$' AS p11 COLUMNS ( foo int ),
- NESTED PATH '$' AS p12 COLUMNS ( bar int )
- ),
- NESTED PATH '$' AS p2 COLUMNS (
- NESTED PATH '$' AS p21 COLUMNS ( baz int )
- )
- )
- PLAN (p0 UNION p1 UNION p11)
-) jt;
-
-SELECT * FROM JSON_TABLE(
- jsonb 'null', '$[*]' AS p0
- COLUMNS (
- NESTED PATH '$' AS p1 COLUMNS (
- NESTED PATH '$' AS p11 COLUMNS ( foo int ),
- NESTED PATH '$' AS p12 COLUMNS ( bar int )
- ),
- NESTED PATH '$' AS p2 COLUMNS (
- NESTED PATH '$' AS p21 COLUMNS ( baz int )
- )
- )
- PLAN (p0 OUTER (p1 CROSS p13))
-) jt;
-
-SELECT * FROM JSON_TABLE(
- jsonb 'null', '$[*]' AS p0
- COLUMNS (
- NESTED PATH '$' AS p1 COLUMNS (
- NESTED PATH '$' AS p11 COLUMNS ( foo int ),
- NESTED PATH '$' AS p12 COLUMNS ( bar int )
- ),
- NESTED PATH '$' AS p2 COLUMNS (
- NESTED PATH '$' AS p21 COLUMNS ( baz int )
- )
- )
- PLAN (p0 OUTER (p1 CROSS p2))
-) jt;
-
-SELECT * FROM JSON_TABLE(
- jsonb 'null', '$[*]' AS p0
- COLUMNS (
- NESTED PATH '$' AS p1 COLUMNS (
- NESTED PATH '$' AS p11 COLUMNS ( foo int ),
- NESTED PATH '$' AS p12 COLUMNS ( bar int )
- ),
- NESTED PATH '$' AS p2 COLUMNS (
- NESTED PATH '$' AS p21 COLUMNS ( baz int )
- )
- )
- PLAN (p0 OUTER ((p1 UNION p11) CROSS p2))
-) jt;
-
-SELECT * FROM JSON_TABLE(
- jsonb 'null', '$[*]' AS p0
- COLUMNS (
- NESTED PATH '$' AS p1 COLUMNS (
- NESTED PATH '$' AS p11 COLUMNS ( foo int ),
- NESTED PATH '$' AS p12 COLUMNS ( bar int )
- ),
- NESTED PATH '$' AS p2 COLUMNS (
- NESTED PATH '$' AS p21 COLUMNS ( baz int )
- )
- )
- PLAN (p0 OUTER ((p1 INNER p11) CROSS p2))
-) jt;
-
-SELECT * FROM JSON_TABLE(
- jsonb 'null', '$[*]' AS p0
- COLUMNS (
- NESTED PATH '$' AS p1 COLUMNS (
- NESTED PATH '$' AS p11 COLUMNS ( foo int ),
- NESTED PATH '$' AS p12 COLUMNS ( bar int )
- ),
- NESTED PATH '$' AS p2 COLUMNS (
- NESTED PATH '$' AS p21 COLUMNS ( baz int )
- )
- )
- PLAN (p0 OUTER ((p1 INNER (p12 CROSS p11)) CROSS p2))
-) jt;
-
-SELECT * FROM JSON_TABLE(
- jsonb 'null', 'strict $[*]' AS p0
- COLUMNS (
- NESTED PATH '$' AS p1 COLUMNS (
- NESTED PATH '$' AS p11 COLUMNS ( foo int ),
- NESTED PATH '$' AS p12 COLUMNS ( bar int )
- ),
- NESTED PATH '$' AS p2 COLUMNS (
- NESTED PATH '$' AS p21 COLUMNS ( baz int )
- )
- )
- PLAN (p0 OUTER ((p1 INNER (p12 CROSS p11)) CROSS (p2 INNER p21)))
-) jt;
-
-SELECT * FROM JSON_TABLE(
- jsonb 'null', 'strict $[*]' -- without root path name
- COLUMNS (
- NESTED PATH '$' AS p1 COLUMNS (
- NESTED PATH '$' AS p11 COLUMNS ( foo int ),
- NESTED PATH '$' AS p12 COLUMNS ( bar int )
- ),
- NESTED PATH '$' AS p2 COLUMNS (
- NESTED PATH '$' AS p21 COLUMNS ( baz int )
- )
- )
- PLAN ((p1 INNER (p12 CROSS p11)) CROSS (p2 INNER p21))
-) jt;
-
--- JSON_TABLE: plan execution
-
-CREATE TEMP TABLE jsonb_table_test (js jsonb);
-
-INSERT INTO jsonb_table_test
-VALUES (
- '[
- {"a": 1, "b": [], "c": []},
- {"a": 2, "b": [1, 2, 3], "c": [10, null, 20]},
- {"a": 3, "b": [1, 2], "c": []},
- {"x": "4", "b": [1, 2], "c": 123}
- ]'
-);
-
--- unspecified plan (outer, union)
-select
- jt.*
-from
- jsonb_table_test jtt,
- json_table (
- jtt.js,'strict $[*]' as p
- columns (
- n for ordinality,
- a int path 'lax $.a' default -1 on empty,
- nested path 'strict $.b[*]' as pb columns ( b int path '$' ),
- nested path 'strict $.c[*]' as pc columns ( c int path '$' )
- )
- ) jt;
-
--- default plan (outer, union)
-select
- jt.*
-from
- jsonb_table_test jtt,
- json_table (
- jtt.js,'strict $[*]' as p
- columns (
- n for ordinality,
- a int path 'lax $.a' default -1 on empty,
- nested path 'strict $.b[*]' as pb columns ( b int path '$' ),
- nested path 'strict $.c[*]' as pc columns ( c int path '$' )
- )
- plan default (outer, union)
- ) jt;
-
--- specific plan (p outer (pb union pc))
-select
- jt.*
-from
- jsonb_table_test jtt,
- json_table (
- jtt.js,'strict $[*]' as p
- columns (
- n for ordinality,
- a int path 'lax $.a' default -1 on empty,
- nested path 'strict $.b[*]' as pb columns ( b int path '$' ),
- nested path 'strict $.c[*]' as pc columns ( c int path '$' )
- )
- plan (p outer (pb union pc))
- ) jt;
-
--- specific plan (p outer (pc union pb))
-select
- jt.*
-from
- jsonb_table_test jtt,
- json_table (
- jtt.js,'strict $[*]' as p
- columns (
- n for ordinality,
- a int path 'lax $.a' default -1 on empty,
- nested path 'strict $.b[*]' as pb columns ( b int path '$' ),
- nested path 'strict $.c[*]' as pc columns ( c int path '$' )
- )
- plan (p outer (pc union pb))
- ) jt;
-
--- default plan (inner, union)
-select
- jt.*
-from
- jsonb_table_test jtt,
- json_table (
- jtt.js,'strict $[*]' as p
- columns (
- n for ordinality,
- a int path 'lax $.a' default -1 on empty,
- nested path 'strict $.b[*]' as pb columns ( b int path '$' ),
- nested path 'strict $.c[*]' as pc columns ( c int path '$' )
- )
- plan default (inner)
- ) jt;
-
--- specific plan (p inner (pb union pc))
-select
- jt.*
-from
- jsonb_table_test jtt,
- json_table (
- jtt.js,'strict $[*]' as p
- columns (
- n for ordinality,
- a int path 'lax $.a' default -1 on empty,
- nested path 'strict $.b[*]' as pb columns ( b int path '$' ),
- nested path 'strict $.c[*]' as pc columns ( c int path '$' )
- )
- plan (p inner (pb union pc))
- ) jt;
-
--- default plan (inner, cross)
-select
- jt.*
-from
- jsonb_table_test jtt,
- json_table (
- jtt.js,'strict $[*]' as p
- columns (
- n for ordinality,
- a int path 'lax $.a' default -1 on empty,
- nested path 'strict $.b[*]' as pb columns ( b int path '$' ),
- nested path 'strict $.c[*]' as pc columns ( c int path '$' )
- )
- plan default (cross, inner)
- ) jt;
-
--- specific plan (p inner (pb cross pc))
-select
- jt.*
-from
- jsonb_table_test jtt,
- json_table (
- jtt.js,'strict $[*]' as p
- columns (
- n for ordinality,
- a int path 'lax $.a' default -1 on empty,
- nested path 'strict $.b[*]' as pb columns ( b int path '$' ),
- nested path 'strict $.c[*]' as pc columns ( c int path '$' )
- )
- plan (p inner (pb cross pc))
- ) jt;
-
--- default plan (outer, cross)
-select
- jt.*
-from
- jsonb_table_test jtt,
- json_table (
- jtt.js,'strict $[*]' as p
- columns (
- n for ordinality,
- a int path 'lax $.a' default -1 on empty,
- nested path 'strict $.b[*]' as pb columns ( b int path '$' ),
- nested path 'strict $.c[*]' as pc columns ( c int path '$' )
- )
- plan default (outer, cross)
- ) jt;
-
--- specific plan (p outer (pb cross pc))
-select
- jt.*
-from
- jsonb_table_test jtt,
- json_table (
- jtt.js,'strict $[*]' as p
- columns (
- n for ordinality,
- a int path 'lax $.a' default -1 on empty,
- nested path 'strict $.b[*]' as pb columns ( b int path '$' ),
- nested path 'strict $.c[*]' as pc columns ( c int path '$' )
- )
- plan (p outer (pb cross pc))
- ) jt;
-
-
-select
- jt.*, b1 + 100 as b
-from
- json_table (jsonb
- '[
- {"a": 1, "b": [[1, 10], [2], [3, 30, 300]], "c": [1, null, 2]},
- {"a": 2, "b": [10, 20], "c": [1, null, 2]},
- {"x": "3", "b": [11, 22, 33, 44]}
- ]',
- '$[*]' as p
- columns (
- n for ordinality,
- a int path 'lax $.a' default -1 on error,
- nested path 'strict $.b[*]' as pb columns (
- b text format json path '$',
- nested path 'strict $[*]' as pb1 columns (
- b1 int path '$'
- )
- ),
- nested path 'strict $.c[*]' as pc columns (
- c text format json path '$',
- nested path 'strict $[*]' as pc1 columns (
- c1 int path '$'
- )
- )
- )
- --plan default(outer, cross)
- plan(p outer ((pb inner pb1) cross (pc outer pc1)))
- ) jt;
-
--- Should succeed (JSON arguments are passed to root and nested paths)
-SELECT *
-FROM
- generate_series(1, 4) x,
- generate_series(1, 3) y,
- JSON_TABLE(jsonb
- '[[1,2,3],[2,3,4,5],[3,4,5,6]]',
- 'strict $[*] ? (@[*] < $x)'
- PASSING x AS x, y AS y
- COLUMNS (
- y text FORMAT JSON PATH '$',
- NESTED PATH 'strict $[*] ? (@ >= $y)'
- COLUMNS (
- z int PATH '$'
- )
- )
- ) jt;
-
--- Should fail (JSON arguments are not passed to column paths)
-SELECT *
-FROM JSON_TABLE(
- jsonb '[1,2,3]',
- '$[*] ? (@ < $x)'
- PASSING 10 AS x
- COLUMNS (y text FORMAT JSON PATH '$ ? (@ < $x)')
- ) jt;
-
--- Extension: non-constant JSON path
-SELECT JSON_EXISTS(jsonb '{"a": 123}', '$' || '.' || 'a');
-SELECT JSON_VALUE(jsonb '{"a": 123}', '$' || '.' || 'a');
-SELECT JSON_VALUE(jsonb '{"a": 123}', '$' || '.' || 'b' DEFAULT 'foo' ON EMPTY);
-SELECT JSON_QUERY(jsonb '{"a": 123}', '$' || '.' || 'a');
-SELECT JSON_QUERY(jsonb '{"a": 123}', '$' || '.' || 'a' WITH WRAPPER);
--- Should fail (invalid path)
-SELECT JSON_QUERY(jsonb '{"a": 123}', 'error' || ' ' || 'error');
--- Should fail (not supported)
-SELECT * FROM JSON_TABLE(jsonb '{"a": 123}', '$' || '.' || 'a' COLUMNS (foo int));
-
--- Test parallel JSON_VALUE()
-
-
-CREATE UNLOGGED TABLE test_parallel_jsonb_value AS
-SELECT i::text::jsonb AS js
-FROM generate_series(1, 50000) i;
-
-
--- encourage use of parallel plans
-set parallel_setup_cost=0;
-set parallel_tuple_cost=0;
-set min_parallel_table_scan_size=0;
-set max_parallel_workers_per_gather=4;
-set parallel_leader_participation = off;
-
--- Should be non-parallel due to subtransactions
-EXPLAIN (COSTS OFF)
-SELECT sum(JSON_VALUE(js, '$' RETURNING numeric)) FROM test_parallel_jsonb_value;
-SELECT sum(JSON_VALUE(js, '$' RETURNING numeric)) FROM test_parallel_jsonb_value;
-
--- Should be parallel
-EXPLAIN (COSTS OFF)
-SELECT sum(JSON_VALUE(js, '$' RETURNING numeric ERROR ON ERROR)) FROM test_parallel_jsonb_value;
-SELECT sum(JSON_VALUE(js, '$' RETURNING numeric ERROR ON ERROR)) FROM test_parallel_jsonb_value;
-
-DROP TABLE test_parallel_jsonb_value;
diff --git a/src/test/regress/sql/opr_sanity.sql b/src/test/regress/sql/opr_sanity.sql
index 63fe114fedd..2b292851e3a 100644
--- a/src/test/regress/sql/opr_sanity.sql
+++ b/src/test/regress/sql/opr_sanity.sql
@@ -854,10 +854,8 @@ WHERE a.aggfnoid = p.oid AND
NOT binary_coercible(p.proargtypes[1], ptr.proargtypes[2]))
OR (p.pronargs > 2 AND
NOT binary_coercible(p.proargtypes[2], ptr.proargtypes[3]))
- OR (p.pronargs > 3 AND
- NOT binary_coercible(p.proargtypes[3], ptr.proargtypes[4]))
- -- we could carry the check further, but 4 args is enough for now
- OR (p.pronargs > 4)
+ -- we could carry the check further, but 3 args is enough for now
+ OR (p.pronargs > 3)
);
-- Cross-check finalfn (if present) against its entry in pg_proc.
diff --git a/src/test/regress/sql/sqljson.sql b/src/test/regress/sql/sqljson.sql
deleted file mode 100644
index c2742b40f1d..00000000000
--- a/src/test/regress/sql/sqljson.sql
+++ /dev/null
@@ -1,471 +0,0 @@
--- JSON()
-SELECT JSON();
-SELECT JSON(NULL);
-SELECT JSON('{ "a" : 1 } ');
-SELECT JSON('{ "a" : 1 } ' FORMAT JSON);
-SELECT JSON('{ "a" : 1 } ' FORMAT JSON ENCODING UTF8);
-SELECT JSON('{ "a" : 1 } '::bytea FORMAT JSON ENCODING UTF8);
-SELECT pg_typeof(JSON('{ "a" : 1 } '));
-
-SELECT JSON(' 1 '::json);
-SELECT JSON(' 1 '::jsonb);
-SELECT JSON(' 1 '::json WITH UNIQUE KEYS);
-SELECT JSON(123);
-
-SELECT JSON('{"a": 1, "a": 2}');
-SELECT JSON('{"a": 1, "a": 2}' WITH UNIQUE KEYS);
-SELECT JSON('{"a": 1, "a": 2}' WITHOUT UNIQUE KEYS);
-
-EXPLAIN (VERBOSE, COSTS OFF) SELECT JSON('123');
-EXPLAIN (VERBOSE, COSTS OFF) SELECT JSON('123' FORMAT JSON);
-EXPLAIN (VERBOSE, COSTS OFF) SELECT JSON('123'::bytea FORMAT JSON);
-EXPLAIN (VERBOSE, COSTS OFF) SELECT JSON('123'::bytea FORMAT JSON ENCODING UTF8);
-EXPLAIN (VERBOSE, COSTS OFF) SELECT JSON('123' WITH UNIQUE KEYS);
-EXPLAIN (VERBOSE, COSTS OFF) SELECT JSON('123' WITHOUT UNIQUE KEYS);
-
-SELECT JSON('123' RETURNING text);
-
-EXPLAIN (VERBOSE, COSTS OFF) SELECT JSON('123');
-EXPLAIN (VERBOSE, COSTS OFF) SELECT JSON('123' RETURNING json);
-EXPLAIN (VERBOSE, COSTS OFF) SELECT JSON('123' RETURNING jsonb);
-SELECT pg_typeof(JSON('123'));
-SELECT pg_typeof(JSON('123' RETURNING json));
-SELECT pg_typeof(JSON('123' RETURNING jsonb));
-
--- JSON_SCALAR()
-SELECT JSON_SCALAR();
-SELECT JSON_SCALAR(NULL);
-SELECT JSON_SCALAR(NULL::int);
-SELECT JSON_SCALAR(123);
-SELECT JSON_SCALAR(123.45);
-SELECT JSON_SCALAR(123.45::numeric);
-SELECT JSON_SCALAR(true);
-SELECT JSON_SCALAR(false);
-SELECT JSON_SCALAR(' 123.45');
-SELECT JSON_SCALAR('2020-06-07'::date);
-SELECT JSON_SCALAR('2020-06-07 01:02:03'::timestamp);
-SELECT JSON_SCALAR('{}'::json);
-SELECT JSON_SCALAR('{}'::jsonb);
-
-EXPLAIN (VERBOSE, COSTS OFF) SELECT JSON_SCALAR(123);
-EXPLAIN (VERBOSE, COSTS OFF) SELECT JSON_SCALAR('123');
-EXPLAIN (VERBOSE, COSTS OFF) SELECT JSON_SCALAR(123 RETURNING json);
-EXPLAIN (VERBOSE, COSTS OFF) SELECT JSON_SCALAR(123 RETURNING jsonb);
-
--- JSON_SERIALIZE()
-SELECT JSON_SERIALIZE();
-SELECT JSON_SERIALIZE(NULL);
-SELECT JSON_SERIALIZE(JSON('{ "a" : 1 } '));
-SELECT JSON_SERIALIZE('{ "a" : 1 } ');
-SELECT JSON_SERIALIZE('1');
-SELECT JSON_SERIALIZE('1' FORMAT JSON);
-SELECT JSON_SERIALIZE('{ "a" : 1 } ' RETURNING bytea);
-SELECT JSON_SERIALIZE('{ "a" : 1 } ' RETURNING varchar);
-SELECT pg_typeof(JSON_SERIALIZE(NULL));
-
--- only string types or bytea allowed
-SELECT JSON_SERIALIZE('{ "a" : 1 } ' RETURNING jsonb);
-
-
-EXPLAIN (VERBOSE, COSTS OFF) SELECT JSON_SERIALIZE('{}');
-EXPLAIN (VERBOSE, COSTS OFF) SELECT JSON_SERIALIZE('{}' RETURNING bytea);
-
--- JSON_OBJECT()
-SELECT JSON_OBJECT();
-SELECT JSON_OBJECT(RETURNING json);
-SELECT JSON_OBJECT(RETURNING json FORMAT JSON);
-SELECT JSON_OBJECT(RETURNING jsonb);
-SELECT JSON_OBJECT(RETURNING jsonb FORMAT JSON);
-SELECT JSON_OBJECT(RETURNING text);
-SELECT JSON_OBJECT(RETURNING text FORMAT JSON);
-SELECT JSON_OBJECT(RETURNING text FORMAT JSON ENCODING UTF8);
-SELECT JSON_OBJECT(RETURNING text FORMAT JSON ENCODING INVALID_ENCODING);
-SELECT JSON_OBJECT(RETURNING bytea);
-SELECT JSON_OBJECT(RETURNING bytea FORMAT JSON);
-SELECT JSON_OBJECT(RETURNING bytea FORMAT JSON ENCODING UTF8);
-SELECT JSON_OBJECT(RETURNING bytea FORMAT JSON ENCODING UTF16);
-SELECT JSON_OBJECT(RETURNING bytea FORMAT JSON ENCODING UTF32);
-
-SELECT JSON_OBJECT('foo': NULL::int FORMAT JSON);
-SELECT JSON_OBJECT('foo': NULL::int FORMAT JSON ENCODING UTF8);
-SELECT JSON_OBJECT('foo': NULL::json FORMAT JSON);
-SELECT JSON_OBJECT('foo': NULL::json FORMAT JSON ENCODING UTF8);
-SELECT JSON_OBJECT('foo': NULL::jsonb FORMAT JSON);
-SELECT JSON_OBJECT('foo': NULL::jsonb FORMAT JSON ENCODING UTF8);
-
-SELECT JSON_OBJECT(NULL: 1);
-SELECT JSON_OBJECT('a': 2 + 3);
-SELECT JSON_OBJECT('a' VALUE 2 + 3);
---SELECT JSON_OBJECT(KEY 'a' VALUE 2 + 3);
-SELECT JSON_OBJECT('a' || 2: 1);
-SELECT JSON_OBJECT(('a' || 2) VALUE 1);
---SELECT JSON_OBJECT('a' || 2 VALUE 1);
---SELECT JSON_OBJECT(KEY 'a' || 2 VALUE 1);
-SELECT JSON_OBJECT('a': 2::text);
-SELECT JSON_OBJECT('a' VALUE 2::text);
---SELECT JSON_OBJECT(KEY 'a' VALUE 2::text);
-SELECT JSON_OBJECT(1::text: 2);
-SELECT JSON_OBJECT((1::text) VALUE 2);
---SELECT JSON_OBJECT(1::text VALUE 2);
---SELECT JSON_OBJECT(KEY 1::text VALUE 2);
-SELECT JSON_OBJECT(json '[1]': 123);
-SELECT JSON_OBJECT(ARRAY[1,2,3]: 'aaa');
-
-SELECT JSON_OBJECT(
- 'a': '123',
- 1.23: 123,
- 'c': json '[ 1,true,{ } ]',
- 'd': jsonb '{ "x" : 123.45 }'
-);
-
-SELECT JSON_OBJECT(
- 'a': '123',
- 1.23: 123,
- 'c': json '[ 1,true,{ } ]',
- 'd': jsonb '{ "x" : 123.45 }'
- RETURNING jsonb
-);
-
-/*
-SELECT JSON_OBJECT(
- 'a': '123',
- KEY 1.23 VALUE 123,
- 'c' VALUE json '[1, true, {}]'
-);
-*/
-
-SELECT JSON_OBJECT('a': '123', 'b': JSON_OBJECT('a': 111, 'b': 'aaa'));
-SELECT JSON_OBJECT('a': '123', 'b': JSON_OBJECT('a': 111, 'b': 'aaa' RETURNING jsonb));
-
-SELECT JSON_OBJECT('a': JSON_OBJECT('b': 1 RETURNING text));
-SELECT JSON_OBJECT('a': JSON_OBJECT('b': 1 RETURNING text) FORMAT JSON);
-SELECT JSON_OBJECT('a': JSON_OBJECT('b': 1 RETURNING bytea));
-SELECT JSON_OBJECT('a': JSON_OBJECT('b': 1 RETURNING bytea) FORMAT JSON);
-
-SELECT JSON_OBJECT('a': '1', 'b': NULL, 'c': 2);
-SELECT JSON_OBJECT('a': '1', 'b': NULL, 'c': 2 NULL ON NULL);
-SELECT JSON_OBJECT('a': '1', 'b': NULL, 'c': 2 ABSENT ON NULL);
-
-SELECT JSON_OBJECT(1: 1, '1': NULL WITH UNIQUE);
-SELECT JSON_OBJECT(1: 1, '1': NULL ABSENT ON NULL WITH UNIQUE);
-SELECT JSON_OBJECT(1: 1, '1': NULL NULL ON NULL WITH UNIQUE RETURNING jsonb);
-SELECT JSON_OBJECT(1: 1, '1': NULL ABSENT ON NULL WITH UNIQUE RETURNING jsonb);
-
-SELECT JSON_OBJECT(1: 1, '2': NULL, '1': 1 NULL ON NULL WITH UNIQUE);
-SELECT JSON_OBJECT(1: 1, '2': NULL, '1': 1 ABSENT ON NULL WITH UNIQUE);
-SELECT JSON_OBJECT(1: 1, '2': NULL, '1': 1 ABSENT ON NULL WITHOUT UNIQUE);
-SELECT JSON_OBJECT(1: 1, '2': NULL, '1': 1 ABSENT ON NULL WITH UNIQUE RETURNING jsonb);
-SELECT JSON_OBJECT(1: 1, '2': NULL, '1': 1 ABSENT ON NULL WITHOUT UNIQUE RETURNING jsonb);
-SELECT JSON_OBJECT(1: 1, '2': NULL, '3': 1, 4: NULL, '5': 'a' ABSENT ON NULL WITH UNIQUE RETURNING jsonb);
-
-
--- JSON_ARRAY()
-SELECT JSON_ARRAY();
-SELECT JSON_ARRAY(RETURNING json);
-SELECT JSON_ARRAY(RETURNING json FORMAT JSON);
-SELECT JSON_ARRAY(RETURNING jsonb);
-SELECT JSON_ARRAY(RETURNING jsonb FORMAT JSON);
-SELECT JSON_ARRAY(RETURNING text);
-SELECT JSON_ARRAY(RETURNING text FORMAT JSON);
-SELECT JSON_ARRAY(RETURNING text FORMAT JSON ENCODING UTF8);
-SELECT JSON_ARRAY(RETURNING text FORMAT JSON ENCODING INVALID_ENCODING);
-SELECT JSON_ARRAY(RETURNING bytea);
-SELECT JSON_ARRAY(RETURNING bytea FORMAT JSON);
-SELECT JSON_ARRAY(RETURNING bytea FORMAT JSON ENCODING UTF8);
-SELECT JSON_ARRAY(RETURNING bytea FORMAT JSON ENCODING UTF16);
-SELECT JSON_ARRAY(RETURNING bytea FORMAT JSON ENCODING UTF32);
-
-SELECT JSON_ARRAY('aaa', 111, true, array[1,2,3], NULL, json '{"a": [1]}', jsonb '["a",3]');
-
-SELECT JSON_ARRAY('a', NULL, 'b' NULL ON NULL);
-SELECT JSON_ARRAY('a', NULL, 'b' ABSENT ON NULL);
-SELECT JSON_ARRAY(NULL, NULL, 'b' ABSENT ON NULL);
-SELECT JSON_ARRAY('a', NULL, 'b' NULL ON NULL RETURNING jsonb);
-SELECT JSON_ARRAY('a', NULL, 'b' ABSENT ON NULL RETURNING jsonb);
-SELECT JSON_ARRAY(NULL, NULL, 'b' ABSENT ON NULL RETURNING jsonb);
-
-SELECT JSON_ARRAY(JSON_ARRAY('{ "a" : 123 }' RETURNING text));
-SELECT JSON_ARRAY(JSON_ARRAY('{ "a" : 123 }' FORMAT JSON RETURNING text));
-SELECT JSON_ARRAY(JSON_ARRAY('{ "a" : 123 }' FORMAT JSON RETURNING text) FORMAT JSON);
-
-SELECT JSON_ARRAY(SELECT i FROM (VALUES (1), (2), (NULL), (4)) foo(i));
-SELECT JSON_ARRAY(SELECT i FROM (VALUES (NULL::int[]), ('{1,2}'), (NULL), (NULL), ('{3,4}'), (NULL)) foo(i));
-SELECT JSON_ARRAY(SELECT i FROM (VALUES (NULL::int[]), ('{1,2}'), (NULL), (NULL), ('{3,4}'), (NULL)) foo(i) RETURNING jsonb);
---SELECT JSON_ARRAY(SELECT i FROM (VALUES (NULL::int[]), ('{1,2}'), (NULL), (NULL), ('{3,4}'), (NULL)) foo(i) NULL ON NULL);
---SELECT JSON_ARRAY(SELECT i FROM (VALUES (NULL::int[]), ('{1,2}'), (NULL), (NULL), ('{3,4}'), (NULL)) foo(i) NULL ON NULL RETURNING jsonb);
-SELECT JSON_ARRAY(SELECT i FROM (VALUES (3), (1), (NULL), (2)) foo(i) ORDER BY i);
--- Should fail
-SELECT JSON_ARRAY(SELECT FROM (VALUES (1)) foo(i));
-SELECT JSON_ARRAY(SELECT i, i FROM (VALUES (1)) foo(i));
-SELECT JSON_ARRAY(SELECT * FROM (VALUES (1, 2)) foo(i, j));
-
--- JSON_ARRAYAGG()
-SELECT JSON_ARRAYAGG(i) IS NULL,
- JSON_ARRAYAGG(i RETURNING jsonb) IS NULL
-FROM generate_series(1, 0) i;
-
-SELECT JSON_ARRAYAGG(i),
- JSON_ARRAYAGG(i RETURNING jsonb)
-FROM generate_series(1, 5) i;
-
-SELECT JSON_ARRAYAGG(i ORDER BY i DESC)
-FROM generate_series(1, 5) i;
-
-SELECT JSON_ARRAYAGG(i::text::json)
-FROM generate_series(1, 5) i;
-
-SELECT JSON_ARRAYAGG(JSON_ARRAY(i, i + 1 RETURNING text) FORMAT JSON)
-FROM generate_series(1, 5) i;
-
-SELECT JSON_ARRAYAGG(NULL),
- JSON_ARRAYAGG(NULL RETURNING jsonb)
-FROM generate_series(1, 5);
-
-SELECT JSON_ARRAYAGG(NULL NULL ON NULL),
- JSON_ARRAYAGG(NULL NULL ON NULL RETURNING jsonb)
-FROM generate_series(1, 5);
-
-SELECT
- JSON_ARRAYAGG(bar),
- JSON_ARRAYAGG(bar RETURNING jsonb),
- JSON_ARRAYAGG(bar ABSENT ON NULL),
- JSON_ARRAYAGG(bar ABSENT ON NULL RETURNING jsonb),
- JSON_ARRAYAGG(bar NULL ON NULL),
- JSON_ARRAYAGG(bar NULL ON NULL RETURNING jsonb),
- JSON_ARRAYAGG(foo),
- JSON_ARRAYAGG(foo RETURNING jsonb),
- JSON_ARRAYAGG(foo ORDER BY bar) FILTER (WHERE bar > 2),
- JSON_ARRAYAGG(foo ORDER BY bar RETURNING jsonb) FILTER (WHERE bar > 2)
-FROM
- (VALUES (NULL), (3), (1), (NULL), (NULL), (5), (2), (4), (NULL)) foo(bar);
-
-SELECT
- bar, JSON_ARRAYAGG(bar) FILTER (WHERE bar > 2) OVER (PARTITION BY foo.bar % 2)
-FROM
- (VALUES (NULL), (3), (1), (NULL), (NULL), (5), (2), (4), (NULL), (5), (4)) foo(bar);
-
--- JSON_OBJECTAGG()
-SELECT JSON_OBJECTAGG('key': 1) IS NULL,
- JSON_OBJECTAGG('key': 1 RETURNING jsonb) IS NULL
-WHERE FALSE;
-
-SELECT JSON_OBJECTAGG(NULL: 1);
-
-SELECT JSON_OBJECTAGG(NULL: 1 RETURNING jsonb);
-
-SELECT
- JSON_OBJECTAGG(i: i),
--- JSON_OBJECTAGG(i VALUE i),
--- JSON_OBJECTAGG(KEY i VALUE i),
- JSON_OBJECTAGG(i: i RETURNING jsonb)
-FROM
- generate_series(1, 5) i;
-
-SELECT
- JSON_OBJECTAGG(k: v),
- JSON_OBJECTAGG(k: v NULL ON NULL),
- JSON_OBJECTAGG(k: v ABSENT ON NULL),
- JSON_OBJECTAGG(k: v RETURNING jsonb),
- JSON_OBJECTAGG(k: v NULL ON NULL RETURNING jsonb),
- JSON_OBJECTAGG(k: v ABSENT ON NULL RETURNING jsonb)
-FROM
- (VALUES (1, 1), (1, NULL), (2, NULL), (3, 3)) foo(k, v);
-
-SELECT JSON_OBJECTAGG(k: v WITH UNIQUE KEYS)
-FROM (VALUES (1, 1), (1, NULL), (2, 2)) foo(k, v);
-
-SELECT JSON_OBJECTAGG(k: v ABSENT ON NULL WITH UNIQUE KEYS)
-FROM (VALUES (1, 1), (1, NULL), (2, 2)) foo(k, v);
-
-SELECT JSON_OBJECTAGG(k: v ABSENT ON NULL WITH UNIQUE KEYS)
-FROM (VALUES (1, 1), (0, NULL), (3, NULL), (2, 2), (4, NULL)) foo(k, v);
-
-SELECT JSON_OBJECTAGG(k: v WITH UNIQUE KEYS RETURNING jsonb)
-FROM (VALUES (1, 1), (1, NULL), (2, 2)) foo(k, v);
-
-SELECT JSON_OBJECTAGG(k: v ABSENT ON NULL WITH UNIQUE KEYS RETURNING jsonb)
-FROM (VALUES (1, 1), (1, NULL), (2, 2)) foo(k, v);
-
-SELECT JSON_OBJECTAGG(k: v ABSENT ON NULL WITH UNIQUE KEYS RETURNING jsonb)
-FROM (VALUES (1, 1), (0, NULL),(4, null), (5, null),(6, null),(2, 2)) foo(k, v);
-
--- Test JSON_OBJECT deparsing
-EXPLAIN (VERBOSE, COSTS OFF)
-SELECT JSON_OBJECT('foo' : '1' FORMAT JSON, 'bar' : 'baz' RETURNING json);
-
-CREATE VIEW json_object_view AS
-SELECT JSON_OBJECT('foo' : '1' FORMAT JSON, 'bar' : 'baz' RETURNING json);
-
-\sv json_object_view
-
-DROP VIEW json_object_view;
-
-SELECT to_json(a) AS a, JSON_OBJECTAGG(k : v WITH UNIQUE KEYS) OVER (ORDER BY k)
-FROM (VALUES (1,1), (2,2)) a(k,v);
-
-SELECT to_json(a) AS a, JSON_OBJECTAGG(k : v WITH UNIQUE KEYS) OVER (ORDER BY k)
-FROM (VALUES (1,1), (1,2), (2,2)) a(k,v);
-
-SELECT to_json(a) AS a, JSON_OBJECTAGG(k : v ABSENT ON NULL WITH UNIQUE KEYS)
- OVER (ORDER BY k)
-FROM (VALUES (1,1), (1,null), (2,2)) a(k,v);
-
-SELECT to_json(a) AS a, JSON_OBJECTAGG(k : v ABSENT ON NULL)
-OVER (ORDER BY k)
-FROM (VALUES (1,1), (1,null), (2,2)) a(k,v);
-
-SELECT to_json(a) AS a, JSON_OBJECTAGG(k : v ABSENT ON NULL)
-OVER (ORDER BY k RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
-FROM (VALUES (1,1), (1,null), (2,2)) a(k,v);
-
--- Test JSON_ARRAY deparsing
-EXPLAIN (VERBOSE, COSTS OFF)
-SELECT JSON_ARRAY('1' FORMAT JSON, 2 RETURNING json);
-
-CREATE VIEW json_array_view AS
-SELECT JSON_ARRAY('1' FORMAT JSON, 2 RETURNING json);
-
-\sv json_array_view
-
-DROP VIEW json_array_view;
-
--- Test JSON_OBJECTAGG deparsing
-EXPLAIN (VERBOSE, COSTS OFF)
-SELECT JSON_OBJECTAGG(i: ('111' || i)::bytea FORMAT JSON WITH UNIQUE RETURNING text) FILTER (WHERE i > 3)
-FROM generate_series(1,5) i;
-
-EXPLAIN (VERBOSE, COSTS OFF)
-SELECT JSON_OBJECTAGG(i: ('111' || i)::bytea FORMAT JSON WITH UNIQUE RETURNING text) OVER (PARTITION BY i % 2)
-FROM generate_series(1,5) i;
-
-CREATE VIEW json_objectagg_view AS
-SELECT JSON_OBJECTAGG(i: ('111' || i)::bytea FORMAT JSON WITH UNIQUE RETURNING text) FILTER (WHERE i > 3)
-FROM generate_series(1,5) i;
-
-\sv json_objectagg_view
-
-DROP VIEW json_objectagg_view;
-
--- Test JSON_ARRAYAGG deparsing
-EXPLAIN (VERBOSE, COSTS OFF)
-SELECT JSON_ARRAYAGG(('111' || i)::bytea FORMAT JSON NULL ON NULL RETURNING text) FILTER (WHERE i > 3)
-FROM generate_series(1,5) i;
-
-EXPLAIN (VERBOSE, COSTS OFF)
-SELECT JSON_ARRAYAGG(('111' || i)::bytea FORMAT JSON NULL ON NULL RETURNING text) OVER (PARTITION BY i % 2)
-FROM generate_series(1,5) i;
-
-CREATE VIEW json_arrayagg_view AS
-SELECT JSON_ARRAYAGG(('111' || i)::bytea FORMAT JSON NULL ON NULL RETURNING text) FILTER (WHERE i > 3)
-FROM generate_series(1,5) i;
-
-\sv json_arrayagg_view
-
-DROP VIEW json_arrayagg_view;
-
--- Test JSON_ARRAY(subquery) deparsing
-EXPLAIN (VERBOSE, COSTS OFF)
-SELECT JSON_ARRAY(SELECT i FROM (VALUES (1), (2), (NULL), (4)) foo(i) RETURNING jsonb);
-
-CREATE VIEW json_array_subquery_view AS
-SELECT JSON_ARRAY(SELECT i FROM (VALUES (1), (2), (NULL), (4)) foo(i) RETURNING jsonb);
-
-\sv json_array_subquery_view
-
-DROP VIEW json_array_subquery_view;
-
--- IS JSON predicate
-SELECT NULL IS JSON;
-SELECT NULL IS NOT JSON;
-SELECT NULL::json IS JSON;
-SELECT NULL::jsonb IS JSON;
-SELECT NULL::text IS JSON;
-SELECT NULL::bytea IS JSON;
-SELECT NULL::int IS JSON;
-
-SELECT '' IS JSON;
-
-SELECT bytea '\x00' IS JSON;
-
-CREATE TABLE test_is_json (js text);
-
-INSERT INTO test_is_json VALUES
- (NULL),
- (''),
- ('123'),
- ('"aaa "'),
- ('true'),
- ('null'),
- ('[]'),
- ('[1, "2", {}]'),
- ('{}'),
- ('{ "a": 1, "b": null }'),
- ('{ "a": 1, "a": null }'),
- ('{ "a": 1, "b": [{ "a": 1 }, { "a": 2 }] }'),
- ('{ "a": 1, "b": [{ "a": 1, "b": 0, "a": 2 }] }'),
- ('aaa'),
- ('{a:1}'),
- ('["a",]');
-
-SELECT
- js,
- js IS JSON "IS JSON",
- js IS NOT JSON "IS NOT JSON",
- js IS JSON VALUE "IS VALUE",
- js IS JSON OBJECT "IS OBJECT",
- js IS JSON ARRAY "IS ARRAY",
- js IS JSON SCALAR "IS SCALAR",
- js IS JSON WITHOUT UNIQUE KEYS "WITHOUT UNIQUE",
- js IS JSON WITH UNIQUE KEYS "WITH UNIQUE"
-FROM
- test_is_json;
-
-SELECT
- js,
- js IS JSON "IS JSON",
- js IS NOT JSON "IS NOT JSON",
- js IS JSON VALUE "IS VALUE",
- js IS JSON OBJECT "IS OBJECT",
- js IS JSON ARRAY "IS ARRAY",
- js IS JSON SCALAR "IS SCALAR",
- js IS JSON WITHOUT UNIQUE KEYS "WITHOUT UNIQUE",
- js IS JSON WITH UNIQUE KEYS "WITH UNIQUE"
-FROM
- (SELECT js::json FROM test_is_json WHERE js IS JSON) foo(js);
-
-SELECT
- js0,
- js IS JSON "IS JSON",
- js IS NOT JSON "IS NOT JSON",
- js IS JSON VALUE "IS VALUE",
- js IS JSON OBJECT "IS OBJECT",
- js IS JSON ARRAY "IS ARRAY",
- js IS JSON SCALAR "IS SCALAR",
- js IS JSON WITHOUT UNIQUE KEYS "WITHOUT UNIQUE",
- js IS JSON WITH UNIQUE KEYS "WITH UNIQUE"
-FROM
- (SELECT js, js::bytea FROM test_is_json WHERE js IS JSON) foo(js0, js);
-
-SELECT
- js,
- js IS JSON "IS JSON",
- js IS NOT JSON "IS NOT JSON",
- js IS JSON VALUE "IS VALUE",
- js IS JSON OBJECT "IS OBJECT",
- js IS JSON ARRAY "IS ARRAY",
- js IS JSON SCALAR "IS SCALAR",
- js IS JSON WITHOUT UNIQUE KEYS "WITHOUT UNIQUE",
- js IS JSON WITH UNIQUE KEYS "WITH UNIQUE"
-FROM
- (SELECT js::jsonb FROM test_is_json WHERE js IS JSON) foo(js);
-
--- Test IS JSON deparsing
-EXPLAIN (VERBOSE, COSTS OFF)
-SELECT '1' IS JSON AS "any", ('1' || i) IS JSON SCALAR AS "scalar", '[]' IS NOT JSON ARRAY AS "array", '{}' IS JSON OBJECT WITH UNIQUE AS "object" FROM generate_series(1, 3) i;
-
-CREATE VIEW is_json_view AS
-SELECT '1' IS JSON AS "any", ('1' || i) IS JSON SCALAR AS "scalar", '[]' IS NOT JSON ARRAY AS "array", '{}' IS JSON OBJECT WITH UNIQUE AS "object" FROM generate_series(1, 3) i;
-
-\sv is_json_view
-
-DROP VIEW is_json_view;
generated by cgit v1.2.3 (git 2.25.1) at 2025-08-04 06:55:39 +0000