Je suis nouveau sur mongoDB. J'ai deux collections utilisateur et réponse . Je dois compter les réponses à chaque question et en obtenir le pourcentage . Seules 3 réponses sont toujours données dans la collection de réponses.
Collection d'utilisateurs
[{
$lookup: {
from: 'answer',
localField: '_id',
foreignField: 'userId',
as: 'join'
}
}, {
$unwind: {
path: '$join',
preserveNullAndEmptyArrays: true
}
}, {
$group: {
_id: '$_id',
answers: {
$push: {
A1: {
ans: "$join.answer1"
},
A2: {
ans: "$join.answer2"
},
A3: {
ans: "$join.answer3"
}
}
}
}
}, {
$unwind: {
path: '$answers',
preserveNullAndEmptyArrays: true
}
}, {
$group: {
_id: {
_id: '$_id',
Q1: '$answers.Q1.ans'
},
count: {
$sum: 1
}
}
}
///...
}]
Collection de réponses
[{
_id:1,
userId : user1,
mail: "abc@gmail.com",
ans:[
{answer1 :[{"ok":2, percentage:100}]},
{answer2 :[{"bad":1, percentage: 50},{"ok":1,percentage: 50}]},
{answer3 :[{"great":1, percentage: 50},{"bad":1,percentage: 50}]}
]
},
{
_id:1,
userId : user2,
mail: "xyz@gmail.com",
ans:[
{answer1 :[{"ok":3, percentage:100}]},
{answer2 :[{"Not good":1, percentage: 33},{"bad":2,percentage: 66}]},
{answer3 :[{"great":2, percentage: 66},{"bad":1,percentage: 33}]}
]
}]
Les champs de réponse answer1, answer2 et answr3 peuvent avoir des valeurs différentes ou identiques. En fonction des valeurs, je dois compter la réponse et prendre le pourcentage parmi ces 3 réponses.
Résultat attendu
{ _id: 1, userId : user1, answer1: "ok", answer2: "bad", answer3: "great"},
{ _id: 2, userId : user1, answer1: "ok", answer2: "ok", answer3: "bad" },
{ _id: 3, userId : user2, answer1: "ok", answer2: "Not good",answer3: "great"},
{ _id: 4, userId : user2, answer1: "ok", answer2: "bad", answer3: "great"},
{ _id: 5, userId : user2, answer1: "ok", answer2: "bad", answer3: "bad" }
J'ai essayé comme suit,
{
_id : user1,
questionId : 100,
mail: "abc@gmail.com"
},
{
_id : user2,
questionId : 400,
mail: "xyz@gmail.com"
}
Je ne sais pas comment continuer
3 Réponses :
C'est certainement possible. Voici une tentative de force brute, il existe probablement des moyens plus élégants de le faire.
Voici les étapes suivantes:
{k: "answer", v: count} pour que la réponse puisse être la clé plus tard {"answer": count} et mergeObjects pour placer le pourcentage db.user.aggregate([
{$lookup: {
from: "answer",
localField: "_id",
foreignField: "userId",
as: "join"
}},
{$unwind: {
path: "$join",
preserveNullAndEmptyArrays: true
}},
{$group: {
_id: "$_id",
A1: { $push: "$join.answer1" },
A2: { $push: "$join.answer2" },
A3: { $push: "$join.answer3" },
unique1: { $addToSet: "$join.answer1" },
unique2: { $addToSet: "$join.answer2" },
unique3: { $addToSet: "$join.answer3" }
}},
{$addFields: {
answer1: {
$map: {
input: "$unique1",
in: {
k: "$$this",
v: {
$let: {
vars: { ans: "$$this" },
in: {
$size: {
$filter: {
input: "$A1",
cond: {
$eq: [
"$$ans",
"$$this"
]
}
}
}
}
}
}
}
}
},
answer2: {
$map: {
input: "$unique2",
in: {
k: "$$this",
v: {
$let: {
vars: {
ans: "$$this"
},
in: {
$size: {
$filter: {
input: "$A2",
cond: {
$eq: [
"$$ans",
"$$this"
]
}
}
}
}
}
}
}
}
},
answer3: {
$map: {
input: "$unique3",
in: {
k: "$$this",
v: {
$let: {
vars: {
ans: "$$this"
},
in: {
$size: {
$filter: {
input: "$A3",
cond: {
$eq: [
"$$ans",
"$$this"
]
}
}
}
}
}
}
}
}
}
}},
{$addFields: {
answer1: {
$map: {
input: "$answer1",
in: {
$mergeObjects: [
{$arrayToObject: [[ "$$this" ]]},
{percentage: {
$round: {
$multiply: [
100,
{$divide: [
"$$this.v",
{$size: "$A1"}
]}
]
}
}}
]
}
}
},
answer2: {
$map: {
input: "$answer2",
in: {
$mergeObjects: [
{$arrayToObject: [[ "$$this" ]]},
{percentage: {
$round: {
$multiply: [
100,
{$divide: [
"$$this.v",
{$size: "$A2"}
]}
]
}
}}
]
}
}
},
answer3: {
$map: {
input: "$answer3",
in: {
$mergeObjects: [
{$arrayToObject: [[ "$$this" ]]},
{percentage: {
$round: {
$multiply: [
100,
{$divide: [
"$$this.v",
{$size: "$A3"}
]}
]
}
}}
]
}
}
}
}},
{$project: {
A1: 0,
A2: 0,
A3: 0,
unique1: 0,
unique2: 0,
unique3: 0
}}
])
Merci beaucoup. Cela fonctionne aussi. Mais je suis limité pour donner des votes plus corrects: D
Étape 1 - 3. Vous allez bien
Étape 4. Nous devons transformer les réponses avec $ objectToArray a> opérateur.
db.user.aggregate([
{
$lookup: {
from: "answer",
localField: "_id",
foreignField: "userId",
as: "join"
}
},
{
$unwind: "$join"
},
{
$group: {
_id: "$_id",
mail: { $first: "$mail" },
ans: {
$push: {
answer1: "$join.answer1",
answer2: "$join.answer2",
answer3: "$join.answer3"
}
}
}
},
{
$addFields: {
ans: {
$reduce: {
input: {
$map: {
input: "$ans",
in: { $objectToArray: "$$this" }
}
},
initialValue: [],
in: {
$concatArrays: [
"$$value",
"$$this"
]
}
}
}
}
},
{
$unwind: "$ans"
},
{
$group: {
_id: {
userId: "$_id",
mail: "$mail",
k: "$ans.k",
v: "$ans.v"
},
count: { $sum: 1 }
}
},
{
$group: {
_id: {
userId: "$_id.userId",
mail: "$_id.mail",
k: "$_id.k"
},
total: { $sum: "$count" },
ans: {
$push: {
k: "$_id.k",
v: "$_id.v",
count: "$count"
}
}
}
},
{
$addFields: {
ans: {
$map: {
input: "$ans",
in: {
$arrayToObject: [
[
{
k: "$$this.v",
v: "$$this.count"
},
{
k: "percentage",
v: {
$floor: {
$multiply: [
{
$divide: [
"$$this.count",
"$total"
]
},
100
]
}
}
}
]
]
}
}
}
}
},
{
$group: {
_id: {
userId: "$_id.userId",
mail: "$_id.mail"
},
ans: {
$push: {
k: "$_id.k",
v: "$ans"
}
}
}
},
{
$addFields: {
_id: 1,
mail: "$_id.mail",
userId: "$_id.userId",
ans: { $arrayToObject: "$ans" }
}
}
])
Étape 6. Nous devons regrouper par toute réponse (réponse1, réponse2, réponse3) + n'importe quelle valeur (ok, bad, great, ...) et compter Nº fois
Étape 7. Maintenant, nous comptons le total par réponse
Étape 8. Nous transformons:
[{k:"ok|bad|great", v:"Nº times"},{k:"percentage", v:(Nº times/total) * 100}]
to
[{"ok|bad|great":1, percentage:100}]
Étape 9. Maintenant, nous ajoutons dans ans -> answer1 , answer2 , answer3
Étape 10. Nous ajustons la sortie souhaitée.
{answer1: "...", answer2: "...", answer3: "..."}
to
[ {k:"answer1", v:"..."}, {k:"answer2", v:"..."}, {k:"answer3", v:"..."} ]
J'utilise Spring Boot, je pensais que si j'écrivais une requête Mongo, je pouvais facilement convertir en java. Im essayant de convertir cela en forme de démarrage à ressort hier, mais échoue. Puis-je savoir comment convertir en botte de printemps! J'ai besoin de ce urgent. Je l'ai interrogé dans stackoverflow.com/questions/61381113/…
Vous avez la bonne idée en tête, je commencerais personnellement l'agrégation à partir de la collection answers car $ lookup dans ce contexte peut devenir très onéreuse.
db.answers.aggregate(
[
{
$group: {
_id: "$userIds",
answer1: {$push: "$answer1"},
answer2: {$push: "$answer2"},
answer3: {$push: "$answer3"},
}
},
{
addFields: {
answer1: {
$reduce: {
input: "$answer1",
initialValue: {great: 0, ok: 0, notgood: 0, bad: 0, total: 0},
in: {
total: {$add: ["$$value.total", 1]},
great: {$add: ["$$value.great", {$cond: [{$eq: ["$$this", "great"]}, 1, 0]}]},
ok: {$add: ["$$value.ok", {$cond: [{$eq: ["$$this", "ok"]}, 1, 0]}]},
notgood: {$add: ["$$value.notgood", {$cond: [{$eq: ["$$this", "Not good"]}, 1, 0]}]},
bad: {$add: ["$$value.bad", {$cond: [{$eq: ["$$this", "bad"]}, 1, 0]}]},
}
}
},
answer2: {
$reduce: {
input: "$answer2",
initialValue: {great: 0, ok: 0, notgood: 0, bad: 0, total: 0},
in: {
total: {$add: ["$$value.total", 1]},
great: {$add: ["$$value.great", {$cond: [{$eq: ["$$this", "great"]}, 1, 0]}]},
ok: {$add: ["$$value.ok", {$cond: [{$eq: ["$$this", "ok"]}, 1, 0]}]},
notgood: {$add: ["$$value.notgood", {$cond: [{$eq: ["$$this", "Not good"]}, 1, 0]}]},
bad: {$add: ["$$value.bad", {$cond: [{$eq: ["$$this", "bad"]}, 1, 0]}]},
}
}
},
answer3: {
$reduce: {
input: "$answer3",
initialValue: {great: 0, ok: 0, notgood: 0, bad: 0, total: 0},
in: {
total: {$add: ["$$value.total", 1]},
great: {$add: ["$$value.great", {$cond: [{$eq: ["$$this", "great"]}, 1, 0]}]},
ok: {$add: ["$$value.ok", {$cond: [{$eq: ["$$this", "ok"]}, 1, 0]}]},
notgood: {$add: ["$$value.notgood", {$cond: [{$eq: ["$$this", "Not good"]}, 1, 0]}]},
bad: {$add: ["$$value.bad", {$cond: [{$eq: ["$$this", "bad"]}, 1, 0]}]},
}
}
},
}
},
{
$addFields: {
ans: [
{
answer1: {
$arrayToObject: {
$filter: {
input: {
$map: {
input: {$objectToArray: "$answer1"},
as: "map_answer",
in: {
k: "$$map_answer.k", v: {$multiply: [{$divide: ["$$map_answer.v", "$answer1.total"]}, 100]}
}
}
},
as: "answer",
cond: {
$and: [
{$ne: ["$$answer.k", "total"]},
{$gt: ["$$answer.v", 0]}
]
}
}
}
}
},
{
answer2: {
$arrayToObject: {
$filter: {
input: {
$map: {
input: {$objectToArray: "$answer2"},
as: "map_answer",
in: {
k: "$$map_answer.k", v: {$multiply: [{$divide: ["$$map_answer.v", "$answer2.total"]}, 100]}
}
}
},
as: "answer",
cond: {
$and: [
{$ne: ["$$answer.k", "total"]},
{$gt: ["$$answer.v", 0]}
]
}
}
}
}
},
{
answer3: {
$arrayToObject: {
$filter: {
input: {
$map: {
input: {$objectToArray: "$answer3"},
as: "map_answer",
in: {
k: "$$map_answer.k", v: {$multiply: [{$divide: ["$$map_answer.v", "$answer3.total"]}, 100]}
}
}
},
as: "answer",
cond: {
$and: [
{$ne: ["$$answer.k", "total"]},
{$gt: ["$$answer.v", 0]}
]
}
}
}
}
}
]
}
},
{
$lookup: {
from: "users",
localField: "userId",
foreignField: "_id",
as: "user"
}
},
{
$unwind: "$user"
},
{
$project: {
_id: "$user._id",
userId: "$userId",
mail: "$user.mail",
ans: 1
}
}
]
)
Il y a beaucoup de manipulation de données en cours, aussi la suppression du 0 a obligé le pipeline à nécessiter des étapes supplémentaires.
Vous devriez 3 réponses possibles pour chaque question: ok, mauvais, excellent; est-ce une limite réaliste ou devez-vous être capable de gérer des réponses aléatoires?
Avoir à gérer des réponses aléatoires