-- 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 ());
                                                    ^
-- 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, unexpected IDENT_P 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;