CODEWITHSUNDEEP
mongodbmongodb-query
KingFish
KingFish

Reputation: 9153

Aggregating filter for Key

If I have a document as follows:

{
    "_id" : ObjectId("54986d5531a011bb5fb8e0ee"),
    "owner" : "54948a5d85f7a9527a002917",
    "type" : "group",
    "deleted" : false,
    "participants" : {
            "54948a5d85f7a9527a002917" : {
                    "last_message_id" : null
            },
            "5491234568f7a9527a002917" : {
                    "last_message_id" : null
            }
            "1234567aaaa7a9527a002917" : {
                    "last_message_id" : null
            }
    },
  }

How do I do a simple filter for all documents this have participant "54948a5d85f7a9527a002917"?

Thanks

Upvotes: 0

Views: 37

Answers (1)

Neil Lunn
Neil Lunn

Reputation: 151072

Trying to query structures like this does not work well. There are a whole whole host of problems with modelling like this, but the most clear problem is using "data" as the names for "keys".

Try to think a little RDBMS like, at least in the concepts of the limitations to what a database cannot or should not do. You wouldn't design a "table" in a schema that had something like "54948a5d85f7a9527a002917" as the "column" name now would you? But this is essentially what you are doing here.

MongoDB can query this, but not in an efficient way:

db.collection.find({ 
    "participants.54948a5d85f7a9527a002917": { "$exists": true }
})

Naturally this looks for the "presence" of a key in the data. While the query form is available, it does not make efficient use of such things as indexes where available as indexes apply to "data" and not the "key" names.

A better structure and approach is this:

{
    "_id" : ObjectId("54986d5531a011bb5fb8e0ee"),
    "owner" : "54948a5d85f7a9527a002917",
    "type" : "group",
    "deleted" : false,
    "participants" : [
        { "_id": "54948a5d85f7a9527a002917" },
        { "_id": "5491234568f7a9527a002918" },
        { "_id": "1234567aaaa7a9527a002917" }
    ]
}

Now the "data" you are looking for is actual "data" associated with a "key" ( possibly ) and inside an array for binding to the parent object. This is much more efficient to query:

db.collection.find({
    "participants._id": "54948a5d85f7a9527a002917"
})

It's much better to model that way than what you are presently doing and it makes sense to the consumption of objects.

BTW. It's probably just cut and paste in your question but you cannot possibly duplicate keys such as "54948a5d85f7a9527a002917" as you have. That is a basic hash rule that is being broken there.

Upvotes: 1

Related Questions