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

Commit6ee3020

Browse files
committed
SQL/JSON: support the IS JSON predicate
This patch introduces the SQL standard IS JSON predicate. It operateson text and bytea values representing JSON, as well as on the json andjsonb types. Each test has IS and IS NOT variants and supports a WITHUNIQUE KEYS flag. The tests are:IS JSON [VALUE]IS JSON ARRAYIS JSON OBJECTIS JSON SCALARThese should be self-explanatory.The WITH UNIQUE KEYS flag makes these return false when duplicate keysexist in any object within the value, not necessarily directly containedin the outermost object.Author: Nikita Glukhov <n.gluhov@postgrespro.ru>Author: Teodor Sigaev <teodor@sigaev.ru>Author: Oleg Bartunov <obartunov@gmail.com>Author: Alexander Korotkov <aekorotkov@gmail.com>Author: Amit Langote <amitlangote09@gmail.com>Author: Andrew Dunstan <andrew@dunslane.net>Reviewers have included (in no particular order) Andres Freund, AlexanderKorotkov, Pavel Stehule, Andrew Alsup, Erik Rijkers, Zihong Yu,Himanshu Upadhyaya, Daniel Gustafsson, Justin Pryzby.Discussion:https://postgr.es/m/CAF4Au4w2x-5LTnN_bxky-mq4=WOqsGsxSpENCzHRAzSnEd8+WQ@mail.gmail.comDiscussion:https://postgr.es/m/cd0bb935-0158-78a7-08b5-904886deac4b@postgrespro.ruDiscussion:https://postgr.es/m/20220616233130.rparivafipt6doj3@alap3.anarazel.deDiscussion:https://postgr.es/m/abd9b83b-aa66-f230-3d6d-734817f0995d%40postgresql.org
1 parenta2a0c7c commit6ee3020

File tree

25 files changed

+1030
-67
lines changed

25 files changed

+1030
-67
lines changed

‎doc/src/sgml/func.sgml

Lines changed: 80 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -16005,6 +16005,86 @@ table2-mapping
1600516005
</tgroup>
1600616006
</table>
1600716007

