0

I am posting a new question because this is a separate but related issue to this question: Neo4j: Link Different Nodes to the one node

The below query is returning the correct number of nodes. 

MATCH (act)--(obj) 
WHERE obj.filename IN ["6013", "6005"]
WITH act.timestamp as timestamp, collect(DISTINCT act) as acts
CREATE (a:Abs{name: 'SigEvent'}) 
FOREACH (act in acts | CREATE (act)-[:LINK]->(a))

The following query is ignoring the COLLECT and creating an a node for every entry in the collection instead of creating an a node for every pair. Also, when I move the CREATE outside of the FOREACH I get the following error: Type mismatch: expected Any, Map, Node or Relationship but was List<Map>.

 MATCH (act)--(obj) 
    WHERE obj.filename IN ["6013", "6005"]
    WITH act.timestamp as timestamp, collect({act:act, obj:obj}) as pairs
    CREATE (a:act{ Event: pairs.obj.filename, Description:pairs.act.message, timestamp:timestamp}) 
         FOREACH (pair in pairs | CREATE (pair)-[:LINK]->(a))

Example Data set:

Set 1: (act: "Event Log", timestamp: 3461000) (act: "Uptime Report", timestamp: 3461000)

Set 2: (act: "Event Log", timestamp: 149100) (act: "Uptime Report", timestamp: 149100)

Desired result:

(a {Source: "Event Log" & "UPtime Report", timestamp: 3461000})<-[:LINk]-(act)

(a {Source: "Event Log" & "UPtime Report", timestamp: 149100)<-[:LINk]-(act)

N6DYN
  • 325
  • 1
  • 3
  • 17

1 Answers1

1

When you use COLLECT (or any aggrogate) all the other non-aggrogates in the WITH/RETURN will be used as the GROUP BY keys. So by adding act and obj, COLLECT is collecting all act with the same timestamp, act, and obj (aka, 1 act per act). If you need to aggregate multiple columns, you can aggregate multiple columns/properties at the same time

MATCH (act)--(obj) 
WHERE obj.filename IN ["6013", "6005"]
WITH act.timestamp as timestamp, COLLECT(DISTINCT act.message) as messages, COLLECT(DISTINCT obt.filename) as filenames, COLLECT(act) as acts
CREATE (a{ Event: filenames, Description:messages, timestamp:timestamp})
FOREACH (act in acts | CREATE (act)-[:LINK]->(a))
Tezra
  • 8,463
  • 3
  • 31
  • 68
  • tried the above but the query was still creating a one-to-one matching. I updated the original question and code based on answers from my previous question and posted my error. – N6DYN Oct 16 '18 at 20:49
  • 1
    @N6DYN `COLLECT({:,:...})` creates a key-value map, and then collects those maps into a list. So you need to use for each to get each map, or use `pairs[]` to access a specific map from the list. Also, try replacing the WITH and everything after it with `RETURN COUNT(act.timestamp), COUNT(DISTINCT act.timestamp)` What 2 numbers does it return? If every timestamp is distinct, than you will naturally get one per. – Tezra Oct 16 '18 at 21:01
  • `@Terza` I had to use the following: `RETURN COUNT(timestamp), COUNT(DISTINCT timestamp)` which correctly returned 26. I tried your key-value suggestion but still getting errors. Can you provide an example? – N6DYN Oct 16 '18 at 21:11
  • @N6DYN both counts return 26? That would mean all the timestamps are unique. – Tezra Oct 16 '18 at 21:13
  • @N6DYN The cypher in my answer should only create 1 a per timestamp. If you could append `RETURN *` to that cypher, and share the results, that would help me see what when wrong, if anything. – Tezra Oct 16 '18 at 21:14
  • Yes, both counts returned 26. However, there are 3/4 acts and objs that occured at particular timestamps. – N6DYN Oct 16 '18 at 21:15
  • `@Tezra` I updated the code because `CREATE (a:Abs{name: 'SigEvent'})` is `act{ Event: pair.obj.filename, Description:pair.act.message, timestamp:timestamp})` in my origitnal code. – N6DYN Oct 16 '18 at 21:18
  • Tried this: `FOREACH(i IN RANGE(0, LENGTH(pairs))| CREATE (a:Abstraction_test{ Event:'Program Installation', Trigger: pairs[0], Description:pairs[1], timestamp:timestamp}))` and got the following error: `Property values can only be of primitive types or arrays thereof`. – N6DYN Oct 16 '18 at 21:21
  • @N6DYN Can you add to the question an example of your data before and what you want it to look like after? With the event you are creating containing act.timestamp, act.message, and obj.filename; do you want the message and filename properties to be arrays of the different values? That conflict needs to be resolved before you can collapse the rows. – Tezra Oct 16 '18 at 21:24
  • I would like the 26 nodes that are created to be assigned the act.massage and obj.filename that occurred at the same timestamp as properties. – N6DYN Oct 16 '18 at 21:31
  • `@Tezra` added example data and desired result model. – N6DYN Oct 16 '18 at 21:38
  • 1
    @N6DYN I modified the answer to reflect what I think you want; however, the data you want to return/merge has changed with every update to this question. – Tezra Oct 16 '18 at 23:45
  • message, filenames and timestamp are all returning 26 expect for acts. acts is not grouping the nodes as before. I noticed that `WITH act.timestamp as timestamp, collect(DISTINCT act) as act` returns 26 but when other variables are added it ignores the collection. – N6DYN Oct 17 '18 at 00:14
  • `@Tezra` it worked. I had a couple other things I needed to clean up in the rest of the query. Thanks. – N6DYN Oct 17 '18 at 01:42