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

Commitae7412b

Browse files
author
Nikita Glukhov
committed
Add planner support functions for jsonb_path_match() and jsonb_path_exists()
1 parent445253c commitae7412b

File tree

6 files changed

+309
-6
lines changed

6 files changed

+309
-6
lines changed

‎src/backend/catalog/system_views.sql

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1328,6 +1328,7 @@ CREATE OR REPLACE FUNCTION
13281328
RETURNSboolean
13291329
LANGUAGE INTERNAL
13301330
STRICT IMMUTABLE PARALLEL SAFE
1331+
SUPPORT jsonb_path_exists_support
13311332
AS'jsonb_path_exists';
13321333

13331334
CREATEOR REPLACE FUNCTION
@@ -1336,6 +1337,7 @@ CREATE OR REPLACE FUNCTION
13361337
RETURNSboolean
13371338
LANGUAGE INTERNAL
13381339
STRICT IMMUTABLE PARALLEL SAFE
1340+
SUPPORT jsonb_path_match_support
13391341
AS'jsonb_path_match';
13401342

13411343
CREATEOR REPLACE FUNCTION

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

Lines changed: 125 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -63,10 +63,15 @@
6363

6464
#include"postgres.h"
6565

66+
#include"catalog/pg_type_d.h"
67+
#include"catalog/pg_operator_d.h"
6668
#include"funcapi.h"
6769
#include"lib/stringinfo.h"
6870
#include"libpq/pqformat.h"
6971
#include"miscadmin.h"
72+
#include"nodes/makefuncs.h"
73+
#include"nodes/nodeFuncs.h"
74+
#include"nodes/supportnodes.h"
7075
#include"utils/builtins.h"
7176
#include"utils/fmgroids.h"
7277
#include"utils/json.h"
@@ -96,6 +101,7 @@ static intoperationPriority(JsonPathItemType op);
96101
staticboolreplaceVariableReference(JsonPathContext*cxt,JsonPathItem*var,
97102
int32pos);
98103
staticJsonPath*substituteVariables(JsonPath*jsp,Jsonb*vars);
104+
staticNode*jsonb_path_support(Node*rawreq,boolexists);
99105

100106
/**************************** INPUT/OUTPUT ********************************/
101107

@@ -184,6 +190,24 @@ jsonpath_embed_vars(PG_FUNCTION_ARGS)
184190
PG_RETURN_JSONPATH_P(jsp);
185191
}
186192

