/
balmaster
/
postgres
Обзор
Документация
Войти
/
balmaster
/
postgres
Код
Запросы
0
Задачи
Вики
Пакеты
0
Релизы
0
Аналитика
Безопасность
master
src/test/regress/expected/sqljson_jsontable.out
1 180 строк
52 KB
Álvaro Herrera
Reword recent error messages: "should" -> "must"
21 янв 2025, 17:24
Не верифицирован
21 янв 2025, 17:24
db19a50
Код
Авторство
О чём код?
-- 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('[]', '$'); ^ -- Only allow EMPTY and ERROR for ON ERROR SELECT * FROM JSON_TABLE('[]', 'strict $.a' COLUMNS (js2 int PATH '$') DEFAULT 1 ON ERROR); ERROR: invalid ON ERROR behavior LINE 1: ...BLE('[]', 'strict $.a' COLUMNS (js2 int PATH '$') DEFAULT 1 ... ^ DETAIL: Only EMPTY [ ARRAY ] or ERROR is allowed in the top-level ON ERROR clause. SELECT * FROM JSON_TABLE('[]', 'strict $.a' COLUMNS (js2 int PATH '$') NULL ON ERROR); ERROR: invalid ON ERROR behavior LINE 1: ...BLE('[]', 'strict $.a' COLUMNS (js2 int PATH '$') NULL ON ER... ^ DETAIL: Only EMPTY [ ARRAY ] or ERROR is allowed in the top-level ON ERROR clause. SELECT * FROM JSON_TABLE('[]', 'strict $.a' COLUMNS (js2 int PATH '$') EMPTY ON ERROR); js2 ----- (0 rows) SELECT * FROM JSON_TABLE('[]', 'strict $.a' COLUMNS (js2 int PATH '$') ERROR ON ERROR); ERROR: jsonpath member accessor can only be applied to an object -- Column and path names must be distinct SELECT * FROM JSON_TABLE(jsonb'"1.23"', '$.a' as js2 COLUMNS (js2 int path '$')); ERROR: duplicate JSON_TABLE column or path name: js2 LINE 1: ...M JSON_TABLE(jsonb'"1.23"', '$.a' as js2 COLUMNS (js2 int pa... ^ -- 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 --duplicated column name SELECT * FROM JSON_TABLE(jsonb'"1.23"', '$.a' COLUMNS (js2 int path '$', js2 int path '$')); ERROR: duplicate JSON_TABLE column or path name: js2 LINE 1: ...E(jsonb'"1.23"', '$.a' COLUMNS (js2 int path '$', js2 int pa... ^ --return composite data type. create type comp as (a int, b int); SELECT * FROM JSON_TABLE(jsonb '{"rec": "(1,2)"}', '$' COLUMNS (id FOR ORDINALITY, comp comp path '$.rec' omit quotes)) jt; id | comp ----+------- 1 | (1,2) (1 row) drop type comp; -- NULL => empty table SELECT * FROM JSON_TABLE(NULL::jsonb, '$' COLUMNS (foo int)) bar; foo ----- (0 rows) SELECT * FROM JSON_TABLE(jsonb'"1.23"', 'strict $.a' COLUMNS (js2 int PATH '$')); js2 ----- (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'); CREATE TEMP TABLE json_table_test (js) AS (VALUES ('1'), ('[]'), ('{}'), ('[1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""]') ); -- Regular "unformatted" columns SELECT * FROM json_table_test vals LEFT OUTER JOIN JSON_TABLE( vals.js::jsonb, 'lax $[*]' COLUMNS ( id FOR ORDINALITY, "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 '$' ) ) jt ON true; js | id | int | text | char(4) | bool | numeric | domain | js | jb ---------------------------------------------------------------------------------------+----+-----+---------+---------+------+---------+---------+--------------+-------------- 1 | 1 | 1 | 1 | 1 | t | 1 | 1 | 1 | 1 [] | | | | | | | | | {} | 1 | | | | | | | {} | {} [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 1 | 1 | 1 | 1 | t | 1 | 1 | 1 | 1 [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 2 | | 1.23 | 1.23 | | 1.23 | 1.23 | 1.23 | 1.23 [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 3 | 2 | 2 | 2 | | 2 | 2 | "2" | "2" [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 4 | | aaaaaaa | | | | aaaaaaa | "aaaaaaa" | "aaaaaaa" [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 5 | | foo | foo | | | | "foo" | "foo" [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 6 | | | | | | | null | null [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 7 | | f | f | f | | false | false | false [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 8 | | t | t | t | | true | true | true [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 9 | | | | | | | {"aaa": 123} | {"aaa": 123} [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 10 | | [1,2] | | | | [1,2] | "[1,2]" | "[1,2]" [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 11 | | "str" | | | | "str" | "\"str\"" | "\"str\"" (14 rows) -- "formatted" columns SELECT * FROM json_table_test vals LEFT OUTER JOIN JSON_TABLE( vals.js::jsonb, 'lax $[*]' COLUMNS ( id FOR ORDINALITY, 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 ) ) jt ON true; js | id | jst | jsc | jsv | jsb | jsbq ---------------------------------------------------------------------------------------+----+--------------+------+------+--------------+-------------- 1 | 1 | 1 | 1 | 1 | 1 | 1 [] | | | | | | {} | 1 | {} | {} | {} | {} | {} [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 1 | 1 | 1 | 1 | 1 | 1 [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 2 | 1.23 | 1.23 | 1.23 | 1.23 | 1.23 [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 3 | "2" | "2" | "2" | "2" | 2 [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 4 | "aaaaaaa" | | | "aaaaaaa" | [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 5 | "foo" | | | "foo" | [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 6 | null | null | null | null | null [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 7 | false | | | false | false [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 8 | true | true | true | true | true [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 9 | {"aaa": 123} | | | {"aaa": 123} | {"aaa": 123} [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 10 | "[1,2]" | | | "[1,2]" | [1, 2] [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 11 | "\"str\"" | | | "\"str\"" | "str" (14 rows) -- EXISTS columns SELECT * FROM json_table_test vals LEFT OUTER JOIN JSON_TABLE( vals.js::jsonb, 'lax $[*]' COLUMNS ( id FOR ORDINALITY, 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 ) ) jt ON true; js | id | exists1 | exists2 | exists3 | exists4 ---------------------------------------------------------------------------------------+----+---------+---------+---------+--------- 1 | 1 | f | 0 | | false [] | | | | | {} | 1 | f | 0 | | false [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 1 | f | 0 | | false [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 2 | f | 0 | | false [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 3 | f | 0 | | false [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 4 | f | 0 | | false [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 5 | f | 0 | | false [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 6 | f | 0 | | false [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 7 | f | 0 | | false [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 8 | f | 0 | | false [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 9 | t | 1 | 1 | true [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 10 | f | 0 | | false [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 11 | f | 0 | | false (14 rows) -- Other miscellaneous checks SELECT * FROM json_table_test vals LEFT OUTER JOIN JSON_TABLE( vals.js::jsonb, 'lax $[*]' COLUMNS ( id FOR ORDINALITY, aaa int, -- "aaa" has implicit path '$."aaa"' aaa1 int PATH '$.aaa', 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 | aaa | aaa1 | js2 | jsb2w | jsb2q | ia | ta | jba ---------------------------------------------------------------------------------------+----+-----+------+--------------+----------------+--------------+----+----+----- 1 | 1 | | | 1 | [1] | 1 | | | [] | | | | | | | | | {} | 1 | | | {} | [{}] | {} | | | [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 1 | | | 1 | [1] | 1 | | | [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 2 | | | 1.23 | [1.23] | 1.23 | | | [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 3 | | | "2" | ["2"] | 2 | | | [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 4 | | | "aaaaaaa" | ["aaaaaaa"] | | | | [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 5 | | | "foo" | ["foo"] | | | | [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 6 | | | null | [null] | null | | | [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 7 | | | false | [false] | false | | | [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 8 | | | true | [true] | true | | | [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 9 | 123 | 123 | {"aaa": 123} | [{"aaa": 123}] | {"aaa": 123} | | | [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 10 | | | "[1,2]" | ["[1,2]"] | [1, 2] | | | [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 11 | | | "\"str\"" | ["\"str\""] | "str" | | | (14 rows) -- Test using casts in DEFAULT .. ON ERROR expression SELECT * FROM JSON_TABLE(jsonb '{"d1": "H"}', '$' COLUMNS (js1 jsonb_test_domain PATH '$.a2' DEFAULT '"foo1"'::jsonb::text ON EMPTY)); js1 -------- "foo1" (1 row) SELECT * FROM JSON_TABLE(jsonb '{"d1": "H"}', '$' COLUMNS (js1 jsonb_test_domain PATH '$.a2' DEFAULT 'foo'::jsonb_test_domain ON EMPTY)); ERROR: could not coerce ON EMPTY expression (DEFAULT) to the RETURNING type DETAIL: value for domain jsonb_test_domain violates check constraint "jsonb_test_domain_check" SELECT * FROM JSON_TABLE(jsonb '{"d1": "H"}', '$' COLUMNS (js1 jsonb_test_domain PATH '$.a2' DEFAULT 'foo1'::jsonb_test_domain ON EMPTY)); js1 ------ foo1 (1 row) SELECT * FROM JSON_TABLE(jsonb '{"d1": "foo"}', '$' COLUMNS (js1 jsonb_test_domain PATH '$.d1' DEFAULT 'foo2'::jsonb_test_domain ON ERROR)); js1 ------ foo2 (1 row) SELECT * FROM JSON_TABLE(jsonb '{"d1": "foo"}', '$' COLUMNS (js1 oid[] PATH '$.d2' DEFAULT '{1}'::int[]::oid[] ON EMPTY)); js1 ----- {1} (1 row) -- JSON_TABLE: Test backward parsing CREATE VIEW jsonb_table_view2 AS SELECT * FROM JSON_TABLE( jsonb 'null', 'lax $[*]' PASSING 1 + 2 AS a, json '"foo"' AS "b c" COLUMNS ( "int" int PATH '$', "text" text PATH '$', "char(4)" char(4) PATH '$', "bool" bool PATH '$', "numeric" numeric PATH '$', "domain" jsonb_test_domain PATH '$')); CREATE VIEW jsonb_table_view3 AS SELECT * FROM JSON_TABLE( jsonb 'null', 'lax $[*]' PASSING 1 + 2 AS a, json '"foo"' AS "b c" COLUMNS ( js json PATH '$', jb jsonb PATH '$', jst text FORMAT JSON PATH '$', jsc char(4) FORMAT JSON PATH '$', jsv varchar(4) FORMAT JSON PATH '$')); CREATE VIEW jsonb_table_view4 AS SELECT * FROM JSON_TABLE( jsonb 'null', 'lax $[*]' PASSING 1 + 2 AS a, json '"foo"' AS "b c" COLUMNS ( jsb jsonb FORMAT JSON PATH '$', jsbq jsonb FORMAT JSON PATH '$' OMIT QUOTES, aaa int, -- implicit path '$."aaa"', aaa1 int PATH '$.aaa')); CREATE VIEW jsonb_table_view5 AS SELECT * FROM JSON_TABLE( jsonb 'null', 'lax $[*]' PASSING 1 + 2 AS a, json '"foo"' AS "b c" COLUMNS ( exists1 bool EXISTS PATH '$.aaa', exists2 int EXISTS PATH '$.aaa' TRUE ON ERROR, exists3 text EXISTS PATH 'strict $.aaa' UNKNOWN ON ERROR)); CREATE VIEW jsonb_table_view6 AS SELECT * FROM JSON_TABLE( jsonb 'null', 'lax $[*]' PASSING 1 + 2 AS a, json '"foo"' AS "b c" COLUMNS ( js2 json PATH '$', jsb2w jsonb PATH '$' WITH WRAPPER, jsb2q jsonb PATH '$' OMIT QUOTES, ia int[] PATH '$', ta text[] PATH '$', jba jsonb[] PATH '$')); \sv jsonb_table_view2 CREATE OR REPLACE VIEW public.jsonb_table_view2 AS SELECT "int", text, "char(4)", bool, "numeric", domain FROM JSON_TABLE( 'null'::jsonb, '$[*]' AS json_table_path_0 PASSING 1 + 2 AS a, '"foo"'::json AS "b c" COLUMNS ( "int" integer PATH '$', text text PATH '$', "char(4)" character(4) PATH '$', bool boolean PATH '$', "numeric" numeric PATH '$', domain jsonb_test_domain PATH '$' ) ) \sv jsonb_table_view3 CREATE OR REPLACE VIEW public.jsonb_table_view3 AS SELECT js, jb, jst, jsc, jsv FROM JSON_TABLE( 'null'::jsonb, '$[*]' AS json_table_path_0 PASSING 1 + 2 AS a, '"foo"'::json AS "b c" COLUMNS ( js json PATH '$' WITHOUT WRAPPER KEEP QUOTES, jb jsonb PATH '$' WITHOUT WRAPPER KEEP QUOTES, jst text FORMAT JSON PATH '$' WITHOUT WRAPPER KEEP QUOTES, jsc character(4) FORMAT JSON PATH '$' WITHOUT WRAPPER KEEP QUOTES, jsv character varying(4) FORMAT JSON PATH '$' WITHOUT WRAPPER KEEP QUOTES ) ) \sv jsonb_table_view4 CREATE OR REPLACE VIEW public.jsonb_table_view4 AS SELECT jsb, jsbq, aaa, aaa1 FROM JSON_TABLE( 'null'::jsonb, '$[*]' AS json_table_path_0 PASSING 1 + 2 AS a, '"foo"'::json AS "b c" COLUMNS ( jsb jsonb PATH '$' WITHOUT WRAPPER KEEP QUOTES, jsbq jsonb PATH '$' WITHOUT WRAPPER OMIT QUOTES, aaa integer PATH '$."aaa"', aaa1 integer PATH '$."aaa"' ) ) \sv jsonb_table_view5 CREATE OR REPLACE VIEW public.jsonb_table_view5 AS SELECT exists1, exists2, exists3 FROM JSON_TABLE( 'null'::jsonb, '$[*]' AS json_table_path_0 PASSING 1 + 2 AS a, '"foo"'::json AS "b c" COLUMNS ( exists1 boolean EXISTS PATH '$."aaa"', exists2 integer EXISTS PATH '$."aaa"' TRUE ON ERROR, exists3 text EXISTS PATH 'strict $."aaa"' UNKNOWN ON ERROR ) ) \sv jsonb_table_view6 CREATE OR REPLACE VIEW public.jsonb_table_view6 AS SELECT js2, jsb2w, jsb2q, ia, ta, jba FROM JSON_TABLE( 'null'::jsonb, '$[*]' AS json_table_path_0 PASSING 1 + 2 AS a, '"foo"'::json AS "b c" COLUMNS ( js2 json PATH '$' WITHOUT WRAPPER KEEP QUOTES, jsb2w jsonb PATH '$' WITH UNCONDITIONAL WRAPPER KEEP QUOTES, jsb2q jsonb PATH '$' WITHOUT WRAPPER OMIT QUOTES, ia integer[] PATH '$' WITHOUT WRAPPER KEEP QUOTES, ta text[] PATH '$' WITHOUT WRAPPER KEEP QUOTES, jba jsonb[] PATH '$' WITHOUT WRAPPER KEEP QUOTES ) ) EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM jsonb_table_view2; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Table Function Scan on "json_table" Output: "json_table"."int", "json_table".text, "json_table"."char(4)", "json_table".bool, "json_table"."numeric", "json_table".domain Table Function Call: JSON_TABLE('null'::jsonb, '$[*]' AS json_table_path_0 PASSING 3 AS a, '"foo"'::jsonb AS "b c" COLUMNS ("int" integer PATH '$', text text PATH '$', "char(4)" character(4) PATH '$', bool boolean PATH '$', "numeric" numeric PATH '$', domain jsonb_test_domain PATH '$')) (3 rows) EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM jsonb_table_view3; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Table Function Scan on "json_table" Output: "json_table".js, "json_table".jb, "json_table".jst, "json_table".jsc, "json_table".jsv Table Function Call: JSON_TABLE('null'::jsonb, '$[*]' AS json_table_path_0 PASSING 3 AS a, '"foo"'::jsonb AS "b c" COLUMNS (js json PATH '$' WITHOUT WRAPPER KEEP QUOTES, jb jsonb PATH '$' WITHOUT WRAPPER KEEP QUOTES, jst text FORMAT JSON PATH '$' WITHOUT WRAPPER KEEP QUOTES, jsc character(4) FORMAT JSON PATH '$' WITHOUT WRAPPER KEEP QUOTES, jsv character varying(4) FORMAT JSON PATH '$' WITHOUT WRAPPER KEEP QUOTES)) (3 rows) EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM jsonb_table_view4; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Table Function Scan on "json_table" Output: "json_table".jsb, "json_table".jsbq, "json_table".aaa, "json_table".aaa1 Table Function Call: JSON_TABLE('null'::jsonb, '$[*]' AS json_table_path_0 PASSING 3 AS a, '"foo"'::jsonb AS "b c" COLUMNS (jsb jsonb PATH '$' WITHOUT WRAPPER KEEP QUOTES, jsbq jsonb PATH '$' WITHOUT WRAPPER OMIT QUOTES, aaa integer PATH '$."aaa"', aaa1 integer PATH '$."aaa"')) (3 rows) EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM jsonb_table_view5; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Table Function Scan on "json_table" Output: "json_table".exists1, "json_table".exists2, "json_table".exists3 Table Function Call: JSON_TABLE('null'::jsonb, '$[*]' AS json_table_path_0 PASSING 3 AS a, '"foo"'::jsonb AS "b c" COLUMNS (exists1 boolean EXISTS PATH '$."aaa"', exists2 integer EXISTS PATH '$."aaa"' TRUE ON ERROR, exists3 text EXISTS PATH 'strict $."aaa"' UNKNOWN ON ERROR)) (3 rows) EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM jsonb_table_view6; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Table Function Scan on "json_table" Output: "json_table".js2, "json_table".jsb2w, "json_table".jsb2q, "json_table".ia, "json_table".ta, "json_table".jba Table Function Call: JSON_TABLE('null'::jsonb, '$[*]' AS json_table_path_0 PASSING 3 AS a, '"foo"'::jsonb AS "b c" COLUMNS (js2 json PATH '$' WITHOUT WRAPPER KEEP QUOTES, jsb2w jsonb PATH '$' WITH UNCONDITIONAL WRAPPER KEEP QUOTES, jsb2q jsonb PATH '$' WITHOUT WRAPPER OMIT QUOTES, ia integer[] PATH '$' WITHOUT WRAPPER KEEP QUOTES, ta text[] PATH '$' WITHOUT WRAPPER KEEP QUOTES, jba jsonb[] PATH '$' WITHOUT WRAPPER KEEP QUOTES)) (3 rows) -- JSON_TABLE() with alias EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM JSON_TABLE( jsonb 'null', 'lax $[*]' PASSING 1 + 2 AS a, json '"foo"' AS "b c" COLUMNS ( id FOR ORDINALITY, "int" int PATH '$', "text" text PATH '$' )) json_table_func; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Table Function Scan on "json_table" json_table_func Output: id, "int", text Table Function Call: JSON_TABLE('null'::jsonb, '$[*]' AS json_table_path_0 PASSING 3 AS a, '"foo"'::jsonb AS "b c" COLUMNS (id FOR ORDINALITY, "int" integer PATH '$', text text PATH '$')) (3 rows) EXPLAIN (COSTS OFF, FORMAT JSON, VERBOSE) SELECT * FROM JSON_TABLE( jsonb 'null', 'lax $[*]' PASSING 1 + 2 AS a, json '"foo"' AS "b c" COLUMNS ( id FOR ORDINALITY, "int" int PATH '$', "text" text PATH '$' )) json_table_func; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- [ + { + "Plan": { + "Node Type": "Table Function Scan", + "Parallel Aware": false, + "Async Capable": false, + "Table Function Name": "json_table", + "Alias": "json_table_func", + "Disabled": false, + "Output": ["id", "\"int\"", "text"], + "Table Function Call": "JSON_TABLE('null'::jsonb, '$[*]' AS json_table_path_0 PASSING 3 AS a, '\"foo\"'::jsonb AS \"b c\" COLUMNS (id FOR ORDINALITY, \"int\" integer PATH '$', text text PATH '$'))"+ } + } + ] (1 row) DROP VIEW jsonb_table_view2; DROP VIEW jsonb_table_view3; DROP VIEW jsonb_table_view4; DROP VIEW jsonb_table_view5; DROP VIEW jsonb_table_view6; DROP DOMAIN jsonb_test_domain; -- JSON_TABLE: only one FOR ORDINALITY columns allowed SELECT * FROM JSON_TABLE(jsonb '1', '$' COLUMNS (id FOR ORDINALITY, id2 FOR ORDINALITY, a int PATH '$.a' ERROR ON EMPTY)) jt; ERROR: only one FOR ORDINALITY column is allowed LINE 1: ..._TABLE(jsonb '1', '$' COLUMNS (id FOR ORDINALITY, id2 FOR OR... ^ SELECT * FROM JSON_TABLE(jsonb '1', '$' COLUMNS (id FOR ORDINALITY, a int PATH '$' ERROR ON EMPTY)) jt; id | a ----+--- 1 | 1 (1 row) -- 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" -- TABLE-level ERROR ON ERROR is not propagated to columns 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; ERROR: no SQL/JSON item found for specified path of column "a" SELECT * FROM JSON_TABLE(jsonb '1', '$' COLUMNS (a int PATH 'strict $.a' ERROR ON ERROR) 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 found for specified path of column "a" 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' ERROR ON ERROR)); -- ok; can cast to int4 a --- 0 (1 row) SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int4 EXISTS PATH '$' ERROR ON ERROR)); -- ok; can cast to int4 a --- 1 (1 row) SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int2 EXISTS PATH '$.a')); ERROR: could not coerce ON ERROR expression (FALSE) to the RETURNING type DETAIL: invalid input syntax for type smallint: "false" SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int8 EXISTS PATH '$.a')); ERROR: could not coerce ON ERROR expression (FALSE) to the RETURNING type DETAIL: invalid input syntax for type bigint: "false" SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a float4 EXISTS PATH '$.a')); ERROR: could not coerce ON ERROR expression (FALSE) to the RETURNING type DETAIL: invalid input syntax for type real: "false" -- Default FALSE (ON ERROR) doesn't fit char(3) SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a char(3) EXISTS PATH '$.a')); ERROR: could not coerce ON ERROR expression (FALSE) to the RETURNING type DETAIL: value too long for type character(3) SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a char(3) EXISTS PATH '$.a' ERROR ON ERROR)); ERROR: value too long for type character(3) SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a char(5) EXISTS PATH '$.a' ERROR ON ERROR)); a ------- false (1 row) SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a json EXISTS PATH '$.a')); a ------- false (1 row) SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a jsonb EXISTS PATH '$.a')); a ------- false (1 row) -- EXISTS PATH domain over int CREATE DOMAIN dint4 AS int; CREATE DOMAIN dint4_0 AS int CHECK (VALUE <> 0 ); SELECT a, a::bool FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a dint4 EXISTS PATH '$.a' )); a | a ---+--- 0 | f (1 row) SELECT a, a::bool FROM JSON_TABLE(jsonb '{"a":1}', '$' COLUMNS (a dint4_0 EXISTS PATH '$.b')); ERROR: could not coerce ON ERROR expression (FALSE) to the RETURNING type DETAIL: value for domain dint4_0 violates check constraint "dint4_0_check" SELECT a, a::bool FROM JSON_TABLE(jsonb '{"a":1}', '$' COLUMNS (a dint4_0 EXISTS PATH '$.b' ERROR ON ERROR)); ERROR: value for domain dint4_0 violates check constraint "dint4_0_check" SELECT a, a::bool FROM JSON_TABLE(jsonb '{"a":1}', '$' COLUMNS (a dint4_0 EXISTS PATH '$.b' FALSE ON ERROR)); ERROR: could not coerce ON ERROR expression (FALSE) to the RETURNING type DETAIL: value for domain dint4_0 violates check constraint "dint4_0_check" SELECT a, a::bool FROM JSON_TABLE(jsonb '{"a":1}', '$' COLUMNS (a dint4_0 EXISTS PATH '$.b' TRUE ON ERROR)); a | a ---+--- 1 | t (1 row) DROP DOMAIN dint4, dint4_0; -- JSON_TABLE: WRAPPER/QUOTES clauses on scalar columns SELECT * FROM JSON_TABLE(jsonb '"world"', '$' COLUMNS (item text PATH '$' KEEP QUOTES ON SCALAR STRING)); item --------- "world" (1 row) SELECT * FROM JSON_TABLE(jsonb '"world"', '$' COLUMNS (item text PATH '$' OMIT QUOTES ON SCALAR STRING)); item ------- world (1 row) SELECT * FROM JSON_TABLE(jsonb '"world"', '$' COLUMNS (item text FORMAT JSON PATH '$' KEEP QUOTES)); item --------- "world" (1 row) SELECT * FROM JSON_TABLE(jsonb '"world"', '$' COLUMNS (item text FORMAT JSON PATH '$' OMIT QUOTES)); item ------- world (1 row) SELECT * FROM JSON_TABLE(jsonb '"world"', '$' COLUMNS (item text FORMAT JSON PATH '$' WITHOUT WRAPPER KEEP QUOTES)); item --------- "world" (1 row) SELECT * FROM JSON_TABLE(jsonb '"world"', '$' COLUMNS (item text PATH '$' WITHOUT WRAPPER OMIT QUOTES)); item ------- world (1 row) SELECT * FROM JSON_TABLE(jsonb '"world"', '$' COLUMNS (item text FORMAT JSON PATH '$' WITH WRAPPER)); item ----------- ["world"] (1 row) -- Error: OMIT QUOTES should not be specified when WITH WRAPPER is present SELECT * FROM JSON_TABLE(jsonb '"world"', '$' COLUMNS (item text PATH '$' WITH WRAPPER OMIT QUOTES)); ERROR: SQL/JSON QUOTES behavior must not be specified when WITH WRAPPER is used LINE 1: ...T * FROM JSON_TABLE(jsonb '"world"', '$' COLUMNS (item text ... ^ -- But KEEP QUOTES (the default) is fine SELECT * FROM JSON_TABLE(jsonb '"world"', '$' COLUMNS (item text FORMAT JSON PATH '$' WITH WRAPPER KEEP QUOTES)); item ----------- ["world"] (1 row) -- Test PASSING args SELECT * FROM JSON_TABLE( jsonb '[1,2,3]', '$[*] ? (@ < $x)' PASSING 3 AS x COLUMNS (y text FORMAT JSON PATH '$') ) jt; y --- 1 2 (2 rows) -- PASSING arguments are also passed to column paths SELECT * FROM JSON_TABLE( jsonb '[1,2,3]', '$[*] ? (@ < $x)' PASSING 10 AS x, 3 AS y COLUMNS (a text FORMAT JSON PATH '$ ? (@ < $y)') ) jt; a --- 1 2 (3 rows) -- Should fail (not supported) SELECT * FROM JSON_TABLE(jsonb '{"a": 123}', '$' || '.' || 'a' COLUMNS (foo int)); ERROR: only string constants are supported in JSON_TABLE path specification LINE 1: SELECT * FROM JSON_TABLE(jsonb '{"a": 123}', '$' || '.' || '... ^ -- JsonPathQuery() error message mentioning column name SELECT * FROM JSON_TABLE('{"a": [{"b": "1"}, {"b": "2"}]}', '$' COLUMNS (b json path '$.a[*].b' ERROR ON ERROR)); ERROR: JSON path expression for column "b" must return single item when no wrapper is requested HINT: Use the WITH WRAPPER clause to wrap SQL/JSON items into an array. -- JSON_TABLE: nested paths -- Duplicate path names SELECT * FROM JSON_TABLE( jsonb '[]', '$' AS a COLUMNS ( b int, NESTED PATH '$' AS a COLUMNS ( c int ) ) ) jt; ERROR: duplicate JSON_TABLE column or path name: a LINE 5: NESTED PATH '$' AS a ^ SELECT * FROM JSON_TABLE( jsonb '[]', '$' AS a COLUMNS ( b int, NESTED PATH '$' AS n_a COLUMNS ( c int ) ) ) jt; b | c ---+--- | (1 row) SELECT * FROM JSON_TABLE( jsonb '[]', '$' COLUMNS ( b int, NESTED PATH '$' AS b COLUMNS ( c int ) ) ) jt; ERROR: duplicate JSON_TABLE column or path name: b LINE 5: NESTED PATH '$' AS b ^ 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 or path name: a LINE 10: NESTED PATH '$' AS a ^ -- 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} ]' ); 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_id for ordinality, b int path '$' ), nested path 'strict $.c[*]' as pc columns (c_id for ordinality, c int path '$' ) ) ) jt; n | a | b_id | b | c_id | c ---+----+------+---+------+---- 1 | 1 | | | | 2 | 2 | 1 | 1 | | 2 | 2 | 2 | 2 | | 2 | 2 | 3 | 3 | | 2 | 2 | | | 1 | 10 2 | 2 | | | 2 | 2 | 2 | | | 3 | 20 3 | 3 | 1 | 1 | | 3 | 3 | 2 | 2 | | 4 | -1 | 1 | 1 | | 4 | -1 | 2 | 2 | | (11 rows) -- PASSING arguments are passed to nested paths and their columns' paths SELECT * FROM generate_series(1, 3) 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 ---+---+--------------+--- 1 | 1 | [1, 2, 3] | 1 2 | 1 | [1, 2, 3] | 1 2 | 1 | [2, 3, 4, 5] | 3 | 1 | [1, 2, 3] | 1 3 | 1 | [2, 3, 4, 5] | 3 | 1 | [3, 4, 5, 6] | 1 | 2 | [1, 2, 3] | 2 2 | 2 | [1, 2, 3] | 2 2 | 2 | [2, 3, 4, 5] | 2 3 | 2 | [1, 2, 3] | 2 3 | 2 | [2, 3, 4, 5] | 2 3 | 2 | [3, 4, 5, 6] | 1 | 3 | [1, 2, 3] | 3 2 | 3 | [1, 2, 3] | 3 2 | 3 | [2, 3, 4, 5] | 3 3 | 3 | [1, 2, 3] | 3 3 | 3 | [2, 3, 4, 5] | 3 3 | 3 | [3, 4, 5, 6] | 3 (18 rows) -- JSON_TABLE: Test backward parsing with nested paths CREATE VIEW jsonb_table_view_nested AS SELECT * FROM JSON_TABLE( jsonb 'null', 'lax $[*]' PASSING 1 + 2 AS a, json '"foo"' AS "b c" COLUMNS ( id FOR ORDINALITY, 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_nested CREATE OR REPLACE VIEW public.jsonb_table_view_nested AS SELECT id, a1, b1, a11, a21, a22 FROM JSON_TABLE( 'null'::jsonb, '$[*]' AS json_table_path_0 PASSING 1 + 2 AS a, '"foo"'::json AS "b c" COLUMNS ( id FOR ORDINALITY, 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"' ) ) ) ) DROP VIEW jsonb_table_view_nested; CREATE TABLE s (js jsonb); INSERT INTO s VALUES ('{"a":{"za":[{"z1": [11,2222]},{"z21": [22, 234,2345]},{"z22": [32, 204,145]}]},"c": 3}'), ('{"a":{"za":[{"z1": [21,4222]},{"z21": [32, 134,1345]}]},"c": 10}'); -- error SELECT sub.* FROM s, JSON_TABLE(js, '$' PASSING 32 AS x, 13 AS y COLUMNS ( xx int path '$.c', NESTED PATH '$.a.za[1]' columns (NESTED PATH '$.z21[*]' COLUMNS (z21 int path '$?(@ >= $"x")' ERROR ON ERROR)) )) sub; xx | z21 ----+------ 3 | 3 | 234 3 | 2345 10 | 32 10 | 134 10 | 1345 (6 rows) -- Parent columns xx1, xx appear before NESTED ones SELECT sub.* FROM s, (VALUES (23)) x(x), generate_series(13, 13) y, JSON_TABLE(js, '$' AS c1 PASSING x AS x, y AS y COLUMNS ( NESTED PATH '$.a.za[2]' COLUMNS ( NESTED PATH '$.z22[*]' as z22 COLUMNS (c int PATH '$')), NESTED PATH '$.a.za[1]' columns (d int[] PATH '$.z21'), NESTED PATH '$.a.za[0]' columns (NESTED PATH '$.z1[*]' as z1 COLUMNS (a int PATH '$')), xx1 int PATH '$.c', NESTED PATH '$.a.za[1]' columns (NESTED PATH '$.z21[*]' as z21 COLUMNS (b int PATH '$')), xx int PATH '$.c' )) sub; xx1 | xx | c | d | a | b -----+----+-----+---------------+------+------ 3 | 3 | 32 | | | 3 | 3 | 204 | | | 3 | 3 | 145 | | | 3 | 3 | | {22,234,2345} | | 3 | 3 | | | 11 | 3 | 3 | | | 2222 | 3 | 3 | | | | 22 3 | 3 | | | | 234 3 | 3 | | | | 2345 10 | 10 | | {32,134,1345} | | 10 | 10 | | | 21 | 10 | 10 | | | 4222 | 10 | 10 | | | | 32 10 | 10 | | | | 134 10 | 10 | | | | 1345 (15 rows) -- Test applying PASSING variables at different nesting levels SELECT sub.* FROM s, (VALUES (23)) x(x), generate_series(13, 13) y, JSON_TABLE(js, '$' AS c1 PASSING x AS x, y AS y COLUMNS ( xx1 int PATH '$.c', NESTED PATH '$.a.za[0].z1[*]' COLUMNS (NESTED PATH '$ ?(@ >= ($"x" -2))' COLUMNS (a int PATH '$')), NESTED PATH '$.a.za[0]' COLUMNS (NESTED PATH '$.z1[*] ? (@ >= ($"x" -2))' COLUMNS (b int PATH '$')) )) sub; xx1 | a | b -----+------+------ 3 | | 3 | 2222 | 3 | | 2222 10 | 21 | 10 | 4222 | 10 | | 21 10 | | 4222 (7 rows) -- Test applying PASSING variable to paths all the levels SELECT sub.* FROM s, (VALUES (23)) x(x), generate_series(13, 13) y, JSON_TABLE(js, '$' AS c1 PASSING x AS x, y AS y COLUMNS ( xx1 int PATH '$.c', NESTED PATH '$.a.za[1]' COLUMNS (NESTED PATH '$.z21[*]' COLUMNS (b int PATH '$')), NESTED PATH '$.a.za[1] ? (@.z21[*] >= ($"x"-1))' COLUMNS (NESTED PATH '$.z21[*] ? (@ >= ($"y" + 3))' as z22 COLUMNS (a int PATH '$ ? (@ >= ($"y" + 12))')), NESTED PATH '$.a.za[1]' COLUMNS (NESTED PATH '$.z21[*] ? (@ >= ($"y" +121))' as z21 COLUMNS (c int PATH '$ ? (@ > ($"x" +111))')) )) sub; xx1 | b | a | c -----+------+------+------ 3 | 22 | | 3 | 234 | | 3 | 2345 | | 3 | | | 3 | | 234 | 3 | | 2345 | 3 | | | 234 3 | | | 2345 10 | 32 | | 10 | 134 | | 10 | 1345 | | 10 | | 32 | 10 | | 134 | 10 | | 1345 | 10 | | | 10 | | | 1345 (16 rows) ----- test on empty behavior SELECT sub.* FROM s, (values(23)) x(x), generate_series(13, 13) y, JSON_TABLE(js, '$' AS c1 PASSING x AS x, y AS y COLUMNS ( xx1 int PATH '$.c', NESTED PATH '$.a.za[2]' COLUMNS (NESTED PATH '$.z22[*]' as z22 COLUMNS (c int PATH '$')), NESTED PATH '$.a.za[1]' COLUMNS (d json PATH '$ ? (@.z21[*] == ($"x" -1))'), NESTED PATH '$.a.za[0]' COLUMNS (NESTED PATH '$.z1[*] ? (@ >= ($"x" -2))' as z1 COLUMNS (a int PATH '$')), NESTED PATH '$.a.za[1]' COLUMNS (NESTED PATH '$.z21[*] ? (@ >= ($"y" +121))' as z21 COLUMNS (b int PATH '$ ? (@ > ($"x" +111))' DEFAULT 0 ON EMPTY)) )) sub; xx1 | c | d | a | b -----+-----+--------------------------+------+------ 3 | 32 | | | 3 | 204 | | | 3 | 145 | | | 3 | | {"z21": [22, 234, 2345]} | | 3 | | | 2222 | 3 | | | | 234 3 | | | | 2345 10 | | | | 10 | | | 21 | 10 | | | 4222 | 10 | | | | 0 10 | | | | 1345 (12 rows) CREATE OR REPLACE VIEW jsonb_table_view7 AS SELECT sub.* FROM s, (values(23)) x(x), generate_series(13, 13) y, JSON_TABLE(js, '$' AS c1 PASSING x AS x, y AS y COLUMNS ( xx1 int PATH '$.c', NESTED PATH '$.a.za[2]' COLUMNS (NESTED PATH '$.z22[*]' as z22 COLUMNS (c int PATH '$' WITHOUT WRAPPER OMIT QUOTES)), NESTED PATH '$.a.za[1]' COLUMNS (d json PATH '$ ? (@.z21[*] == ($"x" -1))' WITH WRAPPER), NESTED PATH '$.a.za[0]' COLUMNS (NESTED PATH '$.z1[*] ? (@ >= ($"x" -2))' as z1 COLUMNS (a int PATH '$' KEEP QUOTES)), NESTED PATH '$.a.za[1]' COLUMNS (NESTED PATH '$.z21[*] ? (@ >= ($"y" +121))' as z21 COLUMNS (b int PATH '$ ? (@ > ($"x" +111))' DEFAULT 0 ON EMPTY)) )) sub; \sv jsonb_table_view7 CREATE OR REPLACE VIEW public.jsonb_table_view7 AS SELECT sub.xx1, sub.c, sub.d, sub.a, sub.b FROM s, ( VALUES (23)) x(x), generate_series(13, 13) y(y), LATERAL JSON_TABLE( s.js, '$' AS c1 PASSING x.x AS x, y.y AS y COLUMNS ( xx1 integer PATH '$."c"', NESTED PATH '$."a"."za"[2]' AS json_table_path_0 COLUMNS ( NESTED PATH '$."z22"[*]' AS z22 COLUMNS ( c integer PATH '$' WITHOUT WRAPPER OMIT QUOTES ) ), NESTED PATH '$."a"."za"[1]' AS json_table_path_1 COLUMNS ( d json PATH '$?(@."z21"[*] == $"x" - 1)' WITH UNCONDITIONAL WRAPPER KEEP QUOTES ), NESTED PATH '$."a"."za"[0]' AS json_table_path_2 COLUMNS ( NESTED PATH '$."z1"[*]?(@ >= $"x" - 2)' AS z1 COLUMNS ( a integer PATH '$' WITHOUT WRAPPER KEEP QUOTES ) ), NESTED PATH '$."a"."za"[1]' AS json_table_path_3 COLUMNS ( NESTED PATH '$."z21"[*]?(@ >= $"y" + 121)' AS z21 COLUMNS ( b integer PATH '$?(@ > $"x" + 111)' DEFAULT 0 ON EMPTY ) ) ) ) sub DROP VIEW jsonb_table_view7; DROP TABLE s; -- Prevent ON EMPTY specification on EXISTS columns SELECT * FROM JSON_TABLE(jsonb '1', '$' COLUMNS (a int exists empty object on empty)); ERROR: syntax error at or near "empty" LINE 1: ...sonb '1', '$' COLUMNS (a int exists empty object on empty)); ^ -- Test ON ERROR / EMPTY value validity for the function and column types; -- all fail SELECT * FROM JSON_TABLE(jsonb '1', '$' COLUMNS (a int) NULL ON ERROR); ERROR: invalid ON ERROR behavior LINE 1: ... * FROM JSON_TABLE(jsonb '1', '$' COLUMNS (a int) NULL ON ER... ^ DETAIL: Only EMPTY [ ARRAY ] or ERROR is allowed in the top-level ON ERROR clause. SELECT * FROM JSON_TABLE(jsonb '1', '$' COLUMNS (a int true on empty)); ERROR: invalid ON EMPTY behavior for column "a" LINE 1: ...T * FROM JSON_TABLE(jsonb '1', '$' COLUMNS (a int true on em... ^ DETAIL: Only ERROR, NULL, or DEFAULT expression is allowed in ON EMPTY for scalar columns. SELECT * FROM JSON_TABLE(jsonb '1', '$' COLUMNS (a int omit quotes true on error)); ERROR: invalid ON ERROR behavior for column "a" LINE 1: ...N_TABLE(jsonb '1', '$' COLUMNS (a int omit quotes true on er... ^ DETAIL: Only ERROR, NULL, EMPTY ARRAY, EMPTY OBJECT, or DEFAULT expression is allowed in ON ERROR for formatted columns. SELECT * FROM JSON_TABLE(jsonb '1', '$' COLUMNS (a int exists empty object on error)); ERROR: invalid ON ERROR behavior for column "a" LINE 1: ...M JSON_TABLE(jsonb '1', '$' COLUMNS (a int exists empty obje... ^ DETAIL: Only ERROR, TRUE, FALSE, or UNKNOWN is allowed in ON ERROR for EXISTS columns. -- Test JSON_TABLE() column deparsing -- don't emit default ON ERROR / EMPTY -- behavior CREATE VIEW json_table_view8 AS SELECT * from JSON_TABLE('"a"', '$' COLUMNS (a text PATH '$')); \sv json_table_view8; CREATE OR REPLACE VIEW public.json_table_view8 AS SELECT a FROM JSON_TABLE( '"a"'::text, '$' AS json_table_path_0 COLUMNS ( a text PATH '$' ) ) CREATE VIEW json_table_view9 AS SELECT * from JSON_TABLE('"a"', '$' COLUMNS (a text PATH '$') ERROR ON ERROR); \sv json_table_view9; CREATE OR REPLACE VIEW public.json_table_view9 AS SELECT a FROM JSON_TABLE( '"a"'::text, '$' AS json_table_path_0 COLUMNS ( a text PATH '$' ) ERROR ON ERROR ) DROP VIEW json_table_view8, json_table_view9; -- Test JSON_TABLE() deparsing -- don't emit default ON ERROR behavior CREATE VIEW json_table_view8 AS SELECT * from JSON_TABLE('"a"', '$' COLUMNS (a text PATH '$') EMPTY ON ERROR); \sv json_table_view8; CREATE OR REPLACE VIEW public.json_table_view8 AS SELECT a FROM JSON_TABLE( '"a"'::text, '$' AS json_table_path_0 COLUMNS ( a text PATH '$' ) ) CREATE VIEW json_table_view9 AS SELECT * from JSON_TABLE('"a"', '$' COLUMNS (a text PATH '$') EMPTY ARRAY ON ERROR); \sv json_table_view9; CREATE OR REPLACE VIEW public.json_table_view9 AS SELECT a FROM JSON_TABLE( '"a"'::text, '$' AS json_table_path_0 COLUMNS ( a text PATH '$' ) ) DROP VIEW json_table_view8, json_table_view9;