How can I sum the properties along the edges in a deep traversal

user_5659856 0
Viewed 71

I have a part vertex and a used_by edge which a property called quantity.

I am currently starting with something like the following

MATCH (a:part {part_num: '123')-[u:used_by*]->(b:part {part_num: '456')
RETURN [x IN u::jsonb | x.properties.quantity] AS quantities

the array comprehension returns an array of quantities. there is one path from a-[*]->b but there are multiple hops. the u edges returned from the match are for each of the various hop paths and not just the one that connects a to b (longest path).

Once i have the array I need to sum the values in it.

I am not sure if this is possible in agensgraph

agens-graph cypher
1 Answers

Supposing you have a graph with the following setup:

CREATE (a:Part {'part_num': '123'}), (b:Part {'part_num': '345'}), 
       (c:Part {'part_num': '456'}), (d:Part {'part_num': '789'}); 

MATCH (a:Part {'part_num': '123'}), (b:Part {'part_num': '345'})
CREATE (a)-[u:used_by { quantity: 1 }]->(b);

MATCH (b:Part {'part_num': '345'}), (c:Part {'part_num': '456'})
CREATE (b)-[u:used_by { quantity: 2 }]->(c);

MATCH (a:Part {'part_num': '123'}), (d:Part {'part_num': '789'}) 
CREATE (a)-[u:used_by { quantity: 1 }]->(d);

You would then end up with the following graph: graph

Using your base query, we get back a JSON array

MATCH (a:part {part_num: '123'})-[u:used_by*]->(b:part {part_num: '456'})
RETURN [x IN u::jsonb | x.properties.quantity] AS quantities

-- Results
 quantities
------------
 [1, 2]
(1 row)

Finally, we just need to utilize the hybrid SQL/Cypher capabilities of AgensGraph to convert that array using jsonb_array_elements_text and then SUM up that subquery.

SELECT sum(quantity::int) AS sum_of_used_by
FROM (
  SELECT jsonb_array_elements_text(paths.quantity) AS quantity
  FROM ( 
    MATCH (a:part {part_num: '123'})-[u:used_by*]->(b:part {part_num: '456'})
    RETURN [x IN u::jsonb | x.properties.quantity] AS quantity
  ) AS paths
) as total

-- Results
 sum_of_used_by
----------------
              3
(1 row)
user_9740770
Related