Basically, I have an array of objects with nested arrays of objects. I want to query the data based on the "userID" and sum of "flashcardReversed.value" "flashcardReversed.count".

The user will provide the userID and a (value count) value that is desired.

Here is my data:

[
  {
    "_id": "608642db80a36336946620aa",
    "userID": "user1",
    "title": "title2",
    "flashcardReversed": [
      {
        "_id": "608d5b290e635ece6828141X",
        "front": "2front",
        "back": "2back",
        "value": 1,
        "count": 2
      },
      {
        "_id": "608t5b290e635ece6828141Y",
        "front": "2frontReversed",
        "back": "2backReversed",
        "value": 2,
        "count": 3
      },
      {
        "_id": "608a5b31a3f9806de253726X",
        "front": "2front2",
        "back": "2back2",
        "value": 3,
        "count": 4
      },
      {
        "_id": "608a5b31a3f9806de253726Y",
        "front": "2frontReversed2",
        "back": "2backReversed2",
        "value": 4,
        "count": 5
      }
    ]
  },
  {
    "_id": "608642db80a36336946620aa",
    "userID": "user1",
    "title": "title3",
    "flashcardReversed": [
      {
        "_id": "608d5b290e635ece6828142X",
        "front": "2front",
        "back": "2back",
        "value": 12,
        "count": 6
      },
      {
        "_id": "608t5b290e635ece6828143Y",
        "front": "2frontReversed",
        "back": "2backReversed",
        "value": 21,
        "count": 7
      },
      {
        "_id": "608a5b31a3f9806de253727X",
        "front": "2front2",
        "back": "2back2",
        "value": 34,
        "count": 8
      },
      {
        "_id": "608a5b31a3f9806de253729Y",
        "front": "2frontReversed2",
        "back": "2backReversed2",
        "value": 42,
        "count": 9
      }
    ]
  },
  {
    "_id": "608642db80a36336946620aa",
    "userID": "user2",
    "title": "title4",
    "flashcardReversed": [
      {
        "_id": "608d5b290e635ece6828131X",
        "front": "2front",
        "back": "2back",
        "value": 41,
        "count": 10
      },
      {
        "_id": "608t5b290e635ece6828161Y",
        "front": "2frontReversed",
        "back": "2backReversed",
        "value": 54,
        "count": 11
      },
      {
        "_id": "608a5b31a3f9806de253526X",
        "front": "2front2",
        "back": "2back2",
        "value": 63,
        "count": 12
      },
      {
        "_id": "608a5b31a3f9806de253326Y",
        "front": "2frontReversed2",
        "back": "2backReversed2",
        "value": 29,
        "count": 13
      }
    ]
  }
]

For example the query should like:

{
  "userID": {"$eq": provided_user_id}
  {"flashcardReversed.value"   "flashcardReversed.count"}: {"$eq": provided_value_plus_count}
    }

Is it possible to achieve this using pymongo?

CodePudding user response：

Assuming that the uniqueness here is userID title and we want to agg across both the flashcardReversed field and multiple title i.e. getting to one number for a user, then this is a solution.

var targetUser = "user1";
var targetAmt = 163;

c = db.foo.aggregate([
    {$match: {"userID": targetUser}} // will fetch one OR MORE records...                                       
    ,{$addFields: {
        X: {$reduce: {
            input: "$flashcardReversed",
            initialValue: 0,
                // $$value is the running result of the reduce operation ($add)
                // $$this is the current object from the array; it just so
                // happens that a field in your object is named "value" so
                // do not confuse the two.  The expression below translates
                // to:  n = n   oneitem.value   oneitem.count
                in:{$add: [ "$$value", "$$this.value", "$$this.count" ] }
        }}
    }}

    // Now, add up X over multiple docs.  We can group by null because we                                       
    // know ONLY docs where userID = targetUser are coming through:                                             
    ,{$group: {_id: null, n: {$sum: "$X"}}}

    // ...and match against input:                                                                              
    ,{$match: {n: targetAmt}}                                                                                 
]);

The solution above demonstrates the individual $reduce and $group behaviors but these can be combined. Also, let's explore what would need to be done to fetch multiple users for a target amount:

var targetUser = ["user1","user2"];
var targetAmt = 163;

c = db.foo.aggregate([
    {$match: {"userID": {$in: targetUser}}}                                 

    // Combine the $group and $reduce together.  Saves a stage!  Also, since                                    
    // more than one userID can come through, we must change _id to group                                       
    // by $userID instead of null:                                                                              
    ,{$group: {_id: "$userID", n: {$sum: {$reduce: {
            input: "$flashcardReversed",
            initialValue: 0,
                in:{$add: [ "$$value", "$$this.value", "$$this.count" ] }
    }} }  }}

    // Probably want to use >= or <= instead of exact match sometimes                                           
    // so here is an example of that:                                                                           
    ,{$match: {$expr: {$gte: ["$n", targetAmt]}} }
]);

{ "_id" : "user1", "n" : 163 }
{ "_id" : "user2", "n" : 233 }

CodePudding user response：

Query

• match on userid
• filter the array and keep only members that (= ( value count) 3)
• you can replace the 3 with the variable or any number
• if after filter the array is empty, document is rejected
• in final results we keep in the array only those that pass, and only the documents that have at least 1 member that pass

*not sure if you want this

Test code here

aggregate(
[{"$match": {"userID": {"$eq": "user1"}}},
 {"$set":  
   {"flashcardReversed": 
     {"$filter": 
       {"input": "$flashcardReversed",
        "cond": 
        {"$eq": [{"$add": ["$$this.value", "$$this.count"]}, 3]}}}}},
 {"$match": {"$expr": {"$ne": ["$flashcardReversed", []]}}}])