CODEWITHSUNDEEP
neo4jcypheraggregate-functions
D'Arrigo Alessio
D'Arrigo Alessio

Reputation: 13

how to count elements inside a collect() list and use that count() for a WHERE condition

hope to get some help.

having the following query:

MATCH  (a:Academy)-[]-(w:Work)
RETURN collect(distinct a.name)as Associated_academies_for_this_book,w.shortTitle as  
       book_short_title, w.workId as Book_ID order by size(collect(distinct a.name)) desc

with this output: result query

i need to return only results having more than 1 element inside lists of first column (the column generated by collect(distinct a.name)),i tried with count(collect)>1 and something similar but didn't manage to have what i need. Any ideas?

thanks a lot

Upvotes: 1

Views: 2342

Answers (1)

demokritos
demokritos

Reputation: 1446

You can use size

MATCH (a:Academy)-[]-(w:Work)
WITH collect(distinct a.name) as Associated_academies_for_this_book, w.shortTitle as book_short_title, w.WorkID as Book_ID 
WHERE size(Associated_academies_for_this_book)>1
RETURN book_short_title, Book_ID

or

You can use count as referenced in the documentation, the aggregate works with * and that should give you the occurrences.

MATCH (a:Academy)-[]-(w:Work)
WITH collect(distinct a.name) as Associated_academies_for_this_book, 
     w.shortTitle as book_short_title, w.workId as Book_ID, count (*) as occ
WHERE occ>1
RETURN occ, Associated_academies_for_this_book, book_short_title, Book_ID

The second one stores this information as occ variable with other relevant columns in WITH statement as an intermediary step and you can use it if needed later on/in the RETURN statement. You can do either with size or count aggregates.

Upvotes: 3

Related Questions