CODEWITHSUNDEEP
neo4jsubquerycypher
Jake
Jake

Reputation: 2236

How to limit a subquery in cypher?

Say I have 3 things in my graph database and each of these 3 things have 0 or more subthings attached to them. How do I make a query to retrieve the top 2 subthings for each of the things in the graph (according to some arbitrary ordering).

Here's an example setup:

CREATE (t1:Thing)-[:X]->(a1:SubThing),
             (t1)-[:X]->(a2:SubThing),
             (t1)-[:X]->(a3:SubThing),
             (t1)-[:X]->(a4:SubThing),
       (t2:Thing)-[:X]->(b1:SubThing),
             (t2)-[:X]->(b2:SubThing),
             (t2)-[:X]->(b3:SubThing),
       (t3:Thing);

What match command could I run to receive a table like this:

t           s
(0:Thing)   (3:SubThing)
(0:Thing)   (4:SubThing)
(1:Thing)   (7:SubThing)
(1:Thing)   (8:SubThing)
(2:Thing)   null

Here's a console.neo4j.org I was using to work it out.

http://console.neo4j.org/r/k32uyy

Upvotes: 0

Views: 1045

Answers (2)

Jake
Jake

Reputation: 2236

This is probably the best way to do it. I can't unwind the collection or else I'll lose the empty :Thing.

MATCH (t:Thing)
OPTIONAL MATCH (t)-->(s:SubThing)
WITH t, s
ORDER BY id(s)
RETURN t, collect(s)[0..2]
ORDER BY id(t)

And it returns this:

t           collect(s)[0..2]
(0:Thing)   [(3:SubThing), (4:SubThing)]
(1:Thing)   [(7:SubThing), (8:SubThing)]
(2:Thing)   []

Upvotes: 2

Stefan Armbruster
Stefan Armbruster

Reputation: 39905

You need to use an OPTIONAL MATCH here, aggregate by same start node and sort the result by the count of subthings:

MATCH (t:Thing)
OPTIONAL MATCH (t)-[:X]->(s)
RETURN t, count(s) AS degree
ORDER BY degree DESC 
LIMIT 2

Upvotes: 1

Related Questions