Collect multiple results from cypher query.

[Alx]

I have the following cypher query:

MATCH (n:User) WHERE HAS(n.uid)  MATCH (n)-[:LOCATED_IN]->()<-[:LOCATED_IN]-(m),  (n)-[:TEXTED]-(k) WITH  COLLECT (DISTINCT m.name) AS users1, COLLECT( DISTINCT k.name ) AS users2 RETURN users1+ users2

But it returns me no result.  It just returns a title "users1+ users2". I know that there are results in the query because I executed them individually. 

My goal is to collect all the results from individual matches and then count the distinct names. Thanks for the help in advance.

[Lundin]

Hi Alx,

Most probably because your data/graph simply not fullful your total cypher statement, Remeber that your are matchning a pattern and you start with limiting it to users that has a uid property. For example the graph data below returns a resultset for each individually MATCH queries, however once chained, like your query, it fails to match that pattern. That is becuse there is no User that has uid property and other nodes that are connected via the LOCATED_IN (the cities they are living in) relationship, thus your query will return nil in the end.

So with this graph data: 

CREATE (Pontus:User { uid:'Pontus Lundin',name:'Pontus Lundin' }),(John:User { uid: 'John Hellberg',name:'John Hellberg' }),(Jack:User { uid: 'Jack Hellberg',name:'Jack Hellberg' }),(Nisse:User { name: 'Nisse Larsson' }),(Kalle:User { name: 'Kalle Johansson' }),(Johan:User { name:'Johan Davidsson' }),(Ronny:User { name: 'Ronny Stork' }),(Micke:User { name: 'Mikael Jansson' }),(Anna:User { name: 'Anna Karlsson', sex:'female' }),(Sandra:User { name: 'Sandra Johansson', sex:'female' }),(Fredrik:User { name:'Fredrik Hansson' }),(Gothenburg:Location { name:'Gothenburg',dailyhits:1000000 }),(Stockholm:Location { name:'Stockholm',dailyhits:300000 }),(Sundsvall:Location { name:'Sundsvall',dailyhits:6000 }),(Malmo:Location { name:'Malmö',dailyhits:20000 }),

(Pontus)-[:LOCATED_IN]->(Gothenburg),

(Ronny)-[:LOCATED_IN]->(Sundsvall),

(Sandra)-[:LOCATED_IN]->(Sundsvall),

(Pontus)-[:TEXTED]->(Sandra),

(Pontus)-[:TEXTED]->(Fredrik),

(Jack)-[:LOCATED_IN]->(Malmo),

(John)-[:LOCATED_IN]->(Stockholm),

(Jack)-[:TEXTED]->(Pontus);

This pattern will return data:

MATCH (n:User)

WHERE HAS (n.uid)

return n;

As well as:

MATCH (n)-[:LOCATED_IN]->()<-[:LOCATED_IN]-(m),(n)-[:TEXTED]-(k)

WITH COLLECT(DISTINCT m.name) AS users1, COLLECT(DISTINCT k.name) AS users2

RETURN users1+ users2

But not this chained Cypher query which return empty

MATCH (n:User)

WHERE HAS (n.uid)

MATCH (n)-[:LOCATED_IN]->()<-[:LOCATED_IN]-(m),(n)-[:TEXTED]-(k)

WITH COLLECT(DISTINCT m.name) AS users1, COLLECT(DISTINCT k.name) AS users2

RETURN users1+ users2

However if your data has connection that fullfulls your query then it works (now a User with uid lives in the same city as another node), like:

CREATE (Pontus:User { uid:'Pontus Lundin',name:'Pontus Lundin' }),(John:User { uid: 'John Hellberg',name:'John Hellberg' }),(Jack:User { uid: 'Jack Hellberg',name:'Jack Hellberg' }),(Nisse:User { name: 'Nisse Larsson' }),(Kalle:User { name: 'Kalle Johansson' }),(Johan:User { name:'Johan Davidsson' }),(Ronny:User { name: 'Ronny Stork' }),(Micke:User { name: 'Mikael Jansson' }),(Anna:User { name: 'Anna Karlsson', sex:'female' }),(Sandra:User { name: 'Sandra Johansson', sex:'female' }),(Fredrik:User { name:'Fredrik Hansson' }),(Gothenburg:Location { name:'Gothenburg',dailyhits:1000000 }),(Stockholm:Location { name:'Stockholm',dailyhits:300000 }),(Sundsvall:Location { name:'Sundsvall',dailyhits:6000 }),(Malmo:Location { name:'Malmö',dailyhits:20000 }),

(Pontus)-[:LOCATED_IN]->(Sundsvall),

(Ronny)-[:LOCATED_IN]->(Sundsvall),

(Sandra)-[:LOCATED_IN]->(Sundsvall),

