Neo4j Cypher Query based on related node's property. Get node with max property value

Question

I have the following structure:

(A)-[:many]->(B)-[:also_many]->(C)

B has a name property. And C has a date property. A has many B related and further each B has many C related.

I want to get unique B along with C where c.date is the highest.

date is stored like this: "YYYY-MM-DD HH:MM:SS". I am using apoc.date.parse(date) to get a timestamp value.

Can't really want figure out how to proceed with this.

Sample Dataset:

create (o:A {outlet: "SFO"}), (a:B {name: "Varun", date: "2017-04-01 00:00:00"}), (b:B {name: "Karen", date: "2017-04-12 00:00:00"}), (c:B {name: "Vicky", date: "2017-06-01 00:00:00"}), (aa:C {date: "2017-09-8 00:00:00", tag_no: "R2017123"}), (ab:C {date: "2017-09-02 00:00:00", tag_no: "R2017321"}), (ac:C {date: "2017-09-10 00:00:00", tag_no: "R2017423"}), (ad:C {date: "2017-09-23 00:00:00", tag_no: "R2017523"}), (ae:C {date: "2017-09-10 00:00:00", tag_no: "R2017930"}), (ba:C {date: "2017-09-02 00:00:00", tag_no:"R2017928"}), (bb:C {date: "2017-09-15 00:00:00", tag_no:"R2017039"}), (bc:C {date: "2017-09-10 00:00:00", tag_no:"R2017837"}), (bd:C {date: "2017-09-08 00:00:00", tag_no:"R2017022"}), (be:C {date: "2017-09-10 00:00:00", tag_no:"R2017883"}), (ca:C {date: "2017-09-11", tag_no:"R2017827"}), (cb:C {date: "2017-09-18", tag_no:"R2017322"}), (cc:C {date: "2017-09-21", tag_no:"R2017122"}), (cd:C {date: "2017-09-16", tag_no:"R2017877"}), (ce:C {date: "2017-09-08", tag_no:"R2017930"}), (o)-[:owns]->(a),(o)-[:owns]->(b),(o)-[:owns]->(c),(a)-[:subs]->(aa),(a)-[:subs]->(ab),(a)-[:subs]->(ac),(a)-[:subs]->(ad),(a)-[:subs]->(ae),(b)-[:subs]->(ba),(b)-[:subs]->(bb),(b)-[:subs]->(bc),(b)-[:subs]->(bd),(b)-[:subs]->(be),(c)-[:subs]->(ca),(c)-[:subs]->(cb),(c)-[:subs]->(cc),(c)-[:subs]->(cd),(c)-[:subs]->(ce);

I need two queries:

I want to return B nodes which have date within say 2014-04-01 and 2014-05-01 and the latest related C node for each B node by B.date. In the dataset, i have two C nodes which have the same date. But query should only return one result per B node.

And the second query is:

Return B nodes with date within above values, which do not have a C node with date within a date range, say "2017-09-01" and "2017-09-09".

Why do you need APOC for this? Dates in this format can be ordered lexicographically, so ORDER BY c.date DESC LIMIT 1 should get you the largest value. Of course, if you want to use that date, you'll have to parse it, but you can do that in the application code. — Gabor Szarnyas
I was trying to use the max() function and this date format is not compatible. — notANerdDev
What is 'lexicographically'? This is a new term for me. Is there a function to get the highest date for a few nodes? — notANerdDev
For lexicographically, think alphabetical ordering. While I would not recommend it, the max() function should also work: WITH ['2015-07-16 17:07:21', '2017-10-04 14:26:19'] AS xs UNWIND xs AS x RETURN max(x) (but it'll be more difficult to get the actual graph node with the highest value). — Gabor Szarnyas
This query answer your first question? MATCH (b:B)-[:subs]->(c:C) WHERE b.date >= "2017-04-01" and b.date <= "2017-04-15" WITH b as b, max(c.date) as cs RETURN b, cs — Bruno Peres

Bruno Peres Bruno Peres · Accepted Answer · 2017-10-04T18:04:48

I want to return B nodes which have date within say 2014-04-01 and 2014-05-01 and the latest related C node for each B node by B.date. In the dataset, i have two C nodes which have the same date. But query should only return one result per B node.

This should work:

MATCH (b:B)-[:subs]->(c:C)
// filter b nodes by date range
WHERE b.date >= "2017-04-01" and b.date <= "2017-04-15"
// store c nodes into collection and get the max date for c nodes
// grouped by b
WITH b as b, collect(c) as cs, MAX(c.date) as maxDate
// return b, filtering only c nodes that date are equals to maxDate
// getting only the first filtered 
return b, filter(c in cs WHERE c.date = maxDate)[0]

Return B nodes with date within above values, which do not have a C node with date within a date range, say "2017-09-01" and "2017-09-09".

And this:

MATCH (b:B)-[:subs]->(c:C)
WHERE (b.date >= "2017-04-01" and b.date <= "2017-04-15")
AND (c.date <= "2017-09-01" and c.date >= "2017-09-09")
RETURN b

Neo4j Cypher Query based on related node's property. Get node with max property value

1 Answers