Updatemany for each document, but for each document, set value in just one nested object randomly

Hi everyone,

I’m fascinated, studying MongoDB. But still struggling with query sintaxes and pipelines. I’m implementing it a project. Almost everything I was able to solve by myself, but for a harder task, tried for many hour to code the following query, but with no success. So I came here to ask for help. I will give the details:

  1. In a quiz game, players will get a daily question to answer.
  2. Server will assign a random question to each players everyday in a cron-job function.
  3. Each player document have a nested object with all question status on it’s document.
  4. As a condition, It need to filter all questions with “not-attempted” status from each player, pick randomly one question and change it’s status to “assigned”.
  5. Need to run this task for all players, but just randomly update one object item status to “assigned”.

When I began to build this query, it was working, but when I try to use any operator or subfunction in pipeline(count,sort,rand,sample etc), it returns me a sort of errors. Maybe syntax, or maybe there a simpler way to do this, idk…

Here is a document structure:

{
  "_id": "633360536b4cab132e2fc218",
  "username": "Alberto Silva",
  "role": "player",
  "questionList": [
    {
      "questionid": "A0",
      "time": null,
      "answered": null,
      "score": null,
      "status": "not-attempted",
      "startDate": null,
      "respondedDate": null
    },
    {
      "questionid": "A1",
      "time": null,
      "answered": null,
      "score": null,
      "status": "not-attempted",
      "startDate": null,
      "respondedDate": null
    },
    {
      "questionid": "A2",
      "time": null,
      "answered": null,
      "score": null,
      "status": "not-attempted",
      "startDate": null,
      "respondedDate": null
    },
  ],
  "team": "0"
}

And here is the current buggy / incomplete query:

db.users.updateMany(
    {  
		role: "player"
	}, 
    [
	{ 
		$set: {"questionList.$[tocount].availablequestions": { $count: {"$questionList.$[tocount].questionList" : "not-attempted"} }} 
	},
	{ 
		$set: {"questionList.$[index].status": "assigned"} 
	},
	],
	{
      arrayFilters: [
        {
          "elem.tocount": {
            $eq: "not-attempted"
          },
		  "index._(somehow_get_one_pipelinearray_index_randomly)": {
            $floor: { 
						$multiply: [ { $rand: {} }, "$questionList.availablequestions" ] 
				} 
          },
        } 
      ],
    }
	
)

Can anyone give me a light on how to proceed on it?
If someone could give me insights on how to do that, It would be appreciated.

Thanks

Hi,

Some update here,
I’ve changed my strategy a little, by trying to do this task with aggregate() instead of updateMany() & pipelines. By grouping, isolating and randomizing these nested data, temporary adding fields and then choosing one “questionid” for each player and then, merge it’s output with questionList.

Well, I guess there are more effective / clean ways to do that, but here it goes:

Sample data:

[
  {
    "_id": "633360536b4cab132e2fc277",
    "username": "Alberto Silva",
    "role": "player",
    "questionList": [
      {
        "questionid": "A0",
        "time": null,
        "answered": null,
        "score": null,
        "status": "not-attempted",
        "startDate": null,
        "respondedDate": null
      },
      {
        "questionid": "A1",
        "time": null,
        "answered": null,
        "score": null,
        "status": "not-attempted",
        "startDate": null,
        "respondedDate": null
      },
      {
        "questionid": "A2",
        "time": null,
        "answered": null,
        "score": null,
        "status": "not-attempted",
        "startDate": null,
        "respondedDate": null
      },
      
    ],
    "team": "3"
  },
  {
    "_id": "633360536b4cab132e2fc218",
    "username": "Joana Oliveira",
    "role": "player",
    "questionList": [
      {
        "questionid": "A0",
        "time": null,
        "answered": null,
        "score": null,
        "status": "not-attempted",
        "startDate": null,
        "respondedDate": null
      },
      {
        "questionid": "A1",
        "time": null,
        "answered": null,
        "score": null,
        "status": "not-attempted",
        "startDate": null,
        "respondedDate": null
      },
      {
        "questionid": "A2",
        "time": null,
        "answered": null,
        "score": null,
        "status": "not-attempted",
        "startDate": null,
        "respondedDate": null
      },
      
    ],
    "team": "0"
  },
  {
    "_id": "633360536b4cab132e2fc215",
    "username": "Renato Silvestre",
    "role": "player",
    "questionList": [
      {
        "questionid": "A0",
        "time": null,
        "answered": null,
        "score": null,
        "status": "not-attempted",
        "startDate": null,
        "respondedDate": null
      },
      {
        "questionid": "A1",
        "time": null,
        "answered": null,
        "score": null,
        "status": "not-attempted",
        "startDate": null,
        "respondedDate": null
      },
      {
        "questionid": "A2",
        "time": null,
        "answered": null,
        "score": null,
        "status": "failed",
        "startDate": null,
        "respondedDate": null
      },
      
    ],
    "team": "0"
  },
  
]