16008+
<para>
16009+
<xref linkend="functions-sqljson-misc" /> details SQL/JSON
16010+
facilities for testing JSON.
16011+
</para>
16012+
16013+
<table id="functions-sqljson-misc">
16014+
<title>SQL/JSON Testing Functions</title>
16015+
<tgroup cols="1">
16016+
<thead>
16017+
<row>
16018+
<entry role="func_table_entry"><para role="func_signature">
16019+
Function signature
16020+
</para>
16021+
<para>
16022+
Description
16023+
</para>
16024+
<para>
16025+
Example(s)
16026+
</para></entry>
16027+
</row>
16028+
</thead>
16029+
<tbody>
16030+
<row>
16031+
<entry role="func_table_entry"><para role="func_signature">
16032+
<indexterm><primary>IS JSON</primary></indexterm>
16033+
<replaceable>expression</replaceable> <literal>IS</literal> <optional> <literal>NOT</literal> </optional> <literal>JSON</literal>
16034+
<optional> { <literal>VALUE</literal> | <literal>SCALAR</literal> | <literal>ARRAY</literal> | <literal>OBJECT</literal> } </optional>
16035+
<optional> { <literal>WITH</literal> | <literal>WITHOUT</literal> } <literal>UNIQUE</literal> <optional> <literal>KEYS</literal> </optional> </optional>
16036+
</para>
16037+
<para>
16038+
This predicate tests whether <replaceable>expression</replaceable> can be
16039+
parsed as JSON, possibly of a specified type.
16040+
If <literal>SCALAR</literal> or <literal>ARRAY</literal> or
16041+
<literal>OBJECT</literal> is specified, the
16042+
test is whether or not the JSON is of that particular type. If
16043+
<literal>WITH UNIQUE KEYS</literal> is specified, then any object in the
16044+
<replaceable>expression</replaceable> is also tested to see if it
16045+
has duplicate keys.
16046+
</para>
16047+
<para>
16048+
<programlisting>
16049+
SELECT js,
16050+
js IS JSON "json?",
16051+
js IS JSON SCALAR "scalar?",
16052+
js IS JSON OBJECT "object?",
16053+
js IS JSON ARRAY "array?"
16054+
FROM (VALUES
16055+
('123'), ('"abc"'), ('{"a": "b"}'), ('[1,2]'),('abc')) foo(js);
16056+
js | json? | scalar? | object? | array?
16057+
------------+-------+---------+---------+--------
16058+
123 | t | t | f | f
16059+
"abc" | t | t | f | f
16060+
{"a": "b"} | t | f | t | f
16061+
[1,2] | t | f | f | t
16062+
abc | f | f | f | f
16063+
</programlisting>
16064+
</para>
16065+
<para>
16066+
<programlisting>
16067+
SELECT js,
16068+
js IS JSON OBJECT "object?",
16069+
js IS JSON ARRAY "array?",
16070+
js IS JSON ARRAY WITH UNIQUE KEYS "array w. UK?",
16071+
js IS JSON ARRAY WITHOUT UNIQUE KEYS "array w/o UK?"
16072+
FROM (VALUES ('[{"a":"1"},
16073+
{"b":"2","b":"3"}]')) foo(js);
16074+
-[ RECORD 1 ]-+--------------------
16075+
js | [{"a":"1"}, +
16076+
| {"b":"2","b":"3"}]
16077+
object? | f
16078+
array? | t
16079+
array w. UK? | f
16080+
array w/o UK? | t
16081+
</programlisting>
16082+
</para></entry>
16083+
</row>
16084+
</tbody>
16085+
</tgroup>
16086+
</table>
16087+
1600816088
<para>
1600916089
<xref linkend="functions-json-processing-table"/> shows the functions that
1601016090
are available for processing <type>json</type> and <type>jsonb</type> values.

‎src/backend/executor/execExpr.c

Lines changed: 13 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2370,6 +2370,19 @@ ExecInitExprRec(Expr *node, ExprState *state,
23702370
}
23712371
break;
23722372

2373+
caseT_JsonIsPredicate:
2374+
{
2375+
JsonIsPredicate*pred= (JsonIsPredicate*)node;
2376+
2377+
ExecInitExprRec((Expr*)pred->expr,state,resv,resnull);
2378+
2379+
scratch.opcode=EEOP_IS_JSON;
2380+
scratch.d.is_json.pred=pred;
2381+
2382+
ExprEvalPushStep(state,&scratch);
2383+
break;
2384+
}
2385+
23732386
caseT_NullTest:
23742387
{
23752388
NullTest*ntest= (NullTest*)node;

‎src/backend/executor/execExprInterp.c

Lines changed: 99 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -73,6 +73,7 @@
7373
#include"utils/expandedrecord.h"
7474
#include"utils/json.h"
7575
#include"utils/jsonb.h"
76+
#include"utils/jsonfuncs.h"
7677
#include"utils/lsyscache.h"
7778
#include"utils/memutils.h"
7879
#include"utils/timestamp.h"
@@ -477,6 +478,7 @@ ExecInterpExpr(ExprState *state, ExprContext *econtext, bool *isnull)
477478
&&CASE_EEOP_HASHED_SCALARARRAYOP,
478479
&&CASE_EEOP_XMLEXPR,
479480
&&CASE_EEOP_JSON_CONSTRUCTOR,
481+
&&CASE_EEOP_IS_JSON,
480482
&&CASE_EEOP_AGGREF,
481483
&&CASE_EEOP_GROUPING_FUNC,
482484
&&CASE_EEOP_WINDOW_FUNC,
@@ -1521,6 +1523,14 @@ ExecInterpExpr(ExprState *state, ExprContext *econtext, bool *isnull)
15211523
EEO_NEXT();
15221524
}
15231525

1526+
EEO_CASE(EEOP_IS_JSON)
1527+
{
1528+
/* too complex for an inline implementation */
1529+
ExecEvalJsonIsPredicate(state,op);
1530+
1531+
EEO_NEXT();
1532+
}
1533+
15241534
EEO_CASE(EEOP_AGGREF)
15251535
{
15261536
/*
@@ -3921,6 +3931,95 @@ ExecEvalJsonConstructor(ExprState *state, ExprEvalStep *op,
39213931
*op->resnull=isnull;
39223932
}
39233933

3934+
/*
3935+
* Evaluate a IS JSON predicate.
3936+
*/
3937+
void
3938+
ExecEvalJsonIsPredicate(ExprState*state,ExprEvalStep*op)
3939+
{
3940+
JsonIsPredicate*pred=op->d.is_json.pred;
3941+
Datumjs=*op->resvalue;
3942+
Oidexprtype;
3943+
boolres;
3944+
3945+
if (*op->resnull)
3946+
{
3947+
*op->resvalue=BoolGetDatum(false);
3948+
return;
3949+
}
3950+
3951+
exprtype=exprType(pred->expr);
3952+
3953+
if (exprtype==TEXTOID||exprtype==JSONOID)
3954+
{
3955+
text*json=DatumGetTextP(js);
3956+
3957+
if (pred->item_type==JS_TYPE_ANY)
3958+
res= true;
3959+
else
3960+
{
3961+
switch (json_get_first_token(json, false))
3962+
{
3963+
caseJSON_TOKEN_OBJECT_START:
3964+
res=pred->item_type==JS_TYPE_OBJECT;
3965+
break;
3966+
caseJSON_TOKEN_ARRAY_START:
3967+
res=pred->item_type==JS_TYPE_ARRAY;
3968+
break;
3969+
caseJSON_TOKEN_STRING:
3970+
caseJSON_TOKEN_NUMBER:
3971+
caseJSON_TOKEN_TRUE:
3972+
caseJSON_TOKEN_FALSE:
3973+
caseJSON_TOKEN_NULL:
3974+
res=pred->item_type==JS_TYPE_SCALAR;
3975+
break;
3976+
default:
3977+
res= false;
3978+
break;
3979+
}
3980+
}
3981+
3982+
/*
3983+
* Do full parsing pass only for uniqueness check or for JSON text
3984+
* validation.
3985+
*/
3986+
if (res&& (pred->unique_keys||exprtype==TEXTOID))
3987+
res=json_validate(json,pred->unique_keys, false);
3988+
}
3989+
elseif (exprtype==JSONBOID)
3990+
{
3991+
if (pred->item_type==JS_TYPE_ANY)
3992+
res= true;
3993+
else
3994+
{
3995+
Jsonb*jb=DatumGetJsonbP(js);
3996+
3997+
switch (pred->item_type)
3998+
{
3999+
caseJS_TYPE_OBJECT:
4000+
res=JB_ROOT_IS_OBJECT(jb);
4001+
break;
4002+
caseJS_TYPE_ARRAY:
4003+
res=JB_ROOT_IS_ARRAY(jb)&& !JB_ROOT_IS_SCALAR(jb);
4004+
break;
4005+
caseJS_TYPE_SCALAR:
4006+
res=JB_ROOT_IS_ARRAY(jb)&&JB_ROOT_IS_SCALAR(jb);
4007+
break;
4008+
default:
4009+
res= false;
4010+
break;
4011+
}
4012+
}
4013+
4014+
/* Key uniqueness check is redundant for jsonb */
4015+
}
4016+
else
4017+
res= false;
4018+
4019+
*op->resvalue=BoolGetDatum(res);
4020+
}
4021+
4022+
39244023
/*
39254024
* ExecEvalGroupingFunc
39264025
*

‎src/backend/jit/llvm/llvmjit_expr.c

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1848,6 +1848,12 @@ llvm_compile_expr(ExprState *state)
18481848
LLVMBuildBr(b,opblocks[opno+1]);
18491849
break;
18501850

1851+
caseEEOP_IS_JSON:
1852+
build_EvalXFunc(b,mod,"ExecEvalJsonIsPredicate",
1853+
v_state,op);
1854+
LLVMBuildBr(b,opblocks[opno+1]);
1855+
break;
1856+
18511857
caseEEOP_AGGREF:
18521858
{
18531859
LLVMValueRefv_aggno;

‎src/backend/jit/llvm/llvmjit_types.c

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -133,6 +133,7 @@ void *referenced_functions[] =
133133
ExecEvalWholeRowVar,
134134
ExecEvalXmlExpr,
135135
ExecEvalJsonConstructor,
136+
ExecEvalJsonIsPredicate,
136137
MakeExpandedObjectReadOnlyInternal,
137138
slot_getmissingattrs,
138139
slot_getsomeattrs_int,

‎src/backend/nodes/makefuncs.c

Lines changed: 19 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -894,3 +894,22 @@ makeJsonKeyValue(Node *key, Node *value)
894894

895895
return (Node*)n;
896896
}
897+
898+
/*
899+
* makeJsonIsPredicate -
900+
* creates a JsonIsPredicate node
901+
*/
902+
Node*
903+
makeJsonIsPredicate(Node*expr,JsonFormat*format,JsonValueTypeitem_type,
904+
boolunique_keys,intlocation)
905+
{
906+
JsonIsPredicate*n=makeNode(JsonIsPredicate);
907+
908+
n->expr=expr;
909+
n->format=format;
910+
n->item_type=item_type;
911+
n->unique_keys=unique_keys;
912+
n->location=location;
913+
914+
return (Node*)n;
915+
}

‎src/backend/nodes/nodeFuncs.c

Lines changed: 26 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -261,6 +261,9 @@ exprType(const Node *expr)
261261
caseT_JsonConstructorExpr:
262262
type= ((constJsonConstructorExpr*)expr)->returning->typid;
263263
break;
264+
caseT_JsonIsPredicate:
265+
type=BOOLOID;
266+
break;
264267
default:
265268
elog(ERROR,"unrecognized node type: %d", (int)nodeTag(expr));
266269
type=InvalidOid;/* keep compiler quiet */
@@ -983,6 +986,9 @@ exprCollation(const Node *expr)
983986
coll=InvalidOid;
984987
}
985988
break;
989+
caseT_JsonIsPredicate:
990+
coll=InvalidOid;/* result is always an boolean type */
991+
break;
986992
default:
987993
elog(ERROR,"unrecognized node type: %d", (int)nodeTag(expr));
988994
coll=InvalidOid;/* keep compiler quiet */
@@ -1205,6 +1211,9 @@ exprSetCollation(Node *expr, Oid collation)
12051211
* json[b] type */
12061212
}
12071213
break;
1214+
caseT_JsonIsPredicate:
1215+
Assert(!OidIsValid(collation));/* result is always boolean */
1216+
break;
12081217
default:
12091218
elog(ERROR,"unrecognized node type: %d", (int)nodeTag(expr));
12101219
break;
@@ -1653,6 +1662,9 @@ exprLocation(const Node *expr)
16531662
caseT_JsonConstructorExpr:
16541663
loc= ((constJsonConstructorExpr*)expr)->location;
16551664
break;
1665+
caseT_JsonIsPredicate:
1666+
loc= ((constJsonIsPredicate*)expr)->location;
1667+
break;
16561668
default:
16571669
/* for any other node type it's just unknown... */
16581670
loc=-1;
@@ -2406,6 +2418,8 @@ expression_tree_walker_impl(Node *node,
24062418
return true;
24072419
}
24082420
break;
2421+
caseT_JsonIsPredicate:
2422+
returnwalker(((JsonIsPredicate*)node)->expr,context);
24092423
default:
24102424
elog(ERROR,"unrecognized node type: %d",
24112425
(int)nodeTag(node));
@@ -3413,6 +3427,16 @@ expression_tree_mutator_impl(Node *node,
34133427
MUTATE(newnode->coercion,jve->coercion,Expr*);
34143428
MUTATE(newnode->returning,jve->returning,JsonReturning*);
34153429

3430+
return (Node*)newnode;
3431+
}
3432+
caseT_JsonIsPredicate:
3433+
{
3434+
JsonIsPredicate*pred= (JsonIsPredicate*)node;
3435+
JsonIsPredicate*newnode;
3436+
3437+
FLATCOPY(newnode,pred,JsonIsPredicate);
3438+
MUTATE(newnode->expr,pred->expr,Node*);
3439+
34163440
return (Node*)newnode;
34173441
}
34183442
default:
@@ -4261,6 +4285,8 @@ raw_expression_tree_walker_impl(Node *node,
42614285
return true;
42624286
}
42634287
break;
4288+
caseT_JsonIsPredicate:
4289+
returnwalker(((JsonIsPredicate*)node)->expr,context);
42644290
default:
42654291
elog(ERROR,"unrecognized node type: %d",
42664292
(int)nodeTag(node));

0 commit comments

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