How to sum every property from a variable length edge

Question

I have a graph that has vertices as cities and it also has edges that connect each city. These edges contain a property called travelTime that represents the time to got from one city to another.

SELECT * FROM cypher('Saxeburg', $$
MATCH (v1)-[e]->(v2) RETURN v1.name, e.travelTime, v2.name
$$) as (from_city agtype, travel_time agtype, to_city agtype);

      from_city       | travel_time |       to_city        
----------------------+-------------+----------------------
 "Cavite Island"      | 2.5         | "Intramuros"
 "Cavite Island"      | 3           | "Fort Tilden"
 "St Germain"         | 9           | "Intramuros"
 "St Germain"         | 5.6         | "China Town"
 "Pigalle"            | 6           | "China Town"
 "Pigalle"            | 4           | "Montreal"
 "Pigalle"            | 8.5         | "NYC"
 "Montreal"           | 3           | "Quebec"
 "Fort Tilden"        | 13          | "Brooklyn"
 "Coney Island"       | 1.5         | "Brooklyn"
 "Brooklyn"           | 2.5         | "Uptown"
 "Brooklyn"           | 5           | "The Ruins"
 "Uptown"             | 5           | "Intramuros"
 "Intramuros"         | 11          | "China Town"
 "Intramuros"         | 16.5        | "Bastille"
 "China Town"         | 7.5         | "Divisoria"
 "China Town"         | 4.5         | "Ermita"
 "China Town"         | 12.5        | "NYC"
 "The Ruins"          | 4           | "Card Shark"
 "The Ruins"          | 5.5         | "Phoenix"
 "The Ruins"          | 2.5         | "Red Light"
 "Card Shark"         | 4.5         | "Phoenix"
 "Divisoria"          | 6.5         | "Bastille"
 "Ermita"             | 9           | "Puerto del Postigo"
 "NYC"                | 10.5        | "Puerto del Postigo"
 "NYC"                | 5           | "St Domingo"
 "NYC"                | 2           | "Sta Isabel"
 "Phoenix"            | 3.5         | "Red Light"
 "Phoenix"            | 10          | "Bastille"
 "Bastille"           | 6.5         | "Hotel St Paul"
 "Bastille"           | 6           | "Puerto del Postigo"
 "Puerto del Postigo" | 3           | "Sta Isabel"

How could I get, for example, the total travel time from Cavite Island to Bastille and the multiple routes it takes to get to it?

Muhammad Taha Naveed · Accepted Answer · 2023-03-25T20:03:52.510

You can use the following query

SELECT * FROM cypher('stack75632692',$$
    MATCH path = (a {name: 'Cavite Island'})-[*]->(b {name: 'Bastille'})
    WITH path, relationships(path) AS edges
    UNWIND edges AS edge
    RETURN path, sum(edge.travelTime)
$$) as (path agtype, travelTime agtype);

