MongoDB preferred schema for embedded collections. documents vs. arrays

Question

I believe there at least two ways to have embedded data in a mongodb document. In a simplified case we could have something like this:

{
    'name' : 'bill',
    'lines': {
       'idk73716': {'name': 'Line A'},
       'idk51232': {'name': 'Line B'},
       'idk23321': {'name': 'Line C'}
    }
}

and as an array:

{
    'name' : 'bill',
    'lines': [
       {'id': 'idk73716', 'name': 'Line A'},
       {'id': 'idk51232', 'name': 'Line B'},
       {'id': 'idk23321', 'name': 'Line C'}
    ]
}

As you can see in this use case it's important to keep the id of each line.

I'm wondering if there are pros and cons between these two schemas. Especially when it comes to using indexes I have the feeling that the second may be easier to work with as one could create an index on 'lines.id' or even 'lines.name' to search for an id or name accross all documents. I didn't find any working solution to index the ids ('idk73716' and so on) in the first example.

Is it generally preferred to use the second approach if you have a use case like this?

Answer 1

In your first approach you can't index the id fields, since id used as key. Its kind of act like key value dictionary. This approach is useful if you have the known set of ids (of course less number).Assume In your first example the id is well known at front ,

>>db.your_colleection.find()
 { "_id" : ObjectId("4ebbb6f974235464de49c3a5"), "name" : "bill", 
  "lines" : { 
             "idk73716" : { "name" : "Line A" },
             "idk51232" : { "name" : "Line B" } ,
             "idk23321":  { "name" : "Line C" }
            } 
  }

so to find the values for id field idk73716, you can do this by

 db.your_colleection.find({},{'lines.idk73716':1})
 { "_id" : ObjectId("4ebbb6f974235464de49c3a5"), "lines" : { "idk73716" : { "name" : "Line A" } } }

the empty {} denotes the query, and the second part {'lines.idk73716':1} is a query selector.

having ids as keys having an advantage of picking the particular field alone. Even though {'lines.idk73716':1} is a field selector, here it serves as a query and selector. but this cannot be done in your second approach. Assume the second collection is kind of like this

> db.second_collection.find()
{ "_id" : ObjectId("4ebbb9c174235464de49c3a6"), "name" : "bill", "lines" : [
    {
        "id" : "idk73716",
        "name" : "Line A"
    },
    {
        "id" : "idk51232",
        "name" : "Line B"
    },
    {
        "id" : "idk23321",
        "name" : "Line C"
    }
] }
> 

And you indexed the field id, so if you want to query by id

> db.second_collection.find({'lines.id' : 'idk73716' })

{ "_id" : ObjectId("4ebbb9c174235464de49c3a6"), "name" : "bill", "lines" : [
    {
        "id" : "idk73716",
        "name" : "Line A"
    },
    {
        "id" : "idk51232",
        "name" : "Line B"
    },
    {
        "id" : "idk23321",
        "name" : "Line C"
    }
] }
> 

by seeing the above output, its visible that there is no way to pick the matching sub(embedded) documents alone, but it is possible in the the first approach. This is the default behavior of mongodb.

see

db.second_collection.find({'lines.id' : 'idk73716' },{'lines':1})

will fetch all lines, not just idk73716

{ "_id" : ObjectId("4ebbb9c174235464de49c3a6"), "lines" : [
    {
        "id" : "idk73716",
        "name" : "Line A"
    },
    {
        "id" : "idk51232",
        "name" : "Line B"
    },
    {
        "id" : "idk23321",
        "name" : "Line C"
    }
] }

Hope this helps

EDIT

Thanks to @Gates VP for pointing out

db.your_collection.find({'lines.idk73716':{$exists:true}}). If you want to use the "ids as keys" version, the exists query will work, but it will not be indexable

We still can use $exists to query the id, but it will not be indexable

Answer 2

Today we have $eleMatch operator to achieve this, as discussed here - Retrieve only the queried element in an object array in MongoDB collection

But this question poses some interesting design choices, which I am also struggling to make today. What should be the preferred choice from given two options if frequent CRUD is required in embedded documents?

I found, it is easy to perform CRUD with new $set/$unset operators, on embedded documents, when ID s used as property names. And if client can get hold of ID to make edits, it is better than array, IMO. Here is another useful blogpost by Mongodb about schema design and making these design decisions

http://blog.mongodb.org/post/87200945828/6-rules-of-thumb-for-mongodb-schema-design-part-1