Skip to content

Commit 4e34747

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

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
case T_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
case T_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
case JSON_BEHAVIOR_NULL:
46044604
case JSON_BEHAVIOR_UNKNOWN:
4605+
case JSON_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+
MemoryContext oldcxt = 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+
int ecategory;
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+
case JSON_TABLE_OP:
4995+
*resnull = false;
4996+
return item;
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
return newnode;
26842687
}
26852688

2689+
/*
2690+
* _copyJsonTable
2691+
*/
2692+
static JsonTable *
2693+
_copyJsonTable(const JsonTable *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+
return newnode;
2704+
}
2705+
2706+
/*
2707+
* _copyJsonTableColumn
2708+
*/
2709+
static JsonTableColumn *
2710+
_copyJsonTableColumn(const JsonTableColumn *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+
return newnode;
2727+
}
2728+
2729+
/*
2730+
* _copyJsonTableParent
2731+
*/
2732+
static JsonTableParent *
2733+
_copyJsonTableParent(const JsonTableParent *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+
return newnode;
2743+
}
2744+
2745+
/*
2746+
* _copyJsonTableSibling
2747+
*/
2748+
static JsonTableSibling *
2749+
_copyJsonTableSibling(const JsonTableSibling *from)
2750+
{
2751+
JsonTableSibling *newnode = makeNode(JsonTableSibling);
2752+
2753+
COPY_NODE_FIELD(larg);
2754+
COPY_NODE_FIELD(rarg);
2755+
2756+
return newnode;
2757+
}
2758+
26862759
/* ****************************************************************
26872760
* pathnodes.h copy functions
26882761
*
@@ -5850,6 +5923,18 @@ copyObjectImpl(const void *from)
58505923
case T_JsonItemCoercions:
58515924
retval = _copyJsonItemCoercions(from);
58525925
break;
5926+
case T_JsonTable:
5927+
retval = _copyJsonTable(from);
5928+
break;
5929+
case T_JsonTableColumn:
5930+
retval = _copyJsonTableColumn(from);
5931+
break;
5932+
case T_JsonTableParent:
5933+
retval = _copyJsonTableParent(from);
5934+
break;
5935+
case T_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
static bool
128128
_equalTableFunc(const TableFunc *a, const TableFunc *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+
static bool
151+
_equalJsonTable(const JsonTable *a, const JsonTable *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+
static bool
163+
_equalJsonTableColumn(const JsonTableColumn *a, const JsonTableColumn *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+
static bool
181+
_equalJsonTableParent(const JsonTableParent *a, const JsonTableParent *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+
static bool
192+
_equalJsonTableSibling(const JsonTableSibling *a, const JsonTableSibling *b)
193+
{
194+
COMPARE_NODE_FIELD(larg);
195+
COMPARE_NODE_FIELD(rarg);
196+
197+
return true;
198+
}
199+
147200
static bool
148201
_equalIntoClause(const IntoClause *a, const IntoClause *b)
149202
{
@@ -3719,6 +3772,12 @@ equal(const void *a, const void *b)
37193772
case T_JsonItemCoercions:
37203773
retval = _equalJsonItemCoercions(a, b);
37213774
break;
3775+
case T_JsonTableParent:
3776+
retval = _equalJsonTableParent(a, b);
3777+
break;
3778+
case T_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
case T_JsonArgument:
43424401
retval = _equalJsonArgument(a, b);
43434402
break;
4403+
case T_JsonTable:
4404+
retval = _equalJsonTable(a, b);
4405+
break;
4406+
case T_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
case T_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+
case T_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+
case T_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)