CODEWITHSUNDEEP
mongodbmongodb-query
SomeStudent
SomeStudent

Reputation: 3048

How to write a query to get all documents where field doesn't matches

I am having some trouble writing a query where I want to get all documents where they don't have a single subdocument where its property IsCurrentRevision is true. In other words, only getting documents that don't have a single revision marked as current.

What I have so far is: 

{StartTimeUtc: {$gte: new ISODate('12/14/2019')}, 
EndTimeUtc: {$lte: new ISODate('01/21/2020')}, 
IsDeleted: false, 'MyDocument.IsCurrentRevision': false, 
"MyDocument.1.IsCurrentRevision" : {$exists: false}}

This gets me somewhere, but the issue is that I have to manually query the index. In this case, the above query returns all documents with only 1 revision (so only one element in the sub document array), that doesn't have its revision marked as current. Thus if I wanted to get documents that have 2 revisions, with the second one marked as false as well I would need to update the query to be: 

{StartTimeUtc: {$gte: new ISODate('12/14/2019')}, 
EndTimeUtc: {$lte: new ISODate('01/21/2020')}, 
IsDeleted: false, 'MyDocument.1.IsCurrentRevision': false, 
"MyDocument.2.IsCurrentRevision" : {$exists: false}}

This is obviously tedious, is there a better way? If it helps, I am using Mongo Compass

Edit:

Here is how my document looks like 

{
  _id: Guid,
  TemplateId: guid,
  StartTimeUtc: DateTime,
  EndTimeUtc: DateTime
  ..
  ..
  SoapNoteRevisions: Array {
      _id: Guid,
      IsCurrentRevision: boolean,
      ..
      ..
    }
}

I got rid of the unneeded bits as the document itself has a fair bit of fields within the revision as well (another sub array)

Upvotes: 2

Views: 173

Answers (1)

whoami - fakeFaceTrueSoul
whoami - fakeFaceTrueSoul

Reputation: 17925

If you need documents where MyDocument elements/objects doesn't have IsCurrentRevision : true, try below query :

db.collection.find({'MyDocument.IsCurrentRevision': {$ne : true}})

If using aggregation :

db.collection.aggregate([{$match : {'MyDocument.IsCurrentRevision': {$ne : true}}}])

Collection Data :

/* 1 */
{
    "_id" : ObjectId("5e27282ad02e05b69498b814"),
    "MyDocument" : [ 
        {
            "IsCurrentRevision" : false
        }, 
        {
            "IsCurrentRevision" : false
        }, 
        {
            "IsCurrentRevision" : false
        }
    ]
}

/* 2 */
{
    "_id" : ObjectId("5e272839d02e05b69498b947"),
    "MyDocument" : [ 
        {
            "IsCurrentRevision" : true
        }, 
        {
            "IsCurrentRevision" : true
        }, 
        {
            "IsCurrentRevision" : true
        }
    ]
}

/* 3 */
{
    "_id" : ObjectId("5e272846d02e05b69498ba5c"),
    "MyDocument" : [ 
        {
            "IsCurrentRevision" : false
        }, 
        {
            "IsCurrentRevision" : true
        }, 
        {
            "IsCurrentRevision" : false
        }
    ]
}

Result :

/* 1 */
{
    "_id" : ObjectId("5e27282ad02e05b69498b814"),
    "MyDocument" : [ 
        {
            "IsCurrentRevision" : false
        }, 
        {
            "IsCurrentRevision" : false
        }, 
        {
            "IsCurrentRevision" : false
        }
    ]
}

Upvotes: 3

Related Questions