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.
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