Skip to content

Commit fadb48b

Browse files
adunstanadunstan
committed
PLAN clauses for JSON_TABLE
These clauses allow the user to specify how data from nested paths are joined, allowing considerable freedom in shaping the tabular output of JSON_TABLE. PLAN DEFAULT allows the user to specify the global strategies when dealing with sibling or child nested paths. The is often sufficient to achieve the necessary goal, and is considerably simpler than the full PLAN clause, which allows the user to specify the strategy to be used for each named nested path. Nikita Glukhov Reviewers have included (in no particular order) Andres Freund, Alexander Korotkov, Pavel Stehule, Andrew Alsup, Erik Rijkers, Zhihong Yu, Himanshu Upadhyaya, Daniel Gustafsson, Justin Pryzby. Discussion: https://postgr.es/m/7e2cb85d-24cf-4abb-30a5-1a33715959bd@postgrespro.ru
1 parent e83ebfe commit fadb48b

File tree

17 files changed

+1614
-109
lines changed

17 files changed

+1614
-109
lines changed

‎src/backend/nodes/copyfuncs.c

Lines changed: 26 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2696,6 +2696,7 @@ _copyJsonTable(const JsonTable *from)
26962696

26972697
COPY_NODE_FIELD(common);
26982698
COPY_NODE_FIELD(columns);
2699+
COPY_NODE_FIELD(plan);
26992700
COPY_NODE_FIELD(on_error);
27002701
COPY_NODE_FIELD(alias);
27012702
COPY_SCALAR_FIELD(location);
@@ -2715,6 +2716,7 @@ _copyJsonTableColumn(const JsonTableColumn *from)
27152716
COPY_STRING_FIELD(name);
27162717
COPY_NODE_FIELD(typeName);
27172718
COPY_STRING_FIELD(pathspec);
2719+
COPY_STRING_FIELD(pathname);
27182720
COPY_SCALAR_FIELD(format);
27192721
COPY_SCALAR_FIELD(wrapper);
27202722
COPY_SCALAR_FIELD(omit_quotes);
@@ -2726,6 +2728,24 @@ _copyJsonTableColumn(const JsonTableColumn *from)
27262728
return newnode;
27272729
}
27282730

2731+
/*
2732+
* _copyJsonTablePlan
2733+
*/
2734+
static JsonTablePlan *
2735+
_copyJsonTablePlan(const JsonTablePlan *from)
2736+
{
2737+
JsonTablePlan *newnode = makeNode(JsonTablePlan);
2738+
2739+
COPY_SCALAR_FIELD(plan_type);
2740+
COPY_SCALAR_FIELD(join_type);
2741+
COPY_STRING_FIELD(pathname);
2742+
COPY_NODE_FIELD(plan1);
2743+
COPY_NODE_FIELD(plan2);
2744+
COPY_SCALAR_FIELD(location);
2745+
2746+
return newnode;
2747+
}
2748+
27292749
/*
27302750
* _copyJsonTableParent
27312751
*/
@@ -2735,7 +2755,9 @@ _copyJsonTableParent(const JsonTableParent *from)
27352755
JsonTableParent *newnode = makeNode(JsonTableParent);
27362756

27372757
COPY_NODE_FIELD(path);
2758+
COPY_STRING_FIELD(name);
27382759
COPY_NODE_FIELD(child);
2760+
COPY_SCALAR_FIELD(outerJoin);
27392761
COPY_SCALAR_FIELD(colMin);
27402762
COPY_SCALAR_FIELD(colMax);
27412763

@@ -2752,6 +2774,7 @@ _copyJsonTableSibling(const JsonTableSibling *from)
27522774

27532775
COPY_NODE_FIELD(larg);
27542776
COPY_NODE_FIELD(rarg);
2777+
COPY_SCALAR_FIELD(cross);
27552778

27562779
return newnode;
27572780
}
@@ -5929,6 +5952,9 @@ copyObjectImpl(const void *from)
59295952
case T_JsonTableColumn:
59305953
retval = _copyJsonTableColumn(from);
59315954
break;
5955+
case T_JsonTablePlan:
5956+
retval = _copyJsonTablePlan(from);
5957+
break;
59325958
case T_JsonTableParent:
59335959
retval = _copyJsonTableParent(from);
59345960
break;

