I'm searching in for documents that match some value in a sub-array of object. I'm obtaining my results with $elemMatch on $or condition. But I'm unable to project the only elements of the subarray that match my search.

This is my model:

{
  "_id": "636a69262df2b0acadeb6fc2",
  "companyId": "854725",
  "buyerId": "10",
  "companyName": "My Company!",
  "country": {
    "dataId": "8",
    "code": "ITA",
    "value": "Italy"
  },
  "erpCode": "0000054321",
  "legalForm": {
    "dataId": "7",
    "code": "198",
    "value": "S.R.L."
  },
  "searchKeys": [
    {
      "type": "address",
      "value": "Main street 24",
      "valueClean": "MainStreet24"
    },
    {
      "type": "country_key",
      "value": "RO-123",
      "valueClean": "R0123"
    },
    {
      "type": "vendor_name",
      "value": "My Company!",
      "valueClean": "MyCompany"
    },
    {
      "type": "vendor_full_name",
      "value": "My Company! S.R.L.",
      "valueClean": "MyCompanySRL"
    },
    {
      "type": "vendor_code",
      "value": "0000054321",
      "valueClean": "0000054321"
    },
    {
      "type": "vat",
      "value": "IT01234567890",
      "valueClean": "IT01234567890"
    },
    {
      "type": "website",
      "value": "http://www.my-company.com/",
      "valueClean": "httpwwwmycompanycom"
    },
    {
      "type": "contact",
      "value": "[email protected]",
      "valueClean": "lucalanzafamebaglionispacom"
    },
    {
      "type": "company_registration_number",
      "value": "RO-123",
      "valueClean": "RO123"
    },
    {
      "type": "city",
      "value": "Torino",
      "valueClean": "Torino"
    }
  ],
  "vat": "IT01234567890"
}

This is my query:

db.collection.aggregate(
{$match: {
    $and: [
    {$or: [
        {'searchKeys': {$elemMatch: { 'value': {$regex : "company", '$options' : 'i'}}}},
        {'searchKeys': {$elemMatch: { 'valueClean': {$regex : "company", '$options' : 'i'}}}}
    ]},
    {'buyerId': 10}]
}},
{$project: {
   companyId: 1,
   buyerId: 1,
   companyName: 1,
   legalForm: 1,
   country: 1,
   vat: 1,
   erpCode: 1,
   searchKeys: 1
}})

The results returns each elements of searchKeys, how can I project only matching elements of searchKeys?

I've tried using $cond like this:

   searchKeys: {$cond: [{$or: [{$elemMatch: { 'value': {$regex : "company", '$options' : 'i'}}}, {$elemMatch: { 'valueClean': {$regex : "company", '$options' : 'i'}}}]}]}

but I've obtained "Invalid $project :: caused by :: Unrecognized expression '$elemMatch'"

CodePudding user response：

There are a few things here, but the short of it is that the expression in the projection using the $filter operator will probably look something like this:

      searchKeys: {
        "$filter": {
          "input": "$searchKeys",
          "cond": {
            "$or": [
              {
                $regexMatch: {
                  input: "$$this.value",
                  regex: "company",
                  options: "i"
                }
              },
              {
                $regexMatch: {
                  input: "$$this.valueClean",
                  regex: "company",
                  options: "i"
                }
              }
            ]
          }
        }
      }

Playground example here.

A few points:

• $elemMatch is not strictly needed anywhere here since you are ultimately querying on a single query condition.
• I similarly removed your explicit $and since the implicit and is sufficient here.
• There was a mismatch in types (string versus number) on your buyerId in the example, so I converted one to the other for the playground example. Almost certainly just a typo in the example.

But most importantly - please keep in mind that doing a case-insensitive regex search is generally not a scalable approach. While the playground example probably satisfies your direct question today, you may wish to look into alternative text searching approaches to ultimately satisfy your full set of requirements.