Using aggregate for data consistency testing, comparing a value to a sum of values inside an array ?

[Matthieu Rigal]

Hi all,

I've tried various paths, using project,  group, match, unwind and a bit of everything, but without success until now... If someone has a hint on how to do it, it would be great. I have basically the following document structure:

{

    "id": "ID",

    "total": 200,

    "list": [

        {

            "id": "ID1",

            "integer": 50

        },

        {

            "id": "ID2",

            "integer": 100

        }

    ]

}

I want to get as output the ids of the document where the total is not equal to the sum of the integer of the list. Sounds quite easy, but I couldn't find docs describing that case and my tries were all unsuccessful...

Thanks in advance,

Matthieu

[Matthieu Rigal]

To give an example, I would have expected the following pipeline to work: (Note that I had simplified the model, total and integer have one nested level)

 [{'$unwind': '$list'},

 {'$group': {'_id': '$id',

   'total': {'$sum': '$total.amount'},

   'sum': {'$sum': '$list.integer.amount'}}},

 {'$match': {'total': {'$ne': '$sum'}}}]

Unfortunately, it returns all the results, even if then, when displaying one, total and sum are indeed equal...

This is running on Mongo 2.6.1

Best,

Matthieu

[Matthieu Rigal]

Nobody on this ? Because it is impossible, stupid, ridiculous or not really feasible ?

Thanks in advance for any advice,

Matthieu

[Joe Wagner]

Looks like you have misunderstood the use of the `$ne` operator.  Here are some mongo commands that demonstrate the functionality you are after

https://gist.github.com/JoeWagner/dfa165db3ac6599caa50 

[Asya Kamsky]

Joe's sample code is correct - you cannot do anything in match that you cannot do in regular find query (such as comparing two fields within a document) so you have to use $project to create a new field to represent whether the sums are equal, then you can filter on that new field.

--
You received this message because you are subscribed to the Google Groups "mongodb-user"
group.
 
For other MongoDB technical support options, see: http://www.mongodb.org/about/support/.
---
You received this message because you are subscribed to the Google Groups "mongodb-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mongodb-user...@googlegroups.com.
To post to this group, send email to mongod...@googlegroups.com.
Visit this group at http://groups.google.com/group/mongodb-user.
To view this discussion on the web visit https://groups.google.com/d/msgid/mongodb-user/882b7eb9-56f0-4f86-a655-043187062664%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Matthieu Rigal]

Hi Joe and Asya,

Thanks for your help on this. I had read that $ne was using a list, but I was thinking it was as flexible as for regular queries ! :-/ Ok, lessons learned and it works well this way using project !

I still think a similar example in the tutorial, i.e. to test data consistency, would benefit to more people than just me.

Thanks again and have a nice weekend !

Matthieu

PS: just for the record if other people read, the following works:

 [{'$unwind': '$list'},

 {'$group': {'_id': '$id',

   'total': {'$sum': '$total.amount'},

   'sum': {'$sum': '$list.integer.amount'}}},

{"$project": {"id": "$_id",

                  "invalid": {"$ne": ['$sum', '$total']}, 

                  "sum": "$sum",

                  "total": "$total"}},

{'$match': {'invalid': True}}]