Question 191103 in Mongodb Question posted in
The official documentation can be found here.

complex @Aggregation gives me `UncategorizedMongoDbException`

I have some data like this:

[
{
  "id": "dc4b9a2f-12b3-42f8-b0b3-dd1bff19fccc",
  "name": "name 1",
  "fields": [
     {"id": 2},{"id": 1}, {"id": 3}
  ],
  "order": [1,3,2]
},
{
  "id": "dc4b9a2f-12b3-42f8-b0b3-dd1bff19fccd",
  "name": "name 2",
  "fields": [
     {"id": 1},{"id": 2}, {"id": 3}
  ],
  "order": [1,4,2]
}
]

Now I want to sort fields field based on their id, but the order will come from order field.

So the final response should be like:

[
{
  "id": "dc4b9a2f-12b3-42f8-b0b3-dd1bff19fccc",
  "name": "name 1",
  "fields": [
     {"id": 1},{"id": 3}, {"id": 2}
  ],
  "order": [1,3,2]
},
{
  "id": "dc4b9a2f-12b3-42f8-b0b3-dd1bff19fccd",
  "name": "name 2",
  "fields": [
     {"id": 1},{"id": 2}, {"id": 3}
  ],
  "order": [1,4,2]
}
]

Here is the query i’ve written:

@Aggregation(pipeline = {
       "{$unwind: '$fields'}",
       "{$sort: {'fields.id': 1, 'order': 1}}",
       "{$group: {_id: '$_id', name: {$first: '$name'}, fields: {$push: '$fields'}, order: {$first: '$order'}}}",
       "{$project: {_id: 1, name: 1, fields: 1, order: 1}}"
})

But it gives me:

Caused by: org.springframework.data.mongodb.UncategorizedMongoDbException: 
Command failed with error 40234 (Location40234): 'The field 'id' must be an accumulator object' on server localhost:27017. 
The full response is {"ok": 0.0, "errmsg": "The field 'id' must be an accumulator object", "code": 40234, "codeName": "Location40234"}; 
  nested exception is com.mongodb.MongoCommandException: Command failed with error 40234 (Location40234): 'The field 'id' must be an accumulator object' on server localhost:27017. 
The full response is {"ok": 0.0, "errmsg": "The field 'id' must be an accumulator object", "code": 40234, "codeName": "Location40234"}
Tags:

2

Answers


  1. 0

    One option is to use $reduce:

    db.collection.aggregate([
  {$set: {
      fields: {$reduce: {
          input: "$order",
          initialValue: [],
          in: {$concatArrays: [
              "$$value",
              [{$arrayElemAt: [
                    "$fields",
                    {$indexOfArray: ["$fields.id", "$$this"]}
              ]}]
          ]}
      }}
  }}
])

    See how it works on the playground example

    Login or Signup to reply.
  2. 0

    Instead of $unwind, $sort and $group, you may use $sortArray if you use MongoDB version 5.2 or newer. Should be much faster and much less code:

    db.collection.aggregate([
  {
    $set: {
      fields: {
        $sortArray: {
          input: "$fields",
          sortBy: { id: 1 }
        }
      }
    }
  }
])

    Mongo Playground

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search