0

I've run into a slight dilemma creating the schema and setting up my database the "right" way. I have a collection that contains objects which holds 4 different arrays. The objects in the collection entries looks similar to:

itemInCollection{
    ...
    theObj: {arr1: ["user1685", "user5342"], arr2: ["user1432"], arr3: [], arr4: ["user1632", "user2312", "user6452"]},
    ...
}

The schema I made already requires all array items to be unique, but I also want none of the arrays to contain the same entry as another. I also need to sometimes move an item that's already in one array to another array (and of course delete the entry from the old array because none of the arrays should contain the same entry). This is obviously not the best way to keep track of something like this, or is it?

What I have so far is 4 queries that check each array individually for a certain entry, and another query that adds the entry to the corresponding array if no entry was found... and ANOTHER query that would delete the entry that might have been found in another before adding it to a different array.

Is there a better way to set up the database to keep track of something like this? Or is there a dynamic way of querying multiple arrays instead of using 6-7 different queries?

Thanks to all in advance for you time and help :)

hewiefreeman
  • 1,137
  • 1
  • 14
  • 31

1 Answers1

3

You could structure your "user" data as subdocuments instead of as strings like this:

itemInCollection{
    ...
    theObj: [
        { userid: "user1685", type: "arr1" },
        { userid: "user5342", type: "arr1" },
        { userid: "user1432", type: "arr2" },
        { userid: "user1632", type: "arr4" },
        { userid: "user2312", type: "arr4" },
        { userid: "user6452", type: "arr4" }
    ],
    ...
}

With this structure in place, it should be easier to ensure uniqueness (at this stage, unfortunately, there is no way to ensure uniqueness of a particular field inside an array - see this link).

dnickless
  • 10,733
  • 1
  • 19
  • 34
  • Theoretically, if I were to use this and I knew the ID of the collection item I wanted, should I use something like `db.collection.find({"_id": theID, "theObj": {$elemMatch: {"userID": "someUser"}}})` to check if the user is in the array and either replace the item or insert a new one? Or is there an `upsert` operation that would do both in one? – hewiefreeman Aug 15 '18 at 21:36
  • That is funny indeed. And, yes, there is an upsert operation which, however, won't help an awful lot because of the array... See this link: https://stackoverflow.com/questions/13588342/can-mongo-upsert-array-data – dnickless Aug 15 '18 at 21:53
  • I'll look a little more into this... but are you also saying that making the schema specify `theObj` have unique items wouldn't work in this case because they're documents? – hewiefreeman Aug 15 '18 at 22:03
  • 1
    Yes, I'm afraid so. You can make some bits unique but across documents only, though, so not inside the array of a specific document. That just cannot be done. But you can control this uniqueness upon updating your arrays by using `$addToSet` instead of `$push`. – dnickless Aug 17 '18 at 04:56