CODEWITHSUNDEEP
mongodbmeteor
flimflam57
flimflam57

Reputation: 1334

How to return all documents that have a certain value in an array in mongodb

I have a collection of documents with the following structure in Mongo using Meteor:

 {
 ...
 Name: 'Jane Doe',
 Finances: { 
   Owed: 0, 
   Due: 0, 
   Paid: [
     [400, 'Oct 2015'], 
     [300, 'Jan 2016'], 
     [500, 'Mar 2016']
   ]
 },
 Address: '123 Mains Street',
 ...
 }

I'd like to return all the documents that have a payment value made in 2016. So, in this example, the document above would indeed be returned since it has at least one sub-array [, 'Jan 2016'] within the 'Paid' property. How could I set up the query in Mongo?

I tried something like:

Collection.find({ 'Finances.Paid[0]': { $regex: '2016' } });

But I'd need to go through all the inner arrays within 'Paid' to check. Can I do this strictly in Mongo?

Upvotes: 1

Views: 789

Answers (2)

sergiuz
sergiuz

Reputation: 5539

Is difficult with this schema design.

Mongo is not designed to query well, a list which stores lists that stores different types of values i.e. [400, 'Oct 2015']. Better will be for queries to design the Finances field like this:

Finances: [
    { 'a': 400, 'b': 'Oct 2015'},
    { 'a': 300, 'b': 'Oct 2016'},
    { 'a': 500, 'b': 'Oct 2016'}
]

Than you can do queries like:

db.Collection.find({ "Finances.Paid.b": { $regex:'2016'}})

With your schema you can do it with aggregation:

db.Collection.aggregate([
    {
        $unwind: '$Finances.Paid'},
    {
        $match: {'Finances.Paid':{ $regex: '2016' }}
    },
    {
        $group: {
            _id:'$_id', 
            Name: {'$first': '$Name'},
            Address: {'$first': '$Address'},
            Finances: {'$first': '$Finances'}
        }
    },
]);

query result:

{
    "_id" : ObjectId("580fb7d6c37ebe91b0f943df"),
    "Name" : "Jane Doe",
    "Address" : "123 Mains Street",
    "Finances" : {
        "Owed" : 0,
        "Due" : 0,
        "Paid" : [
            300,
            "Jan 2016"
        ]
    }
}

That will return all the documents that have a payment value made in 2016, the drawback is that returns only one payment from 2016, the first one that it founds.

Upvotes: 2

Mikkel
Mikkel

Reputation: 7777

I haven't tried this code with your data, but you should be able to do it with something like this

Collection.find({ Finances: {$elemMatch: {Paid: {$regex:'2016'}}}})

It should scan the array of Paid elements

Edit: Try this

Collection.find({ "Finances.Paid": {$elemMatch: { $regex:'2016'}}})

See https://docs.mongodb.com/v3.2/reference/operator/query/elemMatch/ for an explanation of $elemMatch

Upvotes: 0

Related Questions