If one MATCH is empty the entire query returns empty

[cle...@kasual.biz]

Hi,

I'm trying to find nodes matching a text in their name.

For one node I do :

MATCH (p:Person) WHERE p.name =~ '(?i).test.*'
WITH p, {name: p.name} as person
return COLLECT(DISTINCT person) as persons

It works well.

But if I try for two nodes :

MATCH (p:Person) WHERE p.name =~ '(?i).test.*'
WITH p, {name: p.name} as person
MATCH (pla:Place) WHERE pla.name =~ '(?i).*test.*'
WITH person, {name: pla.name} as place
return COLLECT(DISTINCT person) as persons, COLLECT(DISTINCT place) as places

If the first MATCH returns nothing the the entire query result is empty even if the the second should return results.

I tried to add OPTIONAL, the result is not empty, the MATCH returns expected objects but I get an object with name null for the MATCH which should be empty :

[
   {
     "name": null
   }
]

How can I get the results for the MATCH which should has results and get an empty array for the one which should not have result ?

Thank you.

Regards,

Clément

[Michael Hunger]

WITH a UNION as those 2 queries are unrelated.

If you use your approach you also compute a cross product.

It would be better to aggregate first

MATCH (p:Person) WHERE p.name =~ '(?i).test.*'

WITH COLLECT(DISTINCT {name: p.name}) as persons
OPTIONAL MATCH (pla:Place) WHERE pla.name =~ '(?i).*test.*'
return persons, COLLECT(DISTINCT {name: pla.name}) as places

Empty matches only return any data rows when you return a single aggregation, like your collect, otherwise they will just result in no rows.

(You can just filter out the null value from the optional match)

In general aggregation functions skip null values.

The regular expression match is not very efficient, as it has to scan the full label.

--
You received this message because you are subscribed to the Google Groups "Neo4j" group.
To unsubscribe from this group and stop receiving emails from it, send an email to neo4j+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[cle...@kasual.biz]

OK thank you.

Actually, the null value is "added" when I use COLLECT. So I can't really filter the null values.

If I filter before the collect there is no null value yet. And if I filter on the collect result, there is no null item, because an object is create with the name property which is null.

It cannot return an empty array for one COLLECT and have results in the other... I don't understand why.

I do the following workaround using UNION ALL:

MATCH (p:Person) WHERE p.name =~ '(?i).*test.*'
return {persons: COLLECT(DISTINCT {name: p.name})} as results
UNION ALL MATCH (pla:Place) WHERE pla.name =~ '(?i).*test.*'
return {places: COLLECT(DISTINCT {name: pla.name})} as results

I get this:

╒═════════════════════════════════════════════════════════════╕
│"results"                                                    │
╞═════════════════════════════════════════════════════════════╡
│{"persons":[]}                                               │
├─────────────────────────────────────────────────────────────┤
│{"places":[{"name":"Test 1"},{"name":"Test 2"}]}             │
└─────────────────────────────────────────────────────────────┘

I am surprised there is no way to have two separated keys "persons" and "places" which contains an array of objects like this: 

╒═══════════════╤═════════════════════════════════════════════════╕
│"persons"      │"places"                                         │
╞═══════════════╪═════════════════════════════════════════════════╡
│[ ]            │[{"name":"Test 1"},{"name":"Test 2"}]            │
└───────────────┴─────────────────────────────────────────────────┘

It adds this object in "persons" if I use OPTIONAL:

{"name":null}

Without OPTIONAL, "places" is empty.

Any idea to achieve this ?

Thank you.

To unsubscribe from this group and stop receiving emails from it, send an email to neo4j+un...@googlegroups.com.

[Michael Hunger]

MATCH (p:Person) WHERE p.name =~ '(?i).test.*'
WITH COLLECT(DISTINCT {name: p.name}) as persons
OPTIONAL MATCH (pla:Place) WHERE pla.name =~ '(?i).*test.*'

RETURN persons, [x IN COLLECT(DISTINCT pla) | {name: x.name}] as places

To unsubscribe from this group and stop receiving emails from it, send an email to neo4j+unsubscribe@googlegroups.com.

[cle...@kasual.biz]

Ok thank you ! It works very well !