0

I'm trying to improve the performance of my app and my knowledge of MongoDB. I have been able to execute a fire and forget query that both creates fields if they don't exist and otherwise increment a value as follows:

date = "2018-6"
sid = "012345"
cid = "06789"

key = "MESSAGES.{}.{}.{}.{}".format(date, sid, cid, hour)

db.stats.update({}, { "$inc": { key : 1 }})

This produces a single document with the following structure:

document:

{
  "MESSAGES": {
    "2018-6": {
      "012345": {
        "06789": 1
      },
      "011111": {
        "06667": 5
      }
    },{
    "2018-5": {
      "012345": {
        "06789": 20
      },
      "011111": {
        "06667": 15
      }
    }
  }
}

As you can probably imagine it has become a bit of a nightmare to query this structure with increasing data. I'd like to achieve the same fire and forget query but with the implementation of a better indexable schema. Something like:

documents:

[{ 
  "SID": "012345",
  "MESSAGES: {
    "MONTHS": {
       "KEY": "2018-6",
       "CHANNELS": {
         "KEY": "06789",
         "COUNT": 1
       }
    },{
      "KEY": "2018-5",
      "CHANNELS": {
        "KEY": "06667",
        "COUNT": 20
        }
    }
  } 
},
{ 
  "SID": "011111",
  "MESSAGES: {
    "MONTHS": {
      "KEY": "2018-6",
      "CHANNELS": {
        "KEY": "06667",
        "COUNT": 5
        }
    },{
      "KEY": "2018-5",
      "CHANNELS": {
        "KEY": "06667",
        "COUNT": 15
        }
    }
  } 
}]

I'm working with a quite a large amount of data and these queries can happen many times a second so it's important that I just execute a thing once if at all possible. Any advice you can give is very welcome, feel free to criticise anything you see here too as my goal is to learn.

Thanks in advance!

UPDATED WITH ATTEMPT:

db.test.updateOne({"SERVER_ID": "23894723487sdf"}, {
"$addToSet" : {
    "MESSAGES" : {
        "DATE": "2018-6",
        "CHANNELS": [{
            "ID": "239048349",
            "COUNT": NumberInt(1)
        }]
    }
},
"$inc" : {
    "MESSAGES.CHANNELS.$.COUNT" : 1

}}, 
{upsert: true})
Yandawl
  • 166
  • 1
  • 10
  • You need to use `upsert: true`... It makes new document if does not exists with the query criteria... See here https://stackoverflow.com/questions/13710770/how-to-update-values-using-pymongo?utm_medium=organic&utm_source=google_rich_qa&utm_campaign=google_rich_qa – Ashh Jun 10 '18 at 16:54
  • But how to structure the query such that it increments the count field in this scenario? – Yandawl Jun 10 '18 at 17:21
  • You can see the examples here https://stackoverflow.com/questions/34431435/mongodb-update-an-object-in-nested-array?utm_medium=organic&utm_source=google_rich_qa&utm_campaign=google_rich_qa – Ashh Jun 10 '18 at 17:26
  • Thanks for your input! I tried this and I'm getting an error from MongoDB: The positional operator did not find the match needed from the query. Unexpanded update: MESSAGES.CHANNELS.$.COUNT I would like it to insert the array element if it doesn't exist already, can this be done? (I've specified upsert=true) – Yandawl Jun 10 '18 at 17:51
  • please update your question with your query – Ashh Jun 10 '18 at 17:54
  • Updated! It doesn't work at all with the increment, but if I ignore that and delete it, then it correctly adds all the fields and a new array element for month if I change 2018-6 to 2018-5, but if I then change the CHANNELS.ID part it adds a duplicate element of the month with the new channel ID which I don't want to happen, I'd like it to add a new array element within the CHANNELS field under the same month – Yandawl Jun 10 '18 at 19:05

0 Answers0