Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit4e34747

Browse files
committed
JSON_TABLE
This feature allows jsonb data to be treated as a table and thus used ina FROM clause like other tabular data. Data can be selected from thejsonb using jsonpath expressions, and hoisted out of nested structuresin the jsonb to form multiple rows, more or less like an outer join.Nikita GlukhovReviewers have included (in no particular order) Andres Freund, AlexanderKorotkov, Pavel Stehule, Andrew Alsup, Erik Rijkers, Zhihong Yu (whosename I previously misspelled), Himanshu Upadhyaya, Daniel Gustafsson,Justin Pryzby.Discussion:https://postgr.es/m/7e2cb85d-24cf-4abb-30a5-1a33715959bd@postgrespro.ru
1 parentc42a6fc commit4e34747

31 files changed

+2605
-34
lines changed

‎src/backend/commands/explain.c

Lines changed: 7 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -3796,7 +3796,13 @@ ExplainTargetRel(Plan *plan, Index rti, ExplainState *es)
37963796
break;
37973797
caseT_TableFuncScan:
37983798
Assert(rte->rtekind==RTE_TABLEFUNC);
3799-
objectname="xmltable";
3799+
if (rte->tablefunc)
3800+
if (rte->tablefunc->functype==TFT_XMLTABLE)
3801+
objectname="xmltable";
3802+
else/* Must be TFT_JSON_TABLE */
3803+
objectname="json_table";
3804+
else
3805+
objectname=NULL;
38003806
objecttag="Table Function Name";
38013807
break;
38023808
caseT_ValuesScan:

