CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
newbieeyo
newbieeyo

Reputation: 695

How can I do an inner join of two collections in mongodb

// orders collection
[
  {
    "id": 1,
    "orderName": "a",
    "seqId": 100,
    "etc": [],
    "desc": [],
  },
  {
    "id": 2,
    "orderName": "b",
    "seqId": 200,
    "etc": [],
    "desc": []
  },
  {
    "id": 3,
    "orderName": "c",
    "seqId": 100,
    "etc": [],
    "desc": [],
  },
]

// goods collection
[
  {
    "id": 1,
    "title": "example1",
    "items": [
      {
        "id": 10,
        "details": [
          {
            "id": 100
          },
          {
            "id": 101,
          }
        ]
      },
      {
        "id": 20,
        "details": [
          {
            "id": 102,
          },
          {
            "id": 103,
          }
        ]
      },
    ]
  },
[
  {
    "id": 2,
    "title": "example2",
    "items": [
      {
        "id": 30,
        "details": [
          {
            "id": 200
          },
          {
            "id": 201
          }
        ]
      },
      {
        "id": 40,
        "details": [
          {
            "id": 202
          },
          {
            "id": 203
          }
        ]
      },
    ]
  },
]

When the value of the seqId field of the document whose etc field and desc field arrays of the orders collection are empty and the value of the "goods.details.id field of the goods collection are the same, I want to get the following output. How can I do that?

[
  {orderName: "a", title: "example1"},
  {orderName: "b", title: "example2"},
  {orderName: "c", title: "example1"},
]

Additionally, I would like to perform a sum operation based on the title of the goods collection.

[
  {"example1": 2}, 
  {"example2": 1}
]

Upvotes: 0

Views: 228

Answers (1)

ray
ray

Reputation: 15287

Simply perform a $lookup between orders.seqId and goods.items.details.id. Use $unwind to eliminate empty lookups(i.e. inner join behaviour). Finally, do a $group with $sum to get the count.

db.orders.aggregate([
  {
    "$match": {
      "etc": [],
      "desc": []
    }
  },
  {
    "$lookup": {
      "from": "goods",
      "localField": "seqId",
      "foreignField": "items.details.id",
      "pipeline": [
        {
          $project: {
            _id: 0,
            title: 1
          }
        }
      ],
      "as": "goodsLookup"
    }
  },
  {
    "$unwind": "$goodsLookup"
  },
  {
    $group: {
      _id: "$goodsLookup.title",
      cnt: {
        $sum: 1
      }
    }
  }
])

Mongo Playground

Upvotes: 1

Related Questions