Mongodb - aggregate match within attribute value

Question

MongoDB Data:

    {
    "_id" : ObjectId("123"),
    "attr" : [ 
        {
            "nameLable" : "First Name",
            "userEnteredValue" : [ 
                "Amanda"
            ],
            "rowNumber":"1"
        }, 
        {
            "nameLable" : "Last Name",
            "userEnteredValue" : [ 
                "Peter"
            ],
            "rowNumber":"1"
        }, 
        {
            "nameLable" : "First Name",
            "userEnteredValue" : [ 
                "Sandra"
            ],
            "rowNumber":"2"
        }, 
        {
            "nameLable" : "Last Name",
            "userEnteredValue" : [ 
                "Peter"
            ],
            "rowNumber":"2"
        }
    ]
}

Matching (First Name equals "Amanda" && Last Name equals "Peter") -> Match should happen within rowNumber so that i will get rowNumber1 record but now i am getting both rows as "Peter" happens to be in both "rowNumber" attribute.

Criteria Code:

Criteria cr = Criteria.where("attr").elemMatch(Criteria.where("nameLable").is(map.get("value1")).and("userEnteredValue").regex(map.get("value2").trim(), "i"); //Inside loop

AggregationOperation match = Aggregation.match(Criteria.where("testId").is("test").andOperator(cr.toArray(new Criteria[criteria.size()])));

DB Query for above search Criteria Match:

    db.Col1.aggregate([
      {
         "$match":{
         "testId":"test",
         "$and":[
               {
                  "attr":{
                     "$elemMatch":{
                        "nameLable":"First Name",
                        "userEnteredValue":{
                           "$regex":"Amanda",
                           "$options":"i"
                        }
                     }
                  }
               },
               {
                  "attr":{
                     "$elemMatch":{
                        "nameLable":"Last Name",
                        "userEnteredValue":{
                           "$regex":"Peter",
                           "$options":"i"
                        }
                     }
                  }
               }
            ]
         }
      }
   ]
)

Please let me know how can we do match within "rowNumber" attribute.

user_10770370 · Answer

Let me start by recommending you reconsider your document structure, I do not know your product but this structure is very unique and definitely makes most "simple" access patterns I can think of to very cumbersome to execute. This will be noticeable in my answer.

So the current query you have just required 2 separate elements in the array exist, as you mentioned you want the same rowNumber, due to the document structure this isn't really queryable, we will have to first use your query to match "potential" matching documents. At that point we can filter our the matched rows and see if we have both a first name and a last name matching.

Finally we could filter out the none matching rows from the result, here is the pipeline:

db.collection.aggregate([
  {
    "$match": {
      "testId": "test",
      "$and": [
        {
          "attr": {
            "$elemMatch": {
              "nameLable": "First Name",
              "userEnteredValue": {
                "$regex": "Amanda",
                "$options": "i"
              }
            }
          }
        },
        {
          "attr": {
            "$elemMatch": {
              "nameLable": "Last Name",
              "userEnteredValue": {
                "$regex": "Peter",
                "$options": "i"
              }
            }
          }
        }
      ]
    }
  },
  {
    $addFields: {
      goodRows: {
        "$setIntersection": [
          {
            $map: {
              input: {
                $filter: {
                  input: "$attr",
                  cond: {
                    $and: [
                      {
                        $eq: [
                          "$$this.nameLable",
                          "First Name"
                        ]
                      },
                      {
                        "$regexMatch": {
                          "input": {
                            "$arrayElemAt": [
                              "$$this.userEnteredValue",
                              0
                            ]
                          },
                          "regex": "Amanda",
                          "options": "i"
                        }
                      }
                    ]
                  }
                }
              },
              in: "$$this.rowNumber"
            }
          },
          {
            $map: {
              input: {
                $filter: {
                  input: "$attr",
                  cond: {
                    $and: [
                      {
                        $eq: [
                          "$$this.nameLable",
                          "Last Name"
                        ]
                      },
                      {
                        "$regexMatch": {
                          "input": {
                            "$arrayElemAt": [
                              "$$this.userEnteredValue",
                              0
                            ]
                          },
                          "regex": "Peter",
                          "options": "i"
                        }
                      }
                    ]
                  }
                }
              },
              in: "$$this.rowNumber"
            }
          }
        ]
      }
    }
  },
  {
    $match: {
      $expr: {
        $gt: [
          {
            $size: "$goodRows"
          },
          0
        ]
      }
    }
  },
  {
    $addFields: {
      attr: {
        $filter: {
          input: "$attr",
          cond: {
            $in: [
              "$$this.rowNumber",
              "$goodRows"
            ]
          }
        }
      }
    }
  }
])

Mongo Playground

Mongodb - aggregate match within attribute value

1 Answers