This is just an approach to your situation.
first question would be: this structure looks OK knowing that I might have enterprises with 10k+ sites? or should I have a documents for each site?
Have a structure like the following - assuming that the number of sites
will be at some constant number (not growing indefinitely), and the site id
’s are unique within the sites
array.
{
"_id" : ObjectId("5e9d100bf4e2664344ac733d"),
"enterprise_id" : "1",
"date" : ISODate("2020-04-20T02:59:23.118Z"),
"sites" : [
{
"id" : "12",
"status" : "OK",
"fld1" : "some_value_1"
},
{
"id" : "23",
"status" : "OK",
"fld1" : "some_value_2"
},
{
"id" : "99",
"status" : "FAILED",
"fld1" : "some_value_3"
}
]
}
second question: if I’ll go on with the initial structure, how can I access (and update) (for example: site 12 from OKs).
You can query for a specific site id
and status
or specific site id
only - for a given enterprise_id
:
db.collection.findOne(
{ enterprise_id: "1" },
{ sites: { $elemMatch: { id: "23", status: "OK" } } }
)
db.collection.findOne(
{ enterprise_id: "1" },
{ sites: { $elemMatch: { id: "23" } } }
)
The output will be same for both the queries. Note the $elemMatch
is the projection operator:
{
"_id" : ObjectId("5e9d100bf4e2664344ac733d"),
"sites" : [
{
"id" : "23",
"status" : "OK",
"fld1" : "some_value_2"
}
]
}
You can update a specific site’s field values as follows. Note the $elemmatch
is the update operator:
db.collecion.updateOne(
{ enterprise_id: "1", sites: { $elemMatch: { id: "23", status: "OK" } } },
{ $set: { "sites.$.fld1": "new_value_22" } }
)
Query and find that updated value:
{
"_id" : ObjectId("5e9d100bf4e2664344ac733d"),
"sites" : [
{
"id" : "23",
"status" : "OK",
"fld1" : "new_value_22"
}
]
}
Another way to update:
db.sites.updateOne(
{ enterprise_id: "1", "sites.id": "23" },
{ $set: { "sites.$.fld1": "new_value_99" } }
)
Here the fld1
’s value would have changed to "new_value_99"
.
Note about Performance:
Indexing is to be used for fast access for queries as well as updates and sorting. Indexes on array fields is called as Multikey Indexes. This index will give a fast access to sites array queries and updates. Also, Multikey indexes result in large sized indexes and occupy more memory during operation (and consequently can affect the performance).
Some example indexes (and need to be determined based upon your needs): { "sites.id": 1 } )
or { "sites.id": 1, "sites.status": 1 }
or { enterprise_id: 1, "sites.id": 1 }
.