Skip to content

Commit ef30f6f

Browse files
feodorfeodor
committed
add jsonb any element
1 parent a4656b3 commit ef30f6f

File tree

6 files changed

+236
-25
lines changed

6 files changed

+236
-25
lines changed

‎src/backend/parser/gram.y

Lines changed: 53 additions & 19 deletions
Original file line numberDiff line numberDiff line change
@@ -176,8 +176,8 @@ static void processCASbits(int cas_bits, int location, const char *constrType,
176176
bool *deferrable, bool *initdeferred, bool *not_valid,
177177
bool *no_inherit, core_yyscan_t yyscanner);
178178
static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
179-
static SelectStmt * makeElementSubselect(Node *of, const char *aliasname,
180-
Node *clause, int location);
179+
static SelectStmt * makeElementSubselect(int kind, bool recursive, Node *of,
180+
const char *aliasname, Node *clause, int location);
181181

182182
%}
183183

@@ -291,6 +291,7 @@ static SelectStmt * makeElementSubselect(Node *of, const char *aliasname,
291291
%type <boolean> opt_or_replace
292292
opt_grant_grant_option opt_grant_admin_option
293293
opt_nowait opt_if_exists opt_with_data
294+
opt_anywhere
294295
%type <ival> opt_nowait_or_skip
295296

296297
%type <list> OptRoleList AlterOptRoleList
@@ -448,6 +449,7 @@ static SelectStmt * makeElementSubselect(Node *of, const char *aliasname,
448449
%type <node> case_expr case_arg when_clause case_default
449450
%type <list> when_clause_list
450451
%type <ival> sub_type
452+
%type <ival> any_or_each any_or_each_kind
451453
%type <node> ctext_expr
452454
%type <value> NumericOnly
453455
%type <list> NumericOnly_list
@@ -563,7 +565,7 @@ static SelectStmt * makeElementSubselect(Node *of, const char *aliasname,
563565

564566
/* ordinary key words in alphabetical order */
565567
%token <keyword> ABORT_P ABSOLUTE_P ACCESS ACTION ADD_P ADMIN AFTER
566-
AGGREGATE ALL ALSO ALTER ALWAYS ANALYSE ANALYZE AND ANY ARRAY AS ASC
568+
AGGREGATE ALL ALSO ALTER ALWAYS ANALYSE ANALYZE AND ANY ANYWHERE ARRAY AS ASC
567569
ASSERTION ASSIGNMENT ASYMMETRIC AT ATTRIBUTE AUTHORIZATION
568570

569571
BACKWARD BEFORE BEGIN_P BETWEEN BIGINT BINARY BIT
@@ -12058,30 +12060,42 @@ c_expr: columnref { $$ = $1; }
1205812060
g->location = @1;
1205912061
$$ = (Node *)g;
1206012062
}
12061-
| ANY_EL ELEMENT OF b_expr AS ColId SATISFIES '(' a_expr ')'
12063+
| any_or_each any_or_each_kind opt_anywhere OF b_expr AS ColId SATISFIES '(' a_expr ')'
1206212064
{
1206312065
SubLink *n = makeNode(SubLink);
1206412066

1206512067
n->subLinkType = EXISTS_SUBLINK;
1206612068
n->subLinkId = 0;
1206712069
n->testexpr = NULL;
1206812070
n->operName = NIL;
12069-
n->subselect = (Node*)makeElementSubselect($4, $6, $9, @1);
12071+
n->subselect = (Node*)makeElementSubselect(
12072+
$2, $3,
12073+
$5, $7,
12074+
($1 == EACH) ? makeNotExpr($10, @1) : $10,
12075+
@1
12076+
);
1207012077
n->location = @1;
12071-
$$ = (Node *)n;
12078+
if ($1 == EACH)
12079+
$$ = makeNotExpr((Node *)n, @1);
12080+
else
12081+
$$ = (Node *)n;
1207212082
}
12073-
| EACH ELEMENT OF b_expr AS ColId SATISFIES '(' a_expr ')'
12074-
{
12075-
SubLink *n = makeNode(SubLink);
12083+
;
1207612084

12077-
n->subLinkType = EXISTS_SUBLINK;
12078-
n->subLinkId = 0;
12079-
n->testexpr = NULL;
12080-
n->operName = NIL;
12081-
n->subselect = (Node*)makeElementSubselect($4, $6, makeNotExpr($9, @1), @1);
12082-
n->location = @1;
12083-
$$ = makeNotExpr((Node *)n, @1);;
12084-
}
12085+
any_or_each:
12086+
ANY_EL { $$ = ANY_EL; }
12087+
| EACH { $$ = EACH; }
12088+
;
12089+
12090+
any_or_each_kind:
12091+
ELEMENT { $$ = ELEMENT; }
12092+
| KEY { $$ = KEY; }
12093+
| VALUE_P { $$ = VALUE_P; }
12094+
;
12095+
12096+
opt_anywhere:
12097+
ANYWHERE { $$ = true; }
12098+
| /* empty */ { $$ = false; }
1208512099
;
1208612100

1208712101
func_application: func_name '(' ')'
@@ -13614,6 +13628,7 @@ unreserved_keyword:
1361413628
| ALSO
1361513629
| ALTER
1361613630
| ALWAYS
13631+
| ANYWHERE
1361713632
| ASSERTION
1361813633
| ASSIGNMENT
1361913634
| AT
@@ -14858,17 +14873,36 @@ makeRecursiveViewSelect(char *relname, List *aliases, Node *query)
1485814873
}
1485914874

1486014875
static SelectStmt *
14861-
makeElementSubselect(Node *of, const char *aliasname, Node *clause, int location)
14876+
makeElementSubselect(int kind, bool recursive, Node *of,
14877+
const char *aliasname, Node *clause, int location)
1486214878
{
1486314879
ResTarget *target = makeNode(ResTarget);
1486414880
FuncCall *func_call;
1486514881
RangeFunction *table_ref = makeNode(RangeFunction);
1486614882
SelectStmt *subselect = makeNode(SelectStmt);
14883+
char *funcname;
1486714884

1486814885
target->val = (Node*)makeIntConst(1, location);
1486914886
target->location = location;
1487014887

14871-
func_call = makeFuncCall(SystemFuncName("unnest"), list_make1(of), location);
14888+
switch(kind)
14889+
{
14890+
case ELEMENT:
14891+
funcname = "unnest_element";
14892+
break;
14893+
case KEY:
14894+
funcname = "unnest_key";
14895+
break;
14896+
case VALUE_P:
14897+
funcname = "unnest_value";
14898+
break;
14899+
default:
14900+
elog(ERROR, "unkown ANY_EL");
14901+
}
14902+
14903+
func_call = makeFuncCall(SystemFuncName(funcname),
14904+
list_make2(of, makeBoolAConst(recursive, location)),
14905+
location);
1487214906

1487314907
table_ref->functions = list_make1(list_make2(func_call, NIL));
1487414908
table_ref->alias = makeAlias(aliasname, NIL);

‎src/backend/parser/parser.c

Lines changed: 9 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -191,8 +191,15 @@ base_yylex(YYSTYPE *lvalp, YYLTYPE *llocp, core_yyscan_t yyscanner)
191191
}
192192
break;
193193
case ANY:
194-
if (next_token == ELEMENT)
195-
cur_token = ANY_EL;
194+
/* Replace ANY by ANY_EL if it's followed by ELEMENT or KEY or VALUE */
195+
switch (next_token)
196+
{
197+
case ELEMENT:
198+
case KEY:
199+
case VALUE_P:
200+
cur_token = ANY_EL;
201+
break;
202+
}
196203
break;
197204
}
198205

