Mongodb aggregation lookup join two collection array of object fields sum of matched object index fi-CodePudding

I have a two collections "datasets" and "users". I tried to lookup for array of object both collections.

I want to join the "datasets.stateHistory.date" field and "users.prices.date" field. get the result of the datasets collection i want sum of "users.prices.price" sum values

Datasets json Data:

"datasets": [
    {
      "colorDescription": "braun, rose gold",
      "stateHistory": [
        {
          "state": "scanning",
          "date": "2022-02-22T13:06:13.493 00:00"
        },
        {
          "state": "scanned",
          "date": "2022-02-18T13:06:13.493 00:00"
        },
        {
          "state": "reconstructing",
          "date": "2022-02-16T13:06:13.493 00:00"
        }
      ]
    },
    {
      "colorDescription": "beige, silber",
      "stateHistory": [
        {
          "state": "scanning",
          "date": "2022-03-22T13:06:13.493 00:00"
        },
        {
          "state": "scanned",
          "date": "2022-03-18T13:06:13.493 00:00"
        },
        {
          "state": "reconstructing",
          "date": "2022-03-16T13:06:13.493 00:00"
        }
      ]
    }
  ]

Users json Data:

"users": [
    {
      "name": "Aravinth",
      "prices": [
        {
          "date": "2022-02-16T13:06:13.493 00:00",
          "price": 45
        },
        {
          "date": "2022-03-22T13:06:13.493 00:00",
          "price": 55
        }
      ]
    },
    {
      "name": "Raja",
      "prices": [
        {
          "date": "2022-02-24T13:06:13.493 00:00",
          "price": 75
        },
        {
          "date": "2022-03-23T13:06:13.493 00:00",
          "price": 85
        }
      ]
    }
  ]

Expected result json Data:

[
    {
      "colorDescription": "braun, rose gold",
      "cgPrices: 45,
      "stateHistory": [
        {
          "state": "scanning",
          "date": "2022-02-22T13:06:13.493 00:00"
        },
        {
          "state": "scanned",
          "date": "2022-02-18T13:06:13.493 00:00"
        },
        {
          "state": "reconstructing",
          "date": "2022-02-16T13:06:13.493 00:00"
        }
      ]
    },
    {
      "colorDescription": "beige, silber",
      "cgPrices: 0,
      "stateHistory": [
        {
          "state": "scanning",
          "date": "2022-03-22T13:06:13.493 00:00"
        },
        {
          "state": "scanned",
          "date": "2022-03-18T13:06:13.493 00:00"
        },
        {
          "state": "reconstructing",
          "date": "2022-03-16T13:06:13.493 00:00"
        }
      ]
    }
  ]

"cgPrice" field i need to sum of matched prices with date of two collection added.

my code:

db.datasets.aggregate([
  {
    "$lookup": {
      "from": "users",
      "as": "details",
      "localField": "stateHistory.date",
      "foreignField": "prices.date"
    }
  },
  {
    "$project": {
      color: "$details.colorDescription",
      prices: "$details"
    }
  }
])

How to join the lookup and get prices for matched field add the additional field "cgPrice" count sum.

mongo playground link: https://mongoplayground.net/p/vv8R3DlEDYo

CodePudding user response：

You just need to do quite a lot of restructure, here is an example using the $map, $filter and $reduce operators:

db.datasets.aggregate([
  {
    "$lookup": {
      "from": "users",
      "as": "details",
      "localField": "stateHistory.date",
      "foreignField": "prices.date"
    }
  },
  {
    "$project": {
      colorDescription: 1,
      stateHistory: 1,
      prices: {
        $sum: {
          $map: {
            input: {
              $filter: {
                input: {
                  $reduce: {
                    input: {
                      $map: {
                        input: "$details",
                        in: "$$this.prices"
                      }
                    },
                    initialValue: [],
                    in: {
                      "$concatArrays": [
                        "$$this",
                        "$$value"
                      ]
                    }
                  }
                },
                cond: {
                  $in: [
                    "$$this.date",
                    "$stateHistory.date"
                  ]
                }
              }
            },
            in: "$$this.price"
          }
        }
      }
    }
  }
])

Mongo Playground