Numeric key for array of objects

Working with Data
1

Dear all,

I need to save data into a nested array of objects for some reports, but I’d like the structure to be something like this:

{
  enterprise_id: 1,
  date: <today>,
  sites: {
    OK: {
      12: {<site object>},
      23: {<another site>},
      45: {<site>}
      ...
    },
    FAILED: {
      99: {<site>},
       ....
    }
  }
}

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?

second question: if I’ll go on with the initial structure, how can I access (and update) (for example: site 12 from OKs).

Thank you very much,
Silviu

2

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 }.

3

Hello,

thank you for your reply. I’ll give it a try and come back with the outcome.

Cheers,
Silviu