Get part of array value

Question

I have an object in the database that looks like:

{
  'name':  'foo',
  'table':
  [
    {
      'date' : ISODate("2000-01-13T23:00:00Z")
    },
    {
      'date' : ISODate("2000-01-14T23:00:00Z")
    },
    {
      'date' : ISODate("2000-01-15T23:00:00Z")
    },
    {
      'date' : ISODate("2000-01-16T23:00:00Z")
    },
    {
      'date' : ISODate("2000-01-17T23:00:00Z")
    }
  ]
}

I wish to query for the following result:

{
  'name':  'foo',
  'table':
  [
    {
      'date' : ISODate("2000-01-15T23:00:00Z")
    },
    {
      'date' : ISODate("2000-01-16T23:00:00Z")
    }
  ]
}

So I'm looking for a way to extract the children that is between two different dates.

So far I have tried the following:

db.stock.find({'table.date' : {$gte : '2000-01-15', $lt : '2000-01-17'}});

db.stock.find({'table.date' : {$gte : new Date('2000-01-15'), $lt : new Date('2000-01-17')}});

db.stock.find({'table.date' : {$gte : '2000-01-15T23:00:00Z', $lt : '2000-01-17T23:00:00Z'}});

db.stock.find({'table.date' : {$gte : ISODate('2000-01-15T23:00:00Z'), $lt : ISODate('2000-01-17T23:00:00Z')}});

db.stock.find({'table.date' : {$gte : new Date('2000-01-15T23:00:00Z'), $lt : new Date('2000-01-17T23:00:00Z')}});

Is this possible to do? If so, how can it be resolved?

score 1 · Accepted Answer · edited May 23 '17 at 12:05

It is not possible for .find() to "filter" the returned elements of an array by itself. You can use the positional $ operator with a match condition to "project" one matching element only ( see the documentation ).

The reason here is that the query here is not matching the array elements, but rather the document that "contains" the matching array elements.

But to "filter" elements to just the ones you want, you need to use aggregate:

db.stock.aggregate([
  // Matching first is a good idea to filter the documents that contain
  // the matching elements
  { "$match": { 
      "table.date": { 
          "$gte": new Date("2000-01-15"), 
           "$lt": new Date("2000-01-17")
      }
   }},              

   // Unwind the array, this "de-normalizes"
   { "$unwind": "$table" },

  // Now use the match to actually "filter" results in the array
  { "$match": { 
      "table.date": { 
          "$gte": new Date("2000-01-15"), 
           "$lt": new Date("2000-01-17")
      }
   }},              

   // Group the results back by a "key", probably the document
   { "$group": {
       "_id": "$_id",
       "table": { "$push": "$table" }
   }}

])

Also worth noting from your examples that you need to use dates as actual date types and not "strings". This is also true in other language implementations, where the native date type will be sent as a BSON date to the MongoDB server where it can be compared internally.

For more information on returning the original document form with this type of query, see here.

Get part of array value

1 Answers1