mongodb, lookup by hash key

Question

I have a collection like this:

• Category:

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

• GroupPeople:

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

The wishing output I really want:

{_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}}

I try in many ways, but there is no luck. Do you have experience with this case?

Answer 1

You can use the below aggregation with MongoDB 3.6 and above

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"
    }
  }}
])

And with 3.4.4 and above

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