‎src/backend/utils/adt/jsonfuncs.c

Lines changed: 153 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3898,3 +3898,156 @@ replacePathArray(JsonbIterator **it, Datum *path_elems, bool *path_nulls,
38983898
}
38993899
}
39003900
}
3901+
3902+
typedef struct UnnestState
3903+
{
3904+
MemoryContext ctx;
3905+
Jsonb *jb;
3906+
JsonbIterator *it;
3907+
bool skipNested;
3908+
JsonbIteratorToken type;
3909+
}
3910+
UnnestState;
3911+
3912+
static void finiUnnest(UnnestState *state);
3913+
3914+
static void
3915+
initUnnest(FuncCallContext *funcctx, Datum jsonb, bool recursive,
3916+
JsonbIteratorToken type)
3917+
{
3918+
MemoryContext oldcontext;
3919+
UnnestState *state;
3920+
JsonbValue v;
3921+
int r;
3922+
3923+
oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
3924+
3925+
state = palloc0(sizeof(*state));
3926+
state->ctx = funcctx->multi_call_memory_ctx;
3927+
state->jb = DatumGetJsonbCopy(jsonb);
3928+
state->it = JsonbIteratorInit(&state->jb->root);
3929+
state->skipNested = !recursive;
3930+
state->type = type;
3931+
3932+
r = JsonbIteratorNext(&state->it, &v, false);
3933+
3934+
if (JB_ROOT_IS_SCALAR(state->jb))
3935+
{
3936+
r = JsonbIteratorNext(&state->it, &v, true);
3937+
r = JsonbIteratorNext(&state->it, &v, true);
3938+
Assert(r == WJB_DONE);
3939+
}
3940+
3941+
MemoryContextSwitchTo(oldcontext);
3942+
3943+
if (r == WJB_DONE)
3944+
{
3945+
finiUnnest(state);
3946+
state = NULL;
3947+
}
3948+
3949+
funcctx->user_fctx = (void *) state;
3950+
}
3951+
3952+
static Jsonb*
3953+
nextUnnest(UnnestState *state)
3954+
{
3955+
MemoryContext oldcontext;
3956+
JsonbValue v;
3957+
int r;
3958+
3959+
/*
3960+
* Iterator should work in long-lived memory context
3961+
*/
3962+
oldcontext = MemoryContextSwitchTo(state->ctx);
3963+
3964+
while((r = JsonbIteratorNext(&state->it, &v, state->skipNested)) != WJB_DONE)
3965+
{
3966+
if (r == state->type)
3967+
break;
3968+
}
3969+
3970+
MemoryContextSwitchTo(oldcontext);
3971+
3972+
return (r == state->type) ? JsonbValueToJsonb(&v) : NULL;
3973+
}
3974+
3975+
static void
3976+
finiUnnest(UnnestState *state)
3977+
{
3978+
if (state)
3979+
{
3980+
pfree(state->jb);
3981+
pfree(state);
3982+
}
3983+
}
3984+
3985+
Datum
3986+
jsonb_unnest_element(PG_FUNCTION_ARGS)
3987+
{
3988+
FuncCallContext *funcctx;
3989+
UnnestState *state;
3990+
Jsonb *r;
3991+
3992+
if (SRF_IS_FIRSTCALL())
3993+
initUnnest(SRF_FIRSTCALL_INIT(), PG_GETARG_DATUM(0),
3994+
PG_GETARG_BOOL(1), WJB_ELEM);
3995+
3996+
funcctx = SRF_PERCALL_SETUP();
3997+
state = funcctx->user_fctx;
3998+
3999+
if (state == NULL || (r = nextUnnest(state)) == NULL)
4000+
{
4001+
finiUnnest(state);
4002+
SRF_RETURN_DONE(funcctx);
4003+
}
4004+
4005+
SRF_RETURN_NEXT(funcctx, JsonbGetDatum(r));
4006+
}
4007+
4008+
Datum
4009+
jsonb_unnest_value(PG_FUNCTION_ARGS)
4010+
{
4011+
FuncCallContext *funcctx;
4012+
UnnestState *state;
4013+
Jsonb *r;
4014+
4015+
if (SRF_IS_FIRSTCALL())
4016+
initUnnest(SRF_FIRSTCALL_INIT(), PG_GETARG_DATUM(0),
4017+
PG_GETARG_BOOL(1), WJB_VALUE);
4018+
4019+
funcctx = SRF_PERCALL_SETUP();
4020+
state = funcctx->user_fctx;
4021+
4022+
if (state == NULL || (r = nextUnnest(state)) == NULL)
4023+
{
4024+
finiUnnest(state);
4025+
SRF_RETURN_DONE(funcctx);
4026+
}
4027+
4028+
SRF_RETURN_NEXT(funcctx, JsonbGetDatum(r));
4029+
}
4030+
4031+
Datum
4032+
jsonb_unnest_key(PG_FUNCTION_ARGS)
4033+
{
4034+
FuncCallContext *funcctx;
4035+
UnnestState *state;
4036+
Jsonb *r;
4037+
4038+
if (SRF_IS_FIRSTCALL())
4039+
initUnnest(SRF_FIRSTCALL_INIT(), PG_GETARG_DATUM(0),
4040+
PG_GETARG_BOOL(1), WJB_KEY);
4041+
4042+
funcctx = SRF_PERCALL_SETUP();
4043+
state = funcctx->user_fctx;
4044+
4045+
if (state == NULL || (r = nextUnnest(state)) == NULL)
4046+
{
4047+
finiUnnest(state);
4048+
SRF_RETURN_DONE(funcctx);
4049+
}
4050+
4051+
SRF_RETURN_NEXT(funcctx, JsonbGetDatum(r));
4052+
}
4053+

