WHERE

WHERE子句可以根据条件过滤输出结果。

WHERE子句通常用于如下查询:

  • 原生nGQL,例如GOLOOKUP语句。

  • openCypher方式,例如MATCHWITH语句。

openCypher兼容性

  • 不支持在WHERE子句中使用Pattern(TODO: planning),例如WHERE (v)-->(v2)

  • 过滤Rank是原生nGQL功能。如需在openCypher兼容语句中直接获取Rank值,可以使用rank()函数,例如MATCH (:player)-[e:follow]->() RETURN rank(e);

基础用法

Note

下文示例中的$$$^等是引用符号,详情请参见引用符

用布尔运算符定义条件

WHERE子句中使用布尔运算符NOTANDORXOR定义条件。关于运算符的优先级,请参见运算符优先级

  1. nebula> MATCH (v:player) \
  2. WHERE v.name == "Tim Duncan" \
  3. XOR (v.age < 30 AND v.name == "Yao Ming") \
  4. OR NOT (v.name == "Yao Ming" OR v.name == "Tim Duncan") \
  5. RETURN v.name, v.age;
  6. +-------------------------+-------+
  7. | v.name | v.age |
  8. +-------------------------+-------+
  9. | "Marco Belinelli" | 32 |
  10. +-------------------------+-------+
  11. | "Aron Baynes" | 32 |
  12. +-------------------------+-------+
  13. | "LeBron James" | 34 |
  14. +-------------------------+-------+
  15. | "James Harden" | 29 |
  16. +-------------------------+-------+
  17. | "Manu Ginobili" | 41 |
  18. +-------------------------+-------+
  19. ...
  1. nebula> GO FROM "player100" \
  2. OVER follow \
  3. WHERE follow.degree > 90 \
  4. OR $$.player.age != 33 \
  5. AND $$.player.name != "Tony Parker";
  6. +-------------+
  7. | follow._dst |
  8. +-------------+
  9. | "player101" |
  10. +-------------+
  11. | "player125" |
  12. +-------------+

过滤属性

WHERE子句中使用点或边的属性定义条件。

  • 过滤点属性:

    1. nebula> MATCH (v:player)-[e]->(v2) \
    2. WHERE v2.age < 25 \
    3. RETURN v2.name, v2.age;
    4. +----------------------+--------+
    5. | v2.name | v2.age |
    6. +----------------------+--------+
    7. | "Luka Doncic" | 20 |
    8. +----------------------+--------+
    9. | "Kristaps Porzingis" | 23 |
    10. +----------------------+--------+
    11. | "Ben Simmons" | 22 |
    12. +----------------------+--------+
    1. nebula> GO FROM "player100" \
    2. OVER follow \
    3. WHERE $^.player.age >= 42;
    4. +-------------+
    5. | follow._dst |
    6. +-------------+
    7. | "player101" |
    8. +-------------+
    9. | "player125" |
    10. +-------------+
  • 过滤边属性:

    1. nebula> MATCH (v:player)-[e]->() \
    2. WHERE e.start_year < 2000 \
    3. RETURN DISTINCT v.name, v.age;
    4. +--------------------+-------+
    5. | v.name | v.age |
    6. +--------------------+-------+
    7. | "Shaquille O'Neal" | 47 |
    8. +--------------------+-------+
    9. | "Steve Nash" | 45 |
    10. +--------------------+-------+
    11. | "Ray Allen" | 43 |
    12. +--------------------+-------+
    13. | "Grant Hill" | 46 |
    14. +--------------------+-------+
    15. | "Tony Parker" | 36 |
    16. +--------------------+-------+
    17. ...
    1. nebula> GO FROM "player100" \
    2. OVER follow \
    3. WHERE follow.degree > 90;
    4. +-------------+
    5. | follow._dst |
    6. +-------------+
    7. | "player101" |
    8. +-------------+
    9. | "player125" |
    10. +-------------+

