Skip to content

Commit 1a36bc9

Browse files
adunstanadunstan
committed
SQL/JSON query functions
This introduces the SQL/JSON functions for querying JSON data using jsonpath expressions. The functions are: JSON_EXISTS() JSON_QUERY() JSON_VALUE() All of these functions only operate on jsonb. The workaround for now is to cast the argument to jsonb. JSON_EXISTS() tests if the jsonpath expression applied to the jsonb value yields any values. JSON_VALUE() must return a single value, and an error occurs if it tries to return multiple values. JSON_QUERY() must return a json object or array, and there are various WRAPPER options for handling scalar or multi-value results. Both these functions have options for handling EMPTY and ERROR conditions. Nikita Glukhov Reviewers have included (in no particular order) Andres Freund, Alexander Korotkov, Pavel Stehule, Andrew Alsup, Erik Rijkers, Zihong Yu, Himanshu Upadhyaya, Daniel Gustafsson, Justin Pryzby. Discussion: https://postgr.es/m/cd0bb935-0158-78a7-08b5-904886deac4b@postgrespro.ru
1 parent 3d067c5 commit 1a36bc9

39 files changed

+4716
-121
lines changed

‎src/backend/executor/execExpr.c

+155-51
Original file line numberDiff line numberDiff line change
@@ -47,6 +47,7 @@
4747
#include "utils/array.h"
4848
#include "utils/builtins.h"
4949
#include "utils/datum.h"
50+
#include "utils/jsonpath.h"
5051
#include "utils/lsyscache.h"
5152
#include "utils/typcache.h"
5253

@@ -85,6 +86,40 @@ static void ExecBuildAggTransCall(ExprState *state, AggState *aggstate,
8586
bool nullcheck);
8687

8788

89+
static ExprState *
90+
ExecInitExprInternal(Expr *node, PlanState *parent, ParamListInfo ext_params,
91+
Datum *caseval, bool *casenull)
92+
{
93+
ExprState *state;
94+
ExprEvalStep scratch = {0};
95+
96+
/* Special case: NULL expression produces a NULL ExprState pointer */
97+
if (node == NULL)
98+
return NULL;
99+
100+
/* Initialize ExprState with empty step list */
101+
state = makeNode(ExprState);
102+
state->expr = node;
103+
state->parent = parent;
104+
state->ext_params = ext_params;
105+
state->innermost_caseval = caseval;
106+
state->innermost_casenull = casenull;
107+
108+
/* Insert EEOP_*_FETCHSOME steps as needed */
109+
ExecInitExprSlots(state, (Node *) node);
110+
111+
/* Compile the expression proper */
112+
ExecInitExprRec(node, state, &state->resvalue, &state->resnull);
113+
114+
/* Finally, append a DONE step */
115+
scratch.opcode = EEOP_DONE;
116+
ExprEvalPushStep(state, &scratch);
117+
118+
ExecReadyExpr(state);
119+
120+
return state;
121+
}
122+
88123
/*
89124
* ExecInitExpr: prepare an expression tree for execution
90125
*
@@ -122,32 +157,7 @@ static void ExecBuildAggTransCall(ExprState *state, AggState *aggstate,
122157
ExprState *
123158
ExecInitExpr(Expr *node, PlanState *parent)
124159
{
125-
ExprState *state;
126-
ExprEvalStep scratch = {0};
127-
128-
/* Special case: NULL expression produces a NULL ExprState pointer */
129-
if (node == NULL)
130-
return NULL;
131-
132-
/* Initialize ExprState with empty step list */
133-
state = makeNode(ExprState);
134-
state->expr = node;
135-
state->parent = parent;
136-
state->ext_params = NULL;
137-
138-
/* Insert EEOP_*_FETCHSOME steps as needed */
139-
ExecInitExprSlots(state, (Node *) node);
140-
141-
/* Compile the expression proper */
142-
ExecInitExprRec(node, state, &state->resvalue, &state->resnull);
143-
144-
/* Finally, append a DONE step */
145-
scratch.opcode = EEOP_DONE;
146-
ExprEvalPushStep(state, &scratch);
147-
148-
ExecReadyExpr(state);
149-
150-
return state;
160+
return ExecInitExprInternal(node, parent, NULL, NULL, NULL);
151161
}
152162

153163
/*
@@ -159,32 +169,20 @@ ExecInitExpr(Expr *node, PlanState *parent)
159169
ExprState *
160170
ExecInitExprWithParams(Expr *node, ParamListInfo ext_params)
161171
{
162-
ExprState *state;
163-
ExprEvalStep scratch = {0};
164-
165-
/* Special case: NULL expression produces a NULL ExprState pointer */
166-
if (node == NULL)
167-
return NULL;
168-
169-
/* Initialize ExprState with empty step list */
170-
state = makeNode(ExprState);
171-
state->expr = node;
172-
state->parent = NULL;
173-
state->ext_params = ext_params;
174-
175-
/* Insert EEOP_*_FETCHSOME steps as needed */
176-
ExecInitExprSlots(state, (Node *) node);
177-
178-
/* Compile the expression proper */
179-
ExecInitExprRec(node, state, &state->resvalue, &state->resnull);
180-
181-
/* Finally, append a DONE step */
182-
scratch.opcode = EEOP_DONE;
183-
ExprEvalPushStep(state, &scratch);
184-
185-
ExecReadyExpr(state);
172+
return ExecInitExprInternal(node, NULL, ext_params, NULL, NULL);
173+
}
186174

