mongodb

Mongo searching in a nested query


I have documents like this

{ "_id" : 1, "item" : { "code": "ABC1", skus: [ " c1", "c2 ", "c3"] }
{ "_id" : 2, "item" : { "code": "ABC2", skus: [ "c1", "c2", "d 3"] }
{ "_id" : 3, "item" : { "code": "ABC3", skus: [ "d1", "d2", "c3"] }

search all those docs matching the input sku array

["c1", "d3"] 
{ "_id" : 1, "item" : { "code": "ABC1", skus: [ " c1", "c2", "c3"] }
{ "_id" : 2, "item" : { "code": "ABC2", skus: [ "c1", "c2", "d 3"] }

the requirement is if the skus contain at least one element from the input search array then the full document should be returned and the search should ignore any spaces with in values in the mongo

I could use $in operator but it dont ignore spaces as far as what i have tried is correct

Also I cant use any $regex to ignore spaces as the search array may contain more than 5K elements and creating a regex out of such big array have thrown the below error

{"ok": 0.0, "errmsg": "Regular expression is invalid: regular expression is too large", "code": 51091, "codeName": "Location51091"}

Any help is much appreciated

The query response should return the full documents in original , not any trimmed doc

Thanks Sree

I have tried unwind and group for reversing the unwind but group doesn't return the full document after projection


Solution

  • Updated

    The key concepts:

    1. $ne - Check the result returned from 1.1 is not an empty array.

    1.1. $setIntersection - Intersect the result from 1.1.1 and the filter criteria.

    1.1.1 $map - Iterate the element and update the element with 1.1.1.1.

    1.1.1.1. $replaceAll - Remove space from the string.

    As $map, $setIntersection and $replaceAll are aggregation operators, hence you need to work with the $expr operator.

    db.collection.find({
      $expr: {
        $ne: [
          {
            $setIntersection: [
              {
                $map: {
                  input: "$item.skus",
                  in: {
                    $replaceAll: {
                      input: "$$this",
                      find: " ",
                      replacement: ""
                    }
                  }
                }
              },
              [
                "c1",
                "d3"
              ]
            ]
          },
          []
        ]
      }
    })
    

    Demo @ Mongo Playground