mongodb - 子文档中的 Mongoose 聚合管道连接字段

Question

我有以下 3 个集合:

1. runs:
{
  "_id":ObjectId("5cda6191e1b7c889e0442dff"),
  "status":1,
  "runName":"My Run Test 01"
}

2. runsets:
{
  "_id":ObjectId("5cda6191e1b7c889e0442e01"),
  "_run":ObjectId("5cda6191e1b7c889e0442dff"),
  "config": "Config 1",
  "suites":[{
    "_id":"ObjectId(5cda6191e1b7c889e0442e03"),
    "suiteid":ObjectId("5c748822e0ae897b0c312719"),
    "suitename":"My Test Suite 1",
    "tests":[{
      "_id":ObjectId("5cda6191e1b7c889e0442e04"),
      "testid":ObjectId("5c746708cefe8d75349b6486"),
      "testname":"Buy items with validations 002"
    }]
  }]
}

3. testresults:
{
  "_id":ObjectId("5cda65f6e494c61d30cee49c"),
  "_runset":ObjectId("5cda6191e1b7c889e0442e01"),
  "_test":"ObjectId(5c746708cefe8d75349b6486)",
  status: 1
}

我想将 3 个集合聚合成一个文档，如下所示:

[
  {
    "_id": "5cda6191e1b7c889e0442dff",
    "status": 1,
    "runName": "My Run Test 01",
    "runsetitems": [
      {
        "_id": "5cda6191e1b7c889e0442e01",
        "_run": "5cda6191e1b7c889e0442dff",
        "_config": "Config 1",
        "suites": [
          {
            "_id": "5cda6191e1b7c889e0442e03",
            "suiteid": "5c748822e0ae897b0c312719",
            "suitename": "My Test Suite 1",
            "suitedesc": "My Test Suite 1 Description",
            "tests": [
              {
                "_id": "5cda6191e1b7c889e0442e04",
                "testid": "5c746708cefe8d75349b6486",
                "testname": "Buy items with validations 002",
                "testdesc": "Buy more than one items and validate price",
                "testitem": {
                  "status": 1
                }
              }
            ]
          }
        ]
      }
    ]
  }
]

const run = await RunModel.aggregate([
    {$match: {'_user': mongoose.Types.ObjectId('5c6c997de0a0b446e87c3389')}},
    {
      $lookup:
      {
        from: 'runsets',
        let: { runid: '$_id' },
        pipeline: [
          {
            $match:
            {
              $expr:
              {
                $eq: ['$_run', '$$runid']
              }
            }
          }, {
            $lookup:
            {
              from: 'testresults',
              let: { runsetid: '$_id', testid: '$suites.tests.testid' },
              pipeline: [
                {
                  $match:
                  {
                    $expr:
                    {
                      $and:
                        [
                          { $eq: [ '$_test',  '$$testid' ] },
                          { $eq: [ '$_runset',  '$$runsetid' ] }
                        ]
                    }
                  }
                },
                {$project : { 'status': 1 }}
              ],
              as: 'suites.tests.testitem'
            }
          }         
        ],
        as: 'runsetitems'
      }
    },
    {$project : { '_id': 1 , 'runName': 1, 'runDesc': 1, 'status': 1, 'submitTime': 1, 'endTime': 1, 'runsetitems': 1, 'projectitem.projectName': 1 } }
])

但是有两个问题:

首先，加入_test和suites.tests.testid是不行的。它在包含时返回 testitem:[]。

如果我删除上面提到的连接，testitem 返回但它会产生另一个问题。它会删除测试的属性“testname”和“testdesc”，如下所示:

"tests": {
  "testitem": [
    {
       "_id": "5cda67d4e494c61d30cee5d6",
       "status": 1
    }
  ]
}

任何建议将不胜感激!

Answer 1

您有 testid 嵌套在数组的数组中。你需要先$unwind suites 和 suites.tests 然后是 $group管理相同的订单。

db.runs.aggregate([
  { "$lookup": {
    "from": "runsets",
    "let": { "runid": "$_id" },
    "pipeline": [
      { "$match": { "$expr": { "$eq": ["$_run", "$$runid"] }}},
      { "$unwind": "$suites" },
      { "$unwind": "$suites.tests" },
      { "$lookup": {
        "from": "testresults",
        "let": { "runsetid": "$_id", "testid": "$suites.tests.testid" },
        "pipeline": [
          { "$match": {
            "$expr": {
              "$and": [
                { "$in": ["$_test", "$$testid"] },
                { "$eq": ["$_runset", "$$runsetid"] }
              ]
            }
          }},
          { "$project": { "status": 1 }}
        ],
        "as": "suites.tests.testitem"
      }},
      { "$unwind": "$suites.tests.testitem" },
      { "$group": {
        "_id": { "_id": "$_id", "suiteid": "$suites._id" },
        "_run": { "$first": "$_run" },
        "suiteid": { "$first": "$suites.suiteid" },
        "suitename": { "$first": "$suites.suitename" },
        "config": { "$first": "$config" },
        "config": { "$first": "$config" },
        "tests": { "$push": "$suites.tests" }
      }},
      { "$group": {
        "_id": "$_id._id",
        "_run": { "$first": "$_run" },
        "config": { "$first": "$config" },
        "suites": {
          "$push": {
            "_id": "$_id.suiteid",
            "tests": "$tests",
            "suiteid": "$suiteid",
            "suitename": "$suitename"
          }
        }
      }}
    ],
    "as": "runsetitems"
  }},
  { "$project": {
    "_id": 1,
    "runName": 1,
    "runDesc": 1,
    "status": 1,
    "submitTime": 1,
    "endTime": 1,
    "runsetitems": 1,
    "projectitem.projectName": 1
  }}
])

MongoPlayground