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¶
- Using patterns in
WHEREis not supported (TODO: planning), for exampleWHERE (v)-->(v2).
- 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.
ngql
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 |
...
ngql
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:
ngql 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 | +----------------------+---------------+ngql nebula> GO FROM "player100" OVER follow \ WHERE $^.player.age >= 42 \ YIELD dst(edge); +-------------+ | dst(EDGE) | +-------------+ +-------------+ | "player101" | | "player125" | +-------------+
- Filter on an edge property:
ngql 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 | ...ngql nebula> GO FROM "player100" OVER follow \ WHERE follow.degree > 90 \ YIELD dst(edge); +-------------+ | dst(EDGE) | +-------------+ | "player101" | | "player125" | +-------------+
Filter on dynamically-calculated properties¶
ngql
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¶
ngql
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.
```ngql
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 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.
ngql
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.
ngql
nebula> MATCH (v:player) \
WHERE v.player.name STARTS WITH "t" \
RETURN 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.
ngql
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.
ngql
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.
ngql
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.
```ngql 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.
ngql
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 |
+---------------------+-----+
...