1

I'm trying to find all documents with a certain value in an array field using Mongoose and Node.js. I can do this in MongoDB with no trouble, but I'm having difficulty in Mongoose. I used Find document with array that contains a specific value as my guide for how to do this, but I'm not getting the results I expect. My model:

const mongoose = require("mongoose");

const Schema = mongoose.Schema;
const ApptSchema = new Schema({
  store: { type: String, required: true },
  dotw: { type: String, required: true },
  month: { type: Number, required: true },
  day: { type: Number, required: true },
  hr: { type: Number, required: true },
  min: { type: Number, required: true },
  customers: [{ type: mongoose.Schema.Types.ObjectId, ref: "User" }],
  full: { type: Boolean, required: true, default: false }
});

const Appt = mongoose.model("Appt", ApptSchema);

module.exports = Appt;

I want to find all documents that contain a certain customer id. In MongoDB shell, this is what I would do:

db.appts.find({customers: "5e7e3bc4ac4f196474d8bf69"})

This works as expected, giving me all documents, (one document in this case), where this id is in the customers array.

{ "_id" : ObjectId("5e7e719c806ef76b35b4fd69"), "customers" : [ "5e7e3bc4ac4f196474d8bf69" ], "full" : false, "store" : "Nashville", "dotw" : "Friday", "month" : 4, "day" : 15, "hr" : 13, "min" : 0 }

In Mongoose, this is what I'm trying:

Appt.find({ customers: "5e7e3bc4ac4f196474d8bf69" }, (err, docs) => {
    if (err) {
      console.log(err);
    } else {
      console.log(docs);
    }
  });

This prints an empty array, even though there is clearly a document where this id is in the customers array. This seems like this should work, but I'm clearly missing some piece of the puzzle. Any insight into what I'm doing wrong would be very much appreciated.

Edit: In case anyone would like to/be willing to take a more in-depth look, a GitHub repo of the app so far can be found here. The query in question is in routes/routes.js at line 111 (as of the time of writing).

Another edit: It appears this has something to do with the Schema type of the field in question. I eliminated the ref attribute of the entries in the customers field, just in case that was causing a problem, but my query still returned an empty array. The next test was to add a new field to my model, myStrings: [String]. I then added a string to the array of one of my Appt documents, "working", and queried Appt.find({myStrings: "working"}) and this finally returns the Appt document that I updated. This tells me there's something squirrely about working with the mongoose.Schema.Types.ObjectId, but I can't figure out how to resolve it.

FINAL EDIT: After much tribulation, this is solved. The issue was as follows... For testing, I was adding items to my database using MongoDB shell, which does not enforce data types like Mongoose does. I didn't realize that I was simply adding user ids as strings to the customers array. When Mongoose went looking for ObjectIds, of course it didn't find any, and returned an empty array. Adding customers to the customers array with db.appts.updateOne({<whatever information>},{$push:{customers: new ObjectId(<id string>)}}), Mongoose was able to return the information I was looking for.