187-
return state;
175+
/*
176+
* ExecInitExprWithCaseValue: prepare an expression tree for execution
177+
*
178+
* This is the same as ExecInitExpr, except that a pointer to the value for
179+
* CasTestExpr is passed here.
180+
*/
181+
ExprState *
182+
ExecInitExprWithCaseValue(Expr *node, PlanState *parent,
183+
Datum *caseval, bool *casenull)
184+
{
185+
return ExecInitExprInternal(node, parent, NULL, caseval, casenull);
188186
}
189187

190188
/*
@@ -2526,6 +2524,112 @@ ExecInitExprRec(Expr *node, ExprState *state,
25262524
break;
25272525
}
25282526

2527+
case T_JsonExpr:
2528+
{
2529+
JsonExpr *jexpr = castNode(JsonExpr, node);
2530+
ListCell *argexprlc;
2531+
ListCell *argnamelc;
2532+
2533+
scratch.opcode = EEOP_JSONEXPR;
2534+
scratch.d.jsonexpr.jsexpr = jexpr;
2535+
2536+
scratch.d.jsonexpr.formatted_expr =
2537+
palloc(sizeof(*scratch.d.jsonexpr.formatted_expr));
2538+
2539+
ExecInitExprRec((Expr *) jexpr->formatted_expr, state,
2540+
&scratch.d.jsonexpr.formatted_expr->value,
2541+
&scratch.d.jsonexpr.formatted_expr->isnull);
2542+
2543+
scratch.d.jsonexpr.pathspec =
2544+
palloc(sizeof(*scratch.d.jsonexpr.pathspec));
2545+
2546+
ExecInitExprRec((Expr *) jexpr->path_spec, state,
2547+
&scratch.d.jsonexpr.pathspec->value,
2548+
&scratch.d.jsonexpr.pathspec->isnull);
2549+
2550+
scratch.d.jsonexpr.res_expr =
2551+
palloc(sizeof(*scratch.d.jsonexpr.res_expr));
2552+
2553+
scratch.d.jsonexpr.result_expr = jexpr->result_coercion
2554+
? ExecInitExprWithCaseValue((Expr *) jexpr->result_coercion->expr,
2555+
state->parent,
2556+
&scratch.d.jsonexpr.res_expr->value,
2557+
&scratch.d.jsonexpr.res_expr->isnull)
2558+
: NULL;
2559+
2560+
scratch.d.jsonexpr.default_on_empty = !jexpr->on_empty ? NULL :
2561+
ExecInitExpr((Expr *) jexpr->on_empty->default_expr,
2562+
state->parent);
2563+
2564+
scratch.d.jsonexpr.default_on_error =
2565+
ExecInitExpr((Expr *) jexpr->on_error->default_expr,
2566+
state->parent);
2567+
2568+
if (jexpr->omit_quotes ||
2569+
(jexpr->result_coercion && jexpr->result_coercion->via_io))
2570+
{
2571+
Oid typinput;
2572+
2573+
/* lookup the result type's input function */
2574+
getTypeInputInfo(jexpr->returning->typid, &typinput,
2575+
&scratch.d.jsonexpr.input.typioparam);
2576+
fmgr_info(typinput, &scratch.d.jsonexpr.input.func);
2577+
}
2578+
2579+
scratch.d.jsonexpr.args = NIL;
2580+
2581+
forboth(argexprlc, jexpr->passing_values,
2582+
argnamelc, jexpr->passing_names)
2583+
{
2584+
Expr *argexpr = (Expr *) lfirst(argexprlc);
2585+
String *argname = lfirst_node(String, argnamelc);
2586+
JsonPathVariableEvalContext *var = palloc(sizeof(*var));
2587+
2588+
var->name = pstrdup(argname->sval);
2589+
var->typid = exprType((Node *) argexpr);
2590+
var->typmod = exprTypmod((Node *) argexpr);
2591+
var->estate = ExecInitExpr(argexpr, state->parent);
2592+
var->econtext = NULL;
2593+
var->evaluated = false;
2594+
var->value = (Datum) 0;
2595+
var->isnull = true;
2596+
2597+
scratch.d.jsonexpr.args =
2598+
lappend(scratch.d.jsonexpr.args, var);
2599+
}
2600+
2601+
scratch.d.jsonexpr.cache = NULL;
2602+
2603+
if (jexpr->coercions)
2604+
{
2605+
JsonCoercion **coercion;
2606+
struct JsonCoercionState *cstate;
2607+
Datum *caseval;
2608+
bool *casenull;
2609+
2610+
scratch.d.jsonexpr.coercion_expr =
2611+
palloc(sizeof(*scratch.d.jsonexpr.coercion_expr));
2612+
2613+
caseval = &scratch.d.jsonexpr.coercion_expr->value;
2614+
casenull = &scratch.d.jsonexpr.coercion_expr->isnull;
2615+
2616+
for (cstate = &scratch.d.jsonexpr.coercions.null,
2617+
coercion = &jexpr->coercions->null;
2618+
coercion <= &jexpr->coercions->composite;
2619+
coercion++, cstate++)
2620+
{
2621+
cstate->coercion = *coercion;
2622+
cstate->estate = *coercion ?
2623+
ExecInitExprWithCaseValue((Expr *)(*coercion)->expr,
2624+
state->parent,
2625+
caseval, casenull) : NULL;
2626+
}
2627+
}
2628+
2629+
ExprEvalPushStep(state, &scratch);
2630+
break;
2631+
}
2632+
25292633
default:
25302634
elog(ERROR, "unrecognized node type: %d",
25312635
(int) nodeTag(node));

0 commit comments

Comments
 (0)