neo4jcypher

Aggregative query in neo4j


First, sorry for my English. I am modeling a railways DB in neo4j. I want to link stations in the order that they are linked by the railway, using the stops table. Every stop owns a "stop sequence" that unfortunately isn't like 1,2,3 (not always) but only progressive like 1,3,4,6. I wrote this query that, for the problem described, doesn't always work.

MATCH (a:Station)-[:stop]->(c:Stops_times)-[:trip]->(z:Trips)<-[:trip]-(d:Stops_times)<-[:stop]-(b:Station)  WHERE toint(c.stop_sequence)=toint(d.stop_sequence)+1  CREATE (a)-[s:next]->(b)

To find the right "next" I need a query similar to this:

MATCH (a:Station)-[:stop]->(c:Stops_times)-[:trip]->(z:Trips)<-[:trip]-(d:Stops_times)<-[:stop]-(b:Station) WITH c as c, d as d, MIN(d.stop_sequence) as min_ WHERE min_>c.stop_sequence  CREATE UNIQUE (a)-[s:next]->(b)

therefore, for every stop, I have to found the minimum "stop_sequence" between the higher ones than the "stop_sequence" of the stop of which I want to find the next


Solution

  • The following query seems to do what you want. It orders all the stops by stop_sequence, aggregates all the stops (still in order) for each trip, pairs up all adjoining stops for each trip, UNWINDs the pairs so that MERGE can use the paired nodes, and then uses MERGE to ensure that the :next relationship exists between all node pairs.

    MATCH (a:Station)-[:stop]->(c:Stops_times)-[:trip]->(t:Trips)
    WITH a, c, t
    ORDER BY c.stop_sequence
    WITH t, COLLECT(a) AS s
    WITH REDUCE(x =[], i IN RANGE(1, SIZE(s)-1)| x + {a: s[i-1], b: s[i]}) AS pairs
    UNWIND pairs AS p
    WITH p.a AS a, p.b AS b
    MERGE (a)-[n:next]->(b);
    

    It works properly in 2.3.2 on my Mac (but the neo4j versions available at http://console.neo4j.org/ do not work correctly when the query gets to MERGE).