Mongodb nested object array query
I know this has been asked a million times, but I couldn't find my exact scenario, so we go:
my data looks like this:
{
"_id" : ObjectId("55bf22a73d64bd3c495adf3d"),
"_data_type" : "questionnaire",
"title" : "Food quality",
"_namespace" : "questionnaire",
"description" : "tell us your opinion",
"created_time" : ISODate("2015-08-03T08:13:27.874Z"),
"questions" : {
"sub_id_1" : {
"o2" : "Bad",
"o1" : "Good",
"_namespace" : "questionnaire::question",
"question" : "How do you rate our food quality"
},
"sub_id_2" : {
"o2" : "Bad",
"o1" : "Good",
"_namespace" : "questionnaire::question",
"question" : "How do you rate the colouring of our dishes "
},
"sub_id_3" : {
"o2" : "I don't care!",
"o1" : "It is greate",
"_namespace" : "questionnaire::question",
"question" : "How do you like our couches"
}
},
"updated_time" : ISODate("2015-08-03T08:20:37.982Z")
}
I want to get all questions for which "o2" is "Bad".
I have tried the following but no luck.
> db.questionnaire.find({"questions.$.o2": "Bad"})
> db.questionnaire.find({"questions.o2": "Bad"})
+3
source to share
1 answer
With your current document structure, you need to use an operator that evaluates JavaScript. $where
db.collection.find(function(){
for (var key in this.questions) {
if (this.questions[key]["o2"] == "Bad")
return true;
}
})
But I suggest that you change the structure of the document, because usually you don't want to use $where
. You need to use Api for this. Bulk()
var bulk = db.collection.initializeUnorderedBulkOp();
var questions = [];
var subdoc = {};
var count = 0;
db.collection.find().forEach(function(doc) {
for (var key in doc.questions) {
subdoc["o2"] = doc.questions[key]["o2"];
subdoc["o1"] = doc.questions[key]["o1"];
subdoc["_namespace"] = doc.questions[key]["_namespace"];
subdoc["question"] = doc.questions[key]["question"];
subdoc["id"] = key;
questions.push(subdoc);
subdoc = {};
}
bulk.find({ "_id": doc._id }).updateOne({
"$set": { "questions": questions }});
count++;
if (count % 1000 == 0) {
// Execute per 1000 operations and re-init.
bulk.execute();
bulk = db.collection.initializeUnorderedBulkOp();
}
})
// Clean up queues
if ( count % 1000 != 0 ) {
bulk.execute();
}
Then the docs look like this:
> db.collection.find().pretty()
{
"_id" : ObjectId("55bf22a73d64bd3c495adf3d"),
"_data_type" : "questionnaire",
"title" : "Food quality",
"_namespace" : "questionnaire",
"description" : "tell us your opinion",
"created_time" : ISODate("2015-08-03T08:13:27.874Z"),
"questions" : [
{
"o2" : "Bad",
"o1" : "Good",
"_namespace" : "questionnaire::question",
"question" : "How do you rate our food quality",
"id" : "sub_id_1"
},
{
"o2" : "Bad",
"o1" : "Good",
"_namespace" : "questionnaire::question",
"question" : "How do you rate the colouring of our dishes ",
"id" : "sub_id_2"
},
{
"o2" : "I don't care!",
"o1" : "It is greate",
"_namespace" : "questionnaire::question",
"question" : "How do you like our couches",
"id" : "sub_id_3"
}
],
"updated_time" : ISODate("2015-08-03T08:20:37.982Z")
}
The query is simplified and can use indexes, if any.
db.collection.find({ "questions.o2": "Bad" })
+3
source to share