‎src/backend/nodes/equalfuncs.c

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -181,7 +181,9 @@ static bool
181181
_equalJsonTableParent(const JsonTableParent *a, const JsonTableParent *b)
182182
{
183183
COMPARE_NODE_FIELD(path);
184+
COMPARE_STRING_FIELD(name);
184185
COMPARE_NODE_FIELD(child);
186+
COMPARE_SCALAR_FIELD(outerJoin);
185187
COMPARE_SCALAR_FIELD(colMin);
186188
COMPARE_SCALAR_FIELD(colMax);
187189

@@ -193,6 +195,7 @@ _equalJsonTableSibling(const JsonTableSibling *a, const JsonTableSibling *b)
193195
{
194196
COMPARE_NODE_FIELD(larg);
195197
COMPARE_NODE_FIELD(rarg);
198+
COMPARE_SCALAR_FIELD(cross);
196199

197200
return true;
198201
}

‎src/backend/nodes/makefuncs.c

Lines changed: 19 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -867,6 +867,25 @@ makeJsonBehavior(JsonBehaviorType type, Node *default_expr)
867867
return behavior;
868868
}
869869

870+
/*
871+
* makeJsonTableJoinedPlan -
872+
* creates a joined JsonTablePlan node
873+
*/
874+
Node *
875+
makeJsonTableJoinedPlan(JsonTablePlanJoinType type, Node *plan1, Node *plan2,
876+
int location)
877+
{
878+
JsonTablePlan *n = makeNode(JsonTablePlan);
879+
880+
n->plan_type = JSTP_JOINED;
881+
n->join_type = type;
882+
n->plan1 = castNode(JsonTablePlan, plan1);
883+
n->plan2 = castNode(JsonTablePlan, plan2);
884+
n->location = location;
885+
886+
return (Node *) n;
887+
}
888+
870889
/*
871890
* makeJsonEncoding -
872891
* converts JSON encoding name to enum JsonEncoding

‎src/backend/nodes/outfuncs.c

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1875,7 +1875,9 @@ _outJsonTableParent(StringInfo str, const JsonTableParent *node)
18751875
WRITE_NODE_TYPE("JSONTABPNODE");
18761876

18771877
WRITE_NODE_FIELD(path);
1878+
WRITE_STRING_FIELD(name);
18781879
WRITE_NODE_FIELD(child);
1880+
WRITE_BOOL_FIELD(outerJoin);
18791881
WRITE_INT_FIELD(colMin);
18801882
WRITE_INT_FIELD(colMax);
18811883
}
@@ -1887,6 +1889,7 @@ _outJsonTableSibling(StringInfo str, const JsonTableSibling *node)
18871889

18881890
WRITE_NODE_FIELD(larg);
18891891
WRITE_NODE_FIELD(rarg);
1892+
WRITE_BOOL_FIELD(cross);
18901893
}
18911894

18921895
/*****************************************************************************

‎src/backend/nodes/readfuncs.c

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1541,7 +1541,9 @@ _readJsonTableParent(void)
15411541
READ_LOCALS(JsonTableParent);
15421542

15431543
READ_NODE_FIELD(path);
1544+
READ_STRING_FIELD(name);
15441545
READ_NODE_FIELD(child);
1546+
READ_BOOL_FIELD(outerJoin);
15451547
READ_INT_FIELD(colMin);
15461548
READ_INT_FIELD(colMax);
15471549

@@ -1555,6 +1557,7 @@ _readJsonTableSibling(void)
15551557

15561558
READ_NODE_FIELD(larg);
15571559
READ_NODE_FIELD(rarg);
1560+
READ_BOOL_FIELD(cross);
15581561

15591562
READ_DONE();
15601563
}

‎src/backend/parser/gram.y

Lines changed: 126 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -683,6 +683,18 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
683683
json_table_formatted_column_definition
684684
json_table_exists_column_definition
685685
json_table_nested_columns
686+
json_table_plan_clause_opt
687+
json_table_specific_plan
688+
json_table_plan
689+
json_table_plan_simple
690+
json_table_plan_parent_child
691+
json_table_plan_outer
692+
json_table_plan_inner
693+
json_table_plan_sibling
694+
json_table_plan_union
695+
json_table_plan_cross
696+
json_table_plan_primary
697+
json_table_default_plan
686698

687699
%type <list> json_name_and_value_list
688700
json_value_expr_list
@@ -698,6 +710,9 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
698710

699711
%type <ival> json_encoding
700712
json_encoding_clause_opt
713+
json_table_default_plan_choices
714+
json_table_default_plan_inner_outer
715+
json_table_default_plan_union_cross
701716
json_wrapper_clause_opt
702717
json_wrapper_behavior
703718
json_conditional_or_unconditional_opt
@@ -812,7 +827,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
812827
ORDER ORDINALITY OTHERS OUT_P OUTER_P
813828
OVER OVERLAPS OVERLAY OVERRIDING OWNED OWNER
814829

815-
PARALLEL PARSER PARTIAL PARTITION PASSING PASSWORD PATH PLACING PLANS POLICY
830+
PARALLEL PARSER PARTIAL PARTITION PASSING PASSWORD PATH PLACING PLAN PLANS POLICY
816831
POSITION PRECEDING PRECISION PRESERVE PREPARE PREPARED PRIMARY
817832
PRIOR PRIVILEGES PROCEDURAL PROCEDURE PROCEDURES PROGRAM PUBLICATION
818833

@@ -15928,13 +15943,15 @@ json_table:
1592815943
JSON_TABLE '('
1592915944
json_api_common_syntax
1593015945
json_table_columns_clause
15946+
json_table_plan_clause_opt
1593115947
json_table_error_clause_opt
1593215948
')'
1593315949
{
1593415950
JsonTable *n = makeNode(JsonTable);
1593515951
n->common = (JsonCommon *) $3;
1593615952
n->columns = $4;
15937-
n->on_error = $5;
15953+
n->plan = (JsonTablePlan *) $5;
15954+
n->on_error = $6;
1593815955
n->location = @1;
1593915956
$$ = (Node *) n;
1594015957
}
@@ -16055,12 +16072,15 @@ json_table_formatted_column_definition:
1605516072
;
1605616073

1605716074
json_table_nested_columns:
16058-
NESTED path_opt Sconst json_table_columns_clause
16075+
NESTED path_opt Sconst
16076+
json_as_path_name_clause_opt
16077+
json_table_columns_clause
1605916078
{
1606016079
JsonTableColumn *n = makeNode(JsonTableColumn);
1606116080
n->coltype = JTC_NESTED;
1606216081
n->pathspec = $3;
16063-
n->columns = $4;
16082+
n->pathname = $4;
16083+
n->columns = $5;
1606416084
n->location = @1;
1606516085
$$ = (Node *) n;
1606616086
}
@@ -16071,6 +16091,106 @@ path_opt:
1607116091
| /* EMPTY */ { }
1607216092
;
1607316093