‎src/backend/executor/execExpr.c

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2635,6 +2635,7 @@ ExecInitExprRec(Expr *node, ExprState *state,
26352635
var->typmod=exprTypmod((Node*)argexpr);
26362636
var->estate=ExecInitExpr(argexpr,state->parent);
26372637
var->econtext=NULL;
2638+
var->mcxt=NULL;
26382639
var->evaluated= false;
26392640
var->value= (Datum)0;
26402641
var->isnull= true;

‎src/backend/executor/execExprInterp.c

Lines changed: 16 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -4602,6 +4602,7 @@ ExecEvalJsonBehavior(ExprContext *econtext, JsonBehavior *behavior,
46024602

46034603
caseJSON_BEHAVIOR_NULL:
46044604
caseJSON_BEHAVIOR_UNKNOWN:
4605+
caseJSON_BEHAVIOR_EMPTY:
46054606
*is_null= true;
46064607
return (Datum)0;
46074608

@@ -4694,8 +4695,14 @@ EvalJsonPathVar(void *cxt, char *varName, int varNameLen,
46944695

46954696
if (!var->evaluated)
46964697
{
4698+
MemoryContextoldcxt=var->mcxt ?
4699+
MemoryContextSwitchTo(var->mcxt) :NULL;
4700+
46974701
var->value=ExecEvalExpr(var->estate,var->econtext,&var->isnull);
46984702
var->evaluated= true;
4703+
4704+
if (oldcxt)
4705+
MemoryContextSwitchTo(oldcxt);
46994706
}
47004707

47014708
if (var->isnull)
@@ -4843,6 +4850,7 @@ ExecEvalJsonExprSubtrans(JsonFunc func, ExprEvalStep *op,
48434850
PG_CATCH();
48444851
{
48454852
ErrorData*edata;
4853+
intecategory;
48464854

48474855
/* Save error info in oldcontext */
48484856
MemoryContextSwitchTo(oldcontext);
@@ -4854,8 +4862,10 @@ ExecEvalJsonExprSubtrans(JsonFunc func, ExprEvalStep *op,
48544862
MemoryContextSwitchTo(oldcontext);
48554863
CurrentResourceOwner=oldowner;
48564864

4857-
if (ERRCODE_TO_CATEGORY(edata->sqlerrcode)!=
4858-
ERRCODE_DATA_EXCEPTION)
4865+
ecategory=ERRCODE_TO_CATEGORY(edata->sqlerrcode);
4866+
4867+
if (ecategory!=ERRCODE_DATA_EXCEPTION&&/* jsonpath and other data errors */
4868+
ecategory!=ERRCODE_INTEGRITY_CONSTRAINT_VIOLATION)/* domain errors */
48594869
ReThrowError(edata);
48604870

48614871
res= (Datum)0;
@@ -4981,6 +4991,10 @@ ExecEvalJsonExpr(ExprEvalStep *op, ExprContext *econtext,
49814991
break;
49824992
}
49834993

4994+
caseJSON_TABLE_OP:
4995+
*resnull= false;
4996+
returnitem;
4997+
49844998
default:
49854999
elog(ERROR,"unrecognized SQL/JSON expression op %d",jexpr->op);
49865000
return (Datum)0;

‎src/backend/executor/nodeTableFuncscan.c

Lines changed: 14 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -28,6 +28,7 @@
2828
#include"miscadmin.h"
2929
#include"nodes/execnodes.h"
3030
#include"utils/builtins.h"
31+
#include"utils/jsonpath.h"
3132
#include"utils/lsyscache.h"
3233
#include"utils/memutils.h"
3334
#include"utils/xml.h"
@@ -161,8 +162,9 @@ ExecInitTableFuncScan(TableFuncScan *node, EState *estate, int eflags)
161162
scanstate->ss.ps.qual=
162163
ExecInitQual(node->scan.plan.qual,&scanstate->ss.ps);
163164

164-
/* Only XMLTABLE is supported currently */
165-
scanstate->routine=&XmlTableRoutine;
165+
/* Only XMLTABLE and JSON_TABLE are supported currently */
166+
scanstate->routine=
167+
tf->functype==TFT_XMLTABLE ?&XmlTableRoutine :&JsonbTableRoutine;
166168

167169
scanstate->perTableCxt=
168170
AllocSetContextCreate(CurrentMemoryContext,
@@ -381,14 +383,17 @@ tfuncInitialize(TableFuncScanState *tstate, ExprContext *econtext, Datum doc)
381383
routine->SetNamespace(tstate,ns_name,ns_uri);
382384
}
383385

384-
/* Install the row filter expression into the table builder context */
385-
value=ExecEvalExpr(tstate->rowexpr,econtext,&isnull);
386-
if (isnull)
387-
ereport(ERROR,
388-
(errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED),
389-
errmsg("row filter expression must not be null")));
386+
if (routine->SetRowFilter)
387+
{
388+
/* Install the row filter expression into the table builder context */
389+
value=ExecEvalExpr(tstate->rowexpr,econtext,&isnull);
390+
if (isnull)
391+
ereport(ERROR,
392+
(errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED),
393+
errmsg("row filter expression must not be null")));
390394

391-
routine->SetRowFilter(tstate,TextDatumGetCString(value));
395+
routine->SetRowFilter(tstate,TextDatumGetCString(value));
396+
}
392397

393398
/*
394399
* Install the column filter expressions into the table builder context.

‎src/backend/nodes/copyfuncs.c

Lines changed: 85 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1394,6 +1394,7 @@ _copyTableFunc(const TableFunc *from)
13941394
{
13951395
TableFunc*newnode=makeNode(TableFunc);
13961396

1397+
COPY_SCALAR_FIELD(functype);
13971398
COPY_NODE_FIELD(ns_uris);
13981399
COPY_NODE_FIELD(ns_names);
13991400
COPY_NODE_FIELD(docexpr);
@@ -1404,7 +1405,9 @@ _copyTableFunc(const TableFunc *from)
14041405
COPY_NODE_FIELD(colcollations);
14051406
COPY_NODE_FIELD(colexprs);
14061407
COPY_NODE_FIELD(coldefexprs);
1408+
COPY_NODE_FIELD(colvalexprs);
14071409
COPY_BITMAPSET_FIELD(notnulls);
1410+
COPY_NODE_FIELD(plan);
14081411
COPY_SCALAR_FIELD(ordinalitycol);
14091412
COPY_LOCATION_FIELD(location);
14101413

@@ -2683,6 +2686,76 @@ _copyJsonArgument(const JsonArgument *from)
26832686
returnnewnode;
26842687
}
26852688

2689+
/*
2690+
* _copyJsonTable
2691+
*/
2692+
staticJsonTable*
2693+
_copyJsonTable(constJsonTable*from)
2694+
{
2695+
JsonTable*newnode=makeNode(JsonTable);
2696+
2697+
COPY_NODE_FIELD(common);
2698+
COPY_NODE_FIELD(columns);
2699+
COPY_NODE_FIELD(on_error);
2700+
COPY_NODE_FIELD(alias);
2701+
COPY_SCALAR_FIELD(location);
2702+
2703+
returnnewnode;
2704+
}
2705+
2706+
/*
2707+
* _copyJsonTableColumn
2708+
*/
2709+
staticJsonTableColumn*
2710+
_copyJsonTableColumn(constJsonTableColumn*from)
2711+
{
2712+
JsonTableColumn*newnode=makeNode(JsonTableColumn);
2713+
2714+
COPY_SCALAR_FIELD(coltype);
2715+
COPY_STRING_FIELD(name);
2716+
COPY_NODE_FIELD(typeName);
2717+
COPY_STRING_FIELD(pathspec);
2718+
COPY_SCALAR_FIELD(format);
2719+
COPY_SCALAR_FIELD(wrapper);
2720+
COPY_SCALAR_FIELD(omit_quotes);
2721+
COPY_NODE_FIELD(columns);
2722+
COPY_NODE_FIELD(on_empty);
2723+
COPY_NODE_FIELD(on_error);
2724+
COPY_SCALAR_FIELD(location);
2725+
2726+
returnnewnode;
2727+
}
2728+
2729+
/*
2730+
* _copyJsonTableParent
2731+
*/
2732+
staticJsonTableParent*
2733+
_copyJsonTableParent(constJsonTableParent*from)
2734+
{
2735+
JsonTableParent*newnode=makeNode(JsonTableParent);
2736+
2737+
COPY_NODE_FIELD(path);
2738+
COPY_NODE_FIELD(child);
2739+
COPY_SCALAR_FIELD(colMin);
2740+
COPY_SCALAR_FIELD(colMax);
2741+
2742+
returnnewnode;
2743+
}
2744+
2745+
/*
2746+
* _copyJsonTableSibling
2747+
*/
2748+
staticJsonTableSibling*
2749+
_copyJsonTableSibling(constJsonTableSibling*from)
2750+
{
2751+
JsonTableSibling*newnode=makeNode(JsonTableSibling);
2752+
2753+
COPY_NODE_FIELD(larg);
2754+
COPY_NODE_FIELD(rarg);
2755+
2756+
returnnewnode;
2757+
}
2758+
26862759
/* ****************************************************************
26872760
*pathnodes.h copy functions
26882761
*
@@ -5850,6 +5923,18 @@ copyObjectImpl(const void *from)
58505923
caseT_JsonItemCoercions:
58515924
retval=_copyJsonItemCoercions(from);
58525925
break;
5926+
caseT_JsonTable:
5927+
retval=_copyJsonTable(from);
5928+
break;
5929+
caseT_JsonTableColumn:
5930+
retval=_copyJsonTableColumn(from);
5931+
break;
5932+
caseT_JsonTableParent:
5933+
retval=_copyJsonTableParent(from);
5934+
break;
5935+
caseT_JsonTableSibling:
5936+
retval=_copyJsonTableSibling(from);
5937+
break;
58535938

58545939
/*
58555940
* RELATION NODES

‎src/backend/nodes/equalfuncs.c

Lines changed: 65 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -127,6 +127,7 @@ _equalRangeVar(const RangeVar *a, const RangeVar *b)
127127
staticbool
128128
_equalTableFunc(constTableFunc*a,constTableFunc*b)
129129
{
130+
COMPARE_SCALAR_FIELD(functype);
130131
COMPARE_NODE_FIELD(ns_uris);
131132
COMPARE_NODE_FIELD(ns_names);
132133
COMPARE_NODE_FIELD(docexpr);
@@ -137,13 +138,65 @@ _equalTableFunc(const TableFunc *a, const TableFunc *b)
137138
COMPARE_NODE_FIELD(colcollations);
138139
COMPARE_NODE_FIELD(colexprs);
139140
COMPARE_NODE_FIELD(coldefexprs);
141+
COMPARE_NODE_FIELD(colvalexprs);
140142
COMPARE_BITMAPSET_FIELD(notnulls);
143+
COMPARE_NODE_FIELD(plan);
141144
COMPARE_SCALAR_FIELD(ordinalitycol);
142145
COMPARE_LOCATION_FIELD(location);
143146

144147
return true;
145148
}
146149

150+
staticbool
151+
_equalJsonTable(constJsonTable*a,constJsonTable*b)
152+
{
153+
COMPARE_NODE_FIELD(common);
154+
COMPARE_NODE_FIELD(columns);
155+
COMPARE_NODE_FIELD(on_error);
156+
COMPARE_NODE_FIELD(alias);
157+
COMPARE_SCALAR_FIELD(location);
158+
159+
return true;
160+
}
161+
162+
staticbool
163+
_equalJsonTableColumn(constJsonTableColumn*a,constJsonTableColumn*b)
164+
{
165+
COMPARE_SCALAR_FIELD(coltype);
166+
COMPARE_STRING_FIELD(name);
167+
COMPARE_NODE_FIELD(typeName);
168+
COMPARE_STRING_FIELD(pathspec);
169+
COMPARE_SCALAR_FIELD(format);
170+
COMPARE_SCALAR_FIELD(wrapper);
171+
COMPARE_SCALAR_FIELD(omit_quotes);
172+
COMPARE_NODE_FIELD(columns);
173+
COMPARE_NODE_FIELD(on_empty);
174+
COMPARE_NODE_FIELD(on_error);
175+
COMPARE_SCALAR_FIELD(location);
176+
177+
return true;
178+
}
179+
180+
staticbool
181+
_equalJsonTableParent(constJsonTableParent*a,constJsonTableParent*b)
182+
{
183+
COMPARE_NODE_FIELD(path);
184+
COMPARE_NODE_FIELD(child);
185+
COMPARE_SCALAR_FIELD(colMin);
186+
COMPARE_SCALAR_FIELD(colMax);
187+
188+
return true;
189+
}
190+
191+
staticbool
192+
_equalJsonTableSibling(constJsonTableSibling*a,constJsonTableSibling*b)
193+
{
194+
COMPARE_NODE_FIELD(larg);
195+
COMPARE_NODE_FIELD(rarg);
196+
197+
return true;
198+
}
199+
147200
staticbool
148201
_equalIntoClause(constIntoClause*a,constIntoClause*b)
149202
{
@@ -3719,6 +3772,12 @@ equal(const void *a, const void *b)
37193772
caseT_JsonItemCoercions:
37203773
retval=_equalJsonItemCoercions(a,b);
37213774
break;
3775+
caseT_JsonTableParent:
3776+
retval=_equalJsonTableParent(a,b);
3777+
break;
3778+
caseT_JsonTableSibling:
3779+
retval=_equalJsonTableSibling(a,b);
3780+
break;
37223781

37233782
/*
37243783
* RELATION NODES
@@ -4341,6 +4400,12 @@ equal(const void *a, const void *b)
43414400
caseT_JsonArgument:
43424401
retval=_equalJsonArgument(a,b);
43434402
break;
4403+
caseT_JsonTable:
4404+
retval=_equalJsonTable(a,b);
4405+
break;
4406+
caseT_JsonTableColumn:
4407+
retval=_equalJsonTableColumn(a,b);
4408+
break;
43444409

43454410
default:
43464411
elog(ERROR,"unrecognized node type: %d",

‎src/backend/nodes/nodeFuncs.c

Lines changed: 27 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2466,6 +2466,8 @@ expression_tree_walker(Node *node,
24662466
return true;
24672467
if (walker(tf->coldefexprs,context))
24682468
return true;
2469+
if (walker(tf->colvalexprs,context))
2470+
return true;
24692471
}
24702472
break;
24712473
caseT_JsonValueExpr:
@@ -3513,6 +3515,7 @@ expression_tree_mutator(Node *node,
35133515
MUTATE(newnode->rowexpr,tf->rowexpr,Node*);
35143516
MUTATE(newnode->colexprs,tf->colexprs,List*);
35153517
MUTATE(newnode->coldefexprs,tf->coldefexprs,List*);
3518+
MUTATE(newnode->colvalexprs,tf->colvalexprs,List*);
35163519
return (Node*)newnode;
35173520
}
35183521
break;
@@ -4530,6 +4533,30 @@ raw_expression_tree_walker(Node *node,
45304533
return true;
45314534
}
45324535
break;
4536+
caseT_JsonTable:
4537+
{
4538+
JsonTable*jt= (JsonTable*)node;
4539+
4540+
if (walker(jt->common,context))
4541+
return true;
4542+
if (walker(jt->columns,context))
4543+
return true;
4544+
}
4545+
break;
4546+
caseT_JsonTableColumn:
4547+
{
4548+
JsonTableColumn*jtc= (JsonTableColumn*)node;
4549+
4550+
if (walker(jtc->typeName,context))
4551+
return true;
4552+
if (walker(jtc->on_empty,context))
4553+
return true;
4554+
if (walker(jtc->on_error,context))
4555+
return true;
4556+
if (jtc->coltype==JTC_NESTED&&walker(jtc->columns,context))
4557+
return true;
4558+
}
4559+
break;
45334560
default:
45344561
elog(ERROR,"unrecognized node type: %d",
45354562
(int)nodeTag(node));

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp