Aggregate found documents with related documents

Hello,

Consider we have the following Shipment document:

{
  "client": "John Doe",
  "id": "2345656",
  "category": "Urgent",
  "address": "Some Street",
  "buildingNumber": "23",
}

Let’s say this document is part of a collection that contains thousands of different clients, addresses and a couple of different categories.

Suppose we wanted to see all the Urgent shipments and we ran the find() query obtaining the desired result.

Here’s the question: When running this main query to retrieve all the Urgent shipments available, what would be the best and the most optimal way to in addition also obtain related results such as:

  • Other shipments to John Doe that are of different category than Urgent, let’s say, Normal
  • Other shipments directed to different clients but at the exactly same address: Some Street, 23
  • Other shipments directed to different clients and different buildings but on the same street: Some Street

An example of the desired outcome:

Example of the first found URGENT element presented

[ ] Shipment[#345656] to John Doe, Some Street, 23.

Shipments of different category to the same client have been found:
[ ] Shipment[#845543] to John Doe, Some Street, 23, Category: NORMAL
[ ] Shipment[#345543] to John Doe, Some Street, 23, Category: NORMAL

Other shipments to the same building:
[ ] Shipment[#141123] to Mary White, Some Street, 23, Category: NORMAL

Other shipments to the same street:
[ ] Shipment[#741456] to Simon Brown, Some Street, 74, Category: URGENT

After that the query listing would continue with another URGENT document found:

[ ] Shipment[#342346] to Brian Smith, Some Other Street, 90.

Shipments of different category to the same client have been found:
[ ] Shipment[#242345] to Brian Smith, Some Other Street, 90, Category: NORMAL

No other shipments to the same building found.

Other shipments to the same street:
[ ] Shipment[#141456] to Susy White, Some Other Street, 103, Category: NORMAL

And etc…

What would be the best way to achieve this?

Thank you very much for your attention and time, and help,

Ren

Publish a couple of sample documents that matches all the possibilities you want. Without real sample documents it is hard to experiment and test.

What have you tried so far? How did it fails? Knowing this will prevent us investigating in a direction that you already know does not work.

At first glance, it will probably involve $lookup within $facet after a $match for category:Urgent.

It is not clear what you want to do when multiple category:Urgent are for the same client.