Cypher: Use UNWIND with potentially empty collection

Question

Is there any way to use UNWIND for potentially empty collections (e.g. OPTIONAL UNWIND g)?

For instance, in the attached query it occurs that the collection (items) is empty sometimes (3rd block), but the results of the other collections are still relevant.

Here I want to crunch some numbers for a sub-graph and return the counts of various node types (group, users, location, item, itemgroups). The itemgroups can be derived via the items only. And because there are so many items attached to multiple users, it would be super slow if I include the itemgroups directly in the second block without aggregating first.

MATCH(group: Group {id: "12345"})
OPTIONAL MATCH(group) - [: IS_PARENT * 0..] - > (subgroup: Group)

WITH collect(distinct subgroup) as groups
UNWIND groups as group
  OPTIONAL MATCH(u: User) - [: BELONGS_TO] - > (group)
  OPTIONAL MATCH(u) --(i: Item)
  OPTIONAL MATCH(u) --(l: Location)
WITH groups, collect(distinct u) as users, collect(distinct i) as items, collect(distinct l) as locations
UNWIND items as i
  OPTIONAL MATCH(i) --(ig: FunctionalArea)
RETURN length(groups), length(users), length(items), length(locations), count(distinct ig)

I found a workaround, but I'm not really happy with that. When I insert a dummy node to the items collection, I can unwind it everytime without loosing results.

MATCH(group: Group {id: "12345"})
OPTIONAL MATCH(group) - [: IS_PARENT * 0..] - > (subgroup: Group)

WITH collect(distinct subgroup) as groups
UNWIND groups as group
  OPTIONAL MATCH(u: User) - [: BELONGS_TO] - > (group)
  OPTIONAL MATCH(u) --(i: Item)
  OPTIONAL MATCH(u) --(l: Location)
WITH groups, collect(distinct u) as users, collect(distinct i) as items, collect(distinct l) as locations

>> 
MATCH(ig:ItemGroup)
WITH groups, users, ([head(collect(ig))] + items) as items, locations
<<
UNWIND items as i
  OPTIONAL MATCH(i) --(ig: FunctionalArea)
RETURN length(groups), length(users), length(items), length(locations), count(distinct ig)

I'm considering writing two separate queries, but that would lead to complex client logic.

Any ideas and hints are very much appreciated.

Thanks!

I had this same conundrum. I went the `dummy` node root as well. It definitely lacked elegance but it did seem to work well. I would be interested to hear about other potential solutions, especially more elegant ones. — Dave Bennett, Apr 24 '15 at 10:32

score 21 · Answer 1 · answered Sep 13 '15 at 00:03

21

You could use:

UNWIND (CASE items WHEN [] then [null] else items end) as item

answered Sep 13 '15 at 00:03

Michael Hunger

41,339
3
57
80

3

or even `unwind coalesce(items, [null]) as item` with new functionalities – tscherg Jun 14 '18 at 15:15
1

`unwind coalesce(items, [null]) as item` will work only when items is explicitly null. If items is an empty collection then this approach won't work. Better solution is as mentioned i.e. `UNWIND (CASE items WHEN [] then [null] else items end) as item` – AbhishekTripathi Nov 07 '19 at 10:39
I tried this but it returns following error. Unknown function 'UNWIND' (line 2, column 8 (offset: 21)) "RETURN UNWIND (CASE f WHEN [] then [null] else f end) as items" – Zohaib Jun 09 '22 at 12:27

score 2 · Answer 2 · answered Aug 10 '15 at 15:10

i suggest you, instead of using so many unwind, try to reorganize your query.

Its a quick refactored one, but its just a suggestion ;) check it pls:

MATCH(group: Group {id: "12345"})-[:IS_PARENT*0..]->(subgroup: Group)
OPTIONAL MATCH(u: User)-[: BELONGS_TO]->(subgroup)
OPTIONAL MATCH(u) -- (l: Location)

WITH COLLECT(DISTINCT subgroup) AS g, COLLECT(DISTINCT u) AS uc,
    count(distinct subgroup) as groups, 
    count(distinct u) as users, 
    count(distinct l) as locations

UNWIND uc as u

OPTIONAL MATCH(u) --(i: Item)
OPTIONAL MATCH(i) --(ig: FunctionalArea)

RETURN groups, users, count(DISTINCT i) AS items, locations, count(distinct ig)

The subgroup will collect the standalone root groups as well, bc the *0.. . So the first optional match isn't necessary anymore.

I made the counts so early as possible. Only users needed on second level for collecting items data. Enjoy :)

Cypher: Use UNWIND with potentially empty collection

2 Answers2