Get part of array value

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?

Answers


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.


Need Your Help

How do I create a responsive navigation bar?

javascript jquery html css responsive-design

I am having trouble making my website responsive, I have done it for all of the pages and just need to finish off with my navigation.

It's Possible To Run Windows Mobile 5 SDK In Visual Studio 2008?

visual-studio-2008 windows-mobile windows-7 sdk windows-mobile-5.0

I was seeing about Windows Mobile 5 SDK at Microsoft Download Center and I also see some requirements to run it: