cancel
Showing results for 
Search instead for 
Did you mean: 

Head's Up! These forums are read-only. All users and content have migrated. Please join us at community.neo4j.com.

return empty array from collect() instead of nothing, if nothing found

graphene
Node Clone

I have this query, which works to show me the appIds of all the nodes connected to a given node, organized by two "connectionTypes"
But I would like it to return an empty array for each connectionType for which there are no nodes, instead of returning nothing, so if I got an empty return value, I would know it's because the root user was not found, and not just that it was found but has no other connected to it's node.

  MATCH (n:User {appId: $appId})
  MATCH p=(n)-[*]-(m)
  WHERE single(i in nodes(p) WHERE id(i) = id(n))
  WITH m, relationships(p) AS rels
  WITH m,
        any(r IN rels WHERE type(r) = 'HAS_IP_ADDRESS') AS HAS_IP_ADDRESS
  WITH m, CASE
    WHEN HAS_IP_ADDRESS THEN "ipConnections"
    WHEN NOT HAS_IP_ADDRESS THEN "nonIpConnections"
    END AS connectionType
  RETURN
    collect(DISTINCT m.appId) as appIds,
    connectionType

 

7 REPLIES 7

You can change the match on line 2 to 'optional match'.  that should work to 1) return nothing if the user is not found and 2) return an empty list if no connected nodes are found for the user. 

Question: what is the purpose of the predicate on line 3? Is this to avoid loops in the path where the path would pass through the user nodes more than once? 

Yep! the purpose of the predicate on line 3 is to avoid loops in the path where the path would pass through the user nodes more than once. (got the idea here on the forum!)

When I try "OPTIONAL MATCH" it gets half way there. For instance, with a User node with zero relationships, it will return an empty array for "appIds" and null for "connectionTypes",

[
	{
		"appIds": [],
		"connectionType": null
	}
]


but I am hoping to get an empty array for "appIds" for both "ipConnections" and "nonIpConnections"

[
	{
		"appIds": [],
		"connectionType": "ipConnections"
	},
	{
		"appIds": [],
		"connectionType": "nonIpConnections"
	},
]

And if there only exist connections for one type, to get an empty array for the other type:

[
	{
		"appIds": ["id-2123", "id-2345"],
		"connectionType": "ipConnections"
	},
	{
		"appIds": [],
		"connectionType": "nonIpConnections"
	},
]

Is this possible? 

Try this.  It is a slightly different format, but it I believe it gives the info you want.

MATCH (n:User {appId: 100})
OPTIONAL MATCH p=(n)-[*]-(m)
WHERE single(i in nodes(p) WHERE id(i) = id(n))
with collect({p:p, m:m}) as nodes
call{
    with nodes
    unwind [i in nodes where any(r IN relationships(i.p) WHERE type(r) = 'HAS_IP_ADDRESS')|i.m] as x
    return collect(distinct x) as ipConnections
}
call{
    with nodes
    unwind [i in nodes where none(r IN relationships(i.p) WHERE type(r) = 'HAS_IP_ADDRESS')|i.m] as x
    return collect(distinct x) as nonIpConnectionsNodes
}return ipConnections, nonIpConnectionsNodes

ok, I figured out how to get your format:

MATCH (n:User {appId: 100})
OPTIONAL MATCH p=(n)-[*]-(m)
WHERE single(i in nodes(p) WHERE id(i) = id(n))
with collect({p:p, m:m}) as nodes
call{
    with nodes
    unwind [i in nodes where any(r IN relationships(i.p) WHERE type(r) = 'HAS_IP_ADDRESS')|i.m] as x
    return collect(distinct x) as ipConnections
}
call{
    with nodes
    unwind [i in nodes where none(r IN relationships(i.p) WHERE type(r) = 'HAS_IP_ADDRESS')|i.m] as x
    return collect(distinct x) as nonIpConnectionsNodes
} 
return [{connectionType: "ipConnections", appIds: ipConnections},{connectionType: "nonIpConnections", appIds: nonIpConnectionsNodes}] as result

Oops, I forgot to strip out just the appId. 

MATCH (n:User {appId: 100})
OPTIONAL MATCH p=(n)-[*]-(m)
WHERE single(i in nodes(p) WHERE id(i) = id(n))
with collect({p:p, m:m}) as nodes
call{
    with nodes
    unwind [i in nodes where any(r IN relationships(i.p) WHERE type(r) = 'HAS_IP_ADDRESS')|i.m.appId] as x
    return collect(distinct x) as ipConnections
}
call{
    with nodes
    unwind [i in nodes where none(r IN relationships(i.p) WHERE type(r) = 'HAS_IP_ADDRESS')|i.m.appId] as x
    return collect(distinct x) as nonIpConnectionsNodes
} 
return [{connectionType: "ipConnections", appIds: ipConnections},{connectionType: "nonIpConnections", appIds: nonIpConnectionsNodes}] as result

Thank you for your help! That formats it perfectly, except for some reason now the 'optional match' doesn't result in nothing being returned if the root user is not found. 

I fixed that this way, but not sure if it's best practice:

MATCH (n:User {appId: "100"})
OPTIONAL MATCH p=(u)-[*]-(m)
WHERE single(i IN nodes(p) WHERE id(i) = id(u))
WITH u, collect({p:p, m:m}) AS nodes
call{
    WITH nodes
    UNWIND [i IN nodes WHERE any(r IN relationships(i.p) WHERE type(r) = 'HAS_IP_ADDRESS') | i.m.appId] AS x
    RETURN collect(distinct x) AS ipConnections
}
call{
    WITH nodes
    UNWIND [i IN nodes WHERE none(r IN relationships(i.p) WHERE type(r) = 'HAS_IP_ADDRESS') | i.m.appId] AS x
    RETURN collect(distinct x) AS nonIpConnectionsNodes
}
WITH u, CASE
  WHEN u IS NULL THEN u
  WHEN u IS NOT NULL THEN [{connectionType: "ipConnections", appIds: ipConnections},{connectionType: "nonIpConnections", appIds: nonIpConnectionsNodes}]
  END AS result
RETURN result

 

 

unfortunately, this query now takes 4 seconds to run (on a medium-sized sample)! Is that expected based on its complexity? Which part of it do you think is taking so long? When I use 'PROFILE' I get "1894715 total db hits in 4100 ms"
The original version, (with 'OPTIONAL' added) only takes 43 ms using the same sample data!
 "12511 total db hits in 43 ms"