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!

Wembley

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. – Latrena

Answer 1

You could use:

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

Ergot

or even unwind coalesce(items, [null]) as item with new functionalities – Stour

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 – Raynold

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" – Cheerio

Answer 2

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 :)

Precambrian

Answer 3

@Michael Hunger's answer worked for me. But, I was facing issue when the list itself was null. So, I found a workaround for both null and empty lists like this:

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

Berthoud