如何在mongodb中对两个集合进行内连接

ilmyapht 于 2023-02-11 发布在 Go

关注(0)|答案(1)|浏览(239)

// 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
          }
        ]
      },
    ]
  },
]

当订单集合的etc字段和desc字段数组为空的文档的seqId字段的值与货物集合的"www.example.com"字段的值相同时，我希望得到以下输出。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"},
]

另外，我想根据货物集合的标题执行求和操作。

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

mongodb

来源：https://stackoverflow.com/questions/75402207/how-can-i-do-an-inner-join-of-two-collections-in-mongodb

1条答案

按热度按时间

y3bcpkx11#

只需在orders.seqId和goods.items.details.id之间执行$lookup。使用$unwind消除空查找（即内部连接行为）。最后，使用$sum执行$group以获得计数。

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

赞(0）回复(0）举报 2023-02-11

我来回答

如何在mongodb中对两个集合进行内连接

1条答案

相关问题

热门标签

最新问答