Cypher - Aggregation with optional match

Question

I have the following node structure in database:

Artifact {id:'art1'}
|ArtifactProperty {key:'prop1_1', value:1}
|ArtifactProperty {key:'prop1_2', value:2}
=Artifact {id:'art1_1'}
=|ArtifactProperty {key:'prop1_1_1', value:1}
=|ArtifactProperty {key:'prop1_1_2', value:2}
==Artifact {id:'art1_1_1'}
==|ArtifactProperty {key:'prop1_1_1_1', value:1}
==|ArtifactProperty {key:'prop1_1_1_2', value:2}
==Artifact {id:'art1_1_2'}
==|ArtifactProperty {key:'prop1_1_2_1', value:1}
==|ArtifactProperty {key:'prop1_1_2_2', value:2}
==Artifact {id:'art1_1_3'}
==|ArtifactProperty {key:'prop1_1_3_1', value:1}
==|ArtifactProperty {key:'prop1_1_3_2', value:2}

Where an Artifact is a node that can be related to other Artifact nodes via a CURRENT relation and/or ArtifactProperties also via a CURRENT relation. The CURRENT relation holds the id of the root

My goal is to fetch all artifacts whith its children and properties.

The following request:

MATCH (a:Artifact)-[:CURRENT {root: 'art1'}]->(c:ArtifactProperty)
OPTIONAL MATCH (a:Artifact)-[:CURRENT {root: 'art1'}]->(b:Artifact) 
WITH a {.id, children: collect(b {.id}), properties: collect(c {.key, .value})} as mapped
return mapped.id, mapped.children, mapped.properties

Provides the following result:

╒═══════════╤══════════════════════════════════════════════════════════════════════╤══════════════════════════════════════════════════════════════════════╕
│"mapped.id"│"mapped.children"                                                     │"mapped.properties"                                                   │
╞═══════════╪══════════════════════════════════════════════════════════════════════╪══════════════════════════════════════════════════════════════════════╡
│"art1_1"   │[{"id":"art1_1_3"},{"id":"art1_1_2"},{"id":"art1_1_1"},{"id":"art1_1_3│[{"value":1.0,"key":"prop_1_1_1"},{"value":1.0,"key":"prop_1_1_1"},{"v│
│           │"},{"id":"art1_1_2"},{"id":"art1_1_1"}]                               │alue":1.0,"key":"prop_1_1_1"},{"value":2.0,"key":"prop_1_1_2"},{"value│
│           │                                                                      │":2.0,"key":"prop_1_1_2"},{"value":2.0,"key":"prop_1_1_2"}]           │
├───────────┼──────────────────────────────────────────────────────────────────────┼──────────────────────────────────────────────────────────────────────┤
│"art1_1_1" │[]                                                                    │[{"value":1.0,"key":"prop_1_1_1_1"},{"value":2.0,"key":"prop_1_1_1_2"}│
│           │                                                                      │]                                                                     │
├───────────┼──────────────────────────────────────────────────────────────────────┼──────────────────────────────────────────────────────────────────────┤
│"art1"     │[{"id":"art1_1"},{"id":"art1_1"}]                                     │[{"value":1.0,"key":"prop_1_1"},{"value":2.0,"key":"prop_1_2"}]       │
├───────────┼──────────────────────────────────────────────────────────────────────┼──────────────────────────────────────────────────────────────────────┤
│"art1_1_2" │[]                                                                    │[{"value":1.0,"key":"prop_1_1_2_1"},{"value":2.0,"key":"prop_1_1_2_2"}│
│           │                                                                      │]                                                                     │
├───────────┼──────────────────────────────────────────────────────────────────────┼──────────────────────────────────────────────────────────────────────┤
│"art1_1_3" │[]                                                                    │[{"value":1.0,"key":"prop_1_1_2_1"},{"value":2.0,"key":"prop_1_1_2_2"}│
│           │                                                                      │]

The issue is my results are repeated for Artefacts with children (properties and children fields contain twice the same result).

There is probably a better way to perform this aggregation than to do an OPTIONAL MATCH so let me know if I did not pick the best option.

EDIT: This is the result I am trying to get, note that each child and property appear only once:

╒═══════════╤══════════════════════════════════════════════════════════════════════╤══════════════════════════════════════════════════════════════════════╕
│"mapped.id"│"mapped.children"                                                     │"mapped.properties"                                                   │
╞═══════════╪══════════════════════════════════════════════════════════════════════╪══════════════════════════════════════════════════════════════════════╡
│"art1_1"   │[{"id":"art1_1_3"},{"id":"art1_1_2"},{"id":"art1_1_1"}]               │[{"value":1.0,"key":"prop_1_1_1"},{"value":2.0,"key":"prop_1_1_2"}    │
│           │                                                                      │                                                                      │
│           │                                                                      │                                                                      │
├───────────┼──────────────────────────────────────────────────────────────────────┼──────────────────────────────────────────────────────────────────────┤
│"art1_1_1" │[]                                                                    │[{"value":1.0,"key":"prop_1_1_1_1"},{"value":2.0,"key":"prop_1_1_1_2"}│
│           │                                                                      │]                                                                     │
├───────────┼──────────────────────────────────────────────────────────────────────┼──────────────────────────────────────────────────────────────────────┤
│"art1"     │[{"id":"art1_1"}]                                                     │[{"value":1.0,"key":"prop_1_1"},{"value":2.0,"key":"prop_1_2"}]       │
├───────────┼──────────────────────────────────────────────────────────────────────┼──────────────────────────────────────────────────────────────────────┤
│"art1_1_2" │[]                                                                    │[{"value":1.0,"key":"prop_1_1_2_1"},{"value":2.0,"key":"prop_1_1_2_2"}│
│           │                                                                      │]                                                                     │
├───────────┼──────────────────────────────────────────────────────────────────────┼──────────────────────────────────────────────────────────────────────┤
│"art1_1_3" │[]                                                                    │[{"value":1.0,"key":"prop_1_1_2_1"},{"value":2.0,"key":"prop_1_1_2_2"}│
│           │                                                                      │]

what is the exact result you are looking for ? These types of questions are most probably best solved with a query with a variable pattern length. — Graphileon
It is not exactly clear what would you like to achieve. What does this mean? The issue is my results are repeated for Artefacts with children (properties and children fields contain twice the same result) — Tomaž Bratanič
Hello, I would like to fetch all Artifacts as {id: <id>, children: [<child artifact id1>, child artifact id2>, ...], properties: [<artifact property 1>, <artifact property 2>]} I have added the result I'm trying to get in question — Zillon

stdob-- stdob-- · Accepted Answer · 2020-07-02T14:28:36

Try to use DISTINCT:

MATCH (a:Artifact)-[:CURRENT {root: 'art1'}]->(c:ArtifactProperty)
OPTIONAL MATCH (a:Artifact)-[:CURRENT {root: 'art1'}]->(b:Artifact) 
WITH a {
  .id, 
  children: collect(DISTINCT b {.id}), 
  properties: collect(DISTINCT c {.key, .value})
} AS mapped
RETURN mapped.id, mapped.children, mapped.properties

Cypher - Aggregation with optional match

1 Answers