diff options
Diffstat (limited to 'src/test')
| -rw-r--r-- | src/test/regress/expected/json_sqljson.out | 6 | ||||
| -rw-r--r-- | src/test/regress/expected/jsonb_sqljson.out | 562 | ||||
| -rw-r--r-- | src/test/regress/sql/json_sqljson.sql | 4 | ||||
| -rw-r--r-- | src/test/regress/sql/jsonb_sqljson.sql | 284 |
4 files changed, 856 insertions, 0 deletions
diff --git a/src/test/regress/expected/json_sqljson.out b/src/test/regress/expected/json_sqljson.out index bb62634314a..5c4dfa5f7c5 100644 --- a/src/test/regress/expected/json_sqljson.out +++ b/src/test/regress/expected/json_sqljson.out @@ -13,3 +13,9 @@ SELECT JSON_QUERY(NULL FORMAT JSON, '$'); ERROR: JSON_QUERY() is not yet implemented for json type LINE 1: SELECT JSON_QUERY(NULL FORMAT JSON, '$'); ^ +-- JSON_TABLE +SELECT * FROM JSON_TABLE(NULL FORMAT JSON, '$' COLUMNS (foo text)); +ERROR: JSON_TABLE() is not yet implemented for json type +LINE 1: SELECT * FROM JSON_TABLE(NULL FORMAT JSON, '$' COLUMNS (foo ... + ^ +HINT: Try casting the argument to jsonb diff --git a/src/test/regress/expected/jsonb_sqljson.out b/src/test/regress/expected/jsonb_sqljson.out index 3661b7a810b..144cc0c5578 100644 --- a/src/test/regress/expected/jsonb_sqljson.out +++ b/src/test/regress/expected/jsonb_sqljson.out @@ -1020,3 +1020,565 @@ CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$[1, 0 to $.a ? (@.dateti 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]' COLUMNS ( + a1 int, + NESTED PATH '$[*]' COLUMNS ( + a11 text + ), + b1 text + ), + NESTED PATH '$[2]' COLUMNS ( + NESTED PATH '$[*]' COLUMNS ( + a21 text + ), + NESTED PATH '$[*]' 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, '$[*]' + 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]' + COLUMNS ( + a1 integer PATH '$."a1"', + b1 text PATH '$."b1"', + NESTED PATH '$[*]' + COLUMNS ( + a11 text PATH '$."a11"' + ) + ), + NESTED PATH '$[2]' + COLUMNS ( + NESTED PATH '$[*]' + COLUMNS ( + a21 text PATH '$."a21"' + ), + NESTED PATH '$[*]' + COLUMNS ( + a22 text PATH '$."a22"' + ) + ) + ) + ) +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, '$[*]' 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]' COLUMNS (a1 integer PATH '$."a1"', b1 text PATH '$."b1"', NESTED PATH '$[*]' COLUMNS (a11 text PATH '$."a11"')), NESTED PATH '$[2]' COLUMNS ( NESTED PATH '$[*]' COLUMNS (a21 text PATH '$."a21"'), NESTED PATH '$[*]' COLUMNS (a22 text PATH '$."a22"')))) +(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 (column names must be distinct) +SELECT * FROM JSON_TABLE( + jsonb '[]', '$' + COLUMNS ( + a int, + b text, + a jsonb + ) +) 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 '$' + COLUMNS ( + c int, + b text + ) + ) +) 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 '$' + COLUMNS ( + b int + ), + NESTED PATH '$' + COLUMNS ( + NESTED PATH '$' + COLUMNS ( + c int, + b text + ) + ) + ) +) jt; +ERROR: duplicate JSON_TABLE column name: b +HINT: JSON_TABLE column names must be distinct from one another +-- 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 $[*]' + columns ( + n for ordinality, + a int path 'lax $.a' default -1 on empty, + nested path 'strict $.b[*]' columns ( b int path '$' ), + nested path 'strict $.c[*]' 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) + +-- 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 TABLE test_parallel_jsonb_value AS +SELECT i::text::jsonb AS js +FROM generate_series(1, 1000000) i; +-- 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 +-------------- + 500000500000 +(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: 2 + -> 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 +-------------- + 500000500000 +(1 row) + diff --git a/src/test/regress/sql/json_sqljson.sql b/src/test/regress/sql/json_sqljson.sql index 4f30fa46b91..df4a430d885 100644 --- a/src/test/regress/sql/json_sqljson.sql +++ b/src/test/regress/sql/json_sqljson.sql @@ -9,3 +9,7 @@ SELECT JSON_VALUE(NULL FORMAT JSON, '$'); -- JSON_QUERY SELECT JSON_QUERY(NULL FORMAT JSON, '$'); + +-- JSON_TABLE + +SELECT * FROM JSON_TABLE(NULL FORMAT JSON, '$' COLUMNS (foo text)); diff --git a/src/test/regress/sql/jsonb_sqljson.sql b/src/test/regress/sql/jsonb_sqljson.sql index 697b8ed1267..62236c9fb15 100644 --- a/src/test/regress/sql/jsonb_sqljson.sql +++ b/src/test/regress/sql/jsonb_sqljson.sql @@ -319,3 +319,287 @@ CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$[1, $.a ? (@.datetime() CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$[1, 0 to $.a ? (@.datetime() == $x)]' PASSING '12:34'::time AS x)); CREATE INDEX ON test_jsonb_mutability (JSON_QUERY(js, '$[1, $.a ? (@.datetime("HH:MI") == $x)]' PASSING '12:34'::time AS x)); DROP TABLE test_jsonb_mutability; + +-- JSON_TABLE + +-- Should fail (JSON_TABLE can be used only in FROM clause) +SELECT JSON_TABLE('[]', '$'); + +-- Should fail (no columns) +SELECT * FROM JSON_TABLE(NULL, '$' COLUMNS ()); + +-- NULL => empty table +SELECT * FROM JSON_TABLE(NULL::jsonb, '$' COLUMNS (foo int)) bar; + +-- +SELECT * FROM JSON_TABLE(jsonb '123', '$' + COLUMNS (item int PATH '$', foo int)) bar; + +-- JSON_TABLE: basic functionality +CREATE DOMAIN jsonb_test_domain AS text CHECK (value <> 'foo'); + +SELECT * +FROM + (VALUES + ('1'), + ('[]'), + ('{}'), + ('[1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""]') + ) vals(js) + LEFT OUTER JOIN +-- JSON_TABLE is implicitly lateral + JSON_TABLE( + vals.js::jsonb, 'lax $[*]' + COLUMNS ( + id FOR ORDINALITY, + id2 FOR ORDINALITY, -- allowed additional ordinality columns + "int" int PATH '$', + "text" text PATH '$', + "char(4)" char(4) PATH '$', + "bool" bool PATH '$', + "numeric" numeric PATH '$', + "domain" jsonb_test_domain PATH '$', + js json PATH '$', + jb jsonb PATH '$', + jst text FORMAT JSON PATH '$', + jsc char(4) FORMAT JSON PATH '$', + jsv varchar(4) FORMAT JSON PATH '$', + jsb jsonb FORMAT JSON PATH '$', + jsbq jsonb FORMAT JSON PATH '$' OMIT QUOTES, + aaa int, -- implicit path '$."aaa"', + aaa1 int PATH '$.aaa', + exists1 bool EXISTS PATH '$.aaa', + exists2 int EXISTS PATH '$.aaa', + exists3 int EXISTS PATH 'strict $.aaa' UNKNOWN ON ERROR, + exists4 text EXISTS PATH 'strict $.aaa' FALSE ON ERROR, + + js2 json PATH '$', + jsb2w jsonb PATH '$' WITH WRAPPER, + jsb2q jsonb PATH '$' OMIT QUOTES, + ia int[] PATH '$', + ta text[] PATH '$', + jba jsonb[] PATH '$' + ) + ) jt + ON true; + +-- JSON_TABLE: Test backward parsing + +CREATE VIEW jsonb_table_view AS +SELECT * FROM + JSON_TABLE( + jsonb 'null', 'lax $[*]' PASSING 1 + 2 AS a, json '"foo"' AS "b c" + COLUMNS ( + id FOR ORDINALITY, + id2 FOR ORDINALITY, -- allowed additional ordinality columns + "int" int PATH '$', + "text" text PATH '$', + "char(4)" char(4) PATH '$', + "bool" bool PATH '$', + "numeric" numeric PATH '$', + "domain" jsonb_test_domain PATH '$', + js json PATH '$', + jb jsonb PATH '$', + jst text FORMAT JSON PATH '$', + jsc char(4) FORMAT JSON PATH '$', + jsv varchar(4) FORMAT JSON PATH '$', + jsb jsonb FORMAT JSON PATH '$', + jsbq jsonb FORMAT JSON PATH '$' OMIT QUOTES, + aaa int, -- implicit path '$."aaa"', + aaa1 int PATH '$.aaa', + exists1 bool EXISTS PATH '$.aaa', + exists2 int EXISTS PATH '$.aaa' TRUE ON ERROR, + exists3 text EXISTS PATH 'strict $.aaa' UNKNOWN ON ERROR, + + js2 json PATH '$', + jsb2w jsonb PATH '$' WITH WRAPPER, + jsb2q jsonb PATH '$' OMIT QUOTES, + ia int[] PATH '$', + ta text[] PATH '$', + jba jsonb[] PATH '$', + + NESTED PATH '$[1]' COLUMNS ( + a1 int, + NESTED PATH '$[*]' COLUMNS ( + a11 text + ), + b1 text + ), + NESTED PATH '$[2]' COLUMNS ( + NESTED PATH '$[*]' COLUMNS ( + a21 text + ), + NESTED PATH '$[*]' COLUMNS ( + a22 text + ) + ) + ) + ); + +\sv jsonb_table_view + +EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM jsonb_table_view; + +DROP VIEW jsonb_table_view; +DROP DOMAIN jsonb_test_domain; + +-- JSON_TABLE: ON EMPTY/ON ERROR behavior +SELECT * +FROM + (VALUES ('1'), ('"err"')) vals(js), + JSON_TABLE(vals.js::jsonb, '$' COLUMNS (a int PATH '$')) jt; + +SELECT * +FROM + (VALUES ('1'), ('"err"')) vals(js) + LEFT OUTER JOIN + JSON_TABLE(vals.js::jsonb, '$' COLUMNS (a int PATH '$') ERROR ON ERROR) jt + ON true; + +SELECT * +FROM + (VALUES ('1'), ('"err"')) vals(js) + LEFT OUTER JOIN + JSON_TABLE(vals.js::jsonb, '$' COLUMNS (a int PATH '$' ERROR ON ERROR)) jt + ON true; + +SELECT * FROM JSON_TABLE(jsonb '1', '$' COLUMNS (a int PATH '$.a' ERROR ON EMPTY)) jt; +SELECT * FROM JSON_TABLE(jsonb '1', '$' COLUMNS (a int PATH 'strict $.a' ERROR ON EMPTY) ERROR ON ERROR) jt; +SELECT * FROM JSON_TABLE(jsonb '1', '$' COLUMNS (a int PATH 'lax $.a' ERROR ON EMPTY) ERROR ON ERROR) jt; + +SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int PATH '$' DEFAULT 1 ON EMPTY DEFAULT 2 ON ERROR)) jt; +SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int PATH 'strict $.a' DEFAULT 1 ON EMPTY DEFAULT 2 ON ERROR)) jt; +SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int PATH 'lax $.a' DEFAULT 1 ON EMPTY DEFAULT 2 ON ERROR)) jt; + +-- JSON_TABLE: EXISTS PATH types +SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int4 EXISTS PATH '$.a')); +SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int2 EXISTS PATH '$.a')); +SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int8 EXISTS PATH '$.a')); +SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a float4 EXISTS PATH '$.a')); +SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a char(3) EXISTS PATH '$.a')); +SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a json EXISTS PATH '$.a')); +SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a jsonb EXISTS PATH '$.a')); + +-- JSON_TABLE: nested paths and plans + +-- Should fail (column names must be distinct) +SELECT * FROM JSON_TABLE( + jsonb '[]', '$' + COLUMNS ( + a int, + b text, + a jsonb + ) +) jt; + +SELECT * FROM JSON_TABLE( + jsonb '[]', '$' + COLUMNS ( + b int, + NESTED PATH '$' + COLUMNS ( + c int, + b text + ) + ) +) jt; + +SELECT * FROM JSON_TABLE( + jsonb '[]', '$' + COLUMNS ( + NESTED PATH '$' + COLUMNS ( + b int + ), + NESTED PATH '$' + COLUMNS ( + NESTED PATH '$' + COLUMNS ( + c int, + b text + ) + ) + ) +) jt; + +-- JSON_TABLE: plan execution + +CREATE TEMP TABLE jsonb_table_test (js jsonb); + +INSERT INTO jsonb_table_test +VALUES ( + '[ + {"a": 1, "b": [], "c": []}, + {"a": 2, "b": [1, 2, 3], "c": [10, null, 20]}, + {"a": 3, "b": [1, 2], "c": []}, + {"x": "4", "b": [1, 2], "c": 123} + ]' +); + +-- unspecified plan (outer, union) +select + jt.* +from + jsonb_table_test jtt, + json_table ( + jtt.js,'strict $[*]' + columns ( + n for ordinality, + a int path 'lax $.a' default -1 on empty, + nested path 'strict $.b[*]' columns ( b int path '$' ), + nested path 'strict $.c[*]' columns ( c int path '$' ) + ) + ) jt; + +-- Should succeed (JSON arguments are passed to root and nested paths) +SELECT * +FROM + generate_series(1, 4) x, + generate_series(1, 3) y, + JSON_TABLE(jsonb + '[[1,2,3],[2,3,4,5],[3,4,5,6]]', + 'strict $[*] ? (@[*] < $x)' + PASSING x AS x, y AS y + COLUMNS ( + y text FORMAT JSON PATH '$', + NESTED PATH 'strict $[*] ? (@ >= $y)' + COLUMNS ( + z int PATH '$' + ) + ) + ) jt; + +-- Should fail (JSON arguments are not passed to column paths) +SELECT * +FROM JSON_TABLE( + jsonb '[1,2,3]', + '$[*] ? (@ < $x)' + PASSING 10 AS x + COLUMNS (y text FORMAT JSON PATH '$ ? (@ < $x)') + ) jt; + +-- Extension: non-constant JSON path +SELECT JSON_EXISTS(jsonb '{"a": 123}', '$' || '.' || 'a'); +SELECT JSON_VALUE(jsonb '{"a": 123}', '$' || '.' || 'a'); +SELECT JSON_VALUE(jsonb '{"a": 123}', '$' || '.' || 'b' DEFAULT 'foo' ON EMPTY); +SELECT JSON_QUERY(jsonb '{"a": 123}', '$' || '.' || 'a'); +SELECT JSON_QUERY(jsonb '{"a": 123}', '$' || '.' || 'a' WITH WRAPPER); +-- Should fail (invalid path) +SELECT JSON_QUERY(jsonb '{"a": 123}', 'error' || ' ' || 'error'); +-- Should fail (not supported) +SELECT * FROM JSON_TABLE(jsonb '{"a": 123}', '$' || '.' || 'a' COLUMNS (foo int)); + +-- Test parallel JSON_VALUE() +CREATE TABLE test_parallel_jsonb_value AS +SELECT i::text::jsonb AS js +FROM generate_series(1, 1000000) i; + +-- Should be non-parallel due to subtransactions +EXPLAIN (COSTS OFF) +SELECT sum(JSON_VALUE(js, '$' RETURNING numeric)) FROM test_parallel_jsonb_value; +SELECT sum(JSON_VALUE(js, '$' RETURNING numeric)) FROM test_parallel_jsonb_value; + +-- Should be parallel +EXPLAIN (COSTS OFF) +SELECT sum(JSON_VALUE(js, '$' RETURNING numeric ERROR ON ERROR)) FROM test_parallel_jsonb_value; +SELECT sum(JSON_VALUE(js, '$' RETURNING numeric ERROR ON ERROR)) FROM test_parallel_jsonb_value; |