LIKE operator into arrays with $elemMatch not working properly

Working with Data
,
#1

I’m trying to implement a LIKE operator with $elemMatch and $regex to find any document that match the search term I’m passing but I can’t figure out how to do that.

The collection I have:

{
       ...others arrays,
	"discounts": [
		{
			"_id": "628fd4cd7176540e9e1ad6c2",
			"name": "asd < % $",
			"code": "EGX2-VA2Q-H9LV",
			"createdAt": "2022-05-26T19:28:13.168Z",
			"updatedAt": "2022-05-26T19:28:13.168Z"
		},
		{
			"_id": "62e28bf56b91f508bbd8b703",
			"name": "test edit",
			"code": "51AD-QBQG-TRNJ",
			"createdAt": "2022-07-28T13:15:33.140Z",
			"updatedAt": "2022-07-28T13:15:33.140Z"
		}
	],
       ...others fields
}

What I’m trying to do:

db.Store.find(
     $or: [
              {
                discounts: {
                  $elemMatch: { name: { $regex: searchTerm, $options: 'i' } }
                }
              },
              {
                discounts: {
                  $elemMatch: { code: { $regex: searchTerm, $options: 'i' } }
                }
              }
      ])

The result of this always return all documents of discounts array. If I hardcoded the search term, like:

{
  discounts: {
    name: 'test edit'
  }
}

I get the same result, all documents.

What Am I doing wrong here? Thanks!

#2

The expected result for this will be, e.g. the search term is ‘test edit’:

"discounts": [
		{
			"_id": "62e28bf56b91f508bbd8b703",
			"name": "test edit",
			"code": "51AD-QBQG-TRNJ",
			"createdAt": "2022-07-28T13:15:33.140Z",
			"updatedAt": "2022-07-28T13:15:33.140Z"
		}
	]
#3

I found this solution that works fine:

db.Store.find( 
   {"discounts.name": "test edit"}, 
   {_id: 0, discounts: {$elemMatch: {name: "test edit"}}}
);

but the goal here is to implement a $OR operator too, must find a way to search by the code field as well.

#4

Hello @Rafael_Rodrigues, Welcome to MongoDB Community Forum,

The query part does not filter the nested array elements in the result, It requires to do filter in projection.

You can use $filter operator to filter elements from an array and $regexMatch operator to check expression string similar to $regex operator, because $regex operator is only supported in the query part,
Projection:

{
  "discounts": {
    "$filter": {
      "input": "$discounts",
      "cond": {
        "$or": [
          {
            "$regexMatch": {
              "input": "$$this.name",
              "regex": "test edit",
              "options": "i"
            }
          },
          {
            "$regexMatch": {
              "input": "$$this.code",
              "regex": "test edit",
              "options": "i"
            }
          }
        ]
      }
    }
  }
}

Playground

#5

Hello @turivishal, thanks for helping me!

I must put your filter inside the find() and that is? like this:

await db.Store.find(
{
  "discounts": {
    "$filter": {
      "input": "$discounts",
      "cond": {
        "$or": [
          {
            "$regexMatch": {
              "input": "$$this.name",
              "regex": "test edit",
              "options": "i"
            }
          },
          {
            "$regexMatch": {
              "input": "$$this.code",
              "regex": "test edit",
              "options": "i"
            }
          }
        ]
      }
    }
  }
}
)

Because if so, I got this error: {"error":{"message":"An error occurred while retrieving the discounts and their information.","stack":"Error: Can't use $filter with Array

I’m using the version "^5.9.2" of MongoDB.

#6

I already said, “The query part does not filter the nested array elements in the result, It requires to do filter in projection.”

You need to add that in the projection part of .find() method,

db.Store.find({
    $or: [
        { "discounts.name": { $regex: searchTerm, $options: 'i' } },
        { "discounts.code": { $regex: searchTerm, $options: 'i' } }
    ]
},
{
    "discounts": {
      "$filter": {
        "input": "$discounts",
        "cond": {
          "$or": [
            {
              "$regexMatch": {
                "input": "$$this.name",
                "regex": searchTerm,
                "options": "i"
              }
            },
            {
              "$regexMatch": {
                "input": "$$this.code",
                "regex": searchTerm,
                "options": "i"
              }
            }
          ]
        }
      }
    }
})
#7

@turivishal I undesrtand now, thank you!

1 Like
#8

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.