Search mongo document based on filter-CodePudding

I need to search documents using mongo query. I am working on fetching documents that have a list of objects. I am using Spring Data Mongo. I am trying to get documents using the condition but getting this error.

Due to limitations of the com.mongodb.BasicDocument, you can't add a second 'additionalFields.apiKey' criteria. Query already contains '{ "additionalFields.apiKey" : "yourName763836569"}'

Query prepared dynamically by code based on filter

Query mongoQuery = new Query(); mongoQuery.addCriteria(Criteria.where("additionalFields.apiKey").is("yourName763836569"));
mongoQuery.addCriteria(Criteria.where("additionalFields.data").is("KK")); mongoQuery.addCriteria(Criteria.where("additionalFields.apiKey").is("joiningDate11742346"));
mongoQuery.addCriteria(Criteria.where("additionalFields.data").is("2022-12-21"));

I need all documents that match the condition, Documents must have two additional fields one with apikey = "joiningDate11742346", data = "2022-12-21" and another one with apikey = "yourName763836569", data = "KK"

A list of Documents exists in DB

1st Document

{
 "_id": {
 "$oid": "63a194458f72c5e00240a594"
},
"fieldId": "1427896432",
"tenantId": "localhost",
"additionalFields": [
{
  "apiKey": "yourName763836569",
  "data": "Nitin",
  "fieldType": "text"
},
{
  "apiKey": "yourJobTitle21385087",
  "data": [
    "1671531432945"
  ],
  "fieldType": "dropdown"
},
{
  "apiKey": "joiningDate11742346",
  "data": "2022-12-21",
  "fieldType": "date"
}
]
}

2nd Document

{
 "_id": {
 "$oid": "63a197cb8f72c5e00240a7a9"
},
"fieldId": "221154425",
 "tenantId": "localhost",
 "additionalFields": [
{
  "apiKey": "joiningDate11742346",
  "data": "2022-12-21",
  "fieldType": "date"
}
]
}

3rd Document

{
"_id": {
"$oid": "63a199488f72c5e00240a875"
},
"fieldId": "174263785",
"tenantId": "localhost",
"additionalFields": [
{
  "apiKey": "yourName763836569",
  "data": "KK",
  "fieldType": "text"
},
{
  "apiKey": "yourJobTitle21385087",
  "data": [
    "1671531428578"
  ],
  "fieldType": "dropdown"
},
{
  "apiKey": "joiningDate11742346",
  "data": "2022-12-21",
  "fieldType": "date"
}
]
}

Condition is

apiKey : joiningDate11742346
data : "2022-12-21"

and

apiKey : yourName763836569
data : "KK"

Expected Result :

{
"_id": {
"$oid": "63a199488f72c5e00240a875"
},
"fieldId": "174263785",
"tenantId": "localhost",
"additionalFields": [
{
  "apiKey": "yourName763836569",
  "data": "KK",
  "fieldType": "text"
},
{
  "apiKey": "yourJobTitle21385087",
  "data": [
    "1671531428578"
  ],
  "fieldType": "dropdown"
},
{
  "apiKey": "joiningDate11742346",
  "data": "2022-12-21",
  "fieldType": "date"
}
]
}

CodePudding user response：

A MongoDB query filter is a JSON/BSON object, so it cannot have multiple fields with the same name. If you need to specify the same field name to match different elements of an array, you can use $and or $elemMatch.

CodePudding user response：

Fixed by using this pipeline

[
{
$match: {
  $and: [
    {
      additionalFields: {
        $elemMatch: {
          apiKey: "yourName763836569",
          data: { $eq: "KK" },
        },
      },
    },
    {
      additionalFields: {
        $elemMatch: {
          apiKey: "joiningDate11742346",
          data: { $eq: "2022-12-21" },
        },
      },
    },
    {
      additionalFields: {
        $elemMatch: {
          apiKey: "yourJobTitle21385087",
          data: { $in: ["1671531428578"] },
        },
      },
    },
  ],
},
},
]

and Java code is

List<Document> documents = Arrays.asList(new Document("$match", 
new Document("$and", Arrays.asList(new Document("additionalFields", 
            new Document("$elemMatch", 
            new Document("apiKey", "yourName763836569")
                        .append("data", 
            new Document("$eq", "KK")))), 
            new Document("additionalFields", 
            new Document("$elemMatch", 
            new Document("apiKey", "joiningDate11742346")
                        .append("data", 
            new Document("$eq", "2022-12-21")))), 
            new Document("additionalFields", 
            new Document("$elemMatch", 
            new Document("apiKey", "yourJobTitle21385087")
                        .append("data", 
            new Document("$in", Arrays.asList("1671531428578")))))))));


MongoCollection<Document> collection = 
mongoTemplate.getCollection(YOUR_COLLECTION_NAME);

AggregateIterable<Document> result = collection.aggregate(documents);

MongoCursor<Document> curson = result.iterator();
while (curson.hasNext()) {
    Document doc = curson.next();
    // This doc is your document
}