Limit the number of elements in array based on type (max N elements for each type)

Working with Data
,
1

I would like to create a query that will limit the number of elements to a max N=2 for each entityType .

Besides entityType & entityId , the original document also has some other properties (eg: timestamp ) which I simply removed for simplicity.

Here is the initial/reference document.

{
    "_id" : ObjectId("100000"),
    "agency" : "agency_1",
    "username" : "user_one",
    "recentEntities" : {
        "entities" : [ 
            {
                "entityType" : "type_one",
                "entityId" : "11",
                "other" : "aa",
            },
            {
                "entityType" : "type_one",
                "entityId" : "12",
                "other" : "ab",
            },
            {
                "entityType" : "type_two",
                "entityId" : "21",
                "other" : "ba",
            }
        ]
    }
}

Here are 3 specifications/cases for this problem :

  • Every time a new entity is added as the first element in the entities array, meaning most recent visied entity.

Let’s say that I want to update the initial document with the following entity :

{
    "entityType" : "type_two",
    "entityId" : "22",
    "other" : "bb",
}

Since we did not reach the limit for the "entityType" = "type_two" , we will simply add the object to the array and the updated document will look like:

{
    "_id" : ObjectId("100000"),
    "agency" : "agency_1",
    "username" : "user_one",
    "recentEntities" : {
        "entities" : [ 
            {
                "entityType" : "type_two",
                "entityId" : "22",
                "other" : "bb",
            },
            {
                "entityType" : "type_one",
                "entityId" : "11",
                "other" : "aa",
            },
            {
                "entityType" : "type_one",
                "entityId" : "12",
                "other" : "ab",
            },
            {
                "entityType" : "type_two",
                "entityId" : "21",
                "other" : "ba",
            }
        ]
    }
}
  • If the document with a particular entityId already exists, but the other fields inside the object have changed, then I would like to replace that entity object with the recent one.

Updating the reference document with this entity:

{
    "entityType" : "type_one",
    "entityId" : "12",
    "other" : "xy",
}

Will result in :

{
    "_id" : ObjectId("100000"),
    "agency" : "agency_1",
    "username" : "user_one",
    "recentEntities" : {
        "entities" : [ 
            {
                "entityType" : "type_one",
                "entityId" : "12",
                "other" : "xy",
            },
            {
                "entityType" : "type_one",
                "entityId" : "11",
                "other" : "aa",
            },
            {
                "entityType" : "type_two",
                "entityId" : "21",
                "other" : "ba",
            }
        ]
    }
}
  • On the other hand, if the limit has been reached, then the oldest entity of a particular type will be deleted.

For example by adding the following entity:

{
    "entityType" : "type_one",
    "entityId" : "13",
    "other" : "ac",
}

we need to remove the "entityId" = "12" and put the new one on top.

After the update, the reference document will look like:

{
    "_id" : ObjectId("100000"),
    "agency" : "agency_1",
    "username" : "user_one",
    "recentEntities" : {
        "entities" : [ 
            {
                "entityType" : "type_one",
                "entityId" : "13",
                "other" : "ac",
            },
            {
                "entityType" : "type_one",
                "entityId" : "11",
                "other" : "aa",
            },
            {
                "entityType" : "type_two",
                "entityId" : "21",
                "other" : "ba",
            }
        ]
    }
}

I managed to do the first 2 points, but the last one is a bit tricky to implement so any help will be much appreciated.

2

Please share what you have so far. Is there anything else you tried that fails? Having your starting point will help and knowing what failed will help not making the same errors or follows a dead end.

It would be easier if you had different arrays for each entityType. This way a simple $slice could help limit the number of elements for a given entityType. Otherwise you will probably need something like $filter.

3

@Valentin_L_STANCA, your post was quite long, many people took the time to read it. It will be appreciated if you could follow up on it. I asked you to provide the aggregation you have to

so that we do not start with a blank page. What you tried that fails is also good to know so that we do not pursue a dead end.