如何使用MongoDB的分面操作查找字段的唯一值

Question

使用我提供链接中的查询语句，filteredAccording部分和categorizedBy部分都按预期工作，但在findDistinct部分遇到了问题。

在MongoDB中我有以下数据：

 {
        "_id": 10001,
        "university": "SPYU",
        "Courses": [
          "English",
          "French"
        ],
        "dept": [
          "Literature"
        ],
        "type": [
          "Autonomous"
        ],
        "status": "ACTIVE",
        "isMarked": true
      },
      {
        "_id": 10002,
        "university": "SPYU",
        "Courses": [
          "English",
          "French"
        ],
        "dept": [
          "Literature"
        ],
        "type": [
          "Autonomous"
        ],
        "status": "NON-ACTIVE",
        "isMarked": true
      }

我希望响应内容为：

 "university": [
  {
    "name": "Literature",
    "values": [
      {
        "_id": 10001,
        "university": "SPYU",
        "Courses": [
          "English",
          "French"
        ],
        "dept": [
          "Literature"
        ],
        "type": [
          "Autonomous"
        ],
        "status": "ACTIVE",
        "isMarked": true
      },
      {
        "_id": 10002,
        "university": "SPYU",
        "Courses": [
          "English",
          "French"
        ],
        "dept": [
          "Literature"
        ],
        "type": [
          "Autonomous"
        ],
        "status": "NON-ACTIVE",
        "isMarked": true
      }
    ]
  }
],
 "findDistinct": [
    {​​​​​​​​
      "name": "Courses",
      "values": [
        "English",
         "French"
      ]
    }​​​​​​​​,
    {​​​​​​​​
      "name": "Status",
      "values": [
        "ACTIVE",
        "NON-ACTIVE"
      ]
    }​​​​​​​​
  ]

我尝试使用这个链接，但响应并不像预期的那样返回。 https://mongoplayground.net/p/XECZvRMmt3T

目前，响应是这样的：

 "universities": [
  {
    "name": "Literature",
    "values": [
      {
        "_id": 10001,
        "university": "SPYU",
        "Courses": [
          "English",
          "French"
        ],
        "dept": [
          "Literature"
        ],
        "type": [
          "Autonomous"
        ],
        "status": "ACTIVE",
        "isMarked": true
      },
      {
        "_id": 10002,
        "university": "SPYU",
        "Courses": [
          "English",
          "French"
        ],
        "dept": [
          "Literature"
        ],
        "type": [
          "Autonomous"
        ],
        "status": "NON-ACTIVE",
        "isMarked": true
      }
    ]
  }
],
"findDistinct": [
    {​​​​​​​​
      "Courses": [
        "English",
         "French"
      ]
    }​​​​​​​​,
    {​​​​​​​​
      "status": [
        "ACTIVE",
        "NON-ACTIVE"
      ]
    }​​​​​​​​
  ]

任何帮助都将不胜感激！

- aparna

Answer 1

您的查询中的快速修复：

大学:

• $addFields，删除 $project 并仅添加一个操作 isMarked
• $unwind 拆解 dept 数组
• $group 按 dept 分组并获取根值数组

findDistinct:

• $group 按 null 分组并获取唯一的 courses 数组和 status
• $reduce 迭代嵌套数组 Courses 并使用 $setUnion 获取唯一数组
• 在 dest 字段中制作课程和 status 的数组
• $unwind 拆解 dest 数组
• $replaceRoot 将 dest 对象替换为根

db.collection.aggregate([
  { $match: { university: "SPYU" }
  },
  {
    $facet: {
      universities: [
        { $addFields: { isMarked: { $in: ["French", "$Courses"] } } },
        { $unwind: "$dept" },
        {
          $group: {
            _id: "$dept",
            values: { $push: "$$ROOT" }
          }
        }
      ],
      findDistinct: [
        {
          $group: {
            _id: null,
            Courses: { $addToSet: "$Courses" },
            Status: { $addToSet: "$status" }
          }
        },
        {
          $project: {
            _id: 0,
            dist: [
              {
                name: "Courses",
                values: {
                  $reduce: {
                    input: "$Courses",
                    initialValue: [],
                    in: { $setUnion: ["$$this", "$$value"] }
                  }
                }
              },
              {
                name: "Status",
                values: "$Status"
              }
            ]
          }
        },
        { $unwind: "$dist" },
        { $replaceRoot: { newRoot: "$dist" } }
      ]
    }
  }
])

{{链接1：游乐场}}

- turivishal