I want to sum of values inside array of objects which have another array of objects. In my case; how can I count 'url' values in all documents inside 'urls' array under 'iocs' array;

Mongo playground: open

Here is document example;

[
  {
    "_id": {
      "$oid": "63b4993d0625ebe8b6f5b06e"
    },
    "iocs": [
      {
        "urls": [
          {
            "url": "7.1.5.2",
            
          }
        ],
        
      },
      {
        "urls": [
          {
            "url": "https://l-ink.me/GeheimeBegierde",
            
          },
          {
            "url": "GeheimeBegierde.ch",
            
          }
        ],
        
      },
      {
        "urls": [
          {
            "url": "https://l-ink.me/GeheimeBegierde",
            
          }
        ],
        
      }
    ],
    type: "2"
  },
  {
    "_id": {
      "$oid": "63b4993d0624ebe8b6f5b06e"
    },
    "iocs": [
      {
        "urls": [
          {
            "url": "7.1.5.2",
            
          }
        ],
        
      },
      {
        "urls": [
          {
            "url": "https://l-ink.me/GeheimeBegierde",
            
          },
          {
            "url": "GeheimeBegierde.ch",
            
          }
        ],
        
      },
      {
        "urls": [
          {
            "url": "https://l-ink.me/GeheimeBegierde",
            
          }
        ],
        
      }
    ],
    type: "3"
  },
  {
    "_id": {
      "$oid": "63b4993d0615ebe8b6f5b06e"
    },
    "iocs": [
      {
        "urls": [
          {
            "url": "www.google.com",
            
          }
        ],
        
      },
      {
        "urls": [
          {
            "url": "abc.xyz",
            
          },
          {
            "url": "GeheimeBegierde.ch",
            
          }
        ],
        
      },
      {
        "urls": [
          {
            "url": "https://123.12",
            
          }
        ],
        
      }
    ],
    type: "1"
  }
]

expected output be like;

url: "7.1.5.2",
count:2,
types:[2,3]

url: "https://l-ink.me/GeheimeBegierde",
count:4,
types:[2,3],

url: "abc.xyz",
count:1,
types:[1],

I tried unwind iocs then project urls but can't figure out how to get this output. I think i must use group but how ? Newbie in mongodb.

Any help would be appreciated. Thanks all.

NOTE: All the answers are working. Thank you all for the contributing.

CodePudding user response：

You could do something like this !

db.collection.aggregate([
  {
    "$unwind": "$iocs"
  },
  {
    "$unwind": "$iocs.urls"
  },
  {
    "$group": {
      "_id": "$iocs.urls.url",
      "count": {
        "$sum": 1
      },
      "types": {
        "$addToSet": "$type"
      }
    }
  },
  {
    "$project": {
      url: "$_id",
      _id: 0,
      types: 1,
      count: 1
    }
  },
])

https://mongoplayground.net/p/hhMqh2zI_SX

CodePudding user response：

Here's one way you could do it.

db.collection.aggregate([
  {"$unwind": "$iocs"},
  {"$unwind": "$iocs.urls"},
  {
    "$group": {
      "_id": "$iocs.urls.url",
      "count": {"$count": {}},
      "types": {"$addToSet": {"$toInt": "$type"}}
    }
  },
  {
    "$set": {
      "url": "$_id",
      "_id": "$$REMOVE"
    }
  }
])

Try it on mongoplayground.net.

CodePudding user response：

You can try this query:

• Double $unwind to deconstruct the nested array.
• Then group by url get the count using $sum nad add the types into a set (to avoid duplicates, otherwise you can use simply $push)

db.collection.aggregate([
  {
    "$unwind": "$iocs"
  },
  {
    "$unwind": "$iocs.urls"
  },
  {
    "$group": {
      "_id": "$iocs.urls.url",
      "count": {
        "$sum": 1
      },
      "types": {
        "$addToSet": "$type"
      }
    }
  }
])

Example here

CodePudding user response：

Since $unwind is considered an inefficient operation, another option is to use $reduce and only $unwind once:

db.collection.aggregate([
  {$project: {
      type: 1,
      urls: {
        $reduce: {
          input: "$iocs",
          initialValue: [],
          in: {$concatArrays: ["$$value", "$$this.urls.url"]}
        }
      }
  }},
  {$unwind: "$urls"},
  {$group: {
      _id: "$urls",
      type: {$addToSet: "$type"},
      count: {$sum: 1}
  }},
  {$project: {url: "$_id", count: 1, type: 1}}
])

See how it works on the playground example