MongoDB populate a field and count total 'true' values in nested boolean fields using mongoose

Viewed 225

I have two mongo documents of the structure

links document

{
  "_id": {
    "$oid": "6002d2d627925c4194a15a94"
  },
  "visit_count": 20,
  "password": null,
  "password_protected": false,
  "description": null,
  "analytics": [
    {
      "$oid": "6002d568e9c7d24d34413492"
    },
    {
      "$oid": "6002d612464785401824a782"
    }
  ],
  "alias": "g",
  "short_url": "https://reduced.me/g",
  "long_url": "https://google.com",
  "created": {
    "$date": "2021-01-16T11:49:42.517Z"
  },
  "__v": 2
}

analytics document

[
  {
    "_id": {
      "$oid": "6002d568e9c7d24d34413492"
    },
    "os": {
      "windows": true,
      "linux": false,
      "mac": false,
      "android": false
    },
    "browser": {
      "opera": false,
      "ie": false,
      "edge": false,
      "safari": false,
      "firefox": true,
      "chrome": false
    },
    "details": {
      "os": "Windows 10.0",
      "browser": "Edge",
      "version": "87.0.664.75",
      "platform": "Microsoft Windows",
      "source": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/87.0.4280.141 Safari/537.36 Edg/87.0.664.75"
    },
    "__v": 0
  },
  {
    "_id": {
      "$oid": "6002d612464785401824a782"
    },
    "os": {
      "windows": true,
      "linux": false,
      "mac": false,
      "android": false
    },
    "browser": {
      "opera": false,
      "ie": false,
      "edge": true,
      "safari": false,
      "firefox": false,
      "chrome": false
    },
    "details": {
      "os": "Windows 10.0",
      "browser": "Edge",
      "version": "87.0.664.75",
      "platform": "Microsoft Windows",
      "source": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/87.0.4280.141 Safari/537.36 Edg/87.0.664.75"
    },
    "__v": 0
  }
]

what i want to do is, populate the analytics array in links document with the values from analytics document and also sum up the count of true boolean values for each field in os and browser objects. the output structure that i am looking for is -

{
    "_id": null,
    "visit_count": 20,
    "password_protected": false,
    "description": null,
    "alias": "g",
    "short_url": "https://reduced.me/g",
    "long_url": "https://google.com",
    "created": "2021-01-16T11:49:42.517Z",
    "analytics": {
        "os": {
            "windows": 2,
            "linux": 0,
            "mac": 0,
            "android": 0
        },
        "browser": {
            "opera": 0,
            "ie": 0,
            "edge": 1,
            "safari": 0,
            "firefox": 1,
            "chrome": 0
        }
    }
}

here, for example, windows:2 shows the number of times windows had a value of true throughout all the document.

Currently im doing it like this

const analytics = await LinkModel.aggregate([
        { $match: { short_url: req.body.short_url } },

        {
            $lookup: {
                from: 'analytics',
                localField: 'analytics',
                foreignField: '_id',
                as: 'analytics',
            },
        },
        { $unwind: '$analytics' },
        {
            $group: {
                _id: null,

                visit_count: { $first: '$visit_count' },
                password_protected: { $first: '$password_protected' },
                description: { $first: '$description' },
                alias: { $first: '$alias' },
                short_url: { $first: '$short_url' },
                long_url: { $first: '$long_url' },
                created: { $first: '$created' },

                windows: {
                    $sum: { $cond: ['$analytics.os.windows', 1, 0] },
                },
                linux: {
                    $sum: { $cond: ['$analytics.os.linux', 1, 0] },
                },
                mac: {
                    $sum: { $cond: ['$analytics.os.mac', 1, 0] },
                },
                android: {
                    $sum: { $cond: ['$analytics.os.android', 1, 0] },
                },
                opera: {
                    $sum: { $cond: ['$analytics.browser.opera', 1, 0] },
                },
                ie: {
                    $sum: { $cond: ['$analytics.browser.ie', 1, 0] },
                },
                edge: {
                    $sum: { $cond: ['$analytics.browser.edge', 1, 0] },
                },
                safari: {
                    $sum: {
                        $cond: ['$analytics.browser.safari', 1, 0],
                    },
                },
                firefox: {
                    $sum: {
                        $cond: ['$analytics.browser.firefox', 1, 0],
                    },
                },
                chrome: {
                    $sum: {
                        $cond: ['$analytics.browser.chrome', 1, 0],
                    },
                },
            },
        },
    ])

this does give the output like this

[{
    "_id": null,
    "visit_count": 20,
    "password_protected": false,
    "description": null,
    "alias": "g",
    "short_url": "https://reduced.me/g",
    "long_url": "https://google.com",
    "created": "2021-01-16T11:49:42.517Z",
    "windows": 2,
    "linux": 0,
    "mac": 0,
    "android": 0,
    "opera": 0,
    "ie": 0,
    "edge": 1,
    "safari": 0,
    "firefox": 1,
    "chrome": 0
}]

it has all the data i need but the structure is not right. I am using mongoose as the ORM. Any help would be appreciated. thanks.

1 Answers

You can use $addFields to get your desire output

db.collection.aggregate([
  {
    $addFields: {
      "analytics": {
        "os": {
          "windows": "$windows",
          "linux": "$linux",
          "mac": "$mac",
          "android": "$android"
        },
        "browser": {
          "opera": "$opera",
          "ie": "$ie",
          "edge": "$edge",
          "safari": "$safari",
          "firefox": "$firefox",
          "chrome": "$chrome"
        }
      },
      "windows": "$$REMOVE",
      "linux": "$$REMOVE",
      "mac": "$$REMOVE",
      "android": "$$REMOVE",
      "opera": "$$REMOVE",
      "ie": "$$REMOVE",
      "edge": "$$REMOVE",
      "safari": "$$REMOVE",
      "firefox": "$$REMOVE",
      "chrome": "$$REMOVE"
    }
  }
])

Working Mongo playground

Related