And here it’s my current progress:

db.collection.aggregate([
  {
    "$addFields": {
      "availableQuestions": "$questionList"
    }
  },
  {
    $unwind: "$availableQuestions"
  },
  {
    $group: {
      _id: "$_id",
      name: {
        $first: "$username"
      },
      availableQuestions: {
        $push: {
          $cond: {
            if: {
              $eq: [
                "$availableQuestions.status",
                "not-attempted"
              ]
            },
            then: "$availableQuestions.questionid",
            else: "$$REMOVE"
          },
          
        }
      },
      availableCount: {
        $sum: {
          $cond: {
            if: {
              $eq: [
                "$questionList.status",
                "not-attempted"
              ]
            },
            then: 1,
            else: "$$REMOVE"
          }
        }
      },
      
    },
    
  },
  {
    $group: {
      _id: "$_id",
      name: {
        $first: "$name"
      },
      questionList: {
        $push: {
          questionid: {
            $arrayElemAt: [
              "$availableQuestions",
              {
                $round: {
                  $multiply: [
                    {
                      $rand: {}
                    },
                    {
                      $subtract: [
                        {
                          $add: "$availableCount"
                        },
                        1
                      ]
                    }
                  ]
                }
              }
            ]
          },
          status: "assigned"
        }
      }
    }
  }
])

Test link here

Last part I’m working on is to merge that output in questionList, to update this field to each user without screw up current data.

If someone could give me a light on that, or have a more effective way to do this, I would appreciate it.

Hi @Fabio_Iwano and welcome to the community!!

The feature to randomly select an array element in a document is currently not available in MongoDB. However, you can select a document using the $sample.

Currently, to select a random element, one way would be to use $function in the following way:

db.test.aggregate([
    {$set: {
        questionList: {
            $function: {
                body: function(items) {
                    let filtered = items.filter(x => x.status == 'not-attempted')
                    if (filtered.length == 0) { return items }
                    let picked = Math.floor(Math.random()*filtered.length);
                    let idx = items.findIndex(z => z.questionid == filtered[picked]['questionid'])
                    items[idx]['status'] = 'assigned'
                    return items
                },
                args: ['$questionList'],
                lang: 'js'
            }
        }
    }}
])

Please note that this code is untested and serves as an illustration only, so it may not do what you need it to do.

However, the easiest way to do this currently is perhaps doing the operation on the application side and push the resulting changes to the database

I would also like to mention that if the primary purpose of the collection is to select a random question, perhaps modifying the schema design to be one question per document would be easier in the long run, since you can use $sample to do the random selection, and you would not need to maintain a complex aggregation pipeline.
As this would make the use of $sample to select and hence readability and maintenance of the complex aggregation could be avoided.

Let us know if you have any thoughts on the same.

Best Regards
Aasawari

2 Likes

I would like to propose something completely different. Something that does not answer your question. But something that might simplifies your problem.

From the description of your use case, I understand that

  1. each players are assigned the same set of questions at the beginning
  2. each players has to answer one question every and each player answer a randomly different question everyday
  3. eventually each players answers all the original questions but in a different order.

My solution will be than rather select a random question for each from its unanswered questions every day, 1. create the list randomly when the the user is created
2. have 2 arrays, asked, and unanswered
3. every day you move the first unanswered into the asked and this is the question to ask for the day

This way the order is randomly predetermined for each player when the player is created, so only once. Selecting the question of the day becomes trivial as you simply $pull and $push for each player rather than doing some complicated aggregation everyday for each player. The complicated stuff is all done at the beginning and only once.

2 Likes

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.