SpringData - MongoDB fetch objects by IDs in the given order

user_4108590 0
Viewed 8231

I have a usecase like this. I have questions in my Mongo DB and have a CRUD micro service. There I have exposed an API method to fetch questions by list of IDs given via query params. Let say for the simplicity sake, user gives /api/questions?id=2, id=7, id=4, id = 5

then I need to return list of questions in that exact same order, like so

questions: [
    {
       id: 2,
       prompt: "prompt one",
       ...
    },
    {
        id: 7,
        prompt: "prompt two",
        ...
    },
    {
        id: 4,
        ...
    },
    {
        id: 5
        ...
    }
]

But notice that this is neither ASC nor DESC, rather can be any arbitrary order like /api/questions?id=2, id=7, id=4, id=5

I am using org.springframework.data.mongodb.repository.MongoRepository as my DAO class. Currently I am doing ordering inside my service layer after fetching the data through the repository which is based on spring-data. That solution works. But I would rather prefer to get this ordering done at the DAO repository level itself, since it costs less and does not add unnecessary complexity to my business logic at the service layer. Rather I can delegate the responsibility merely to the DB, assuming it is more capable of doing such things with more optimizations.

The Mongo document structure looks like this. enter image description here

Can I achieve this using spring data? If so how to get that thing done?

spring-data spring-boot mongodb spring java
1 Answers

If you are using MongoRepository then create an interface that extends it. Example:

public interface MyRepository extends MongoRepository<Question, String>{

    @Query("{_id: { $in: ?0 } })")
    List<Question> findByIds(List<String> ids, Sort sort);
}

Then in your Service class or wherever you use your repository. Add the following:

Sort sort = new Sort(Direction.ASC,"_id"); // Or DESC
List<Question> questionsById = repository.findByIds(ids, sort);

Of course Question here is a dummy class that I created to test it out, replace it with yours.

Ok if you need by input user order then you really can't come around using aggregation framework.

If I have the following dataset in my databse inserted in that order:

/* 1 */
{
    "_id" : ObjectId("5a103a434d8a2fe38bec5c5e"),
    "prompt" : "prompt one"
}

/* 2 */
{
    "_id" : ObjectId("5a103a434d8a2fe38bec5c60"),
    "prompt" : "prompt two"
}

/* 3 */
{
    "_id" : ObjectId("5a103a434d8a2fe38bec5c62"),
    "prompt" : "prompt three"
}

/* 4 */
{
    "_id" : ObjectId("5a103a434d8a2fe38bec5c64"),
    "prompt" : "prompt four"
}

Then the aggregation pipeline would have to look like:

db.getCollection('questions').aggregate([{
        "$match": {
            "_id": {
                "$in": [ObjectId("5a103a434d8a2fe38bec5c62"), ObjectId("5a103a434d8a2fe38bec5c60"), ObjectId("5a103a434d8a2fe38bec5c64"), ObjectId("5a103a434d8a2fe38bec5c5e")]
            },
        }
    },
    {
        "$project": {
            "orderByInputId": {
                "$cond": [{
                        "$eq": ["$_id", ObjectId("5a103a434d8a2fe38bec5c62")]
                    },
                    1,
                    {
                        "$cond": [{
                                "$eq": ["$_id", ObjectId("5a103a434d8a2fe38bec5c60")]
                            },
                            2,
                            {
                                "$cond": [{
                                        "$eq": ["$_id", ObjectId("5a103a434d8a2fe38bec5c64")]
                                    },
                                    3,
                                    4
                                ]
                            }
                        ]
                    }
                ]
            },
            prompt: 1
        }
    },

    // Sort the results
    {
        "$sort": {
            "orderByInputId": 1
        }
    }
])

and the results I get are the following:

/* 1 */
{
    "_id" : ObjectId("5a103a434d8a2fe38bec5c62"),
    "prompt" : "prompt three",
    "orderByInputId" : 1.0
}

/* 2 */
{
    "_id" : ObjectId("5a103a434d8a2fe38bec5c60"),
    "prompt" : "prompt two",
    "orderByInputId" : 2.0
}

/* 3 */
{
    "_id" : ObjectId("5a103a434d8a2fe38bec5c64"),
    "prompt" : "prompt four",
    "orderByInputId" : 3.0
}

/* 4 */
{
    "_id" : ObjectId("5a103a434d8a2fe38bec5c5e"),
    "prompt" : "prompt one",
    "orderByInputId" : 4.0
}
user_16906570
Related