Search and update in array of objects MongoDB

Question

I have a collection in MongoDB containing search history of a user where each document is stored like:

"_id": "user1"
searchHistory: {
    "product1": [
        {
            "timestamp": 1623482432,
            "query": {
                "query": "chocolate",
                "qty": 2
            }
        },
        {
            "timestamp": 1623481234,
            "query": {
                "query": "lindor",
                "qty": 4
            }
        },
    ],
    "product2": [
        {
            "timestamp": 1623473622,
            "query": {
                "query": "table",
                "qty": 1
            }
        },
        {
            "timestamp": 1623438232,
            "query": {
                "query": "ike",
                "qty": 1
            }
        },
    ]
}

Here _id of document acts like a foreign key to the user document in another collection.

I have backend running on nodejs and this function is used to store a new search history in the record.

exports.updateUserSearchCount = function (userId, productId, searchDetails) {
   let addToSetData = {}
   let key = `searchHistory.${productId}`
   addToSetData[key] = { "timestamp": new Date().getTime(), "query": searchDetails }
   return client.db("mydb").collection("userSearchHistory").updateOne({ "_id": userId }, { "$addToSet": addToSetData }, { upsert: true }, async (err, res) => {
   })
}

Now, I want to get search history of a user based on query only using the db.find().

I want something like this: db.find({"_id": "user1", "searchHistory.somewildcard.query": "some query"})

I need a wildcard which will replace ".somewildcard." to search in all products searched.

I saw a suggestion that we should store document like:

"_id": "user1"
searchHistory: [
    {
        "key": "product1",
        "value": [
            {
                "timestamp": 1623482432,
                "query": {
                    "query": "chocolate",
                    "qty": 2
                }
            }
        ]
    }
]

However if I store document like this, then adding search history to existing document becomes a tideous and confusing task.

What should I do?

"However if I store document like this, then adding search history to existing document becomes a tideous and confusing task" - Why that? I would say, unless the history is getting very large, it should be the preferred design. — Wernfried Domscheit
– Wernfried Domscheit, Commented Jan 8, 2022 at 11:27

Tom Slabbaert · Accepted Answer · 2022-01-08 08:33:48Z

It's always a bad idea to save values are keys, for this exact reason you're facing. It heavily limits querying that field, obviously the trade off is that it makes updates much easier.

I personally recommend you do not save these searches in nested form at all, this will cause you scaling issues quite quickly, assuming these fields are indexed you will start seeing performance issues when the arrays get's too large ( few hundred searches ).

So my personal recommendation is for you to save it in a new collection like so:

{
    "user_id": "1",
    "key": "product1",
    "timestamp": 1623482432,
    "query": {
        "query": "chocolate",
        "qty": 2
    }
}

Now querying a specific user or a specific product or even a query substring is all very easily supported by creating some basic indexes. an "update" in this case would just be to insert a new document which is also much faster.

If you still prefer to keep the nested structure, then I recommend you do switch to the recommended structure you posted, as you mentioned updates will become slightly more tedious, but you can still do it quite easily using arrayFilters for updating a specific element or just using $push for adding a new search

Collectives™ on Stack Overflow

Search and update in array of objects MongoDB

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related