I have Mongo DB collection which contains items like below:

{ 
    "_id" : ObjectId("123123123123123123"), 
    "title" : "Item-001", 
    "tags" : [
        "red"
    ], 
}
{ 
    "_id" : ObjectId("234234234234234"), 
    "title" : "Item-002", 
    "tags" : [
        "red,yellow"
    ], 
}

Objective: I want to find items which has red tag. So for this example, I want to get both Item-001 and Item-002.

What I have tried I have tried with the below query, but it returns just Item-001. My objective is to get Item-002 also as it contains red tag. How can I structure my query so that I can get both the documents?

db.getCollection("items").find({
    "tags": { '$in': [ 'red'] },
})

CodePudding user response：

First solution

You can do it with find() query and $regex operator:

db.collection.find({
 "tags": {
   "$regex": "red"
 }
})

Working example

Second solution

You can do it with Aggregation framework:

• $match with $expr - to filter documents based on custom filter
• $filter with $regexMatch - to filter tags array in each document and see if it has at least one item that contains "red".
• $size - to get the size of the above filtered array.
• $gt - to check if filtered array have at least one element.

db.collection.aggregate([
  {
    "$match": {
      "$expr": {
        "$gt": [
          {
            "$size": {
              "$filter": {
                "input": "$tags",
                "cond": {
                  "$regexMatch": {
                    "input": "$$this",
                    "regex": "red"
                  }
                }
              }
            }
          },
          0
        ]
      }
    }
  }
])

Working example

CodePudding user response：

Your tags field is an array, but the items are joined by comma delimiter. This looks like a mistake. You should change it if possible.

If not possible, here's a solution by splitting the string to a list and then matching.

db.collection.aggregate([
  {
    "$addFields": {
      "tagList": {
        $split: [
          {"$arrayElemAt": ["$tags", 0]}, ","
        ]
      }
    }
  },
  {
    $match: {
      "tagList": "red"
    }
  }
])

Playground