Modifying entire document in nested array of documents

Working with Data
1

Hello!
I have a collection with documents like

[
  {
    _id: ObjectId("64ad9e7d9f6ecf83a7a693bb"),
    ip: '192.168.1.1',
    date: '1689017660',
    ports: [
      {
        number: 21,
        protocol: 'tcp',
        state: 'open',
        banner: 'product: ProFTPD hostname: 192.168.1.1 ostype: Unix',
        name: 'ftp,
        servicefp: '',
        scripts: '',
        checks: [ { name: 'Anonymous Access', result: 'success' } ]
      },
      {
        number: 80,
        protocol: 'tcp',
        state: 'open',
        banner: 'product: nginx',
        name: 'http',
        servicefp: '',
        scripts: '',
        checks: [],
      },
      {
        number: 444,
        protocol: 'tcp',
        state: 'open',
        name: 'some new'
      }
    ]
  }
]

And I want to add object into array checks like:
{ name: “some name”, description: “some description”, result: “some result”, …}
Number of keys of object is uknown. If element of array “checks” with name of new check already exists, then it should be replaced with new provided values. If element with such name doesn’t exists, then push it into array “checks”.

I’ve tried with arrayFilters and other ways, but can’t figure out how to do that. Any help would be appreciate

2

Hi @amenlust2 and welcome in the MongoDB Community! :muscle:

I have to say that this one made me sweat a little! :sweat_smile:

First of all, you have a typo in your document above, it’s missing a ' after the field ftp.

Then, I’m guessing that you need to provide some extra information to identify which element of the checks array needs to be updated in the ports array so I took the supposition / decision that number was unique in your array of ports and I assumed that this is how you identify which element to update as you probably don’t want to update all the checks arrays.

So I simplified a bit the document model for the sake of the example:

db.c.drop()
db.c.insertOne({
    "ip": "192.168.1.1",
    "ports": [
        {
            "number": 21,
            "checks": [
                {
                    "name": "ABC",
                    "result": "success"
                }
            ]
        },
        {
            "number": 80,
            "checks": []
        },
        {
            "number": 444
        }
    ]
})

Now that I have a sample document, let’s define some variables that I can use as query parameters.

const number = 21
const name = "DEF"
const description = "my new description"
const result = "my new result"

And here is the update query. This query loops through the ports array elements. When the provided number is found, it removes the existing document with the provided name from the checks array (if it exists) and then appends the new document at the end.

db.c.updateOne({"ip": "192.168.1.1", "ports.number": number}, [{
        "$set": {
            "ports": {
                "$map": {
                    input: "$ports",
                    as: "p",
                    in: {
                        "$cond": {
                            if: {"$eq": ["$$p.number", number]},
                            then: {
                                "$mergeObjects": ["$$p", {
                                    "checks": {
                                        "$concatArrays": [
                                            {
                                                "$filter": {
                                                    "input": "$$p.checks",
                                                    "cond": {"$ne": ["$$this.name", name]}
                                                }
                                            },
                                            [{"name": name, "description": description, "result": result}]
                                        ]
                                    }
                                }]
                            },
                            else: "$$p"
                        }
                    }
                }
            }
        }
    }]
)

Note that to support this query, you need the index:

db.c.createIndex({"ip": 1, "ports.number": 1})

Here is the result after the first execution of this query. As DEF doesn’t exist in the checks, it’s added in the array:

{
  _id: ObjectId("64cbf22a5f5937b78f2b78b4"),
  ip: '192.168.1.1',
  ports: [
    {
      number: 21,
      checks: [
        { name: 'ABC', result: 'success' },
        {
          name: 'DEF',
          description: 'my new description',
          result: 'my new result'
        }
      ]
    },
    { number: 80, checks: [] },
    { number: 444 }
  ]
}

Now if I provide new values for description and result but with the same name and run the query again, this time the array entry is “updated”. (It’s actually removed and re-added in the query but you get the idea…).

const description = "my OTHER description"
const result = "my OTHER result"

Result:

{
  _id: ObjectId("64cbf22a5f5937b78f2b78b4"),
  ip: '192.168.1.1',
  ports: [
    {
      number: 21,
      checks: [
        { name: 'ABC', result: 'success' },
        {
          name: 'DEF',
          description: 'my OTHER description',
          result: 'my OTHER result'
        }
      ]
    },
    { number: 80, checks: [] },
    { number: 444 }
  ]
}

Enjoy!
Maxime :smiley:

2 Likes
3

wow, seems really great. More over, your assumptions are correct. In your example I can find some new cool features for my app, but I have to learn and try them first. Thank you so much

1 Like
4

You are welcome!

I forgot to mention it but the main concept I’m using here is the update with an aggregation pipeline. You can notice it because the second parameter is an array, not a document.

This unlocks the power of the aggregation pipeline to perform the update operation and as you can see there is a bunch of possibilities with $mergeObject, $map, $cond, $filter, $concatArrays, …

If my answer was :white_check_mark:, I’d appreciate if you can select it as the solution. :smiley:

Feel free to open another topic if you have more “challenges”.

Cheers,
Maxime.

1 Like
5

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.