0

I've got a collection of items with the following structure (heavily simplified):

{
    _id: 1,
    transfers: [
        {
            date: 2017,
            person: 'C'
        },
        {
            date: 2012,
            person: 'B'
        },
        {
            date: 2010,
            person: 'A'
        }
    ]
}

I would like to query it by date to find out which person 'owned' the item at a given point in time. I have implemented this already in a javascript app by simply pulling the whole transfers array and iterating over it until

searchDate > transfers[i].date

For example, searching for a date of 2014 would return person B

I feel like it must be possible to achieve this logic in the Mongo query alone and reduce the amount of data I have to send to my application (transfers array can grow very large). I have tried to understand other solutions posted on here but I'm not sure I've found any that specifically apply to my problem and I lack the experience with writing mongo queries to adapt any similar to my needs.

Any help would be much appreciated.

Cheers,

P

Batters
  • 729
  • 1
  • 7
  • 19

1 Answers1

0

I have used MongoDB aggregation framework

Stage1 - Filter the transfers by date

Stage2 - unwind the transfers array

stage3 - project only the person field

db.collection.aggregate([
    {
        '$project': {
            'transfers': {
                '$filter': {
                    'input': '$transfers', 
                    'as': 'transfer', 
                    'cond': {
                        '$eq': [
                            '$$transfer.date', 2017
                        ]
                    }
                }
            }
        }
    }, {
        '$unwind': '$transfers'
    }, {
        '$project': {
            '_id': 0, 
            'person': '$transfers.person'
        }
    }
])
Mani
  • 1,471
  • 1
  • 13
  • 19
  • 1
    The question was *"First array element **greater than** 2014"*. Therefore *"...searching for a date of 2014 would return person B"*. ( Actually person C ) That's not what you responded with. In fact, your code can be simplified to `.find({ "transfers.date": 2017}, { "transfers.$.person": 1 })`. The projection isn't as great, but it's much faster and you can just extract the value in client code. But really it's just not what the question asked. – Neil Lunn Mar 02 '19 at 03:48
  • It's actually first element less than 2014 but the duplicate question you've flagged should do the trick, thanks! – Batters Mar 04 '19 at 10:53