CODEWITHSUNDEEP
mongodbmongoosemongodb-query
ronkot
ronkot

Reputation: 6367

Get MongoDB documents where all subdocuments match criteria

I'm Mongo newbie, so this may be a trivial question. Please bear with me :)

My Mongo documents can be logically grouped to valid and archive documents. Document is valid, if any of its revisions.validTo date field is either null or greater than today. Otherwise, document is in archive.

So the collection structure is roughly:

[
    {
        _id: 'valid-1',
        info: 'This is valid document because it has a revision with null validTo value',
        revisions: [
            {
                validTo: ISODate("2014-01-01T00:00:00.000Z")
            },
            {
                validTo: null
            }
        ]
    },
    {
        _id: 'valid-2',
        info: 'This is valid document because it has a revision with validTo value > today',
        revisions: [
            {
                validTo: ISODate("2014-01-01T00:00:00.000Z")
            },
            {
                validTo: ISODate("2050-01-01T00:00:00.000Z")
            }
        ]
    },
    {
        _id: 'archive-1',
        info: 'This document is in archive as all revisions have validTo < today',
        revisions: [
            {
                validTo: ISODate("2014-01-01T00:00:00.000Z")
            }
        ]
    }
]

I can easily query the valid documents with

db.getCollection('collection').find({
  $or: [
    { 'revisions.validTo': { $gte: new Date() } },
    { 'revisions.validTo': null }
  ]
})

which returns [valid-1, valid-2], but I can't find out how to query for the archive documents to get result [archive-1]. For example

db.getCollection('collection').find({
  'revisions.validTo': { $lte: new Date() }
})

returns a document if it has any revision with validTo < today. Instead of that I would like to get document only if all of its revisions.validTo are < today.

As the collection is exactly divided to these two groups, I could probably also get the archive group by querying the valid group and then taking difference between whole collection and valid documents set. This could maybe be done with Mongo aggregation framework, but I couldn't find out how to do that yet.

So, any help with constructing the archive documents query is warmly welcome!

(I'm also using Mongoose, if that could help somehow..?)

Upvotes: 2

Views: 307

Answers (2)

Kevin Smith
Kevin Smith

Reputation: 14466

You'll first need to re-model you're document so that the dates are ISODates so you can query operations on them

[
    {
        info: 'This is valid document because it has a revision with null validTo value',
        revisions: [
            {
                validTo: ISODate("2014-01-01T00:00:00.000Z")
            },
            {
                validTo: null
            }
        ]
    },
    {
        info: 'This document is in archive as all revisions have validTo < today',
        revisions: [
            {
                validTo: ISODate("2014-01-01T00:00:00.000Z")
            }
        ]
    }
]

Then you can use the following query:

db.docs.find({ 'revisions.validTo': { $lte: new Date() }, 'revisions.validTo': { $ne: null } } ).pretty()

To return 

{
        "_id" : ObjectId("584a9a9212767a1fecf0e39f"),
        "info" : "This document is in archive as all revisions have validTo < today",
        "revisions" : [
                {
                        "validTo" : ISODate("2014-01-01T00:00:00Z")
                }
        ]
}

Upvotes: 0

Aruna
Aruna

Reputation: 12022

Can you try with $and, $not and $ne operators as below,

db.getCollection('collection').find({
  $and: [
    { 'revisions.validTo': { $lte: new Date() } },
    { 'revisions.validTo': { $not: { $gte: new Date() } } },
    { 'revisions.validTo': { $ne: null } }
  ]
})

Upvotes: 3

Related Questions