From 288d39c4bf832c538d562d9b42e3951e140ba779 Mon Sep 17 00:00:00 2001 From: Nikita Glukhov Date: Mon, 26 Nov 2018 19:03:43 +0300 Subject: [PATCH 11/11] JSON_TABLE for json type --- src/backend/executor/nodeTableFuncscan.c | 5 +- src/backend/parser/parse_expr.c | 6 - src/include/utils/jsonpath.h | 1 + src/test/regress/expected/json_sqljson.out | 981 ++++++++++++++++++++++++++++- src/test/regress/sql/json_sqljson.sql | 587 ++++++++++++++++- 5 files changed, 1568 insertions(+), 12 deletions(-) diff --git a/src/backend/executor/nodeTableFuncscan.c b/src/backend/executor/nodeTableFuncscan.c index e8f997a..6384efd 100644 --- a/src/backend/executor/nodeTableFuncscan.c +++ b/src/backend/executor/nodeTableFuncscan.c @@ -23,10 +23,12 @@ #include "postgres.h" #include "nodes/execnodes.h" +#include "catalog/pg_type.h" #include "executor/executor.h" #include "executor/nodeTableFuncscan.h" #include "executor/tablefunc.h" #include "miscadmin.h" +#include "nodes/nodeFuncs.h" #include "utils/builtins.h" #include "utils/jsonpath.h" #include "utils/lsyscache.h" @@ -165,7 +167,8 @@ ExecInitTableFuncScan(TableFuncScan *node, EState *estate, int eflags) /* Only XMLTABLE and JSON_TABLE are supported currently */ scanstate->routine = - tf->functype == TFT_XMLTABLE ? &XmlTableRoutine : &JsonbTableRoutine; + tf->functype == TFT_XMLTABLE ? &XmlTableRoutine : + exprType(tf->docexpr) == JSONBOID ? &JsonbTableRoutine : &JsonTableRoutine; scanstate->perTableCxt = AllocSetContextCreate(CurrentMemoryContext, diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c index 112fa59..6fd4c6a 100644 --- a/src/backend/parser/parse_expr.c +++ b/src/backend/parser/parse_expr.c @@ -4731,12 +4731,6 @@ transformJsonFuncExpr(ParseState *pstate, JsonFuncExpr *func) jsexpr->returning.typid = exprType(contextItemExpr); jsexpr->returning.typmod = -1; - if (jsexpr->returning.typid != JSONBOID) - ereport(ERROR, - (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), - errmsg("JSON_TABLE() is not yet implemented for json type"), - parser_errposition(pstate, func->location))); - break; } diff --git a/src/include/utils/jsonpath.h b/src/include/utils/jsonpath.h index 22bb19b..2fc4cbe 100644 --- a/src/include/utils/jsonpath.h +++ b/src/include/utils/jsonpath.h @@ -342,6 +342,7 @@ extern Datum JsonPathQuery(Datum json, JsonPath *jp, JsonWrapper wrapper, extern Datum EvalJsonPathVar(void *cxt, bool *isnull); +extern const TableFuncRoutine JsonTableRoutine; extern const TableFuncRoutine JsonbTableRoutine; #endif diff --git a/src/test/regress/expected/json_sqljson.out b/src/test/regress/expected/json_sqljson.out index 68634d6..21658dd 100644 --- a/src/test/regress/expected/json_sqljson.out +++ b/src/test/regress/expected/json_sqljson.out @@ -1038,10 +1038,978 @@ ERROR: new row for relation "test_json_constraints" violates check constraint " DETAIL: Failing row contains ({"a": 10}, 1, [1, 2]). DROP TABLE test_json_constraints; -- 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 ... - ^ +-- 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, '$' COLUMNS (foo int)) bar; + foo +----- +(0 rows) + +-- invalid json => empty table +SELECT * FROM JSON_TABLE('', '$' COLUMNS (foo int)) bar; + foo +----- +(0 rows) + +SELECT * FROM JSON_TABLE('' FORMAT JSON, '$' COLUMNS (foo int)) bar; + foo +----- +(0 rows) + +-- invalid json => error +SELECT * FROM JSON_TABLE('' FORMAT JSON, '$' COLUMNS (foo int) ERROR ON ERROR) bar; +ERROR: invalid input syntax for type json +DETAIL: The input string ended unexpectedly. +CONTEXT: JSON data, line 1: +-- +SELECT * FROM JSON_TABLE('123' FORMAT JSON, '$' + COLUMNS (item int PATH '$', foo int)) bar; + item | foo +------+----- + 123 | +(1 row) + +SELECT * FROM JSON_TABLE(json '123', '$' + COLUMNS (item int PATH '$', foo int)) bar; + item | foo +------+----- + 123 | +(1 row) + +-- JSON_TABLE: basic functionality +SELECT * +FROM + (VALUES + ('1'), + ('[]'), + ('{}'), + ('[1, 1.23, "2", "aaaaaaa", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""]'), + ('err') + ) vals(js) + LEFT OUTER JOIN +-- JSON_TABLE is implicitly lateral + JSON_TABLE( + vals.js FORMAT json, '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 '$', + 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 '$', + aaa int, -- implicit path '$."aaa"', + aaa1 int PATH '$.aaa' + ) + ) jt + ON true; + js | id | id2 | int | text | char(4) | bool | numeric | js | jb | jst | jsc | jsv | jsb | aaa | aaa1 +--------------------------------------------------------------------------------+----+-----+-----+---------+---------+------+---------+-----------+-----------+--------------+------+------+--------------+-----+------ + 1 | 1 | 1 | 1 | 1 | 1 | | 1 | 1 | 1 | 1 | 1 | 1 | 1 | | + [] | | | | | | | | | | | | | | | + {} | 1 | 1 | | | | | | | | {} | {} | {} | {} | | + [1, 1.23, "2", "aaaaaaa", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 1 | 1 | 1 | 1 | 1 | | 1 | 1 | 1 | 1 | 1 | 1 | 1 | | + [1, 1.23, "2", "aaaaaaa", 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, 1.23, "2", "aaaaaaa", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 3 | 3 | 2 | 2 | 2 | | 2 | "2" | "2" | "2" | "2" | "2" | "2" | | + [1, 1.23, "2", "aaaaaaa", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 4 | 4 | | aaaaaaa | aaaa | | | "aaaaaaa" | "aaaaaaa" | "aaaaaaa" | "aaa | "aaa | "aaaaaaa" | | + [1, 1.23, "2", "aaaaaaa", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 5 | 5 | | | | | | | | null | null | null | null | | + [1, 1.23, "2", "aaaaaaa", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 6 | 6 | 0 | false | fals | f | | false | false | false | fals | fals | false | | + [1, 1.23, "2", "aaaaaaa", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 7 | 7 | 1 | true | true | t | | true | true | true | true | true | true | | + [1, 1.23, "2", "aaaaaaa", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 8 | 8 | | | | | | | | {"aaa": 123} | {"aa | {"aa | {"aaa": 123} | 123 | 123 + [1, 1.23, "2", "aaaaaaa", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 9 | 9 | | [1,2] | [1,2 | | | "[1,2]" | "[1,2]" | "[1,2]" | "[1, | "[1, | "[1,2]" | | + [1, 1.23, "2", "aaaaaaa", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 10 | 10 | | "str" | "str | | | "\"str\"" | "\"str\"" | "\"str\"" | "\"s | "\"s | "\"str\"" | | + err | | | | | | | | | | | | | | | +(14 rows) + +-- JSON_TABLE: Test backward parsing +CREATE VIEW json_table_view AS +SELECT * FROM + JSON_TABLE( + 'null' FORMAT JSON, '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 '$', + 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 '$', + aaa int, -- implicit path '$."aaa"', + aaa1 int PATH '$.aaa', + 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 json_table_view +CREATE OR REPLACE VIEW public.json_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".js, + "json_table".jb, + "json_table".jst, + "json_table".jsc, + "json_table".jsv, + "json_table".jsb, + "json_table".aaa, + "json_table".aaa1, + "json_table".a1, + "json_table".b1, + "json_table".a11, + "json_table".a21, + "json_table".a22 + FROM JSON_TABLE( + 'null'::text FORMAT JSON, '$[*]' 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 '$', + 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 FORMAT JSON PATH '$', + aaa integer PATH '$."aaa"', + aaa1 integer PATH '$."aaa"', + 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 json_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".js, "json_table".jb, "json_table".jst, "json_table".jsc, "json_table".jsv, "json_table".jsb, "json_table".aaa, "json_table".aaa1, "json_table".a1, "json_table".b1, "json_table".a11, "json_table".a21, "json_table".a22 + Table Function Call: JSON_TABLE('null'::text FORMAT JSON, '$[*]' AS json_table_path_1 PASSING 3 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 '$', 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 FORMAT JSON PATH '$', aaa integer PATH '$."aaa"', aaa1 integer PATH '$."aaa"', 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) + +-- JSON_TABLE: ON EMPTY/ON ERROR behavior +SELECT * +FROM + (VALUES ('1'), ('err'), ('"err"')) vals(js), + JSON_TABLE(vals.js FORMAT JSON, '$' COLUMNS (a int PATH '$')) jt; + js | a +-------+--- + 1 | 1 + "err" | +(2 rows) + +SELECT * +FROM + (VALUES ('1'), ('err'), ('"err"')) vals(js) + LEFT OUTER JOIN + JSON_TABLE(vals.js FORMAT JSON, '$' COLUMNS (a int PATH '$') ERROR ON ERROR) jt + ON true; +ERROR: invalid input syntax for type json +DETAIL: Token "err" is invalid. +CONTEXT: JSON data, line 1: err +SELECT * +FROM + (VALUES ('1'), ('err'), ('"err"')) vals(js) + LEFT OUTER JOIN + JSON_TABLE(vals.js FORMAT JSON, '$' COLUMNS (a int PATH '$' ERROR ON ERROR)) jt + ON true; +ERROR: invalid input syntax for type integer: "err" +SELECT * FROM JSON_TABLE('1', '$' COLUMNS (a int PATH '$.a' ERROR ON EMPTY)) jt; + a +--- + +(1 row) + +SELECT * FROM JSON_TABLE('1', '$' COLUMNS (a int PATH 'strict $.a' ERROR ON EMPTY) ERROR ON ERROR) jt; +ERROR: SQL/JSON member not found +SELECT * FROM JSON_TABLE('1', '$' COLUMNS (a int PATH 'lax $.a' ERROR ON EMPTY) ERROR ON ERROR) jt; +ERROR: no SQL/JSON item +SELECT * FROM JSON_TABLE(json '"a"', '$' COLUMNS (a int PATH '$' DEFAULT 1 ON EMPTY DEFAULT 2 ON ERROR)) jt; + a +--- + 2 +(1 row) + +SELECT * FROM JSON_TABLE(json '"a"', '$' COLUMNS (a int PATH 'strict $.a' DEFAULT 1 ON EMPTY DEFAULT 2 ON ERROR)) jt; + a +--- + 2 +(1 row) + +SELECT * FROM JSON_TABLE(json '"a"', '$' COLUMNS (a int PATH 'lax $.a' DEFAULT 1 ON EMPTY DEFAULT 2 ON ERROR)) jt; + a +--- + 1 +(1 row) + +-- JSON_TABLE: nested paths and plans +-- Should fail (JSON_TABLE columns shall contain explicit AS path +-- specifications if explicit PLAN clause is used) +SELECT * FROM JSON_TABLE( + json '[]', '$' -- AS required here + COLUMNS ( + foo int PATH '$' + ) + PLAN DEFAULT (UNION) +) jt; +ERROR: invalid JSON_TABLE expression +LINE 2: json '[]', '$' + ^ +DETAIL: JSON_TABLE columns shall contain explicit AS pathname specification if explicit PLAN clause is used +SELECT * FROM JSON_TABLE( + json '[]', '$' AS path1 + COLUMNS ( + NESTED PATH '$' COLUMNS ( -- AS required here + foo int PATH '$' + ) + ) + PLAN DEFAULT (UNION) +) jt; +ERROR: invalid JSON_TABLE expression +LINE 4: NESTED PATH '$' COLUMNS ( + ^ +DETAIL: JSON_TABLE columns shall contain explicit AS pathname specification if explicit PLAN clause is used +-- Should fail (column names anf path names shall be distinct) +SELECT * FROM JSON_TABLE( + json '[]', '$' AS a + COLUMNS ( + a int + ) +) jt; +ERROR: duplicate JSON_TABLE column name: a +HINT: JSON_TABLE path names and column names shall be distinct from one another +SELECT * FROM JSON_TABLE( + json '[]', '$' AS a + COLUMNS ( + b int, + NESTED PATH '$' AS a + COLUMNS ( + c int + ) + ) +) jt; +ERROR: duplicate JSON_TABLE column name: a +HINT: JSON_TABLE path names and column names shall be distinct from one another +SELECT * FROM JSON_TABLE( + json '[]', '$' + COLUMNS ( + b int, + NESTED PATH '$' AS b + COLUMNS ( + c int + ) + ) +) jt; +ERROR: duplicate JSON_TABLE column name: b +HINT: JSON_TABLE path names and column names shall be distinct from one another +SELECT * FROM JSON_TABLE( + json '[]', '$' + 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 path names and column names shall be distinct from one another +-- JSON_TABLE: plan validation +SELECT * FROM JSON_TABLE( + json '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( + json '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( + json '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( + json '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( + json '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( + json '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( + json '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( + json '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( + json '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( + json '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( + json '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: json 'null', 'strict $[*]' + ^ +DETAIL: JSON_TABLE columns shall contain explicit AS pathname specification if explicit PLAN clause is used +-- JSON_TABLE: plan execution +CREATE TEMP TABLE json_table_test (js text); +INSERT INTO json_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 + json_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 + json_table_test jtt, + json_table ( + jtt.js FORMAT JSON,'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 + json_table_test jtt, + json_table ( + jtt.js FORMAT JSON,'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 + json_table_test jtt, + json_table ( + jtt.js FORMAT JSON,'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 + json_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 + json_table_test jtt, + json_table ( + jtt.js FORMAT JSON,'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 + json_table_test jtt, + json_table ( + jtt.js FORMAT JSON,'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 + json_table_test jtt, + json_table ( + jtt.js FORMAT JSON,'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 + json_table_test jtt, + json_table ( + jtt.js FORMAT JSON,'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 + json_table_test jtt, + json_table ( + jtt.js FORMAT JSON,'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 (json + '[ + {"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(json + '[[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( + json '[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(json '{"a": 123}', '$' || '.' || 'a'); json_exists @@ -1077,3 +2045,8 @@ SELECT JSON_QUERY(json '{"a": 123}', '$' || '.' || 'a' WITH WRAPPER); SELECT JSON_QUERY(json '{"a": 123}', 'error' || ' ' || 'error'); ERROR: bad jsonpath representation DETAIL: syntax error, unexpected IDENT_P at or near " " +-- Should fail (not supported) +SELECT * FROM JSON_TABLE(json '{"a": 123}', '$' || '.' || 'a' COLUMNS (foo int)); +ERROR: only string constants supported in JSON_TABLE path specification +LINE 1: SELECT * FROM JSON_TABLE(json '{"a": 123}', '$' || '.' || 'a... + ^ diff --git a/src/test/regress/sql/json_sqljson.sql b/src/test/regress/sql/json_sqljson.sql index 6fb0c6a..8508957 100644 --- a/src/test/regress/sql/json_sqljson.sql +++ b/src/test/regress/sql/json_sqljson.sql @@ -304,7 +304,590 @@ DROP TABLE test_json_constraints; -- JSON_TABLE -SELECT * FROM JSON_TABLE(NULL FORMAT JSON, '$' COLUMNS (foo text)); +-- 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, '$' COLUMNS (foo int)) bar; + +-- invalid json => empty table +SELECT * FROM JSON_TABLE('', '$' COLUMNS (foo int)) bar; +SELECT * FROM JSON_TABLE('' FORMAT JSON, '$' COLUMNS (foo int)) bar; + +-- invalid json => error +SELECT * FROM JSON_TABLE('' FORMAT JSON, '$' COLUMNS (foo int) ERROR ON ERROR) bar; + +-- +SELECT * FROM JSON_TABLE('123' FORMAT JSON, '$' + COLUMNS (item int PATH '$', foo int)) bar; + +SELECT * FROM JSON_TABLE(json '123', '$' + COLUMNS (item int PATH '$', foo int)) bar; + +-- JSON_TABLE: basic functionality +SELECT * +FROM + (VALUES + ('1'), + ('[]'), + ('{}'), + ('[1, 1.23, "2", "aaaaaaa", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""]'), + ('err') + ) vals(js) + LEFT OUTER JOIN +-- JSON_TABLE is implicitly lateral + JSON_TABLE( + vals.js FORMAT json, '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 '$', + 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 '$', + aaa int, -- implicit path '$."aaa"', + aaa1 int PATH '$.aaa' + ) + ) jt + ON true; + +-- JSON_TABLE: Test backward parsing + +CREATE VIEW json_table_view AS +SELECT * FROM + JSON_TABLE( + 'null' FORMAT JSON, '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 '$', + 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 '$', + aaa int, -- implicit path '$."aaa"', + aaa1 int PATH '$.aaa', + 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 json_table_view + +EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM json_table_view; + +-- JSON_TABLE: ON EMPTY/ON ERROR behavior +SELECT * +FROM + (VALUES ('1'), ('err'), ('"err"')) vals(js), + JSON_TABLE(vals.js FORMAT JSON, '$' COLUMNS (a int PATH '$')) jt; + +SELECT * +FROM + (VALUES ('1'), ('err'), ('"err"')) vals(js) + LEFT OUTER JOIN + JSON_TABLE(vals.js FORMAT JSON, '$' COLUMNS (a int PATH '$') ERROR ON ERROR) jt + ON true; + +SELECT * +FROM + (VALUES ('1'), ('err'), ('"err"')) vals(js) + LEFT OUTER JOIN + JSON_TABLE(vals.js FORMAT JSON, '$' COLUMNS (a int PATH '$' ERROR ON ERROR)) jt + ON true; + +SELECT * FROM JSON_TABLE('1', '$' COLUMNS (a int PATH '$.a' ERROR ON EMPTY)) jt; +SELECT * FROM JSON_TABLE('1', '$' COLUMNS (a int PATH 'strict $.a' ERROR ON EMPTY) ERROR ON ERROR) jt; +SELECT * FROM JSON_TABLE('1', '$' COLUMNS (a int PATH 'lax $.a' ERROR ON EMPTY) ERROR ON ERROR) jt; + +SELECT * FROM JSON_TABLE(json '"a"', '$' COLUMNS (a int PATH '$' DEFAULT 1 ON EMPTY DEFAULT 2 ON ERROR)) jt; +SELECT * FROM JSON_TABLE(json '"a"', '$' COLUMNS (a int PATH 'strict $.a' DEFAULT 1 ON EMPTY DEFAULT 2 ON ERROR)) jt; +SELECT * FROM JSON_TABLE(json '"a"', '$' COLUMNS (a int PATH 'lax $.a' DEFAULT 1 ON EMPTY DEFAULT 2 ON ERROR)) jt; + +-- JSON_TABLE: nested paths and plans + +-- Should fail (JSON_TABLE columns shall contain explicit AS path +-- specifications if explicit PLAN clause is used) +SELECT * FROM JSON_TABLE( + json '[]', '$' -- AS required here + COLUMNS ( + foo int PATH '$' + ) + PLAN DEFAULT (UNION) +) jt; + +SELECT * FROM JSON_TABLE( + json '[]', '$' AS path1 + COLUMNS ( + NESTED PATH '$' COLUMNS ( -- AS required here + foo int PATH '$' + ) + ) + PLAN DEFAULT (UNION) +) jt; + +-- Should fail (column names anf path names shall be distinct) +SELECT * FROM JSON_TABLE( + json '[]', '$' AS a + COLUMNS ( + a int + ) +) jt; + +SELECT * FROM JSON_TABLE( + json '[]', '$' AS a + COLUMNS ( + b int, + NESTED PATH '$' AS a + COLUMNS ( + c int + ) + ) +) jt; + +SELECT * FROM JSON_TABLE( + json '[]', '$' + COLUMNS ( + b int, + NESTED PATH '$' AS b + COLUMNS ( + c int + ) + ) +) jt; + +SELECT * FROM JSON_TABLE( + json '[]', '$' + COLUMNS ( + NESTED PATH '$' AS a + COLUMNS ( + b int + ), + NESTED PATH '$' + COLUMNS ( + NESTED PATH '$' AS a + COLUMNS ( + c int + ) + ) + ) +) jt; + +-- JSON_TABLE: plan validation + +SELECT * FROM JSON_TABLE( + json 'null', '$[*]' AS p0 + COLUMNS ( + NESTED PATH '$' AS p1 COLUMNS ( + NESTED PATH '$' AS p11 COLUMNS ( foo int ), + NESTED PATH '$' AS p12 COLUMNS ( bar int ) + ), + NESTED PATH '$' AS p2 COLUMNS ( + NESTED PATH '$' AS p21 COLUMNS ( baz int ) + ) + ) + PLAN (p1) +) jt; + +SELECT * FROM JSON_TABLE( + json 'null', '$[*]' AS p0 + COLUMNS ( + NESTED PATH '$' AS p1 COLUMNS ( + NESTED PATH '$' AS p11 COLUMNS ( foo int ), + NESTED PATH '$' AS p12 COLUMNS ( bar int ) + ), + NESTED PATH '$' AS p2 COLUMNS ( + NESTED PATH '$' AS p21 COLUMNS ( baz int ) + ) + ) + PLAN (p0) +) jt; + +SELECT * FROM JSON_TABLE( + json 'null', '$[*]' AS p0 + COLUMNS ( + NESTED PATH '$' AS p1 COLUMNS ( + NESTED PATH '$' AS p11 COLUMNS ( foo int ), + NESTED PATH '$' AS p12 COLUMNS ( bar int ) + ), + NESTED PATH '$' AS p2 COLUMNS ( + NESTED PATH '$' AS p21 COLUMNS ( baz int ) + ) + ) + PLAN (p0 OUTER p3) +) jt; + +SELECT * FROM JSON_TABLE( + json 'null', '$[*]' AS p0 + COLUMNS ( + NESTED PATH '$' AS p1 COLUMNS ( + NESTED PATH '$' AS p11 COLUMNS ( foo int ), + NESTED PATH '$' AS p12 COLUMNS ( bar int ) + ), + NESTED PATH '$' AS p2 COLUMNS ( + NESTED PATH '$' AS p21 COLUMNS ( baz int ) + ) + ) + PLAN (p0 UNION p1 UNION p11) +) jt; + +SELECT * FROM JSON_TABLE( + json 'null', '$[*]' AS p0 + COLUMNS ( + NESTED PATH '$' AS p1 COLUMNS ( + NESTED PATH '$' AS p11 COLUMNS ( foo int ), + NESTED PATH '$' AS p12 COLUMNS ( bar int ) + ), + NESTED PATH '$' AS p2 COLUMNS ( + NESTED PATH '$' AS p21 COLUMNS ( baz int ) + ) + ) + PLAN (p0 OUTER (p1 CROSS p13)) +) jt; + +SELECT * FROM JSON_TABLE( + json 'null', '$[*]' AS p0 + COLUMNS ( + NESTED PATH '$' AS p1 COLUMNS ( + NESTED PATH '$' AS p11 COLUMNS ( foo int ), + NESTED PATH '$' AS p12 COLUMNS ( bar int ) + ), + NESTED PATH '$' AS p2 COLUMNS ( + NESTED PATH '$' AS p21 COLUMNS ( baz int ) + ) + ) + PLAN (p0 OUTER (p1 CROSS p2)) +) jt; + +SELECT * FROM JSON_TABLE( + json 'null', '$[*]' AS p0 + COLUMNS ( + NESTED PATH '$' AS p1 COLUMNS ( + NESTED PATH '$' AS p11 COLUMNS ( foo int ), + NESTED PATH '$' AS p12 COLUMNS ( bar int ) + ), + NESTED PATH '$' AS p2 COLUMNS ( + NESTED PATH '$' AS p21 COLUMNS ( baz int ) + ) + ) + PLAN (p0 OUTER ((p1 UNION p11) CROSS p2)) +) jt; + +SELECT * FROM JSON_TABLE( + json 'null', '$[*]' AS p0 + COLUMNS ( + NESTED PATH '$' AS p1 COLUMNS ( + NESTED PATH '$' AS p11 COLUMNS ( foo int ), + NESTED PATH '$' AS p12 COLUMNS ( bar int ) + ), + NESTED PATH '$' AS p2 COLUMNS ( + NESTED PATH '$' AS p21 COLUMNS ( baz int ) + ) + ) + PLAN (p0 OUTER ((p1 INNER p11) CROSS p2)) +) jt; + +SELECT * FROM JSON_TABLE( + json 'null', '$[*]' AS p0 + COLUMNS ( + NESTED PATH '$' AS p1 COLUMNS ( + NESTED PATH '$' AS p11 COLUMNS ( foo int ), + NESTED PATH '$' AS p12 COLUMNS ( bar int ) + ), + NESTED PATH '$' AS p2 COLUMNS ( + NESTED PATH '$' AS p21 COLUMNS ( baz int ) + ) + ) + PLAN (p0 OUTER ((p1 INNER (p12 CROSS p11)) CROSS p2)) +) jt; + +SELECT * FROM JSON_TABLE( + json 'null', 'strict $[*]' AS p0 + COLUMNS ( + NESTED PATH '$' AS p1 COLUMNS ( + NESTED PATH '$' AS p11 COLUMNS ( foo int ), + NESTED PATH '$' AS p12 COLUMNS ( bar int ) + ), + NESTED PATH '$' AS p2 COLUMNS ( + NESTED PATH '$' AS p21 COLUMNS ( baz int ) + ) + ) + PLAN (p0 OUTER ((p1 INNER (p12 CROSS p11)) CROSS (p2 INNER p21))) +) jt; + +SELECT * FROM JSON_TABLE( + json 'null', 'strict $[*]' -- without root path name + COLUMNS ( + NESTED PATH '$' AS p1 COLUMNS ( + NESTED PATH '$' AS p11 COLUMNS ( foo int ), + NESTED PATH '$' AS p12 COLUMNS ( bar int ) + ), + NESTED PATH '$' AS p2 COLUMNS ( + NESTED PATH '$' AS p21 COLUMNS ( baz int ) + ) + ) + PLAN ((p1 INNER (p12 CROSS p11)) CROSS (p2 INNER p21)) +) jt; + +-- JSON_TABLE: plan execution + +CREATE TEMP TABLE json_table_test (js text); + +INSERT INTO json_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 + json_table_test jtt, + json_table ( + jtt.js,'strict $[*]' as p + columns ( + n for ordinality, + a int path 'lax $.a' default -1 on empty, + nested path 'strict $.b[*]' as pb columns ( b int path '$' ), + nested path 'strict $.c[*]' as pc columns ( c int path '$' ) + ) + ) jt; + +-- default plan (outer, union) +select + jt.* +from + json_table_test jtt, + json_table ( + jtt.js FORMAT JSON,'strict $[*]' as p + columns ( + n for ordinality, + a int path 'lax $.a' default -1 on empty, + nested path 'strict $.b[*]' as pb columns ( b int path '$' ), + nested path 'strict $.c[*]' as pc columns ( c int path '$' ) + ) + plan default (outer, union) + ) jt; + +-- specific plan (p outer (pb union pc)) +select + jt.* +from + json_table_test jtt, + json_table ( + jtt.js FORMAT JSON,'strict $[*]' as p + columns ( + n for ordinality, + a int path 'lax $.a' default -1 on empty, + nested path 'strict $.b[*]' as pb columns ( b int path '$' ), + nested path 'strict $.c[*]' as pc columns ( c int path '$' ) + ) + plan (p outer (pb union pc)) + ) jt; + +-- specific plan (p outer (pc union pb)) +select + jt.* +from + json_table_test jtt, + json_table ( + jtt.js FORMAT JSON,'strict $[*]' as p + columns ( + n for ordinality, + a int path 'lax $.a' default -1 on empty, + nested path 'strict $.b[*]' as pb columns ( b int path '$' ), + nested path 'strict $.c[*]' as pc columns ( c int path '$' ) + ) + plan (p outer (pc union pb)) + ) jt; + +-- default plan (inner, union) +select + jt.* +from + json_table_test jtt, + json_table ( + jtt.js,'strict $[*]' as p + columns ( + n for ordinality, + a int path 'lax $.a' default -1 on empty, + nested path 'strict $.b[*]' as pb columns ( b int path '$' ), + nested path 'strict $.c[*]' as pc columns ( c int path '$' ) + ) + plan default (inner) + ) jt; + +-- specific plan (p inner (pb union pc)) +select + jt.* +from + json_table_test jtt, + json_table ( + jtt.js FORMAT JSON,'strict $[*]' as p + columns ( + n for ordinality, + a int path 'lax $.a' default -1 on empty, + nested path 'strict $.b[*]' as pb columns ( b int path '$' ), + nested path 'strict $.c[*]' as pc columns ( c int path '$' ) + ) + plan (p inner (pb union pc)) + ) jt; + +-- default plan (inner, cross) +select + jt.* +from + json_table_test jtt, + json_table ( + jtt.js FORMAT JSON,'strict $[*]' as p + columns ( + n for ordinality, + a int path 'lax $.a' default -1 on empty, + nested path 'strict $.b[*]' as pb columns ( b int path '$' ), + nested path 'strict $.c[*]' as pc columns ( c int path '$' ) + ) + plan default (cross, inner) + ) jt; + +-- specific plan (p inner (pb cross pc)) +select + jt.* +from + json_table_test jtt, + json_table ( + jtt.js FORMAT JSON,'strict $[*]' as p + columns ( + n for ordinality, + a int path 'lax $.a' default -1 on empty, + nested path 'strict $.b[*]' as pb columns ( b int path '$' ), + nested path 'strict $.c[*]' as pc columns ( c int path '$' ) + ) + plan (p inner (pb cross pc)) + ) jt; + +-- default plan (outer, cross) +select + jt.* +from + json_table_test jtt, + json_table ( + jtt.js FORMAT JSON,'strict $[*]' as p + columns ( + n for ordinality, + a int path 'lax $.a' default -1 on empty, + nested path 'strict $.b[*]' as pb columns ( b int path '$' ), + nested path 'strict $.c[*]' as pc columns ( c int path '$' ) + ) + plan default (outer, cross) + ) jt; + +-- specific plan (p outer (pb cross pc)) +select + jt.* +from + json_table_test jtt, + json_table ( + jtt.js FORMAT JSON,'strict $[*]' as p + columns ( + n for ordinality, + a int path 'lax $.a' default -1 on empty, + nested path 'strict $.b[*]' as pb columns ( b int path '$' ), + nested path 'strict $.c[*]' as pc columns ( c int path '$' ) + ) + plan (p outer (pb cross pc)) + ) jt; + + +select + jt.*, b1 + 100 as b +from + json_table (json + '[ + {"a": 1, "b": [[1, 10], [2], [3, 30, 300]], "c": [1, null, 2]}, + {"a": 2, "b": [10, 20], "c": [1, null, 2]}, + {"x": "3", "b": [11, 22, 33, 44]} + ]', + '$[*]' as p + columns ( + n for ordinality, + a int path 'lax $.a' default -1 on error, + nested path 'strict $.b[*]' as pb columns ( + b text format json path '$', + nested path 'strict $[*]' as pb1 columns ( + b1 int path '$' + ) + ), + nested path 'strict $.c[*]' as pc columns ( + c text format json path '$', + nested path 'strict $[*]' as pc1 columns ( + c1 int path '$' + ) + ) + ) + --plan default(outer, cross) + plan(p outer ((pb inner pb1) cross (pc outer pc1))) + ) jt; + +-- Should succeed (JSON arguments are passed to root and nested paths) +SELECT * +FROM + generate_series(1, 4) x, + generate_series(1, 3) y, + JSON_TABLE(json + '[[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( + json '[1,2,3]', + '$[*] ? (@ < $x)' + PASSING 10 AS x + COLUMNS (y text FORMAT JSON PATH '$ ? (@ < $x)') + ) jt; -- Extension: non-constant JSON path SELECT JSON_EXISTS(json '{"a": 123}', '$' || '.' || 'a'); @@ -314,3 +897,5 @@ SELECT JSON_QUERY(json '{"a": 123}', '$' || '.' || 'a'); SELECT JSON_QUERY(json '{"a": 123}', '$' || '.' || 'a' WITH WRAPPER); -- Should fail (invalid path) SELECT JSON_QUERY(json '{"a": 123}', 'error' || ' ' || 'error'); +-- Should fail (not supported) +SELECT * FROM JSON_TABLE(json '{"a": 123}', '$' || '.' || 'a' COLUMNS (foo int)); -- 2.7.4