Result:

                                                                                                                                                                                              
                                                                                                                                                                                              
                                                                                                                                                                                              
                                                                                                                                                                                              
                                                                                                    path                                                                                      
                                                                                                                                                                                              
                                                                                                                                                                                              
                                                                                                                                                                                              
                                                                                                                                                                                              
              | traveltime 
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------+------------
 [{"id": 844424930131969, "label": "Metro", "properties": {"name": "Cavite Island"}}::vertex, {"id": 1125899906842626, "label": "HAS_ROUTE", "end_id": 844424930131974, "start_id": 8444249301
31969, "properties": {"travelTime": 3}}::edge, {"id": 844424930131974, "label": "Metro", "properties": {"name": "Fort Tilden"}}::vertex, {"id": 1125899906842633, "label": "HAS_ROUTE", "end_i
d": 844424930131979, "start_id": 844424930131974, "properties": {"travelTime": 13}}::edge, {"id": 844424930131979, "label": "Metro", "properties": {"name": "Brooklyn"}}::vertex, {"id": 11258
99906842636, "label": "HAS_ROUTE", "end_id": 844424930131986, "start_id": 844424930131979, "properties": {"travelTime": 5}}::edge, {"id": 844424930131986, "label": "Metro", "properties": {"n
ame": "The Ruins"}}::vertex, {"id": 1125899906842643, "label": "HAS_ROUTE", "end_id": 844424930131981, "start_id": 844424930131986, "properties": {"travelTime": 4}}::edge, {"id": 84442493013
1981, "label": "Metro", "properties": {"name": "Card Shark"}}::vertex, {"id": 1125899906842646, "label": "HAS_ROUTE", "end_id": 844424930131987, "start_id": 844424930131981, "properties": {"
travelTime": 4.5}}::edge, {"id": 844424930131987, "label": "Metro", "properties": {"name": "Phoenix"}}::vertex, {"id": 1125899906842653, "label": "HAS_ROUTE", "end_id": 844424930131988, "sta
rt_id": 844424930131987, "properties": {"travelTime": 10}}::edge, {"id": 844424930131988, "label": "Metro", "properties": {"name": "Bastille"}}::vertex]::path                                
                                                                                                                                                                                              
              | 39.5
 [{"id": 844424930131969, "label": "Metro", "properties": {"name": "Cavite Island"}}::vertex, {"id": 1125899906842625, "label": "HAS_ROUTE", "end_id": 844424930131975, "start_id": 8444249301
31969, "properties": {"travelTime": 2.5}}::edge, {"id": 844424930131975, "label": "Metro", "properties": {"name": "Intramuros"}}::vertex, {"id": 1125899906842639, "label": "HAS_ROUTE", "end_
id": 844424930131988, "start_id": 844424930131975, "properties": {"travelTime": 16.5}}::edge, {"id": 844424930131988, "label": "Metro", "properties": {"name": "Bastille"}}::vertex]::path    
                                                                                                                                                                                              
                                                                                                                                                                                              
                                                                                                                                                                                              
                                                                                                                                                                                              
                                                                                                                                                                                              
                                                                                                                                                                                              
              | 19.0
 [{"id": 844424930131969, "label": "Metro", "properties": {"name": "Cavite Island"}}::vertex, {"id": 1125899906842626, "label": "HAS_ROUTE", "end_id": 844424930131974, "start_id": 8444249301
31969, "properties": {"travelTime": 3}}::edge, {"id": 844424930131974, "label": "Metro", "properties": {"name": "Fort Tilden"}}::vertex, {"id": 1125899906842633, "label": "HAS_ROUTE", "end_i
d": 844424930131979, "start_id": 844424930131974, "properties": {"travelTime": 13}}::edge, {"id": 844424930131979, "label": "Metro", "properties": {"name": "Brooklyn"}}::vertex, {"id": 11258
99906842635, "label": "HAS_ROUTE", "end_id": 844424930131980, "start_id": 844424930131979, "properties": {"travelTime": 2.5}}::edge, {"id": 844424930131980, "label": "Metro", "properties": {
"name": "Uptown"}}::vertex, {"id": 1125899906842637, "label": "HAS_ROUTE", "end_id": 844424930131975, "start_id": 844424930131980, "properties": {"travelTime": 5}}::edge, {"id": 844424930131
975, "label": "Metro", "properties": {"name": "Intramuros"}}::vertex, {"id": 1125899906842638, "label": "HAS_ROUTE", "end_id": 844424930131976, "start_id": 844424930131975, "properties": {"t
ravelTime": 11}}::edge, {"id": 844424930131976, "label": "Metro", "properties": {"name": "China Town"}}::vertex, {"id": 1125899906842640, "label": "HAS_ROUTE", "end_id": 844424930131982, "st
art_id": 844424930131976, "properties": {"travelTime": 7.5}}::edge, {"id": 844424930131982, "label": "Metro", "properties": {"name": "Divisoria"}}::vertex, {"id": 1125899906842647, "label": 
"HAS_ROUTE", "end_id": 844424930131988, "start_id": 844424930131982, "properties": {"travelTime": 6.5}}::edge, {"id": 844424930131988, "label": "Metro", "properties": {"name": "Bastille"}}::
vertex]::path | 48.5
 [{"id": 844424930131969, "label": "Metro", "properties": {"name": "Cavite Island"}}::vertex, {"id": 1125899906842625, "label": "HAS_ROUTE", "end_id": 844424930131975, "start_id": 8444249301
31969, "properties": {"travelTime": 2.5}}::edge, {"id": 844424930131975, "label": "Metro", "properties": {"name": "Intramuros"}}::vertex, {"id": 1125899906842638, "label": "HAS_ROUTE", "end_
id": 844424930131976, "start_id": 844424930131975, "properties": {"travelTime": 11}}::edge, {"id": 844424930131976, "label": "Metro", "properties": {"name": "China Town"}}::vertex, {"id": 11
25899906842640, "label": "HAS_ROUTE", "end_id": 844424930131982, "start_id": 844424930131976, "properties": {"travelTime": 7.5}}::edge, {"id": 844424930131982, "label": "Metro", "properties"
: {"name": "Divisoria"}}::vertex, {"id": 1125899906842647, "label": "HAS_ROUTE", "end_id": 844424930131988, "start_id": 844424930131982, "properties": {"travelTime": 6.5}}::edge, {"id": 8444
24930131988, "label": "Metro", "properties": {"name": "Bastille"}}::vertex]::path                                                                                                             
                                                                                                                                                                                              
                                                                                                                                                                                              
                                                                                                                                                                                              
              | 27.5
 [{"id": 844424930131969, "label": "Metro", "properties": {"name": "Cavite Island"}}::vertex, {"id": 1125899906842626, "label": "HAS_ROUTE", "end_id": 844424930131974, "start_id": 8444249301
31969, "properties": {"travelTime": 3}}::edge, {"id": 844424930131974, "label": "Metro", "properties": {"name": "Fort Tilden"}}::vertex, {"id": 1125899906842633, "label": "HAS_ROUTE", "end_i
d": 844424930131979, "start_id": 844424930131974, "properties": {"travelTime": 13}}::edge, {"id": 844424930131979, "label": "Metro", "properties": {"name": "Brooklyn"}}::vertex, {"id": 11258
99906842636, "label": "HAS_ROUTE", "end_id": 844424930131986, "start_id": 844424930131979, "properties": {"travelTime": 5}}::edge, {"id": 844424930131986, "label": "Metro", "properties": {"n
ame": "The Ruins"}}::vertex, {"id": 1125899906842644, "label": "HAS_ROUTE", "end_id": 844424930131987, "start_id": 844424930131986, "properties": {"travelTime": 5.5}}::edge, {"id": 844424930
131987, "label": "Metro", "properties": {"name": "Phoenix"}}::vertex, {"id": 1125899906842653, "label": "HAS_ROUTE", "end_id": 844424930131988, "start_id": 844424930131987, "properties": {"t
ravelTime": 10}}::edge, {"id": 844424930131988, "label": "Metro", "properties": {"name": "Bastille"}}::vertex]::path                                                                          
                                                                                                                                                                                              
                                                                                                                                                                                              
              | 36.5
 [{"id": 844424930131969, "label": "Metro", "properties": {"name": "Cavite Island"}}::vertex, {"id": 1125899906842626, "label": "HAS_ROUTE", "end_id": 844424930131974, "start_id": 8444249301
31969, "properties": {"travelTime": 3}}::edge, {"id": 844424930131974, "label": "Metro", "properties": {"name": "Fort Tilden"}}::vertex, {"id": 1125899906842633, "label": "HAS_ROUTE", "end_i
d": 844424930131979, "start_id": 844424930131974, "properties": {"travelTime": 13}}::edge, {"id": 844424930131979, "label": "Metro", "properties": {"name": "Brooklyn"}}::vertex, {"id": 11258
99906842635, "label": "HAS_ROUTE", "end_id": 844424930131980, "start_id": 844424930131979, "properties": {"travelTime": 2.5}}::edge, {"id": 844424930131980, "label": "Metro", "properties": {
"name": "Uptown"}}::vertex, {"id": 1125899906842637, "label": "HAS_ROUTE", "end_id": 844424930131975, "start_id": 844424930131980, "properties": {"travelTime": 5}}::edge, {"id": 844424930131
975, "label": "Metro", "properties": {"name": "Intramuros"}}::vertex, {"id": 1125899906842639, "label": "HAS_ROUTE", "end_id": 844424930131988, "start_id": 844424930131975, "properties": {"t
ravelTime": 16.5}}::edge, {"id": 844424930131988, "label": "Metro", "properties": {"name": "Bastille"}}::vertex]::path                                                                        
                                                                                                                                                                                              
                                                                                                                                                                                              
              | 40.0
(6 rows)

For this to work, please pull the recent commits of age on master branch.

`UNWIND p AS path` statement is unnecessary as `UNWIND` only functions with lists, not paths. — Mohayu Din, Mar 23 '23 at 10:29
I'm receiving an error: `UNWIND clause does not support agtype edge`. I am at the `AGE_label_inheritance` branch btw. Should I switch to another one? — Matheus Farias, Mar 24 '23 at 21:03
@MatheusFarias Yes, recent commits on master branch fix this error. — Muhammad Taha Naveed, Mar 25 '23 at 18:06

How to sum every property from a variable length edge

1 Answers1

Linked

Related