(Pontus)-[:TEXTED]->(Sandra),

(Pontus)-[:TEXTED]->(Fredrik),

(Jack)-[:LOCATED_IN]->(Malmo),

(John)-[:LOCATED_IN]->(Stockholm),

(Jack)-[:TEXTED]->(Pontus);

If you still want to return *something* (if it makes sense for your result) event though the "match fails" you can use the OPTIONAL match like the one below:

MATCH (n:User)

WHERE HAS (n.uid)

OPTIONAL

MATCH (n)-[:LOCATED_IN]->()<-[:LOCATED_IN]-(m)

WITH n,m

MATCH (n)-[:TEXTED]-(k)

WITH COLLECT(DISTINCT m.name) AS users1, COLLECT(DISTINCT k.name) AS users2

RETURN users1+ users2

[Alx]

Hi Lundin,

Thanks for the elaborate response. I can understand your thinking and it is helpful. The point is: what is it more efficient? a) To first find users with uid and then try to optional match a pattern?  b) Or try to optional match the pattern and after that keep ('WHERE' command) only those results that users have uids?

I still have the question: how do I remove the duplicates from the combined users1+ users2 list? Any suggestions?

[Lundin]

Alx.

Wes Freeman wrote a blog post about OPTIONAL match, here:
http://wes.skeweredrook.com/cypher-2-0-optional-match/
At the bottom he explains the differences between using where before and after. Also limiting the questions before match seems logical so we dont have to match all users and then say but we are only intrested of user with a given property.

For the question about removing duplicates, i have no answer for the result as it is now with two collections. But i fail to see how that output, the list make any sense other than for testing puropse. How do you delimit and know what belongs to what query ?
What do you want from the query ?

For me this one seems to fit a purpose:

 

MATCH (n:User)

WHERE HAS (n.uid)
OPTIONAL

MATCH (n)-[:LOCATED_IN]->()<-[:LOCATED_IN]-(m)

WITH n,(m.name) AS users

MATCH (n)-[:TEXTED]->(k)

WHERE k.name<>users

RETURN users,collect(k.name) AS texted_users,count(k.name) AS NoUserText

This one list user that fullfulls the (n)-[:LOCATED_IN]->()<-[:LOCATED_IN]-(m) pattern and list them in the first column (from my perspective that is some kind of search of people that has no uid but is LOCATED_IN same as users with a uid).
The next column lists all users where users with an uid has TEXTED to and removes the user in the first column (if he exists in the TEXTED list) (for me that is some kind of recommendation or analytic search)

Of course you can add distinct to the various columns if a person has texted to the same user multiple times.

You can also look into what UNION does for MATCH based queries as well.

[Alx]

Lundin,

Thanks for the reply. Basically I want to do what UNION (http://docs.neo4j.org/chunked/stable/query-union.html) does exactly but instead of stopping at the end of return continue with other queries. For example something like this:

MATCH (n:User{id:'1'} - [:REL1] - (m1) RETURN m1.id AS users

MATCH (n:User{id:'1'} - [:REL2] - (m2) RETURN m2.id AS users

MATCH (n:User{id:'1'} - [:REL3] - (m3) RETURN m3.id AS users

Then collect the results like 'COLLECT (users) AS group1 ' because it is meaningful that they are grouped together and then continue executing the following queries:

MATCH (n:User{id:'1'} - [:REL4] - (m4) WITH collect(m4.id) AS group 2 ,

MATCH (n:User{id:'1'} - [:REL5] - (m5) WITH collect(m5.id) AS group 3

And finally return the collections:

RETURN  group1, group2, group3

I wonder if this is possible... 

Thanks a lot.

[Mark Needham]

What about?

MATCH (n:User{id:'1'}) - [:REL1|:REL2|:REL3] - (m1)

WITH n, COLLECT(m1) as group1

MATCH (n) - [:REL2] - (m2)

WITH n, group1, COLLECT(m2) AS group2

MATCH (n) - [:REL3] - (m3)

RETURN n, group1, group2, COLLECT(m3) as group3

--
You received this message because you are subscribed to the Google Groups "Neo4j" group.
To unsubscribe from this group and stop receiving emails from it, send an email to neo4j+un...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Lundin]

What Mark said above or if you need to differantiate the user id into the collection (array in array) like: 

MATCH (n:User { uid:'Pontus Lundin' })-[:REL 1]-(m1), n-[:REL 2]-(m2),n-[:REL 3]-(m3)

WITH n,collect(DISTINCT[m1.name,m2.name,m3.name]) AS group1

MATCH n -[:REL4]-(m1)

WITH group1,collect(m1.name) AS group2

RETURN group1,group2

You can continue building with with and match.

[Alx]

Great help guys! I appreciate it.