0

I am trying to figure out how best to find documents that contain an array of sub documents in the array named statusHistory where either of these 2 conditions exist:

  1. The statusHistory array is empty (no sub docs)
  2. The statusHistory array only contains any number of sub documents where all the sub documents have a deletedOn property with an ISO date set; stated differently, any and all sub docs must have a deletedOn; these are "soft deletes"

In this example, the query should only return the unit docs for the following matching units:

  • 200 (all sub docs have deletedOn)
  • 300 (all sub docs have deletedOn)
  • 400 (statusHistory array is empty)
  • 500 (statusHistory array is empty)

In this example, the following docs shouldn't match:

  • 100 (one sub doc exists that doesn't have a deletedOn)
  • 600 (not all of the sub docs contain deletedOn, in this case none of them do)
{
   "unit":"100",
   "statusHistory":[
      {
         "action":"Move-In",
         "deletedOn":ISODate("2017-02-08T18:27:52.938+0000")
      },
      {
         "action":"Assignment"
      }
   ]
},
{
   "unit":"200",
   "statusHistory":[
      {
         "action":"Move-In",
         "deletedOn":ISODate("2017-06-08T21:27:52.938+0000")
      },
      {
         "action":"Assignment"
         "deletedOn":ISODate("2017-05-08T21:27:52.938+0000")
      }
   ]
},
{
   "unit":"300",
   "statusHistory":[
      {
         "action":"Move-In",
         "deletedOn":ISODate("2017-06-08T21:27:52.938+0000")
      },
      {
         "action":"Assignment"
         "deletedOn":ISODate("2017-05-08T21:27:52.938+0000")
      },
      {
         "action":"Move-In",
         "deletedOn":ISODate("2017-04-08T21:27:52.938+0000")
      },
      {
         "action":"Assignment"
         "deletedOn":ISODate("2017-03-08T21:27:52.938+0000")
      }
   ]
},
{
   "unit":"400",
   "statusHistory":[]
},
{
   "unit":"500",
   "statusHistory":[]
},
{
   "unit":"600",
   "statusHistory":[
      {
         "action":"Move-In",
      },
      {
         "action":"Assignment"
      }
   ]
}
joshblair
  • 877
  • 1
  • 6
  • 14
  • You can use `db.collection_name.find({ "statusHistory": { $not: { "$elemMatch": { $nor: [{ "deletedOn": {$exists:true} }] } } } });`See this for explanation.https://stackoverflow.com/questions/43472047/querying-array-of-objects-in-mongodb-using-only-find-method/43565176#43565176 – s7vr Sep 29 '17 at 19:42
  • @Veeram thanks for the proposed solution and the reference link. I read through your explanation and tried the query you dropped in above. I am getting invalid results however. I seem to also get docs that contain statusHistory sub docs with deletedOn = null. Here is similar query based on your work with a small tweak that seems to get the job done: db.units.find({ "statusHistory": { $not: { "$elemMatch": { $nor: [{ "deletedOn": {$type:"date"} }] } } } }); – joshblair Sep 29 '17 at 20:37
  • Here I am using the $type to filter out any entries that have the deletedOn property that is set to null. This seems like a viable solution but maybe I am missing something. I suppose that another clause could be added to do something similar to your proposed solution. Thanks for your help, it got me to my current solution. Do you want to formally drop in an answer here so I can mark it as the solution? – joshblair Sep 29 '17 at 20:42
  • This query also seems to achieve the same result: db.units.find({ "statusHistory": { $not: { "$elemMatch": { $nor: [{ "deletedOn": {$ne: null } } ] } } } }); The whole double negative thing blows my mind. Thanks so much for the help there. I sure didn't see that myself... – joshblair Sep 29 '17 at 21:38

0 Answers0