-1

Below is my sample Data set

[
  {
    _id: "B1",
    racks: [
      {
        rackId: 1,
        options: [
          {
            optionId: 1,
            status: "FREE"
          },
          {
            optionId: 2,
            status: "FREE"
          },
          {
            optionId: 3,
            status: "FREE"
          }
        ]
      },
      {
        rackId: 2,
        options: [
          {
            optionId: 1,
            status: "FREE"
          },
          {
            optionId: 2,
            status: "BUSY"
          },
          {
            optionId: 3,
            status: "BUSY"
          }
        ]
      },
      {
        rackId: 3,
        options: [
          {
            optionId: 1,
            status: "BUSY"
          },
          {
            optionId: 2,
            status: "BUSY"
          },
          {
            optionId: 3,
            status: "BUSY"
          }
        ]
      }
    ]
  },
  {
    _id: "B2",
    racks: [
      {
        rackId: 1,
        options: [
          {
            optionId: 1,
            status: "BUSY"
          },
          {
            optionId: 2,
            status: "FREE"
          },
          {
            optionId: 3,
            status: "BUSY"
          }
        ]
      },
      {
        rackId: 2,
        options: [
          {
            optionId: 1,
            status: "FREE"
          },
          {
            optionId: 2,
            status: "BUSY"
          },
          {
            optionId: 3,
            status: "BUSY"
          }
        ]
      },
      {
        rackId: 3,
        options: [
          {
            optionId: 1,
            status: "BUSY"
          },
          {
            optionId: 2,
            status: "BUSY"
          },
          {
            optionId: 3,
            status: "BUSY"
          }
        ]
      }
    ]
  }
  
]

Note : We know "_id" and racks array index during query. In below example search in parent document id "B1" and racks array index 0.

Mongo Query

db.collection.find({
  "_id": "B1",
  "racks.0.options": {
    $all: [
      {
        optionId: 1,
        status: "FREE"
      },
      {
        optionId: 2,
        status: "FREE"
      },
      {
        optionId: 3,
        status: "FREE"
      }
    ]
  }
})

Mongo query response returning document B1, as it matches all conditions

[
  {
    "_id": "B1",
    "racks": [
      {
        "options": [
          {
            "optionId": 1,
            "status": "FREE"
          },
          {
            "optionId": 2,
            "status": "FREE"
          },
          {
            "optionId": 3,
            "status": "FREE"
          }
        ],
        "rackId": 1
      },
      {
        "options": [
          {
            "optionId": 1,
            "status": "FREE"
          },
          {
            "optionId": 2,
            "status": "BUSY"
          },
          {
            "optionId": 3,
            "status": "BUSY"
          }
        ],
        "rackId": 2
      },
      {
        "options": [
          {
            "optionId": 1,
            "status": "BUSY"
          },
          {
            "optionId": 2,
            "status": "BUSY"
          },
          {
            "optionId": 3,
            "status": "BUSY"
          }
        ],
        "rackId": 3
      }
    ]
  }
]

Can you help in writing the same query in Spring using Criteria ?

Tried below one but didn't work as expected

List<Option> list = new ArrayList<>();
list.add(new Option(1, "FREE");
list.add(new Option(2, "FREE");
list.add(new Option(3, "FREE");
List<Criteria> bigCriteria = new ArrayList<Criteria>();
        
        for (Option option : list) {
            bigCriteria.add(
                    Criteria.where("racks.0.options").elemMatch(
                        Criteria.where("optionId").is(option.getOptionId())
                            .and("status").is(option.getStatus().toString())
                    )
                );
        }
        
        Query query = Query.query(
                Criteria.where("_id").is(id).andOperator( bigCriteria.toArray(new Criteria[bigCriteria.size()]))
                                     
      );

// this matches document "B1" for unwanted conditions like this. (AND operation is not happening)
list.add(new Option(1, "FREE");
list.add(new Option(2, "BUSY");
list.add(new Option(3, "FREE");

0 Answers0