MongoDB query for excluding records based on some field value

Question

I have a ticketing system where each ticket id has multiple versions/documents. A ticket is considered closed when its last document has status=closed. I need to find out all the open tickets. Following will be the query in SQL:

select distinct(id) from tickets where id not in (select id from tickets where status = 'closed');

What can be the corresponding query in MongoDB?

Following is the sample of one ticket document.

{
    "id" : 100,
    "timestamp": 1427863300000,
    "status" : "open",
    "description": "abc",
    "comments": "pqr"
    "assigned_to": "xyz"
}

There could be several documents with same ticket id. This is done to maintain the history of tickets. The application allows user to view ticket details in historical mode. The ticket is considered when a document with same id but status "closed" is inserted in the database.

{
    "id" : 100,
    "timestamp": 1429200480000,
    "status" : "closed",
    "description": "abc",
    "comments": "pqr"
    "assigned_to": "xyz"
}

I want to query distinct ticket ids who do not have status=closed in any of their documents.

Answer 1

You need to use the db.collection.distinct() method. To retrieve the "id" for those documents where 'status' is not 'closed' you need to use the $ne operator.

db.collection.distinct( 'id', { 'status': { '$ne': 'closed' } } )