mongodb update document from first element of array

Question

Consider a collection client with the following documents:

[
  {
    "id": 1,
    "Name": "Susie",
    "ownership" : {
        "ownershipContextCode" : "C1"
    },
    "clientIds": [
      {
        "clientClusterCode": "clientClusterCode_1",
        "clientId": "11"
      }
    ]
  },
  {
    "id": 2,
    "Name": "John",
    "ownership" : {
        "ownershipContextCode" : "C2"
    },
    "clientIds": [
      {
        "clientClusterCode": "clientClusterCode_2",
        "clientId": "22"
      }
    ]
  }
]

I am attempting to set a field (ownershipClientCode) as the first element of the clientIds array. The result should be like that:

[
  {
    "id": 1,
    "Name": "Susie",
    "ownership" : {
        "ownershipContextCode" : "C1",
        "ownershipClientCode" : "clientClusterCode_1"
    },
    "clientIds": [
      {
        "clientClusterCode": "clientClusterCode_1",
        "clientId": "11"
      }
    ],
    
  },
  {
    "id": 2,
    "Name": "John",
    "ownership" : {
        "ownershipContextCode" : "C2",
        "ownershipClientCode" : "clientClusterCode_2"
    },
    "clientIds": [
      {
        "clientClusterCode": "clientClusterCode_2",
        "clientId": "22"
      }
    ],
    
  }
]

I'm using this query but I can't get sub object from the first element in the array

db.collection.aggregate([
  {
    $addFields: {
      "Last Semester": {
        "$arrayElemAt": [
          "$clientIds",
          0
        ]
      }
    }
  }
])

This query add the all object but I want only the field (clientClusterCode).

Some thing like that

db.collection.aggregate([
  {
    $addFields: {
      "Last Semester": {
        "$arrayElemAt": [
          "$clientIds",
          0
        ].clientClusterCode
      }
    }
  }
])

I'm using mongodb 4.0.0

dangarfield · Accepted Answer · 2022-12-05 12:27:40Z

2

You're very close: https://mongoplayground.net/p/HY1Pj0P4z12

db.collection.aggregate([
  {
    $addFields: {
      "ownership.ownershipClientCode": {
        "$arrayElemAt": [
          "$clientIds.clientClusterCode",
          0
        ]
      }
    }
  }
])

You can use the dot notation within the $arrayElemAt as well as when you defining the field name.

To directly set the field, do something like this (use aggregation in the update): https://mongoplayground.net/p/js-usEJSH_A

db.collection.update({},
[
  {
    $set: {
      "ownership.ownershipClientCode": {
        "$arrayElemAt": [
          "$clientIds.clientClusterCode",
          0
        ]
      }
    }
  }
],
{
  multi: true
})

Note: The second method to update needs to be an array, so that it functions as an pipeline.

edited Dec 5, 2022 at 12:27

answered Dec 5, 2022 at 11:14

dangarfield

2,3501 gold badge15 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

rennajihi Over a year ago

Thank you so much, that answers my question ^^. Just one more question, this query only get the result output but not updating the collection with new values

rennajihi Over a year ago

I tried with { $out: 'clients' } but I got "a collection 'clients.clients' already exists

dangarfield Over a year ago

I updated the above to include a method for directly settings the values

rennajihi Over a year ago

Thank you, But it seems not working on 4.0.0 server, I got this error "MongoServerError: Expected type object but found array."

dangarfield Over a year ago

I appreciate that. I would kindly advise that version 4.0 is not supported any longer, this code works with the minimum supported version and all above (4.2). I believe the easiest way to do this in v4.0 would be to aggregate and get the results then set the specific values in an iterative process setting to values as a separate action. Version 4.0 $set is not particularly feature rich. Hence my 'note' in the answer.

Collectives™ on Stack Overflow

mongodb update document from first element of array

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related