WHERE¶
The WHERE clause filters the output by conditions.
The WHERE clause usually works in the following queries:
- Native nGQL: such as
GOandLOOKUP.
- OpenCypher syntax: such as
MATCHandWITH.
OpenCypher compatibility¶
Filtering on edge rank is a native nGQL feature. To retrieve the rank value in openCypher statements, use the rank() function, such as MATCH (:player)-[e:follow]->() RETURN rank(e);.
Basic usage¶
Note
In the following examples, $$ and $^ are reference operators. For more information, see Operators.
Define conditions with boolean operators¶
Use the boolean operators NOT, AND, OR, and XOR to define conditions in WHERE clauses. For the precedence of the operators, see Precedence.
nebula> MATCH (v:player) \
WHERE v.player.name == "Tim Duncan" \
XOR (v.player.age < 30 AND v.player.name == "Yao Ming") \
OR NOT (v.player.name == "Yao Ming" OR v.player.name == "Tim Duncan") \
RETURN v.player.name, v.player.age;
+-------------------------+--------------+
| v.player.name | v.player.age |
+-------------------------+--------------+
| "Danny Green" | 31 |
| "Tiago Splitter" | 34 |
| "David West" | 38 |
...
nebula> GO FROM "player100" \
OVER follow \
WHERE properties(edge).degree > 90 \
OR properties($$).age != 33 \
AND properties($$).name != "Tony Parker" \
YIELD properties($$);
+----------------------------------+
| properties($$) |
+----------------------------------+
| {age: 41, name: "Manu Ginobili"} |
+----------------------------------+
Filter on properties¶
Use vertex or edge properties to define conditions in WHERE clauses.
- Filter on a vertex property:
nebula> MATCH (v:player)-[e]->(v2) \ WHERE v2.player.age < 25 \ RETURN v2.player.name, v2.player.age; +----------------------+---------------+ | v2.player.name | v2.player.age | +----------------------+---------------+ | "Ben Simmons" | 22 | | "Luka Doncic" | 20 | | "Kristaps Porzingis" | 23 | +----------------------+---------------+nebula> GO FROM "player100" OVER follow \ WHERE $^.player.age >= 42 \ YIELD dst(edge); +-------------+ | dst(EDGE) | +-------------+ +-------------+ | "player101" | | "player125" | +-------------+
- Filter on an edge property:
nebula> MATCH (v:player)-[e]->() \ WHERE e.start_year < 2000 \ RETURN DISTINCT v.player.name, v.player.age; +--------------------+--------------+ | v.player.name | v.player.age | +--------------------+--------------+ | "Tony Parker" | 36 | | "Tim Duncan" | 42 | | "Grant Hill" | 46 | ...nebula> GO FROM "player100" OVER follow \ WHERE follow.degree > 90 \ YIELD dst(edge); +-------------+ | dst(EDGE) | +-------------+ | "player101" | | "player125" | +-------------+
Filter on dynamically-calculated properties¶
nebula> MATCH (v:player) \
WHERE v[toLower("AGE")] < 21 \
RETURN v.player.name, v.player.age;
+---------------+-------+
| v.name | v.age |
+---------------+-------+
| "Luka Doncic" | 20 |
+---------------+-------+
Filter on existing properties¶
nebula> MATCH (v:player) \
WHERE exists(v.player.age) \
RETURN v.player.name, v.player.age;
+-------------------------+--------------+
| v.player.name | v.player.age |
+-------------------------+--------------+
| "Danny Green" | 31 |
| "Tiago Splitter" | 34 |
| "David West" | 38 |
...
Filter on edge rank¶
In nGQL, if a group of edges has the same source vertex, destination vertex, and properties, the only thing that distinguishes them is the rank. Use rank conditions in WHERE clauses to filter such edges.
# The following example creates test data.
nebula> CREATE SPACE IF NOT EXISTS test (vid_type=FIXED_STRING(30));
nebula> USE test;
nebula> CREATE EDGE IF NOT EXISTS e1(p1 int);
nebula> CREATE TAG IF NOT EXISTS person(p1 int);
nebula> INSERT VERTEX person(p1) VALUES "1":(1);
nebula> INSERT VERTEX person(p1) VALUES "2":(2);
nebula> INSERT EDGE e1(p1) VALUES "1"->"2"@0:(10);
nebula> INSERT EDGE e1(p1) VALUES "1"->"2"@1:(11);
nebula> INSERT EDGE e1(p1) VALUES "1"->"2"@2:(12);
nebula> INSERT EDGE e1(p1) VALUES "1"->"2"@3:(13);
nebula> INSERT EDGE e1(p1) VALUES "1"->"2"@4:(14);
nebula> INSERT EDGE e1(p1) VALUES "1"->"2"@5:(15);
nebula> INSERT EDGE e1(p1) VALUES "1"->"2"@6:(16);
# The following example use rank to filter edges and retrieves edges with a rank greater than 2.
nebula> GO FROM "1" \
OVER e1 \
WHERE rank(edge) > 2 \
YIELD src(edge), dst(edge), rank(edge) AS Rank, properties(edge).p1 | \
ORDER BY $-.Rank DESC;
+-----------+-----------+------+---------------------+
| src(EDGE) | dst(EDGE) | Rank | properties(EDGE).p1 |
+-----------+-----------+------+---------------------+
| "1" | "2" | 6 | 16 |
| "1" | "2" | 5 | 15 |
| "1" | "2" | 4 | 14 |
| "1" | "2" | 3 | 13 |
+-----------+-----------+------+---------------------+
# Filter edges by rank. Find follow edges with rank equal to 0.
nebula> MATCH (v)-[e:follow]->() \
WHERE rank(e)==0 \
RETURN *;
+------------------------------------------------------------+-----------------------------------------------------+
| v | e |
+------------------------------------------------------------+-----------------------------------------------------+
| ("player142" :player{age: 29, name: "Klay Thompson"}) | [:follow "player142"->"player117" @0 {degree: 90}] |
| ("player139" :player{age: 34, name: "Marc Gasol"}) | [:follow "player139"->"player138" @0 {degree: 99}] |
| ("player108" :player{age: 36, name: "Boris Diaw"}) | [:follow "player108"->"player100" @0 {degree: 80}] |
| ("player108" :player{age: 36, name: "Boris Diaw"}) | [:follow "player108"->"player101" @0 {degree: 80}] |
...
Filter on pattern¶
nebula> MATCH (v:player{name:"Tim Duncan"})-[e]->(t) \
WHERE (v)-[e]->(t:team) \
RETURN (v)-->();
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| (v)-->() = (v)-->() |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| [<("player100" :player{age: 42, name: "Tim Duncan"})-[:serve@0 {end_year: 2016, start_year: 1997}]->("team204" :team{name: "Spurs"})>, <("player100" :player{age: 42, name: "Tim Duncan"})-[:follow@0 {degree: 95}]->("player101" :player{age: 36, name: "Tony Parker"})>, <("player100" :player{age: 42, name: "Tim Duncan"})-[:follow@0 {degree: 95}]->("player125" :player{age: 41, name: "Manu Ginobili"})>] |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
nebula> MATCH (v:player{name:"Tim Duncan"})-[e]->(t) \
WHERE NOT (v)-[e]->(t:team) \
RETURN (v)-->();
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| (v)-->() = (v)-->() |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| [<("player100" :player{age: 42, name: "Tim Duncan"})-[:serve@0 {end_year: 2016, start_year: 1997}]->("team204" :team{name: "Spurs"})>, <("player100" :player{age: 42, name: "Tim Duncan"})-[:follow@0 {degree: 95}]->("player101" :player{age: 36, name: "Tony Parker"})>, <("player100" :player{age: 42, name: "Tim Duncan"})-[:follow@0 {degree: 95}]->("player125" :player{age: 41, name: "Manu Ginobili"})>] |
| [<("player100" :player{age: 42, name: "Tim Duncan"})-[:serve@0 {end_year: 2016, start_year: 1997}]->("team204" :team{name: "Spurs"})>, <("player100" :player{age: 42, name: "Tim Duncan"})-[:follow@0 {degree: 95}]->("player101" :player{age: 36, name: "Tony Parker"})>, <("player100" :player{age: 42, name: "Tim Duncan"})-[:follow@0 {degree: 95}]->("player125" :player{age: 41, name: "Manu Ginobili"})>] |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Filter on strings¶
Use STARTS WITH, ENDS WITH, or CONTAINS in WHERE clauses to match a specific part of a string. String matching is case-sensitive.
STARTS WITH¶
STARTS WITH will match the beginning of a string.
The following example uses STARTS WITH "T" to retrieve the information of players whose name starts with T.
nebula> MATCH (v:player) \
WHERE v.player.name STARTS WITH "T" \
RETURN v.player.name, v.player.age;
+------------------+--------------+
| v.player.name | v.player.age |
+------------------+--------------+
| "Tony Parker" | 36 |
| "Tiago Splitter" | 34 |
| "Tim Duncan" | 42 |
| "Tracy McGrady" | 39 |
+------------------+--------------+
If you use STARTS WITH "t" in the preceding statement, an empty set is returned because no name in the dataset starts with the lowercase t.
nebula> MATCH (v:player) \
WHERE v.player.name STARTS WITH "t" \
RETURN v.player.name, v.player.age;
+---------------+--------------+
| v.player.name | v.player.age |
+---------------+--------------+
+---------------+--------------+
Empty set (time spent 5080/6474 us)
ENDS WITH¶
ENDS WITH will match the ending of a string.
The following example uses ENDS WITH "r" to retrieve the information of players whose name ends with r.
nebula> MATCH (v:player) \
WHERE v.player.name ENDS WITH "r" \
RETURN v.player.name, v.player.age;
+------------------+--------------+
| v.player.name | v.player.age |
+------------------+--------------+
| "Tony Parker" | 36 |
| "Tiago Splitter" | 34 |
| "Vince Carter" | 42 |
+------------------+--------------+
CONTAINS¶
CONTAINS will match a certain part of a string.
The following example uses CONTAINS "Pa" to match the information of players whose name contains Pa.
nebula> MATCH (v:player) \
WHERE v.player.name CONTAINS "Pa" \
RETURN v.player.name, v.player.age;
+---------------+--------------+
| v.player.name | v.player.age |
+---------------+--------------+
| "Paul George" | 28 |
| "Tony Parker" | 36 |
| "Paul Gasol" | 38 |
| "Chris Paul" | 33 |
+---------------+--------------+
Negative string matching¶
You can use the boolean operator NOT to negate a string matching condition.
nebula> MATCH (v:player) \
WHERE NOT v.player.name ENDS WITH "R" \
RETURN v.player.name, v.player.age;
+-------------------------+--------------+
| v.player.name | v.player.age |
+-------------------------+--------------+
| "Danny Green" | 31 |
| "Tiago Splitter" | 34 |
| "David West" | 38 |
| "Russell Westbrook" | 30 |
...
Filter on lists¶
Match values in a list¶
Use the IN operator to check if a value is in a specific list.
nebula> MATCH (v:player) \
WHERE v.player.age IN range(20,25) \
RETURN v.player.name, v.player.age;
+-------------------------+--------------+
| v.player.name | v.player.age |
+-------------------------+--------------+
| "Ben Simmons" | 22 |
| "Giannis Antetokounmpo" | 24 |
| "Kyle Anderson" | 25 |
| "Joel Embiid" | 25 |
| "Kristaps Porzingis" | 23 |
| "Luka Doncic" | 20 |
+-------------------------+--------------+
nebula> LOOKUP ON player \
WHERE player.age IN [25,28] \
YIELD properties(vertex).name, properties(vertex).age;
+-------------------------+------------------------+
| properties(VERTEX).name | properties(VERTEX).age |
+-------------------------+------------------------+
| "Kyle Anderson" | 25 |
| "Damian Lillard" | 28 |
| "Joel Embiid" | 25 |
| "Paul George" | 28 |
| "Ricky Rubio" | 28 |
+-------------------------+------------------------+
Match values not in a list¶
Use NOT before IN to rule out the values in a list.
nebula> MATCH (v:player) \
WHERE v.player.age NOT IN range(20,25) \
RETURN v.player.name AS Name, v.player.age AS Age \
ORDER BY Age;
+---------------------+-----+
| Name | Age |
+---------------------+-----+
| "Kyrie Irving" | 26 |
| "Cory Joseph" | 27 |
| "Damian Lillard" | 28 |
| "Paul George" | 28 |
| "Ricky Rubio" | 28 |
+---------------------+-----+
...
Last update:
February 19, 2024