I have 3 collection that have many records. Each of them have some key like price, name, ...

As you see price filed on collection c unlike the other collection is in data.detail field.

I want to calculate the everage of price of there collection accroding to the name.

Final result sould be array of products with calculated price. is it possible?

collection a = [
{"id":1, "price": "1200", "name": "x1"},
{"id":2, "price": "2000", "name": "x2"},
{"id":3, "price": "3000", "name": "x3"},
...
] 


collection b  = [
{"id":1, "price": "1500", "name": "x1"},
{"id":2, "price": "2500", "name": "x2"},
{"id":3, "price": "3125", "name": "x3"},
...
] 

collection c  = [
  {"id":1, "data": {"detail": {"price": 1900}}, "name": "x1"},
  {"id":2, "data": {"detail": {"price": 2900}}, "name": "x2"},
  {"id":3, "data": {"detail": {"price": 3500}}, "name": "x3"},
  ...
]

I want to have this as result:

$result = [
{"id":1, "price": "1533.3", "name": "x1"},
{"id":2, "price": "2466.6", "name": "x2"},
{"id":2, "price": "3208.3", "name": "x3"},
...
]

CodePudding user response：

You can try this query:

• First $lookup twice to do the JOIN with collection B and C.
• Then $unwind to deconstruct the array generated by $lookup.
• Here I've parsed to int values because in your data example are string, I don't know if it is a typo or the data is a string. You can avoid this stage is your data is already an integer.
• Then regroup the values, generating an array for each price (A, B and C).
• Concat these arrays.
• And calculate the average.

db.a.aggregate([
  {
    "$lookup": {
      "from": "b",
      "localField": "name",
      "foreignField": "name",
      "as": "b"
    }
  },
  {
    "$lookup": {
      "from": "c",
      "localField": "name",
      "foreignField": "name",
      "as": "c"
    }
  },
  {
    "$unwind": "$b"
  },
  {
    "$unwind": "$c"
  },
  {
    "$set": {
      "b.price": {
        "$toInt": "$b.price"
      },
      "price": {
        "$toInt": "$price"
      }
    }
  },
  {
    "$group": {
      "_id": "$_id",
      "name": {
        "$first": "$name"
      },
      "id": {
        "$first": "$id"
      },
      "priceA": {
        "$push": "$price"
      },
      "priceB": {
        "$push": "$b.price"
      },
      "priceC": {
        "$push": "$c.data.detail.price"
      }
    }
  },
  {
    "$set": {
      "price": {
        "$concatArrays": [
          "$priceA",
          "$priceB",
          "$priceC"
        ]
      }
    }
  },
  {
    "$project": {
      "_id": 0,
      "id": 1,
      "name": 1,
      "price": {
        "$avg": "$price"
      }
    }
  }
])

Example here

With your input example it works, check if it still works for other input data.