‎src/include/catalog/pg_proc.h

Lines changed: 9 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -924,6 +924,9 @@ DATA(insert OID = 1286 ( array_fill PGNSP PGUID 12 1 0 0 0 f f f f f f i 3 0 22
924924
DESCR("array constructor with value");
925925
DATA(insert OID = 2331 ( unnest PGNSP PGUID 12 1 100 0 0 f f f f t t i 1 0 2283 "2277" _null_ _null_ _null_ _null_ _null_ array_unnest _null_ _null_ _null_ ));
926926
DESCR("expand array to set of rows");
927+
/* just for compatibility with jsonb_unnest* for any/each element clause */
928+
DATA(insert OID = 7644 ( unnest_element PGNSP PGUID 12 1 100 0 0 f f f f t t i 2 0 2283 "2277 16" _null_ _null_ _null_ _null_ _null_ array_unnest _null_ _null_ _null_ ));
929+
DESCR("expand array to set of rows");
927930
DATA(insert OID = 3167 ( array_remove PGNSP PGUID 12 1 0 0 0 f f f f f f i 2 0 2277 "2277 2283" _null_ _null_ _null_ _null_ _null_ array_remove _null_ _null_ _null_ ));
928931
DESCR("remove any occurrences of an element from an array");
929932
DATA(insert OID = 3168 ( array_replace PGNSP PGUID 12 1 0 0 0 f f f f f f i 3 0 2277 "2277 2283 2283" _null_ _null_ _null_ _null_ _null_ array_replace _null_ _null_ _null_ ));
@@ -4863,6 +4866,12 @@ DATA(insert OID = 3305 ( jsonb_replace PGNSP PGUID 12 1 0 0 0 f f f f t f i
48634866
DESCR("Replace part of a jsonb");
48644867
DATA(insert OID = 3306 ( jsonb_pretty PGNSP PGUID 12 1 0 0 0 f f f f t f i 1 0 25 "3802" _null_ _null_ _null_ _null_ _null_ jsonb_pretty _null_ _null_ _null_ ));
48654868
DESCR("Indented text from jsonb");
4869+
DATA(insert OID = 7645 ( unnest_element PGNSP PGUID 12 1 100 0 0 f f f f t t i 2 0 3802 "3802 16" _null_ _null_ _null_ _null_ _null_ jsonb_unnest_element _null_ _null_ _null_ ));
4870+
DESCR("expand elements from jsonb");
4871+
DATA(insert OID = 7646 ( unnest_value PGNSP PGUID 12 1 100 0 0 f f f f t t i 2 0 3802 "3802 16" _null_ _null_ _null_ _null_ _null_ jsonb_unnest_value _null_ _null_ _null_ ));
4872+
DESCR("expand values from jsonb");
4873+
DATA(insert OID = 7647 ( unnest_key PGNSP PGUID 12 1 100 0 0 f f f f t t i 2 0 3802 "3802 16" _null_ _null_ _null_ _null_ _null_ jsonb_unnest_key _null_ _null_ _null_ ));
4874+
DESCR("expand keys from jsonb");
48664875
/* txid */
48674876
DATA(insert OID = 2939 ( txid_snapshot_in PGNSP PGUID 12 1 0 0 0 f f f f t f i 1 0 2970 "2275" _null_ _null_ _null_ _null_ _null_ txid_snapshot_in _null_ _null_ _null_ ));
48684877
DESCR("I/O");

‎src/include/parser/kwlist.h

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -42,6 +42,7 @@ PG_KEYWORD("analyse", ANALYSE, RESERVED_KEYWORD) /* British spelling */
4242
PG_KEYWORD("analyze", ANALYZE, RESERVED_KEYWORD)
4343
PG_KEYWORD("and", AND, RESERVED_KEYWORD)
4444
PG_KEYWORD("any", ANY, RESERVED_KEYWORD)
45+
PG_KEYWORD("anywhere", ANYWHERE, UNRESERVED_KEYWORD)
4546
PG_KEYWORD("array", ARRAY, RESERVED_KEYWORD)
4647
PG_KEYWORD("as", AS, RESERVED_KEYWORD)
4748
PG_KEYWORD("asc", ASC, RESERVED_KEYWORD)

‎src/include/utils/jsonb.h

Lines changed: 11 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -65,10 +65,12 @@ typedef enum
6565
#define JGIN_MAXLENGTH 125 /* max length of text part before hashing */
6666

6767
/* Convenience macros */
68-
#define DatumGetJsonb(d) ((Jsonb *) PG_DETOAST_DATUM(d))
69-
#define JsonbGetDatum(p) PointerGetDatum(p)
70-
#define PG_GETARG_JSONB(x) DatumGetJsonb(PG_GETARG_DATUM(x))
71-
#define PG_RETURN_JSONB(x) PG_RETURN_POINTER(x)
68+
#define DatumGetJsonb(d) ((Jsonb *) PG_DETOAST_DATUM(d))
69+
#define DatumGetJsonbCopy(d) ((Jsonb *) PG_DETOAST_DATUM_COPY(d))
70+
#define JsonbGetDatum(p) PointerGetDatum(p)
71+
#define PG_GETARG_JSONB(x) DatumGetJsonb(PG_GETARG_DATUM(x))
72+
#define PG_GETARG_JSONB_COPY(x) DatumGetJsonbCopy(PG_GETARG_DATUM(x))
73+
#define PG_RETURN_JSONB(x) PG_RETURN_POINTER(x)
7274

7375
typedef struct JsonbPair JsonbPair;
7476
typedef struct JsonbValue JsonbValue;
@@ -400,6 +402,11 @@ extern Datum jsonb_pretty(PG_FUNCTION_ARGS);
400402
/* concatenation */
401403
extern Datum jsonb_concat(PG_FUNCTION_ARGS);
402404

405+
/* unnesting function */
406+
extern Datum jsonb_unnest_element(PG_FUNCTION_ARGS);
407+
extern Datum jsonb_unnest_value(PG_FUNCTION_ARGS);
408+
extern Datum jsonb_unnest_key(PG_FUNCTION_ARGS);
409+
403410
/* deletion */
404411
Datum jsonb_delete(PG_FUNCTION_ARGS);
405412
Datum jsonb_delete_idx(PG_FUNCTION_ARGS);

0 commit comments

Comments
 (0)