MongoDB query: how to find if a string within a nested objects

Question

I have the following Course Schema with an attendance field, which stores a dictionary with the following key-value structure (string: List). In particular, the attendance object's key stores a date of a class while the value stores a list of students who attended (student IDs).

var CourseSchema = new Schema({
  attendance: {},
});

For instance, a single Course item could be as follows:

{"_id":"559a7353186a384b54f9aea9","attendance":{"2015-12-17":["558febdb949eff4711e621e9","559020fe79f141941ddd3246"],"2015-11-14":["558febdb949eff4711e621e9","559020fe79f141941ddd3246"]}

I'm trying to return courses that has a student has attended (so basically return any course that in its attendance field contains a particular student ID. I'm trying to do it as follows, but it's returning empty results. Is there something wrong with my query?

I tried checking this link, but it doesn't work for me too. MongoDB query inside a nested array

Course.find({"attendance.$": {$in: ["558febdb949eff4711e621e9"]}}...)

The problem here is that you have "named keys" like "2015-12-17" which get in the way of things like the referenced solution being useful. Your schema design choice is therefore at fault. If you change the schema then you can do exactly what you want in an efficient way. Right now, the only ways are not very efficient at all. — Neil Lunn
– Neil Lunn, Commented Jun 14, 2017 at 12:41

FabianoLothor · Accepted Answer · 2023-07-11 07:23:50Z

The solution is sound, but your schema is at fault here. There are big problems with using "named keys" with a database like this, and you should consider them as "attributes" instead.

Such as in:

{
    "_id" : ObjectId("559a7353186a384b54f9aea9"),
    "attendance" : [
            {
                    "date" : ISODate("2015-12-17T00:00:00Z"),
                    "student" : ObjectId("558febdb949eff4711e621e9")
            },
            {
                    "date" : ISODate("2015-12-17T00:00:00Z"),
                    "student" : ObjectId("559020fe79f141941ddd3246")
            },
            {
                    "date" : ISODate("2015-11-14T00:00:00Z"),
                    "student" : ObjectId("558febdb949eff4711e621e9")
            },
            {
                    "date" : ISODate("2015-11-14T00:00:00Z"),
                    "student" : ObjectId("559020fe79f141941ddd3246")
            }
    ]
}

Which would be defined in a schema as:

AttendanceSchmema = new Schema({
  "date": Date,
  "student": { "type": Schema.Types.ObjectId, "ref": "Student" }
},{ "_id": false });

CourseSchema = new Schema({
  "attendance": [AttendanceSchema]
});

Then I can simply match for as given student:

Course.find(
  { "attendance.student": "559020fe79f141941ddd3246" },
  { "attendance.$": 1 }
)

Or even a student on a given date:

Course.find(
  { "attendance": { 
     "$elemMatch": { 
       "student": "559020fe79f141941ddd3246",
       "date": { "$gte": new Date("2015-12-15"), "$lt": new Date("2015-12-16") }
     }
  }},
  { "attendance.$": 1 }
)

And even more possibilities in between. The reason we put all of this in a single array with "attribute" like properties is to make it simple to query and store.

Without such a change, you are stuck with horribly performing queries like:

Course.find({ "$where": function() {
  return Object.keys(this.attendance).some(function(k) {
    return this.attendance[k].indexOf("558febdb949eff4711e621e9") > -1
  });
}})

Presuming of course that those values are presently "strings", as they would appear to be by the Mixed type.

And even worse queries for anything more complex, which also cannot tell you which position was matched. But by far the worst thing is these cannot possibly use an "index" in order to match.

Therefore you really should consider changing the database schema here to a form that is going to support querying in the most efficient way.

Thank you so much, and I'll look into this! Is it considered bad form to just change database schemas midway?

Collectives™ on Stack Overflow

MongoDB query: how to find if a string within a nested objects

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related