193+
/* Planner support for jsonb_path_match() */
194+
Datum
195+
jsonb_path_match_support(PG_FUNCTION_ARGS)
196+
{
197+
Node*rawreq= (Node*)PG_GETARG_POINTER(0);
198+
199+
PG_RETURN_POINTER(jsonb_path_support(rawreq, false));
200+
}
201+
202+
/* Planner support for jsonb_path_exists() */
203+
Datum
204+
jsonb_path_exists_support(PG_FUNCTION_ARGS)
205+
{
206+
Node*rawreq= (Node*)PG_GETARG_POINTER(0);
207+
208+
PG_RETURN_POINTER(jsonb_path_support(rawreq, true));
209+
}
210+
187211
/*
188212
* Converts C-string to a jsonpath value.
189213
*
@@ -1456,3 +1480,104 @@ substituteVariables(JsonPath *jsp, Jsonb *vars)
14561480
returnencodeJsonPath(&item, !!(jsp->header&JSONPATH_LAX),
14571481
VARSIZE(jsp)+VARSIZE(vars),vars);
14581482
}
1483+
1484+
staticConst*
1485+
getConstExpr(Expr*expr,Oidtypid)
1486+
{
1487+
if (!IsA(expr,Const)||
1488+
((Const*)expr)->constisnull||
1489+
((Const*)expr)->consttype!=typid)
1490+
returnNULL;
1491+
1492+
return (Const*)expr;
1493+
}
1494+
1495+
/* Planner support for jsonb_path_match() and jsonb_path_exists() */
1496+
staticNode*
1497+
jsonb_path_support(Node*rawreq,boolexists)
1498+
{
1499+
Node*ret=NULL;
1500+
1501+
if (IsA(rawreq,SupportRequestIndexCondition))
1502+
{
1503+
/* Try to convert operator/function call to index conditions */
1504+
SupportRequestIndexCondition*req= (SupportRequestIndexCondition*)rawreq;
1505+
1506+
/*
1507+
* Currently we have no "reverse" match operators with the pattern on
1508+
* the left, so we only need consider cases with the indexkey on the
1509+
* left.
1510+
*/
1511+
if (req->indexarg!=0)
1512+
returnNULL;
1513+
1514+
if (is_funcclause(req->node))
1515+
{
1516+
FuncExpr*clause= (FuncExpr*)req->node;
1517+
Expr*opexpr;
1518+
Expr*jspexpr;
1519+
Expr*jsonexpr;
1520+
Const*pathexpr;
1521+
Const*varsexpr;
1522+
Const*silentexpr;
1523+
Jsonb*vars;
1524+
Oidoproid;
1525+
1526+
if (list_length(clause->args)<4)
1527+
returnNULL;
1528+
1529+
if (!(pathexpr=getConstExpr(lsecond(clause->args),JSONPATHOID)))
1530+
returnNULL;
1531+
1532+
if (!(silentexpr=getConstExpr(lfourth(clause->args),BOOLOID))||
1533+
!DatumGetBool(silentexpr->constvalue))
1534+
returnNULL;
1535+
1536+
if ((varsexpr=getConstExpr(lthird(clause->args),JSONBOID)))
1537+
{
1538+
vars=DatumGetJsonbP(varsexpr->constvalue);
1539+
1540+
if (!JsonContainerIsObject(&vars->root))
1541+
returnNULL;
1542+
1543+
if (JsonContainerSize(&vars->root) <=0)
1544+
jspexpr= (Expr*)pathexpr;
1545+
else
1546+
{
1547+
JsonPath*jsp=DatumGetJsonPathP(pathexpr->constvalue);
1548+
1549+
jsp=substituteVariables(jsp,vars);
1550+
1551+
if (!jsp)
1552+
returnNULL;
1553+
1554+
jspexpr= (Expr*)makeConst(JSONPATHOID,-1,InvalidOid,
1555+
-1,PointerGetDatum(jsp),
1556+
false, false);
1557+
}
1558+
}
1559+
else
1560+
{
1561+
List*args=list_make2(pathexpr,lthird(clause->args));
1562+
1563+
jspexpr= (Expr*)makeFuncExpr(F_JSONPATH_EMBED_VARS,
1564+
JSONPATHOID,args,
1565+
InvalidOid,InvalidOid,
1566+
COERCE_EXPLICIT_CALL);
1567+
}
1568+
1569+
jsonexpr=linitial(clause->args);
1570+
1571+
oproid=exists ?JsonbPathExistsOperator :JsonbPathMatchOperator;
1572+
opexpr=make_opclause(oproid,BOOLOID, false,
1573+
jsonexpr,jspexpr,
1574+
InvalidOid,req->indexcollation);
1575+
1576+
req->lossy= false;
1577+
1578+
return (Node*)list_make1(opexpr);
1579+
}
1580+
}
1581+
1582+
returnret;
1583+
}

‎src/include/catalog/pg_operator.dat

