Return response appears in repeating number

[ri8ika]

I have query like:

UNWIND [{name:'foo'},{name:'bar'}] as lorem
UNWIND [{name:'soo'},{name:'sar'}] as sorem

MERGE (s:Sorem) - [:LOREMED] -> (l:Lorem {
  name: lorem.name
})
MERGE (s2:Sorem) - [:LOREMED] -> (l2:Lorem {
  name: sorem.name
})
RETURN l

It correctly creates the data and there is no repeat in the graph but when I see the result it appears in repeating number. While RETURN l should only return:

{
  name: 'foo'
}
{
  name: 'bar'
}

It is appearing like:

{
  name: 'foo'
}
{
  name: 'bar'
}
{
  name: 'foo'
}
{
  name: 'bar'
}

I can think the result is caused somehow by using MERGE statement multiple times? How can I solve this?

[andrew_bowman]

You need to pay attention to how cardinality works in Cypher.

You did 2 UNWINDs of 2 elements each, so you have 2 * 2 = 4 rows. Each element of your lorem appears twice for that total of 4 (foo with soo, foo with sar, bar with soo, bar with sar).

Looking JUST at that first MERGE, let's look at what happens:

1. MERGE the pattern for foo | soo. The pattern doesn't exist so it's created. A new :Sorem node is created without properties, a new :Lorem node is created with name = 'foo'. A :LOREMED relationship is created between them.

2. MERGE the pattern for foo | sar. The pattern exists (we created it in the previous step: a :Sorem node connected by a :LOREMED rel to a :Lorem node with the name 'foo'). No new nodes or rels are created.

3. MERGE the pattern for bar | soo. The pattern doesn't exist so it's created. A new :Sorem node is created without properties, a new :Lorem node is created with name = 'bar'. A :LOREMED relationship is created between them.

4. MERGE the pattern for foo | sar. The pattern exists (we created it in the previous step: a :Sorem node connected by a :LOREMED rel to a :Lorem node with the name 'bar'). No new nodes or rels are created.

We can ignore the second MERGE statement for now.

When we return l, cardinality is still 4 rows, nothing has been done to grow it (UNWIND, MATCH, OPTIONAL MATCH, CALL ... YIELD) or shrink it (LIMIT, aggregations, filtering from WHERE or MATCHes).

Your return will return 4 rows, but there are two l nodes, each one just gets repeated once.

If you wanted only two rows corresponding to the to l nodes, use RETURN DISTINCT l.

[ri8ika]

Ah, I was thinking about distinct. But was not sure what's going on here.

Thanks again.

[ri8ika]

It doesn't work again if I do return both:

RETURN DISTINCT l, l2

Results in repeating.

[andrew_bowman]

Yes, because you're looking at distinct combinations. DISTINCT applies to the entire row, not just a single element, so your distinct rows are the nodes corresponding with foo | soo, foo | sar, bar | soo, bar | sar. They are distinct.

[andrew_bowman]

If you want to group your l2 nodes by the corresponding l nodes, then you can add collect() into the mix:

RETURN l, collect(l2) as l2s

l will become distinct, though you will see repeats in l2s between the rows, because each l2 occurs once per l.

[ri8ika]

I'm not getting the desired result. Any other way?

Result should appear like if I wish to return both:

{
  name: 'foo'
}
{
  name: 'bar'
}
{
  name: 'soo'
}
{
  name: 'sar'
}

[andrew_bowman]

You don't have all the nodes under the same variable, so if this is REALLY what you want you'll need to do some work in Cypher to gather them there, that way they'll all be under a single column.

Looking at your query, however, given that you're treating lorem elements and sorem elements exactly the same (creating the exact same pattern, and associating their names with a :Lorem element in all cases), it would actually be better to get them into the same column variable early on and treat them as the same, and that way we avoid the cross product you stumbled into, and you'll get expected results at the end.

Try this instead:

UNWIND [{name:'foo'},{name:'bar'}] + [{name:'soo'},{name:'sar'}] as lorem

MERGE (s:Sorem) - [:LOREMED] -> (l:Lorem {
  name: lorem.name
})

RETURN l

[ri8ika]

Both UNWIND can have different properties. The post has just example data. That's why I need to use separately.

[andrew_bowman]

Then can you add a more realistic example?

In the query you posted, there is no need to UNWIND both (this creates a cross product) since you're doing the exact same thing for both variables, so it makes sense to keep them as one variable and treat them all the same. I can't provide good advice if the sample query isn't a good representation of the actual query.

Also, please read the linked knowledge base article on cardinality I provided earlier, it may help you understand how Cypher works here.

[ri8ika]

Just assume second UNWIND can have other property. Eg.

UNWIND [{name:'foo'},{name:'bar'}] as lorem
UNWIND [{other:'soo'},{other:'sar'}] as sorem

I have read the link. Thanks for the link.

[andrew_bowman]

I think you still need to provide a better query. I would address that change by making a small tweak to how we set the property, to use name or other depending on what's available:

UNWIND [{name:'foo'},{name:'bar'}] + [{other:'soo'},{other:'sar'}] as lorem

