CODEWITHSUNDEEP
mongodbspring-boot
Brad Mace
Brad Mace

Reputation: 27886

MongoDB query to find multiple embedded documents

I'm trying to build my first non-trivial query with MongoDB as part of a notification system, and am trying to understand how to check that multiple embedded documents either exist matching my criteria, or don't exist at all. (Also, I'm trying to build the query using the Spring Boot Mongo api.)

I have subscription documents which have criteria maps like this:

{
    ...,
    criteria: [
        { key: "order.companyId", value: "ABC" },
        { key: "order.status", value: "NEW" }
    ]
}

{
    ...,
    criteria: [
        { key: "order.status", value: "NEW" }
    ]
}

{
    ...,
    criteria: [
        { key: "order.companyId", value: "ABC" }
    ]
}

When a new order comes in for company ABC, I want to generate a query which matches all three of the above subscriptions. Seems like I'll need a combination of $and, $elemMatch, and $in, but I can't find a good example of how to piece it all together. Can anyone give me some advice on how to get started with this?

In my initial attempts I've been generating criteria like this: Criteria.where("criteria").elemMatch(Criteria.where("key").is(key).and("value").in(value, null)) but it doesn't seem to like multiple criteria for the same attribute.

Making use of dsharew's advice, I'm now using this method to build up a list of criteria for each key/value pair:

private Criteria equalsOrUnspecified(String key, Object value) {
    Criteria valueMatch = Criteria.where("criteria")
            .elemMatch(Criteria.where("key").is(key).and("value").is(value));
    Criteria notSpecified = Criteria.where("criteria").not().elemMatch(Criteria.where("key").is(key));
    return new Criteria().orOperator(valueMatch, notSpecified);
}

and then combining them using

        q.addCriteria(new Criteria().andOperator(criteria.toArray(new Criteria[criteria.size()])));

but this doesn't seem to be matching anything. Here's an example of one of the queries produced:

{ 
    "isEnabled" : true , 
    "eventType" : "order::positionUpdate" , 
    "$and" : [ 
        { "$or" : [ { "criteria" : { "$elemMatch" : { "key" : "order.bolNumber" , "value" : "51166350"}}} , { "criteria" : { "$not" : { "$elemMatch" : { "key" : "order.bolNumber"}}}}]} ,
        { "$or" : [ { "criteria" : { "$elemMatch" : { "key" : "order.consRefNumber" , "value" : "AGVS"}}} , { "criteria" : { "$not" : { "$elemMatch" : { "key" : "order.consRefNumber"}}}}]} , 
        { "$or" : [ { "criteria" : { "$elemMatch" : { "key" : "order.operationsUser.id" , "value" : "janedoe"}}} , { "criteria" : { "$not" : { "$elemMatch" : { "key" : "order.operationsUser.id"}}}}]} , 
        { "$or" : [ { "criteria" : { "$elemMatch" : { "key" : "order.bookingUser.id" , "value" : "janedoe"}}} , { "criteria" : { "$not" : { "$elemMatch" : { "key" : "order.bookingUser.id"}}}}]} , 
        { "$or" : [ { "criteria" : { "$elemMatch" : { "key" : "order.status" , "value" : "NEW"}}} , { "criteria" : { "$not" : { "$elemMatch" : { "key" : "order.status"}}}}]} , 
        { "$or" : [ { "criteria" : { "$elemMatch" : { "key" : "order.billingCustomer.id" , "value" : "EXQUOTE"}}} , { "criteria" : { "$not" : { "$elemMatch" : { "key" : "order.billingCustomer.id"}}}}]} , 
        { "$or" : [ { "criteria" : { "$elemMatch" : { "key" : "order.bookingCustomer.id" , "value" : "EXBE63A"}}} , { "criteria" : { "$not" : { "$elemMatch" : { "key" : "order.bookingCustomer.id"}}}}]} , 
        { "$or" : [ { "criteria" : { "$elemMatch" : { "key" : "order.id" , "value" : "5849005"}}} , { "criteria" : { "$not" : { "$elemMatch" : { "key" : "order.id"}}}}]} , 
        { "$or" : [ { "criteria" : { "$elemMatch" : { "key" : "companyId" , "value" : "ABC"}}} , { "criteria" : { "$not" : { "$elemMatch" : { "key" : "companyId"}}}}]}
    ]
}

Upvotes: 1

Views: 545

Answers (2)

Brad Mace
Brad Mace

Reputation: 27886

Here's what I ultimately needed. criteriaMap holds attributes of an event, for which we want to find the matching subscriptions. (It has mappings like "order.bookingUser.id" => "janedoe".) If a subscription does not specify a value for a given attribute, then it's treated as a wildcard and matches all values for that attribute.

public Criteria createCriteria(Map<String,?> criteriaMap) {
    List<Criteria> criteria= new ArrayList<>();
    for (Map.Entry<String,Object> entry : criteriaMap) {
        list.add(equalsOrUnspecified(entry.getKey(), entry.getValue()));
    }
    return new Criteria().andOperator(criteria.toArray(new Criteria[criteria.size()]));
}

/**
 * Find a subscription where {@code key} equals {@code value}
 * or where {@code key} is not present.
 */
private void equalsOrUnspecified(String key, Object value) {
    Criteria valueMatch = Criteria.where("criteria")
            .elemMatch(Criteria.where("key").is(key).and("value").is(value));
    Criteria notSpecified = Criteria.where("criteria").not().elemMatch(Criteria.where("key").is(key));
    return new Criteria().orOperator(valueMatch, notSpecified);
}

Upvotes: 0

dsharew
dsharew

Reputation: 10665

I can see you have over simplified the query on the question from what you actually need. From your comments and your note "but it doesn't seem to like multiple criteria for the same attribute.". Yes you cant use one key more than one times.

You should try something like this: 

List<Criteria> criterias = new ArrayList<>();

criterias.add(Criteria.where("criteria.key").is(key1))
criterias.add(Criteria.where("criteria.key").is(key2)) 
criterias.add(Criteria.where("criteria.value").in(values))

if you need the OR of the above criteria you should do: 

Criteria criteria = new Criteria().orOperator(criterias.toArray(new Criteria[criterias.size()]))

If you need the AND of the above queries you should do: 

new Criteria().andOperator(criterias.toArray(new Criteria[criterias.size()]))

Also you can combine some of them with AND and some of them with OR to fit your exact query.

Upvotes: 1

Related Questions