CODEWITHSUNDEEP
arangodb
stragz
stragz

Reputation: 13

ArangoDB for each distinct push other element to an array

So I have a collection in ArangoDB where subnets are assigned to teams and I would like to re-format that data to have a single line per team with an array of their assigned subnets

The collection (Assigment) looks like this:

Query

FOR a IN Assigment
    RETURN a

Result

[
  {
    "assignment_type": "subnet",
    "assigment_crteria": "192.168.1.0/24",
    "team_assignment": "Team1",
    "prime_contact": "John",
  },
  {
    "assignment_type": "subnet",
    "assigment_crteria": "192.168.2.0/24",
    "team_assignment": "Team1",
    "prime_contact": "John",
  },
  {
    "assignment_type": "subnet",
    "assigment_crteria": "192.168.3.0/24",
    "team_assignment": "Team1",
    "prime_contact": "John",
  },
  {
    "assignment_type": "subnet",
    "assigment_crteria": "192.168.4.0/24",
    "team_assignment": "Team2",
    "prime_contact": "Bob",
  },
  {
    "assignment_type": "subnet",
    "assigment_crteria": "192.168.5.0/24",
    "team_assignment": "Team2",
    "prime_contact": "Bob",
  },
  {
    "assignment_type": "subnet",
    "assigment_crteria": "192.168.8.0/24",
    "team_assignment": "Team5",
    "prime_contact": "Anna",
  },
  {
    "assignment_type": "subnet",
    "assigment_crteria": "192.168.9.0/24",
    "team_assignment": "Team2",
    "prime_contact": "Bob",
  },
  {
    "assignment_type": "subnet",
    "assigment_crteria": "192.168.10.0/24",
    "team_assignment": "Team2",
    "prime_contact": "Bob",
  },
  {
    "assignment_type": "subnet",
    "assigment_crteria": "192.168.12.0/24",
    "team_assignment": "Team5",
    "prime_contact": "Anna",
  },
  {
    "assignment_type": "subnet",
    "assigment_crteria": "192.168.13.0/24",
    "team_assignment": "Team1",
    "prime_contact": "John",
  },
  {
    "assignment_type": "subnet",
    "assigment_crteria": "192.168.15.0/24",
    "team_assignment": "Team1",
    "prime_contact": "John",
  },
  {
    "assignment_type": "subnet",
    "assigment_crteria": "192.168.17.0/24",
    "team_assignment": "Team7",
    "prime_contact": "Erick",
  }
]

And I would like to insert the re-formated documents in a different collection (Team_to_Subnet) with this format:

Result

[
  {
    "team": "Team1",
    "cidrs": ["192.168.1.0/24","192.168.2.0/24","192.168.3.0/24","192.168.13.0/24","192.168.15.0/24"],
    "prime_contact": "John"
  },
  {
    "team": "Team2",
    "cidrs": ["192.168.4.0/24","192.168.5.0/24","192.168.9.0/24","192.168.10.0/24"],
    "prime_contact": "Bob"
  },
  {
    "team": "Team5",
    "cidrs": ["192.168.8.0/24","192.168.12.0/24"],
    "prime_contact": "Anna"
  },
  {
    "team": "Team7",
    "cidrs": ["192.168.17.0/24"],
    "prime_contact": "Erick"
  },
]

How would I go about doing this? Is it even possible to do it? I've looked into using COLLECT but I just cant figure it out.

Any help would be appreciated

Upvotes: 0

Views: 168

Answers (1)

CodeManX
CodeManX

Reputation: 11915

You can group by team and contact with COLLECT, and use a projection expression to get an array of subsets that fall into the respective group:

FOR a IN Assigment
  COLLECT team = a.team_assignment, prime_contact = a.prime_contact
  INTO cidrs = a.assigment_crteria
  RETURN { team, prime_contact, cidrs }

To save the results, just replace the last line with:

  INSERT { team, prime_contact, cidrs } INTO Team_to_Subnet

Upvotes: 1

Related Questions