aggregate compare two array in mongodb?

user_16173080 0
Viewed 181

i have following query result.

[
  {
    "dayCount": 3,
    "dayRange": [
      "2021-05-27",
      "2021-05-28",
      "2021-05-29",
      "2021-05-30",
      "2021-05-31",
      "2021-06-01",
      "2021-06-02"
    ],
    "totalUserCount": 4,
    "userCountPerDay": [
      {
        "createdAt": "2021-05-28",
        "userCount": 2
      },
      {
        "createdAt": "2021-06-02",
        "userCount": 1
      },
      {
        "createdAt": "2021-06-01",
        "userCount": 1
      }
    ]
  }
]

This is User visits by date.
I need to compare both "dayRange" and "userCountPerDay.createdAt"

For dates that do not exist in "userCountPerDay.createdAt", set the "userCount" to 0

i want to following result using with mongo aggregate
please help me.

[
  {
    "dayCount": 3,
    "totalUserCount": 4,
    "userCountPerDay": [
      {
        "createdAt": "2021-05-27",
        "userCount": 0
      },
      {
        "createdAt": "2021-05-28",
        "userCount": 2
      },
      {
        "createdAt": "2021-05-29",
        "userCount": 0
      },
      {
        "createdAt": "2021-05-30",
        "userCount": 0
      },
      {
        "createdAt": "2021-05-31",
        "userCount": 0
      },
      {
        "createdAt": "2021-06-01",
        "userCount": 1
      },
      {
        "createdAt": "2021-06-02",
        "userCount": 1
      }
    ]
  }
]

thank you

mongodb compare aggregate arrays
1 Answers
  • $project to show required fields
  • $map to iterate loop of dayRange array
  • $cond to check condition if current date is in userCountPerDay.createdAt array,
    • yes, $indexOfArray to get array index or current date in userCountPerDay array, $arrayElemAt to get element/object from returned index
    • no, return createdAt and userCount fields
db.collection.aggregate([
  {
    $project: {
      dayCount: 1,
      totalUserCount: 1,
      userCountPerDay: {
        $map: {
          input: "$dayRange",
          in: {
            $cond: [
              { $in: ["$$this", "$userCountPerDay.createdAt"] },
              {
                $arrayElemAt: [
                  "$userCountPerDay",
                  { $indexOfArray: ["$userCountPerDay.createdAt", "$$this"] }
                ]
              },
              {
                createdAt: "$$this",
                userCount: 0
              }
            ]
          }
        }
      }
    }
  }
])

Playground

user_89871280
Related