ArangoDB group and sort

Question

In ArangoDB I want to group and sort notification data.

I have the following notification data sets

[
  {id: 1, groupId: 1, text: 'Aoo', time: 23},
  {id: 2, groupId: 2, text: 'Boo', time: 32},
  {id: 3, groupId: 1, text: 'Coo', time: 45},
  {id: 4, groupId: 3, text: 'Doo', time: 56},
  {id: 5, groupId: 1, text: 'Eoo', time: 22},
  {id: 6, groupId: 2, text: 'Foo', time: 23}
]

I want to group the notification by groupId and the recent notification group should appear on top. Final result should be like this

[
  { groupId: 3, notifications: [{id: 4, groupId: 3, text: 'Doo', time: 56}],
  { groupId: 1, notification: [{id: 3, groupId: 1, text: 'Coo', time: 45}, {id: 1, groupId: 1, text: 'Aoo', time: 23}, {id: 5, groupId: 1, text: 'Eoo', time: 22}]},
  { groupId: 2, notifications: [{id: 2, groupId: 2, text: 'Boo', time: 32}, {id: 6, groupId: 2, text: 'Foo', time: 23}] }
]

Tried following AQL

FOR doc IN notificaion
SORT doc.time DESC
COLLECT groupId = doc.groupId INTO g
RETURN { groupId, notifications: g[*].doc }

Above query sorts the inner group elements but the outer groups are not sorted.

I'm struggling to construct an AQL for it. Any pointer will be helpful.

Thanks

some kind of interaction would be expected in this community. Did my answer help? Was I wrong? Without confirmation from your side, this Q&A is mostly worthless for other visitors. — Tom Regner, Mar 17 '20 at 08:12

score 0 · Accepted Answer · answered Mar 11 '20 at 09:46

Sort twice: once the set of documents collected - as you already do, then the collection:

FOR doc IN notification
  SORT doc.time DESC
  COLLECT groupId = doc.groupId INTO g
  SORT g[*].doc.time DESC
  RETURN { groupId, notifications: g[*].doc }

In my tests this yields the desired sequence:

[
  {
    "groupId": 3,
    "notifications": [
      {
        "id": 4,
        "groupId": 3,
        "text": "Doo",
        "time": 56
      }
    ]
  },
  {
    "groupId": 1,
    "notifications": [
      {
        "id": 3,
        "groupId": 1,
        "text": "Coo",
        "time": 45
      },
      {
        "id": 1,
        "groupId": 1,
        "text": "Aoo",
        "time": 23
      },
      {
        "id": 5,
        "groupId": 1,
        "text": "Eoo",
        "time": 22
      }
    ]
  },
  {
    "groupId": 2,
    "notifications": [
      {
        "id": 2,
        "groupId": 2,
        "text": "Boo",
        "time": 32
      },
      {
        "id": 6,
        "groupId": 2,
        "text": "Foo",
        "time": 23
      }
    ]
  }
]

ArangoDB group and sort

1 Answers1