Improving mongo query Performance(using Sub query )

Question

Two documents can have same IMAGE_CONTENT_INSTANCE_HANDLE and state can be BOOKED or RELEASED but I want all image instances handles which are only RELEASED state, Currently I am doing this by firing two queries it introduced performance issues.

{ 
    "state" : "RELEASED"
}

with projection { "imageContentInstance.handle" : 1}

i am iterating through the result which is coming out from this query and firing another query as below and excluding the handles which are also in BOOKED state from adding to the list.So i gets handles only in the RELEASED state

while (cursor.hasNext()) {
ICI ici = objectMapper.readValue(result, ICI_COLLECTION_TYPE_REF);
    String result = JSON.serialize(cursor.next());
    try {
        queryDocument =  { "imageContentInstance.handle" : ici.getImageContentInstance().getHandle() , "state" : "BOOKED"}
        Document bookedDoc = iciDAO.findOne(queryDocument);
        if (null != bookedDoc)
            LOGGER.debug("Calling continue and skipping booked state ");
        continue;
    }
    iciHandles.add(ici.getImageContentInstance().getHandle().toString());
    LOGGER.debug("ImageInstanceHandle is added to the lisr and the lise is "+iciHandles.size());
}

I want to achieve this in a single mongo query as an example query written in sql to increase performance .I really appreciate your comments .

SELECT *
FROM ici i
WHERE i.state = 'RELEASED'
AND NOT EXISTS
  (SELECT * FROM ici ic WHERE ic.handle = i.handle AND ic.state = 'BOOKED'
  );

example : Suppose the documents are as below

{
    "_id" : ObjectId("58c9f524fa8cd6a517cf5ddf"),
    "imageContentInstance" : {
        "handle" : "ICI:1234",
        "key" : null,
        }
    "instanceHandle" : "LCI:RNBM12",
    "state" : "BOOKED",
}

{
    "_id" : ObjectId("58c9f524fa8cd6a517cf5ddf"),
    "imageContentInstance" : {
        "handle" : "ICI:1234",
        "key" : null,
        }
    "instanceHandle" : "LCI:RNBM13",
    "state" : "RELEASED",
}


{
    "_id" : ObjectId("58c9f524fa8cd6a517cf5ddf"),
    "imageContentInstance" : {
        "handle" : "ICI:456",
        "key" : null,
        }
    "instanceHandle" : "LCI:RNBM14",
    "state" : "RELEASED"
}

My query should return the handle of the last document alone .ie, document with the status only with the RELEASED status .i am stuck, I really appreciate your ideas to improve this.

sultania23 · Accepted Answer · 2019-01-15 05:53:04Z

1

From Your question,i understand that you want all state ='Released' ans state!= 'BOOKED' which i think you have written little incorrect.

MongoDB query:

db.inventory.find({'state' : 'RELEASED'}})

Also go through mognodb docs I hope it will help.I am also new to mongodb,if there is an error please make it correct.

edited Jan 15, 2019 at 5:53

answered Jan 14, 2019 at 16:22

sultania23

3223 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Avinash T S Over a year ago

Thanks for your inputs .Suppose the documents are as below document1 --> handle : 1234 state : Booked document2 --> handle : 1234 state : RELEASED document3 --> handle : 4567 state: RELEASED I want to fetch only document 3 . However your query fetches document2 also which i dont want .

sultania23 Over a year ago

db.inventory.distinct( "item.handle", { status: "RELEASED" } ); I hope it will do the trick for you .Please upvote if it helps.

Avinash T S Over a year ago

Sorry :) . It did not work . Since distinct function applied on handle , it fetch one distinct record with RELEASED status . But There should not be any document with this handle with the BOOKED status then only i have fetch the record .

Collectives™ on Stack Overflow

Improving mongo query Performance(using Sub query )

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related