CODEWITHSUNDEEP
arraysmongodb
Luca Riccitelli
Luca Riccitelli

Reputation: 374

Projection of only matching elemets in array

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": "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'"

Upvotes: 0

Views: 37

Answers (1)

user20042973
user20042973

Reputation: 5065

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.

Upvotes: 1

Related Questions