Query on the last element of an array in MongoDB when the array size is stored in a variable

Question

I have a dataset in MongoDB and this is an example of a line of my data:

{ "conversionDate": "2016-08-01",
  "timeLagInDaysHistogram": 0,
  "pathLengthInInteractionsHistogram": 4,
  "campaignPath": [ 
      {"campaignName": "name1", "source": "sr1", "medium": "md1", "click": "0"},
      {"campaignName": "name2", "source": "sr1", "medium": "md1", "click": "0"},
      {"campaignName": "name1", "source": "sr2", "medium": "md2", "click": "1"},
      {"campaignName": "name3", "source": "sr1", "medium": "md3", "click": "1"} 
  ],
  "totalTransactions": 1,
  "totalValue": 37.0,
  "avgCartValue": 37.0
}

(The length of campaignPath is not constant, so each line can have a different amount of elements.

And I want to find elements that matches "source = sr1" in the last element of campaignPath.

I know I can't do a query with something like

db.paths.find(
    {
        'campaignPath.-1.source': "sr1"
    }
)

But, since I have "pathLengthInInteractionsHistogram" stored which is equal to the length of campaignPath lenght, can't I do something like:

db.paths.find(
    {
        'campaignPath.$pathLengthInInteractionsHistogram.source': "sr1"
    }
)

@JohnnyHK I want the whole document, since I need the date and other information. — Martin Mas, Aug 18 '16 at 15:32

JohnnyHK · Accepted Answer · 2016-08-18T19:11:39.680

Starting with MongoDB 3.2, you can do this with aggregate which provides the $arrayElemAt operator which accepts a -1 index to access the last element.

db.paths.aggregate([
  // Project the original doc along with the last campaignPath element
  {$project: {
    doc: '$$ROOT',
    lastCampaign: {$arrayElemAt: ['$campaignPath', -1]}
  }},
  // Filter on the last campaign's source
  {$match: {'lastCampaign.source': 'sr1'}},
  // Remove the added lastCampaign field
  {$project: {doc: 1}}
])

In earlier releases, you're stuck using $where. This will work but has poor performance:

db.paths.find({
    $where: 'this.campaignPath[this.pathLengthInInteractionsHistogram-1].source === "sr1"'
})

which you could also do without using pathLengthInInteractionsHistogram:

db.paths.find({$where: 'this.campaignPath[this.campaignPath.length-1].source === "sr1"'})

Thank you! I'll try this when we upgrade our db version (we are running in 3.0). Just "for science". Can this be done in 3.0? — Martin Mas, Aug 18 '16 at 15:51

Query on the last element of an array in MongoDB when the array size is stored in a variable

1 Answers1