I have collection with documents, for example:

    [
        {
            'id':'1'
            'some_field':'test',
            'rates':[
                {'user_id':'12','rate':'very_good'},
                {'user_id':'13','rate':'very_good'}
                {'user_id':'14','rate':'bad'},
                {'user_id':'15','rate':'normal'}
    
            ]
        }
    ]

And i have collection with values of rates in string:

    [
        {
            "rate_name" : "bad",
            "rate_value" : 1
        },
        {
            "rate_name" : "normal",
            "rate_value" : 2
        },
        {
            "rate_name" : "very_good",
            "rate_value" : 3
        },
    ]

I need map data from first collection from array rates with value from second collection and group this values to new field.

For example:

    [
        {
            'id':'1'
            'some_field':'test',
            'rates':[3,3,1,2]
            ]
        }
    ]

How i can do this?

CodePudding user response：

Here's one way you could do it if "$getField" is available (MongoDB server version 5.0 and above). See below for another option.

db.ratings.aggregate([
  {
    "$lookup": {
      "from": "ratingScale",
      "as": "ratingScale",
      "pipeline": []
    }
  },
  {
    "$set": {
      "rates": {
        "$map": {
          "input": "$rates",
          "as": "rate",
          "in": {
            "$getField": {
              "field": "rate_value",
              "input": {
                "$first": {
                  "$filter": {
                    "input": "$ratingScale",
                    "as": "nameValue",
                    "cond": {"$eq": ["$$rate.rate", "$$nameValue.rate_name"]}
                  }
                }
              }
            }
          }
        }
      }
    }
  },
  {"$unset": "ratingScale"}
])

Try it on mongoplayground.net.

If "$getField" is unavailable, here's another way you could do it.

db.ratings.aggregate([
  {
    "$lookup": {
      "from": "ratingScale",
      "as": "ratingScale",
      "pipeline": []
    }
  },
  {
    "$set": {
      "rates": {
        "$map": {
          "input": "$rates",
          "as": "rate",
          "in": {
            "$first": {
              "$filter": {
                "input": "$ratingScale",
                "as": "nameValue",
                "cond": {"$eq": ["$$rate.rate", "$$nameValue.rate_name"]}
              }
            }
          }
        }
      }
    }
  },
  {
    "$set": {
      "rates": {
        "$map": {
          "input": "$rates",
          "as": "rate",
          "in": "$$rate.rate_value"
        }
      }
    }
  },
  {"$unset": "ratingScale"}
])

Try it on mongoplayground.net.

CodePudding user response：

You can do a plain $unwind then $lookup approach.

db.col1.aggregate([
  {
    $unwind: "$rates"
  },
  {
    "$lookup": {
      "from": "col2",
      "localField": "rates.rate",
      "foreignField": "rate_name",
      "as": "rates"
    }
  },
  {
    "$unwind": "$rates"
  },
  {
    $group: {
      _id: "$_id",
      some_field: {
        $first: "$some_field"
      },
      rates: {
        $push: "$rates.rate_value"
      }
    }
  }
])

Mongo Playground