过滤动态计算属性

  1. nebula> MATCH (v:player) \
  2. WHERE v[toLower("AGE")] < 21 \
  3. RETURN v.name, v.age;
  4. +---------------+-------+
  5. | v.name | v.age |
  6. +---------------+-------+
  7. | "Luka Doncic" | 20 |
  8. +---------------+-------+

过滤现存属性

  1. nebula> MATCH (v:player) \
  2. WHERE exists(v.age) \
  3. RETURN v.name, v.age;
  4. +-------------------------+-------+
  5. | v.name | v.age |
  6. +-------------------------+-------+
  7. | "Boris Diaw" | 36 |
  8. +-------------------------+-------+
  9. | "DeAndre Jordan" | 30 |
  10. +-------------------------+-------+

过滤rank

在nGQL中,如果多个边拥有相同的起始点、目的点和属性,则它们的唯一区别是rank值。在WHERE子句中可以使用rank过滤边。

  1. # 创建测试数据。
  2. nebula> CREATE SPACE test (vid_type=FIXED_STRING(30));
  3. nebula> USE test;
  4. nebula> CREATE EDGE e1(p1 int);
  5. nebula> CREATE TAG person(p1 int);
  6. nebula> INSERT VERTEX person(p1) VALUES "1":(1);
  7. nebula> INSERT VERTEX person(p1) VALUES "2":(2);
  8. nebula> INSERT EDGE e1(p1) VALUES "1"->"2"@0:(10);
  9. nebula> INSERT EDGE e1(p1) VALUES "1"->"2"@1:(11);
  10. nebula> INSERT EDGE e1(p1) VALUES "1"->"2"@2:(12);
  11. nebula> INSERT EDGE e1(p1) VALUES "1"->"2"@3:(13);
  12. nebula> INSERT EDGE e1(p1) VALUES "1"->"2"@4:(14);
  13. nebula> INSERT EDGE e1(p1) VALUES "1"->"2"@5:(15);
  14. nebula> INSERT EDGE e1(p1) VALUES "1"->"2"@6:(16);
  15. # 通过rank过滤边,查找rank大于2的边。
  16. nebula> GO FROM "1" \
  17. OVER e1 \
  18. WHERE e1._rank>2 \
  19. YIELD e1._src, e1._dst, e1._rank AS Rank, e1.p1 | \
  20. ORDER BY $-.Rank DESC;
  21. ====================================
  22. | e1._src | e1._dst | Rank | e1.p1 |
  23. ====================================
  24. | 1 | 2 | 6 | 16 |
  25. ------------------------------------
  26. | 1 | 2 | 5 | 15 |
  27. ------------------------------------
  28. | 1 | 2 | 4 | 14 |
  29. ------------------------------------
  30. | 1 | 2 | 3 | 13 |
  31. ------------------------------------

过滤字符串

WHERE子句中使用STARTS WITHENDS WITHCONTAINS可以匹配字符串的特定部分。匹配时区分大小写。

STARTS WITH

STARTS WITH会从字符串的起始位置开始匹配。

  1. # 查询姓名以T开头的player信息。
  2. nebula> MATCH (v:player) \
  3. WHERE v.name STARTS WITH "T" \
  4. RETURN v.name, v.age;
  5. +------------------+-------+
  6. | v.name | v.age |
  7. +------------------+-------+
  8. | "Tracy McGrady" | 39 |
  9. +------------------+-------+
  10. | "Tony Parker" | 36 |
  11. +------------------+-------+
  12. | "Tim Duncan" | 42 |
  13. +------------------+-------+
  14. | "Tiago Splitter" | 34 |
  15. +------------------+-------+

如果使用小写tSTARTS WITH "t"),会返回空集,因为数据库中没有以小写t开头的姓名。

  1. nebula> MATCH (v:player) \
  2. WHERE v.name STARTS WITH "t" \
  3. RETURN v.name, v.age;
  4. Empty set (time spent 5080/6474 us)

ENDS WITH

