CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
Juan Piza Ferra
Juan Piza Ferra

Reputation: 120

Sort nested array using $lte and $gte

So i have a document that look like this:

{   
    "prop1" : [ 
        {
            "value" : [ 
                {
                    "start" : "09:00",
                    "end" : "23:59"
                }, 
                {
                    "start" : "00:00",
                    "end" : "04:00"
                }
            ]
        }
    ]
}

And i want to query the value array.

This is the query I'm trying:

db.getCollection('someCollection').find({ 
    'prop1': {
        $elemMatch: {
            'value': {
                $elemMatch: {
                    start: { $lte: '10:00' } ,
                    end: { $gte: '10:00' }
                }
            }
        }
    }
})

What's wrong here or how is the right way to query on nested object in nested array of nested array?

Thanks ahead!

Update

The output i need is the same document that the prorp array has any object that the value array has at least one element that matches start and end 

{   
        "prop1" : [ 
            {
                "value" : [ 
                    {
                        "start" : "09:00",
                        "end" : "23:59"
                    }, 
                    {
                        "start" : "00:00",
                        "end" : "04:00"
                    }
                ]
            }
        ]
    }

Upvotes: 1

Views: 400

Answers (2)

Ashh
Ashh

Reputation: 46461

You can use below aggregation using $map and $filter

db.collection.aggregate([
  { "$project": {
    "prop1": {
      "$map": {
        "input": "$prop1",
        "as": "p",
        "in": {
          "value": {
            "$filter": {
              "input": "$$p.value",
              "as": "v",
              "cond": {
                "$and": [
                  { "$lte": ["$$v.start", "10:00"] },
                  { "$gte": ["$$v.end", "10:00"] }
                ]
              }
            }
          }
        }
      }
    }
  }}
])

Output

[
  {
    "prop1": [
      {
        "value": [
          {
            "end": "23:59",
            "start": "09:00"
          }
        ]
      }
    ]
  }
]

Upvotes: 1

Maxim
Maxim

Reputation: 2391

The better way to have schema like this:

"prop1" : [ 
        {
            "value" : [ 
                {
                    "start" : {
                        "h" : 8,
                        "m" : 0
                    },
                    "end" : {
                        "h" : 23,
                        "m" : 59
                    }
                }, 
                {
                    "start" : {
                        "h" : 1,
                        "m" : 0
                    },
                    "end" : {
                        "h" : 4,
                        "m" : 0
                    }
                }
            ]
        }
    ]

AND use query like this

db.yourCollection.find( {"prop1.value": 
    {
        $elemMatch: { 
            'start.h': { $lte: 8 }, 'start.m': { $lte: 0 }, 'end.h': { $gte: 3 }, 'end.m': { $gte: 59 }
        }
    } 
})

Upvotes: 0

Related Questions