LuosRestil
  • 169
  • 3
  • 12
  • The query is correct (https://mongoplayground.net/p/Q9firU4-_0l), make sure you're querying the same database / collection from Mongoshell and from mongoose. – mickl Mar 27 '20 at 23:40
  • Thank you for the suggestion. It's definitely the same database/collection. If I query `Appt.find({ _id: "5e7e719c806ef76b35b4fd69" })`, I get back the same document as the MongoDB shell query `db.appts.find({_id: ObjectId("5e7e719c806ef76b35b4fd69")})`, which has the user I'm looking for in the `customers` array. – LuosRestil Mar 27 '20 at 23:54
  • This answer gives you three solutions to this problem: https://stackoverflow.com/questions/63368225/mongoose-find-documents-if-array-contains-a-value/65179622#65179622 – Rustamjon Kirgizbaev Dec 07 '20 at 10:32
  • [This answer](https://stackoverflow.com/questions/63368225/mongoose-find-documents-if-array-contains-a-value/65179622#65179622) gives you three solutions to this problem. – Rustamjon Kirgizbaev Dec 07 '20 at 10:35

2 Answers2

0

I would say your problem is that you are using a String in the filter, where the field is of ObjectId type. Therefore, you need to convert the string to ObjectId first for mongoose to be able to query it correctly.

Appt.find({ customers: mongoose.Types.ObjectId("5e7e3bc4ac4f196474d8bf69") }, (err, docs) => {});
gasbi
  • 718
  • 7
  • 10
  • I believe the string is cast to ObjectId automatically. If I put a different string in place of a valid id, it throws a cast error. For example, `Appt.find({customers: "fish"})`, will throw the error, `MongooseError [CastError]: Cast to ObjectId failed for value "fish" at path "customers" for model "Appt"`. Just in case, I did try explicitly casting the string to an ObjectId as you suggested, and I still get an empty array in return. Thank you for the suggestion, though! – LuosRestil Mar 27 '20 at 23:16
  • @LuosRestil the `CastError` is usually triggered because of the string you are passing in is not of the correct length:`Error: Argument passed in must be a single String of 12 bytes or a string of 24 hex characters` The following works in my code: ```javascript const experiments = await Experiment.find({ participants: mongoose.Types.ObjectId(participantId) }); res.json({experiments}); }; ``` – gasbi Mar 27 '20 at 23:24
  • Strange! If I do `Appt.find({customers: mongoose.Types.ObjectId("5e7e3bc4ac4f196474d8bf69")}, (err, docs) => console.log(docs))`, I still get an empty array. I wonder what I'm doing differently? – LuosRestil Mar 27 '20 at 23:39
  • It is weird, indeed! Because I was doing some tests, and it actually works for me without converting it to ObjectId... So your original code actually looks fine to me. Make sure you are obtaining the id correctly from the router if you are building this as an API. – gasbi Mar 27 '20 at 23:46
  • No worries trying to get the id from the router, I've gone ahead and hard-coded a known working user id for debugging purposes. – LuosRestil Mar 28 '20 at 00:08
0

add the _id field:

Appt.find({ customers._id: "5e7e3bc4ac4f196474d8bf69" }, (err, docs) => {
if (err) {
  console.log(err);
} else {
  console.log(docs);
}});
Karl L
  • 1,645
  • 1
  • 7
  • 11
  • I'm afraid this also returns an empty array. Thank you for the suggestion, but no luck. – LuosRestil Mar 27 '20 at 23:19
  • I didn't notice you are using ref:user. You might want to use POPULATE. if you can tell me what exactly you want to do and search. that would be good – Karl L Mar 27 '20 at 23:59
  • I don't think I need to use populate for this task, since I don't need to gather any information about the users whose id is stored in the `customers` array. I'm just trying to look up appointments, (Appt), by user id, getting a list of all the appointments that have that id in their `customers` list. – LuosRestil Mar 28 '20 at 00:06
  • when you use REF in customers: [{ type: mongoose.Schema.Types.ObjectId, ref: "User" }]. There is no other way to query the userid via the appt collection but using populate. unless CUSTOMERS field is just a simple array of sub-document NOT referenced to another collection. If you are trying to lookup for appts by user id, you might want to change your user schema and add appt reference there instead. so the other way around. – Karl L Mar 28 '20 at 00:49
  • This made a lot of sense to me, so I went ahead and eliminated the `ref`, and just made this a normal field. Unfortunately, this query still returns an empty array. – LuosRestil Mar 28 '20 at 16:35
  • thats odd. I tested it on my localhost and it worked. hmmm.. would it be possible to provide me a screenshot of whats in the actual collection, in the customers field part. – Karl L Mar 28 '20 at 22:58
  • Solved it at last! My mistake was in adding customers with MongoDB shell, which of course does not enforce data type like Mongoose does. As such, customer ids were just being entered as strings, not ObjectIds, and Mongoose therefore returned no results since it didn’t find the data type it was looking for. Thank you so much for taking the time to help! – LuosRestil Mar 29 '20 at 14:50
  • glad you figured it out – Karl L Mar 30 '20 at 22:44