how can I use field value as a key in aggregation?

Question

I have nmap results in a structure like this:

_id: 64940937ab90b8acfa5386fb
ip : "1.2.3.4"
top_ports: Object
--> 12.168.16.253: Object
--> state: Object
----> state : "up"

I would like to do an aggregation where I can see how many machines are in 'up' state.

So I did

$unwind
{
  path: "$top_ports",
}

but I am not sure how to do the match where the ip field value is used as a key in the match

$match
{
  "$top_ports.$ip.state.state" : "up"
}

If I do it in python it would be easy to replace the string, but I need this aggregation in mongodb compass so I can use it later on in mongodb charts.

I tried

$match{"$top_ports.$ip.state.state" : "up"}

$match{"$top_ports[ip].state.state" : "up"}

and many other desperate combinations

I think I need to use $set

{ 
  allKeys: {$objectToArray: "$top_ports"}
}

but I get stuck :-/

Answer 1

Generally having value as key is not a good practice , if you can fix the schema , more convenient in your case would be to have it as follow:

{
 "_id": 1,
 "ip": "1.2.3.4",
 "top_ports": [
   {
      "addr": "12.168.16.253",
      "state": {
        "state": "up"
      }
   }
 ]
}

Where you can match simply by:

{ $match:{ "top_ports.addr":"12.168.16.253"}}

and filter easily only the matching state up like this:

db.collection.aggregate([
{
"$addFields": {
  "top_ports": {
    "$filter": {
      "input": "$top_ports",
      "as": "tp",
      "cond": {
        $and: [
          {
            "$eq": [
              "$$tp.state.state",
              "up"
            ]
          },
          {
            "$eq": [
              "$$tp.addr",
              "12.168.16.253"
            ]
          }
        ]
      }
    }
  }
 }
 }
])

Playgorund2

Otherways you will need to do some additional transformations like the below that are not very optimal for larger documents and collections:

db.collection.aggregate([
{
"$addFields": {
  "top_ports": {
    "$objectToArray": "$top_ports"
  }
 }
 },
 {
  $match: {
   "top_ports.k": "12.168.16.253"
  }
 },
 {
 "$addFields": {
  "top_ports": {
    "$arrayToObject": "$top_ports"
    }
  }
 }
])

Playgorund