How does the WHERE clause work in the apache AGE extension inside a cypher query?

This is the data setup:

select * from cypher('test', $$ CREATE (david:Person {name: 'David'})
CREATE (alice:Person {name: 'Alice'})
CREATE (bob:Person {name: 'Bob'})
CREATE (charlie:Person {name: 'Charlie'})
CREATE (eve:Person {name: 'Eve'})

CREATE (david)-[:FRIEND]->(alice)
CREATE (david)-[:FRIEND]->(bob)
CREATE (david)-[:FRIEND]->(charlie)
CREATE (david)-[:FRIEND]->(eve)
CREATE (alice)-[:FRIEND]->(bob)                                    
CREATE (charlie)-[:FRIEND]->(bob)
CREATE (eve)-[:FRIEND]->(frank:Person {name: 'Frank'})
CREATE (frank)-[:FRIEND]->(eve) 
CREATE (alice)-[:FRIEND]->(charlie) $$ ) as (a agtype);

Im searching for the query that returns the person that is a friend of David and has 2 or more outgoing relationships. According to the documantation this query should do it:

SELECT *
FROM cypher('graph_name', $$
	MATCH (david {name: 'David'})-[]-(otherPerson)-[]->()
	WITH otherPerson, count(*) AS foaf
	WHERE foaf > 1
	RETURN otherPerson.name
$$) as (name agtype);

However this returns Alice, Eve, and Charlie. It is like the WHERE clause is completely ignored. What is the correct query?

This was a bug that is fixed recently (i.e WITH ignoring WHERE clause). So until now, the newest release that is v1.2.0 doesn't incorporate this fix.

If you want to make it work, pull the recent changes from github repo https://github.com/apache/age and rebuild age.

Here is what I get as output on my machine

One approach of solving this problem is as follows:

Firstly, we run a query on the FRIEND relationship and then use size to count the relationships that are outgoing and the use the with and carry clause to extract the friends that have 2 or more outgoing repationships. Here's the code for it:

SELECT *
FROM cypher('test', $$
    MATCH (david:Person {name: 'David'})-[:FRIEND]->(friend)
    WITH friend, size((friend)-[:FRIEND]->()) as outgoingCount
    WHERE outgoingCount >= 2
    RETURN friend.name
$$) as (name agtype);

This query will return the person that is a friend of David and has 2 or more outgoing relationships.

SELECT *
FROM cypher('graph_name', $$
    MATCH (david:Person {name: 'David'})-[:FRIEND]->(otherPerson)
    WITH otherPerson, count(*) AS foaf
    WHERE foaf >= 2
    RETURN otherPerson.name
$$) as (name agtype);

It appears that the issue with your query lies in the WHERE clause and how you're counting the number of outgoing relationships. The count(*) is returning the count of all relationships of otherPerson, and that's why you're getting unexpected results. To count only the outgoing relationships from otherPerson, you need to adjust your query like this:

SELECT *
FROM cypher('graph_name', $$
    MATCH (david:Person {name: 'David'})-[:FRIEND]->(otherPerson)
    WITH otherPerson, count(otherPerson) AS foaf
    WHERE foaf >= 2
    RETURN otherPerson.name
$$) as (name agtype);

• explicitly specify that the relationship should be of type :FRIEND using MATCH (david:Person {name: 'David'})-[:FRIEND]->(otherPerson).
• count the number of otherPerson nodes, not all relationships, using count(otherPerson) AS foaf.
• use WHERE foaf >= 2 to filter for nodes with 2 or more outgoing :FRIEND relationships.

It does appear that Apache AGE is ignoring the WHERE clause inside the cypher function. One way you could do it is by using the WHERE clause outside the cypher function and adding foaf at the RETURN clause, just like this:

SELECT * FROM cypher('test', $$
    MATCH (david {name: 'David'})-[]-(otherPerson)-[]->() 
    WITH otherPerson, count(*) AS foaf 
    RETURN otherPerson.name, foaf 
    $$) as (name agtype, foaf agtype)
    WHERE foaf > 1;

This query returns every person's name that is a friend of David and has 2 or more outgoing relationships. According to the data provided it would be Alice.