Index and sort on nested variable field MongoDB

Viewed 31

I have a document that looks like this :

{
  "_id": "chatID"
  "presence": {
    "userID1": 1647627240464,
    "userID2": 1647227540464
  },
}

I need to query for each userID the chats where he is present and order by the timestamp in the presence map.

I am aware that this is probably not the best way to do, before i had 1 element per user meaning duplicating the chatIDs, but it's a pain to update them all because it would look like :

{
    "_id": "userID1chatID",
    "at": 1647627240464,
    "ids": "30EYwO01_Nyq7dMqe_O3vfL3AH",
    "members": ["userID1", "userID2", "userID3"],
    "owner": "userID1",
    "present": true,
    "uid": "chatID",
    "url": "databaseURL"
}

This would allow me to find the chats where userID1 is present: true and order by at DESCENDING.
The problem with this is that i need to update the at attribute for all the documents (one per user) for this same chat room.

How can i do this same query while maintaining a single document with present as a map ?

Problem : the index would be on a variable : userID1, userID2, etc...
like : present.userID1 and seems to not be convenient for use when userID1 can be removed from the present map if the user leaves the chat.

Please let me know if this is unclear, thanks in advance.

0 Answers
Related