MERGE (s:Sorem) - [:LOREMED] -> (l:Lorem {
  name: coalesce(lorem.name, lorem.other)
})

RETURN l

I can keep solving these simple queries for you, but it won't make much difference if these don't resemble your actual queries.

[ri8ika]

Ah mysterious going on...

Can I use CASE?

UNWIND $var1 + $var2 as lorem
MERGE (s:Sorem) - [:LOREMED] -> (l:Lorem {
  name: lorem.name,
  mm: $var1 or $var2 which one? if $var1 'ok', if $var2 'good'
})

Hope you understand my query.

[andrew_bowman]

Not quite sure I understand. The query I provided earlier uses coalesce(lorem.name, lorem.other), which means it will use the first term that is not null, so in the cases where the lorem has an other property but not a name property, it will use the other property as it's the first non-null value.

If that isn't what you need, you may need to explain what you need a bit more. CASE can be useful in these situations, but not currently with how you're using UNWIND. I'd need more details about what you're trying to do.

[ri8ika]

I have edited my query. I hope, it's clear now? If $var1 is going to be used in MERGE mm: 'ok' if $var2 is going to be used then mm: 'good'.

[andrew_bowman · Accepted solution]

Maybe? If we assume the same inputs of [{name:'foo'},{name:'bar'}] as $var1 and [{other:'soo'},{other:'sar'}] as $var2, then we can use coalesce() again to set the name accordingly, and use the fact that name properties only exist in $var1 for use in our CASE:

UNWIND $var1 + $var2 as lorem
MERGE (s:Sorem) - [:LOREMED] -> (l:Lorem {
  name: coalesce(lorem.name, lorem.other),
  mm: CASE WHEN exists(lorem.name) THEN 'ok' ELSE 'good' END
})

Though it may be more useful to instead use a list membership check for lorem in $var1, that would cope better if properties can be all the same between $var1 and $var2.

UNWIND $var1 + $var2 as lorem
MERGE (s:Sorem) - [:LOREMED] -> (l:Lorem {
  name: coalesce(lorem.name, lorem.other),
  mm: CASE WHEN lorem IN $var1 THEN 'ok' ELSE 'good' END
})

[ri8ika]

What about with same property in both UNWIND?

[andrew_bowman]

Just improved the query a bit, I'm now using list membership of lorem in $var1 for the condition. Does that work better for you?

[ri8ika]

Yeah, will try that. But also provide previous answer. That you just corrected. Because, I may get help with it in future as well.

Thanks a lot.

[andrew_bowman]

Query edited, glad to help!

[ri8ika]

Is case statement fine? I'm getting error. Please look at this again: (both UNWIND is now with same property)

UNWIND $var1 + $var2 as lorem
MERGE (s:Sorem) - [:LOREMED] -> (l:Lorem {
  name: lorem.name,
  mm: CASE lorem IN $var1 THEN 'ok' ELSE 'good' END
})

So, as per my first post, it should result:

{
  name: 'foo',
  mm: 'ok'
}
{
  name: 'bar',
  mm: 'ok'
}
{
  name: 'soo',
  mm: 'good'
}
{
  name: 'sar',
  mm: 'good'
}

[andrew_bowman]

My mistake there, you need WHEN before the condition, so CASE WHEN lorem IN $var1 ...

[ri8ika]

Ah, I also didn't notice. But still getting error. Variable var1 is not defined.

I think we need to think differently? Because $var1 will hold the array of data and using case statement will look for var1 inside the name of the property. Maybe I'm wrong?

[andrew_bowman]

If you're getting variable var1 is not defined, then you likely missed the $ in $var1 needed to define it as a parameter, not a variable.

As for lorem IN $var1, this is using an IN operator to check for membership of lorem in the $var1 parameter, which should be possible as this should be a list, and you combined the lists in your first line and UNWIND them to lorem.

[ri8ika]

No. I have not missed the dollar sign.

[andrew_bowman]

Then can you provide the entire query you're running again? And the version of Neo4j you are using? Also you are certain you're passing a $var1 list parameter?

[ri8ika]

Ah, it was my mistake. I found missing dollar sign somewhere else.

Finally worked just fine.

[ri8ika]

BTW, can we not just return variables? (Just trying to learn more...)

...
RETURN DISTINCT $var1, $var2

Goodnight.

[andrew_bowman]

That returns your input lists of strings. It doesn't return nodes, and it doesn't really give you any new information that you didn't have before you ran the query.

[ri8ika]

Yeah, I know that. But shouldn't it return as expected? I couldn't get the correct result. Anyways, leave it. Enough with this topic.

[ri8ika]

Hi, I forgot about my expected result and still not getting as desired result. I wanted to group them something like below which doesn't work:

UNWIND $var1 + $var2 as lorem
MERGE (s:Sorem) - [:LOREMED] -> (l:Lorem {
  name: lorem.name,
  mm: CASE lorem IN $var1 THEN 'ok' ELSE 'good' END
})
RETURN l.mm = 'ok' as okay, l.mm='good' as good

I mean to return which have 'ok', return them in array, and which have 'good' return them in separate array. Hope, this is clear.

Further, if it isn't still clear. I meant to return $var1, $var2.
RETURN $var1, $var2