How to remove objects containing NULL from COLLECT() clause in CYPHER query?

[philipp_ldn]

I've run into the following issue with this Cypher query. The overall results are returned in an array of objects, as intended. However, the collect() clause is giving me some trouble.

If user has no myFriends and no theirFriends , an empty array should be returned. Otherwise, if myFriends and/or theirFriends has values, it should be a single array of objects of the combined friends, with the id property of the respective friend.

Query:

MATCH (user:User)
WHERE user.id IN ['1', '2', '3']
OPTIONAL MATCH (user)-[:HAS_FRIEND]->(myFriends:User)
OPTIONAL MATCH (user)<-[:HAS_FRIEND]-(theirFriends:User)
OPTIONAL MATCH (user)-[:HAS_TEACHER]->(myTeachers:User)
WITH user, myFriends, friends2
RETURN {
  name: user.name,
  friends: collect({id: myFriends.id}) + collect({id: theirFriends.id}),
  teachers: collect({id: myTeacher.id})
}

Results in:

[
  {
    name: 'Joe',
    friends: [{id: null}, {id: null}],
    teachers: [{id: null}]
  }, ...
]

Desired result:

[
  {
    name: 'Joe',
    friends: [],
    teachers: []
  }, {
    name: 'Jen',
    friends: [{id: '4'}, {id: '6'}, {id: '7'}],
    teachers: [{id: '8'}, {id: '9'}]
  }
]

[david_allen]

You can use list comprehensions in cypher to remove null values, acting like a "filter" of sorts.

Here's a simplified example:

with [1, 2, null, 3, null, 4] as myList
return [val in myList WHERE val is not null];

(This would return [1, 2, 3, 4])

Use this "list comprehension" filter [val in myList WHERE val is not null] on your friends and teachers lists and it should be fine.

[philipp_ldn]

Thanks! That's a neat solution dealing with lists and does answer my question.

I've also come across another solution using map projections.

RETURN {
  name: user.name,
  friends: collect(myFriends { .id }) + collect(theirFriends { .id }),
  teachers: collect(myTeacher { .id })
}

[EarthlingDavey]

This is a hidden gem! So this is how I can filter an array .

I was searching the docs under Functions > List Functions and couldn't find it.

So, thank you for sharing and linking to the right docs page.