What is the best way to find unique visitors based on unique visitorId in this data? I'm using Mongoose in a Nodejs/Express environment.

{
        username: "jack",
        events: [
         {
           eventType: "party",
           createdAt: "2022-01-23T10:26:11.214Z",
           visitorInfo: {
                visitorId: "87654321-0ebb-4238-8bf7-87654321"
               }
         },
         {
           eventType: "party",
           createdAt: "2022-01-30T10:26:11.214Z",
           visitorInfo: {
                visitorId: "87654321-0ebb-4238-8bf7-87654321"
               }
         },
         {
           eventType: "party",
           createdAt: "2022-01-29T10:26:11.214Z",
           visitorInfo: {
                visitorId: "12345678-0ebb-4238-8bf7-12345678"
               }
         },
        {
           eventType: "party",
           createdAt: "2022-01-31T10:16:11.214Z",
           visitorInfo: {
                visitorId: "12345678-0ebb-4238-8bf7-12345678"
               }
         }
           ]

        }

What I'm trying:

    Event.aggregate([
          { $match: "jack" },
          { $unwind: "$events" },
          { $match: { "events.eventType": "party" } },
    
          {
            $group: {
              _id: "$events.visitorInfo.visitorId",
              count: { $sum: 1 },
            },
          },
        ]);

This gives frequency of unique visitorIds, and I think there could be a way to use that to get unique visitors, but I'm trying to find a faster and quicker way to find number of unique visitors based on unique visitorId before going down the rabbit hole.

what I'm expecting in the end:

    visitors: {
        totalVisitors: 4,
        uniqueVisitors: 2
       }

Appreciate any help or guidance. Thanks!

CodePudding user response：

One Way is using map and lodash !

1. First get All Docs with simple find({}).
2. Use Map on result.
3. Using lodash.

const vistorsIdsList = result.map(_ => visitorInfo.visitorId)

const vistors = {
  total: vistorsIdsList.length,
  uniq: lodash.uniq(vistorsIdsList).length
}

lodash

CodePudding user response：

I think you are actually on the right track. You just need to do a $reduce to keep the totalVisitors before $unwind. And at the final stage, do another $group to get your expected result.

db.event.aggregate([
  {
    "$match": {
      username: "jack"
    }
  },
  {
    "$addFields": {
      "totalVisitors": {
        "$reduce": {
          "input": "$events",
          "initialValue": 0,
          "in": {
            "$cond": {
              "if": {
                $eq: [
                  "$$this.eventType",
                  "party"
                ]
              },
              "then": {
                $add: [
                  "$$value",
                  1
                ]
              },
              "else": "$$value"
            }
          }
        }
      }
    }
  },
  {
    $unwind: "$events"
  },
  {
    $match: {
      "events.eventType": "party"
    }
  },
  {
    $group: {
      _id: "$events.visitorInfo.visitorId",
      count: {
        $sum: 1
      },
      totalVisitors: {
        $first: "$totalVisitors"
      }
    }
  },
  {
    $group: {
      _id: null,
      count: {
        $first: "$count"
      },
      totalVisitors: {
        $first: "$totalVisitors"
      }
    }
  }
])

Here is the Mongo Playground for your reference.

CodePudding user response：

version 1(filter):

IMHO this seems to be the best version , for best results you will need index on "username"

db.collection.aggregate([
{
 $match: {
  username: "jack"
}
},
{
$project: {
  p: {
    $filter: {
      input: "$events",
      as: "item",
      cond: {
        $eq: [
          "$$item.eventType",
          "party"
        ]
       }
     }
   }
 }
 },
 {
  $unwind: "$p"
 },
 {
  $group: {
  _id: "0",
  visitors: {
    $push: "$p.visitorInfo.visitorId"
  },
  uniqueVisitors: {
    $addToSet: "$p.visitorInfo.visitorId"
   }
  }
 },
 {
  $project: {
  _id: 0,
  visitorsCount: {
    $size: "$visitors"
  },
  uniqueVisitorsCount: {
    $size: "$uniqueVisitors"
   }
  }
 }
])

Explained:

1. $match -> match only documents with username:jack
2. $project -> $filter only events with evetType: party
3. $unwind the filtered array to be suitable for group stage
4. $group -> visitors:[all] , uniqueVisitors:[unique]
5. $project: visitorsCount , uniqueVisitorsCount

play

version 2 ( not using filter - suitable for the atlas free tire)

   db.collection.aggregate([
  {
   $match: {
   username: "jack"
   }
   },
   {
    $unwind: "$events"
   },
   {
    $match: {
       "events.eventType": "party"
    }
   },
   {
    $group: {
    _id: "0",
     visitors: {
        $push: "$events.visitorInfo.visitorId"
     },
     uniqueVisitors: {
        $addToSet: "$events.visitorInfo.visitorId"
     }
    }
    },
    {
      $project: {
        _id: 0,
       visitorsCount: {
         $size: "$visitors"
        },
        uniqueVisitorsCount: {
         $size: "$uniqueVisitors"
        }
       }
      }
      ])

1. $match -> match only documents with username:jack
2. unwind the $events since $filter not possible in the free tire
3. $match the eventType: party
4. $group -> visitors:[all] , uniqueVisitors:[unique]
5. $project: visitorsCount , uniqueVisitorsCount

play ( version without filter suitable for the atlas free tire )

CodePudding user response：

version 3: Interesting 3th option using only project/filter/reduce ( no group )

 db.collection.aggregate([
 {
   $match: {
     username: "jack"
 }
 },
 {
   $project: {
    p: {
     $filter: {
      input: "$events",
      as: "item",
      cond: {
        $eq: [
          "$$item.eventType",
          "party"
        ]
       }
      }
     }
    }
  },
  {
   $project: {
    total: {
      $reduce: {
       input: "$p",
        initialValue: {
          visitor: [],
         uniquevisitor: []
        },
      in: {
        visitor: {
          $concatArrays: [
            "$$value.visitor",
            [
              "$$this.visitorInfo.visitorId"
            ]
          ]
        },
        uniquevisitor: {
          $cond: [
            {
              $in: [
                "$$this.visitorInfo.visitorId",
                "$$value.uniquevisitor"
              ]
            },
            "$$value.uniquevisitor",
            {
              $concatArrays: [
                "$$value.uniquevisitor",
                [
                  "$$this.visitorInfo.visitorId"
                ]
              ]
            }
           ]
          }
         }
       }
      }
    }
   },
  {
   $project: {
  _id: 0,
  visitorsCount: {
    $size: "$total.visitor"
  },
  uniqueVisitorsCount: {
     $size: "$total.uniquevisitor"
    }
   }
  }
 ])

Explained:

1. $match the docs with username: jack
2. $filter the docs with events.eventType:party
3. $reduce/$cond/$in and form two arrays with unique & all events.
4. $project to count unique and all.

play