Cypher: use UNWIND with a potentially empty collection

Question

Is it possible to use UNWIND for potentially empty collections (e.g. OPTIONAL UNWIND g)?

For example, in the attached request, it happens that the collection (elements) is sometimes empty (3rd block), but the results of other collections are still relevant.

Here I want to crunch some numbers for a subgraph and return counts of various types of node (group, users, location, element, groups of elements). Product groups can only be obtained through items. And since there are so many elements for many users, it would be really slow if I included group elements directly in the second block without aggregation in the first place.

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 very happy with that. When I insert a dummy node element into a collection of elements, I can unwind it every time without losing 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 am considering writing two separate queries, but this will lead to complex client logic.

Any ideas and tips are greatly appreciated.

Thanks!

Answer 1

You can use:

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

Answer 2

I suggest you, instead of using so many reversals, try to reorganize your request.

Its a quick refactoring, but its just a suggestion;) check it out 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 also assemble autonomous root groups, bc the * 0 ... Therefore, the first optional match is no longer needed.

I made the score as early as possible. At the second level, only users were needed to collect data. Enjoy :)