Question

Match list of host in mongo query doesn't work

I have below query :

mydb.my-collection.aggregate([
  {
    $match: {
    
      $and: [
        {timestamp:
        {
            $gte: $__timeFrom,
            $lte: $__timeTo
        }}       
      ]
    }
  },
  {
    $group: {
      _id: {
        host: "$host"
      },
      sum: {$sum: "$status"},
      count: {$count: {}}
    }
  },
  {
    $project: {
      price: {$divide: ["$sum", "$count"]}
    }
  }
])

I am trying to match only specific hosts, so i updated the query like below:

   mydb.my-collection.aggregate([
      {
        $match: {
            host : {$regex: ".*SER1.*"},
            host:  {$regex: ".*SER2.*"},
            host: {$regex: ".*SER3.*"}
          $and: [
            {timestamp:
            {
                $gte: $__timeFrom,
                $lte: $__timeTo
            }}       
          ]
        }
      },
      {
        $group: {
          _id: {
            host: "$host"
          },
          sum: {$sum: "$status"},
          count: {$count: {}}
        }
      },
      {
        $project: {
          price: {$divide: ["$sum", "$count"]}
        }
      }
    ])

I also tried {host: { $in:["SER1","SER2","SER3"] }} for matching the host. But my result shows all other hosts, i only want to show SER1, SER2, SER3.

Is there any way to use list or array of values which i want to match?

 2  27  2
1 Jan 1970

Solution

 0

You can't have the same field name in the object.

You should work with $expr in order to use the $regexMatch operator. To match any regex pattern, you should wrap the multiple (regex) conditions in the $or operator.

Also, you need to modify the query for comparing the timestamp field as applying the $expr.

{
    $match: {
      $expr: {
        $and: [
          {
            $or: [
              {
                $eq: [
                  {
                    $regexMatch: {
                      input: "$host",
                      regex: ".*SER1.*"
                    }
                  },
                  true
                ]
              },
              {
                $eq: [
                  {
                    $regexMatch: {
                      input: "$host",
                      regex: ".*SER2.*"
                    }
                  },
                  true
                ]
              },
              {
                $eq: [
                  {
                    $regexMatch: {
                      input: "$host",
                      regex: ".*SER3.*"
                    }
                  },
                  true
                ]
              }
            ]
          },
          {
            $and: [
              {
                $gte: [
                  "$timestamp",
                  __timeFrom
                ]
              },
              {
                $lte: [
                  "$timestamp",
                  __timeTo
                ]
              }
            ]
          }
        ]
      }
    }
  }

Another approach is combining the regexes into non-capturing groups ((?:)) and alternatives (|). This will make the query shorter.

Regex 101

  {
    $match: {
      host: {
        $regex: "(?:.*SER1.*)|(?:.*SER2.*)|(?:.*SER3.*)"
      },
      $and: [
        {
          timestamp: {
            $gte: __timeFrom,
            $lte: __timeTo
          }
        }
      ]
    }
  }
2024-07-24
Yong Shun