16094+
json_table_plan_clause_opt:
16095+
json_table_specific_plan { $$ = $1; }
16096+
| json_table_default_plan { $$ = $1; }
16097+
| /* EMPTY */ { $$ = NULL; }
16098+
;
16099+
16100+
json_table_specific_plan:
16101+
PLAN '(' json_table_plan ')' { $$ = $3; }
16102+
;
16103+
16104+
json_table_plan:
16105+
json_table_plan_simple
16106+
| json_table_plan_parent_child
16107+
| json_table_plan_sibling
16108+
;
16109+
16110+
json_table_plan_simple:
16111+
json_table_path_name
16112+
{
16113+
JsonTablePlan *n = makeNode(JsonTablePlan);
16114+
n->plan_type = JSTP_SIMPLE;
16115+
n->pathname = $1;
16116+
n->location = @1;
16117+
$$ = (Node *) n;
16118+
}
16119+
;
16120+
16121+
json_table_plan_parent_child:
16122+
json_table_plan_outer
16123+
| json_table_plan_inner
16124+
;
16125+
16126+
json_table_plan_outer:
16127+
json_table_plan_simple OUTER_P json_table_plan_primary
16128+
{ $$ = makeJsonTableJoinedPlan(JSTPJ_OUTER, $1, $3, @1); }
16129+
;
16130+
16131+
json_table_plan_inner:
16132+
json_table_plan_simple INNER_P json_table_plan_primary
16133+
{ $$ = makeJsonTableJoinedPlan(JSTPJ_INNER, $1, $3, @1); }
16134+
;
16135+
16136+
json_table_plan_sibling:
16137+
json_table_plan_union
16138+
| json_table_plan_cross
16139+
;
16140+
16141+
json_table_plan_union:
16142+
json_table_plan_primary UNION json_table_plan_primary
16143+
{ $$ = makeJsonTableJoinedPlan(JSTPJ_UNION, $1, $3, @1); }
16144+
| json_table_plan_union UNION json_table_plan_primary
16145+
{ $$ = makeJsonTableJoinedPlan(JSTPJ_UNION, $1, $3, @1); }
16146+
;
16147+
16148+
json_table_plan_cross:
16149+
json_table_plan_primary CROSS json_table_plan_primary
16150+
{ $$ = makeJsonTableJoinedPlan(JSTPJ_CROSS, $1, $3, @1); }
16151+
| json_table_plan_cross CROSS json_table_plan_primary
16152+
{ $$ = makeJsonTableJoinedPlan(JSTPJ_CROSS, $1, $3, @1); }
16153+
;
16154+
16155+
json_table_plan_primary:
16156+
json_table_plan_simple { $$ = $1; }
16157+
| '(' json_table_plan ')'
16158+
{
16159+
castNode(JsonTablePlan, $2)->location = @1;
16160+
$$ = $2;
16161+
}
16162+
;
16163+
16164+
json_table_default_plan:
16165+
PLAN DEFAULT '(' json_table_default_plan_choices ')'
16166+
{
16167+
JsonTablePlan *n = makeNode(JsonTablePlan);
16168+
n->plan_type = JSTP_DEFAULT;
16169+
n->join_type = $4;
16170+
n->location = @1;
16171+
$$ = (Node *) n;
16172+
}
16173+
;
16174+
16175+
json_table_default_plan_choices:
16176+
json_table_default_plan_inner_outer { $$ = $1 | JSTPJ_UNION; }
16177+
| json_table_default_plan_inner_outer ','
16178+
json_table_default_plan_union_cross { $$ = $1 | $3; }
16179+
| json_table_default_plan_union_cross { $$ = $1 | JSTPJ_OUTER; }
16180+
| json_table_default_plan_union_cross ','
16181+
json_table_default_plan_inner_outer { $$ = $1 | $3; }
16182+
;
16183+
16184+
json_table_default_plan_inner_outer:
16185+
INNER_P { $$ = JSTPJ_INNER; }
16186+
| OUTER_P { $$ = JSTPJ_OUTER; }
16187+
;
16188+
16189+
json_table_default_plan_union_cross:
16190+
UNION { $$ = JSTPJ_UNION; }
16191+
| CROSS { $$ = JSTPJ_CROSS; }
16192+
;
16193+
1607416194
json_returning_clause_opt:
1607516195
RETURNING Typename
1607616196
{
@@ -16951,6 +17071,7 @@ unreserved_keyword:
1695117071
| PASSING
1695217072
| PASSWORD
1695317073
| PATH
17074+
| PLAN
1695417075
| PLANS
1695517076
| POLICY
1695617077
| PRECEDING
@@ -17568,6 +17689,7 @@ bare_label_keyword:
1756817689
| PASSWORD
1756917690
| PATH
1757017691
| PLACING
17692+
| PLAN
1757117693
| PLANS
1757217694
| POLICY
1757317695
| POSITION

0 commit comments

Comments
 (0)