ENDS WITH会从字符串的结束位置开始匹配。

  1. nebula> MATCH (v:player) \
  2. WHERE v.name ENDS WITH "r" \
  3. RETURN v.name, v.age;
  4. +------------------+-------+
  5. | v.name | v.age |
  6. +------------------+-------+
  7. | "Vince Carter" | 42 |
  8. +------------------+-------+
  9. | "Tony Parker" | 36 |
  10. +------------------+-------+
  11. | "Tiago Splitter" | 34 |
  12. +------------------+-------+

CONTAINS

CONTAINS会检查关键字是否匹配字符串的某一部分。

  1. nebula> MATCH (v:player) \
  2. WHERE v.name CONTAINS "Pa" \
  3. RETURN v.name, v.age;
  4. +---------------+-------+
  5. | v.name | v.age |
  6. +---------------+-------+
  7. | "Paul George" | 28 |
  8. +---------------+-------+
  9. | "Tony Parker" | 36 |
  10. +---------------+-------+
  11. | "Paul Gasol" | 38 |
  12. +---------------+-------+
  13. | "Chris Paul" | 33 |
  14. +---------------+-------+

结合NOT使用

用户可以结合布尔运算符NOT一起使用,否定字符串匹配条件。

  1. nebula> MATCH (v:player) \
  2. WHERE NOT v.name ENDS WITH "R" \
  3. RETURN v.name, v.age;
  4. +-------------------------+-------+
  5. | v.name | v.age |
  6. +-------------------------+-------+
  7. | "Rajon Rondo" | 33 |
  8. +-------------------------+-------+
  9. | "Rudy Gay" | 32 |
  10. +-------------------------+-------+
  11. | "Dejounte Murray" | 29 |
  12. +-------------------------+-------+
  13. | "Chris Paul" | 33 |
  14. +-------------------------+-------+
  15. | "Carmelo Anthony" | 34 |
  16. +-------------------------+-------+
  17. ...

过滤列表

匹配列表中的值

使用IN运算符检查某个值是否在指定列表中。

  1. nebula> MATCH (v:player) \
  2. WHERE v.age IN range(20,25) \
  3. RETURN v.name, v.age;
  4. +-------------------------+-------+
  5. | v.name | v.age |
  6. +-------------------------+-------+
  7. | "Ben Simmons" | 22 |
  8. +-------------------------+-------+
  9. | "Kristaps Porzingis" | 23 |
  10. +-------------------------+-------+
  11. | "Luka Doncic" | 20 |
  12. +-------------------------+-------+
  13. | "Kyle Anderson" | 25 |
  14. +-------------------------+-------+
  15. | "Giannis Antetokounmpo" | 24 |
  16. +-------------------------+-------+
  17. | "Joel Embiid" | 25 |
  18. +-------------------------+-------+
  19. nebula> LOOKUP ON player WHERE player.age IN [25,28] YIELD player.name, player.age;
  20. +-------------+------------------+------------+
  21. | VertexID | player.name | player.age |
  22. +-------------+------------------+------------+
  23. | "player135" | "Damian Lillard" | 28 |
  24. +-------------+------------------+------------+
  25. | "player131" | "Paul George" | 28 |
  26. +-------------+------------------+------------+
  27. | "player130" | "Joel Embiid" | 25 |
  28. +-------------+------------------+------------+
  29. | "player123" | "Ricky Rubio" | 28 |
  30. +-------------+------------------+------------+
  31. | "player106" | "Kyle Anderson" | 25 |
  32. +-------------+------------------+------------+

结合NOT使用

  1. nebula> MATCH (v:player) \
  2. WHERE v.age NOT IN range(20,25) \
  3. RETURN v.name AS Name, v.age AS Age \
  4. ORDER BY Age;
  5. +---------------------+-----+
  6. | Name | Age |
  7. +---------------------+-----+
  8. | "Kyrie Irving" | 26 |
  9. +---------------------+-----+
  10. | "Cory Joseph" | 27 |
  11. +---------------------+-----+
  12. | "Damian Lillard" | 28 |
  13. +---------------------+-----+
  14. | "Paul George" | 28 |
  15. +---------------------+-----+
  16. | "Ricky Rubio" | 28 |
  17. +---------------------+-----+
  18. ...

最后更新: August 25, 2021