mongodb，通过哈希键查找

Question

我有一个这样的集合:

• 类别:

{_id: Object:Id(...), code: 'drink', name: 'Soft Drink and Beer'}
{_id: Object:Id(...), code: 'fast-food', name: 'Burger and Chicken Fry'}

• 团队成员:

{_id: Object:Id(G1), categories: {'drink' => 5, 'fast-food' => 3}}
{_id: Object:Id(G2), categories: {'drink' => 2}}

我真正想要的愿望输出:

{_id: Object:Id(G1), categories: {'Soft Drink and Beer' => 5, 'Burger and Chicken Fry' => 3}}
{_id: Object:Id(G2), categories: {'Soft Drink and Beer' => 2}}

我尝试了很多方法，但没有运气。您有这方面的经验吗？

Answer 1

您可以在 MongoDB 3.6 及更高版本中使用以下聚合

db.GroupPeople.aggregate([
  { "$addFields": { "categories": { "$objectToArray": "$categories" }}},
  { "$unwind": "$categories" },
  { "$lookup": {
    "from": "Category",
    "let": { "category": "$categories.k" },
    "pipeline": [
      { "$match": { "$expr": { "$eq": ["$$category", "$code"] }}}
    ],
    "as": "category"
  }},
  { "$unwind": "$category" },
  { "$group": {
    "_id": "$_id",
    "categories": {
      "$push": {
        "k": "$category.name",
        "v": "$categories.v"
      }
    }
  }},
  { "$project": {
    "categories": {
      "$arrayToObject": "$categories"
    }
  }}
])

3.4.4及以上

db.GroupPeople.aggregate([
  { "$addFields": { "categories": { "$objectToArray": "$categories" }}},
  { "$unwind": "$categories" },
  { "$lookup": {
    "from": "Category",
    "localField": "categories.k",
    "foreignField": "code",
    "as": "category"
  }},
  { "$unwind": "$category" },
  { "$group": {
    "_id": "$_id",
    "categories": {
      "$push": {
        "k": "$category.name",
        "v": "$categories.v"
      }
    }
  }},
  { "$project": {
    "categories": {
      "$arrayToObject": "$categories"
    }
  }}
])

MongoPlayground