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"}│
│           │                                                                      │]

Answer 1

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