Lines changed: 3 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -3319,11 +3319,12 @@
33193319
{ oid => '3287', descr => 'delete path',
33203320
oprname => '#-', oprleft => 'jsonb', oprright => '_text',
33213321
oprresult => 'jsonb', oprcode => 'jsonb_delete_path' },
3322-
{ oid => '4012', descr => 'jsonpath exists',
3322+
{ oid => '4012', oid_symbol => 'JsonbPathExistsOperator',
3323+
descr => 'jsonpath exists',
33233324
oprname => '@?', oprleft => 'jsonb', oprright => 'jsonpath',
33243325
oprresult => 'bool', oprcode => 'jsonb_path_exists_opr(jsonb,jsonpath)',
33253326
oprrest => 'matchingsel', oprjoin => 'matchingjoinsel' },
3326-
{ oid => '4013', descr => 'jsonpath match',
3327+
{ oid => '4013',oid_symbol => 'JsonbPathMatchOperator',descr => 'jsonpath match',
33273328
oprname => '@@', oprleft => 'jsonb', oprright => 'jsonpath',
33283329
oprresult => 'bool', oprcode => 'jsonb_path_match_opr(jsonb,jsonpath)',
33293330
oprrest => 'matchingsel', oprjoin => 'matchingjoinsel' },

‎src/include/catalog/pg_proc.dat

Lines changed: 12 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -9458,8 +9458,9 @@
94589458
prosrc => 'jsonpath_send' },
94599459

94609460
{ oid => '4005', descr => 'jsonpath exists test',
9461-
proname => 'jsonb_path_exists', prorettype => 'bool',
9462-
proargtypes => 'jsonb jsonpath jsonb bool', prosrc => 'jsonb_path_exists' },
9461+
proname => 'jsonb_path_exists', prosupport => 'jsonb_path_exists_support',
9462+
prorettype => 'bool', proargtypes => 'jsonb jsonpath jsonb bool',
9463+
prosrc => 'jsonb_path_exists' },
94639464
{ oid => '4006', descr => 'jsonpath query',
94649465
proname => 'jsonb_path_query', prorows => '1000', proretset => 't',
94659466
prorettype => 'jsonb', proargtypes => 'jsonb jsonpath jsonb bool',
@@ -9472,10 +9473,17 @@
94729473
proname => 'jsonb_path_query_first', prorettype => 'jsonb',
94739474
proargtypes => 'jsonb jsonpath jsonb bool',
94749475
prosrc => 'jsonb_path_query_first' },
9475-
{ oid => '4009', descr => 'jsonpath match',
9476-
proname => 'jsonb_path_match', prorettype => 'bool',
9476+
{ oid => '4009', descr => 'jsonpath match', proname => 'jsonb_path_match',
9477+
prosupport => 'jsonb_path_match_support', prorettype => 'bool',
94779478
proargtypes => 'jsonb jsonpath jsonb bool', prosrc => 'jsonb_path_match' },
94789479

9480+
{ oid => '6103', descr => 'planner support for jsonb_path_match',
9481+
proname => 'jsonb_path_match_support', prorettype => 'internal',
9482+
proargtypes => 'internal', prosrc => 'jsonb_path_match_support' },
9483+
{ oid => '6105', descr => 'planner support for jsonb_path_exists',
9484+
proname => 'jsonb_path_exists_support', prorettype => 'internal',
9485+
proargtypes => 'internal', prosrc => 'jsonb_path_exists_support' },
9486+
94799487
{ oid => '1177', descr => 'jsonpath exists test with timezone',
94809488
proname => 'jsonb_path_exists_tz', provolatile => 's', prorettype => 'bool',
94819489
proargtypes => 'jsonb jsonpath jsonb bool',

‎src/test/regress/expected/jsonb.out

Lines changed: 136 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3167,6 +3167,142 @@ SELECT count(*) FROM testjsonb WHERE j @? '$.bar';
31673167
0
31683168
(1 row)
31693169

3170+
EXPLAIN (COSTS OFF)
3171+
SELECT * FROM testjsonb WHERE jsonb_path_match(j, '$.age == 25');
3172+
QUERY PLAN
3173+
--------------------------------------------------------------------------------
3174+
Seq Scan on testjsonb
3175+
Filter: jsonb_path_match(j, '($."age" == 25)'::jsonpath, '{}'::jsonb, false)
3176+
(2 rows)
3177+
3178+
EXPLAIN (COSTS OFF)
3179+
SELECT * FROM testjsonb WHERE jsonb_path_match(j, '$.age == 25', silent => true);
3180+
QUERY PLAN
3181+
-------------------------------------------------------------------------------
3182+
Bitmap Heap Scan on testjsonb
3183+
Filter: jsonb_path_match(j, '($."age" == 25)'::jsonpath, '{}'::jsonb, true)
3184+
-> Bitmap Index Scan on jidx
3185+
Index Cond: (j @@ '($."age" == 25)'::jsonpath)
3186+
(4 rows)
3187+
3188+
EXPLAIN (COSTS OFF)
3189+
SELECT * FROM testjsonb WHERE jsonb_path_match(j, '$.age == 25', vars => '{"age": 34 }', silent => true);
3190+
QUERY PLAN
3191+
----------------------------------------------------------------------------------------
3192+
Bitmap Heap Scan on testjsonb
3193+
Filter: jsonb_path_match(j, '($."age" == 25)'::jsonpath, '{"age": 34}'::jsonb, true)
3194+
-> Bitmap Index Scan on jidx
3195+
Index Cond: (j @@ '($."age" == 25)'::jsonpath)
3196+
(4 rows)
3197+
3198+
EXPLAIN (COSTS OFF)
3199+
SELECT * FROM testjsonb WHERE jsonb_path_match(j, '$.age == $age', vars => '{"age": 25 }', silent => true);
3200+
QUERY PLAN
3201+
--------------------------------------------------------------------------------------------
3202+
Bitmap Heap Scan on testjsonb
3203+
Filter: jsonb_path_match(j, '($."age" == $"age")'::jsonpath, '{"age": 25}'::jsonb, true)
3204+
-> Bitmap Index Scan on jidx
3205+
Index Cond: (j @@ '($."age" == 25)'::jsonpath)
3206+
(4 rows)
3207+
3208+
EXPLAIN (COSTS OFF)
3209+
SELECT * FROM testjsonb WHERE jsonb_path_match(j, '$.age == $age', vars => '{"age": [25] }', silent => true);
3210+
QUERY PLAN
3211+
----------------------------------------------------------------------------------------------
3212+
Seq Scan on testjsonb
3213+
Filter: jsonb_path_match(j, '($."age" == $"age")'::jsonpath, '{"age": [25]}'::jsonb, true)
3214+
(2 rows)
3215+
3216+
EXPLAIN (COSTS OFF)
3217+
SELECT * FROM testjsonb WHERE jsonb_path_match(j, '$.age == $x || $.age == $y', vars => '{"x": 25, "y": 34}', silent => true);
3218+
QUERY PLAN
3219+
--------------------------------------------------------------------------------------------------------------------
3220+
Bitmap Heap Scan on testjsonb
3221+
Filter: jsonb_path_match(j, '($."age" == $"x" || $."age" == $"y")'::jsonpath, '{"x": 25, "y": 34}'::jsonb, true)
3222+
-> Bitmap Index Scan on jidx
3223+
Index Cond: (j @@ '($."age" == 25 || $."age" == 34)'::jsonpath)
3224+
(4 rows)
3225+
3226+
EXPLAIN (COSTS OFF)
3227+
SELECT * FROM testjsonb t1, testjsonb t2 WHERE jsonb_path_match(t1.j, '$.age == $age', vars => t2.j, silent => true);
3228+
QUERY PLAN
3229+
---------------------------------------------------------------------------------------------
3230+
Nested Loop
3231+
-> Seq Scan on testjsonb t2
3232+
-> Bitmap Heap Scan on testjsonb t1
3233+
Filter: jsonb_path_match(j, '($."age" == $"age")'::jsonpath, t2.j, true)
3234+
-> Bitmap Index Scan on jidx
3235+
Index Cond: (j @@ jsonpath_embed_vars('($."age" == $"age")'::jsonpath, t2.j))
3236+
(6 rows)
3237+
3238+
EXPLAIN (COSTS OFF)
3239+
SELECT * FROM testjsonb WHERE jsonb_path_exists(j, '$ ? (@.age == 25)');
3240+
QUERY PLAN
3241+
-----------------------------------------------------------------------------------
3242+
Seq Scan on testjsonb
3243+
Filter: jsonb_path_exists(j, '$?(@."age" == 25)'::jsonpath, '{}'::jsonb, false)
3244+
(2 rows)
3245+
3246+
EXPLAIN (COSTS OFF)
3247+
SELECT * FROM testjsonb WHERE jsonb_path_exists(j, '$ ? (@.age == 25)', silent => true);
3248+
QUERY PLAN
3249+
----------------------------------------------------------------------------------
3250+
Bitmap Heap Scan on testjsonb
3251+
Filter: jsonb_path_exists(j, '$?(@."age" == 25)'::jsonpath, '{}'::jsonb, true)
3252+
-> Bitmap Index Scan on jidx
3253+
Index Cond: (j @? '$?(@."age" == 25)'::jsonpath)
3254+
(4 rows)
3255+
3256+
EXPLAIN (COSTS OFF)
3257+
SELECT * FROM testjsonb WHERE jsonb_path_exists(j, '$ ? (@.age == 25)', vars => '{"age": 34 }', silent => true);
3258+
QUERY PLAN
3259+
-------------------------------------------------------------------------------------------
3260+
Bitmap Heap Scan on testjsonb
3261+
Filter: jsonb_path_exists(j, '$?(@."age" == 25)'::jsonpath, '{"age": 34}'::jsonb, true)
3262+
-> Bitmap Index Scan on jidx
3263+
Index Cond: (j @? '$?(@."age" == 25)'::jsonpath)
3264+
(4 rows)
3265+
3266+
EXPLAIN (COSTS OFF)
3267+
SELECT * FROM testjsonb WHERE jsonb_path_exists(j, '$ ? (@.age == $age)', vars => '{"age": 25 }', silent => true);
3268+
QUERY PLAN
3269+
-----------------------------------------------------------------------------------------------
3270+
Bitmap Heap Scan on testjsonb
3271+
Filter: jsonb_path_exists(j, '$?(@."age" == $"age")'::jsonpath, '{"age": 25}'::jsonb, true)
3272+
-> Bitmap Index Scan on jidx
3273+
Index Cond: (j @? '$?(@."age" == 25)'::jsonpath)
3274+
(4 rows)
3275+
3276+
EXPLAIN (COSTS OFF)
3277+
SELECT * FROM testjsonb WHERE jsonb_path_exists(j, '$ ? (@.age == $age)', vars => '{"age": [25] }', silent => true);
3278+
QUERY PLAN
3279+
-------------------------------------------------------------------------------------------------
3280+
Seq Scan on testjsonb
3281+
Filter: jsonb_path_exists(j, '$?(@."age" == $"age")'::jsonpath, '{"age": [25]}'::jsonb, true)
3282+
(2 rows)
3283+
3284+
EXPLAIN (COSTS OFF)
3285+
SELECT * FROM testjsonb WHERE jsonb_path_exists(j, '$ ? (@.age == $x || $.age == $y)', vars => '{"x": 25, "y": 34}', silent => true);
3286+
QUERY PLAN
3287+
-----------------------------------------------------------------------------------------------------------------------
3288+
Bitmap Heap Scan on testjsonb
3289+
Filter: jsonb_path_exists(j, '$?(@."age" == $"x" || $."age" == $"y")'::jsonpath, '{"x": 25, "y": 34}'::jsonb, true)
3290+
-> Bitmap Index Scan on jidx
3291+
Index Cond: (j @? '$?(@."age" == 25 || $."age" == 34)'::jsonpath)
3292+
(4 rows)
3293+
3294+
EXPLAIN (COSTS OFF)
3295+
SELECT * FROM testjsonb t1, testjsonb t2 WHERE jsonb_path_exists(t1.j, '$ ? (@.age == $age)', vars => t2.j, silent => true);
3296+
QUERY PLAN
3297+
-----------------------------------------------------------------------------------------------
3298+
Nested Loop
3299+
-> Seq Scan on testjsonb t2
3300+
-> Bitmap Heap Scan on testjsonb t1
3301+
Filter: jsonb_path_exists(j, '$?(@."age" == $"age")'::jsonpath, t2.j, true)
3302+
-> Bitmap Index Scan on jidx
3303+
Index Cond: (j @? jsonpath_embed_vars('$?(@."age" == $"age")'::jsonpath, t2.j))
3304+
(6 rows)
3305+
31703306
-- array exists - array elements should behave as keys (for GIN index scans too)
31713307
CREATE INDEX jidx_array ON testjsonb USING gin((j->'array'));
31723308
SELECT count(*) from testjsonb WHERE j->'array' ? 'bar';

‎src/test/regress/sql/jsonb.sql

Lines changed: 31 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -826,6 +826,37 @@ SELECT count(*) FROM testjsonb WHERE j @? '$';
826826
SELECTcount(*)FROM testjsonbWHERE j @?'$.public';
827827
SELECTcount(*)FROM testjsonbWHERE j @?'$.bar';
828828

829+
EXPLAIN (COSTS OFF)
830+
SELECT*FROM testjsonbWHERE jsonb_path_match(j,'$.age == 25');
831+
EXPLAIN (COSTS OFF)
832+
SELECT*FROM testjsonbWHERE jsonb_path_match(j,'$.age == 25', silent=> true);
833+
EXPLAIN (COSTS OFF)
834+
SELECT*FROM testjsonbWHERE jsonb_path_match(j,'$.age == 25', vars=>'{"age": 34 }', silent=> true);
835+
EXPLAIN (COSTS OFF)
836+
SELECT*FROM testjsonbWHERE jsonb_path_match(j,'$.age == $age', vars=>'{"age": 25 }', silent=> true);
837+
EXPLAIN (COSTS OFF)
838+
SELECT*FROM testjsonbWHERE jsonb_path_match(j,'$.age == $age', vars=>'{"age": [25] }', silent=> true);
839+
EXPLAIN (COSTS OFF)
840+
SELECT*FROM testjsonbWHERE jsonb_path_match(j,'$.age == $x || $.age == $y', vars=>'{"x": 25, "y": 34}', silent=> true);
841+
EXPLAIN (COSTS OFF)
842+
SELECT*FROM testjsonb t1, testjsonb t2WHERE jsonb_path_match(t1.j,'$.age == $age', vars=>t2.j, silent=> true);
843+
844+
EXPLAIN (COSTS OFF)
845+
SELECT*FROM testjsonbWHERE jsonb_path_exists(j,'$ ? (@.age == 25)');
846+
EXPLAIN (COSTS OFF)
847+
SELECT*FROM testjsonbWHERE jsonb_path_exists(j,'$ ? (@.age == 25)', silent=> true);
848+
EXPLAIN (COSTS OFF)
849+
SELECT*FROM testjsonbWHERE jsonb_path_exists(j,'$ ? (@.age == 25)', vars=>'{"age": 34 }', silent=> true);
850+
EXPLAIN (COSTS OFF)
851+
SELECT*FROM testjsonbWHERE jsonb_path_exists(j,'$ ? (@.age == $age)', vars=>'{"age": 25 }', silent=> true);
852+
EXPLAIN (COSTS OFF)
853+
SELECT*FROM testjsonbWHERE jsonb_path_exists(j,'$ ? (@.age == $age)', vars=>'{"age": [25] }', silent=> true);
854+
EXPLAIN (COSTS OFF)
855+
SELECT*FROM testjsonbWHERE jsonb_path_exists(j,'$ ? (@.age == $x || $.age == $y)', vars=>'{"x": 25, "y": 34}', silent=> true);
856+
EXPLAIN (COSTS OFF)
857+
SELECT*FROM testjsonb t1, testjsonb t2WHERE jsonb_path_exists(t1.j,'$ ? (@.age == $age)', vars=>t2.j, silent=> true);
858+
859+
829860
-- array exists - array elements should behave as keys (for GIN index scans too)
830861
CREATEINDEXjidx_arrayON testjsonb USING gin((j->'array'));
831862
SELECTcount(*)from testjsonbWHERE j->'array' ?'bar';

0 commit comments

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