Get a certain amount of data from the database by criteria

Working with Data
,
#1

Hello
I need help building a database query to return specific data
I have a Category document, it is stored in the database with the name categories which has fields ObjectId id, String title
There is also a LinguaSet document, it is stored in the database with the name lingua_sets, which has the fields ObjectId id, String title, List<Category> categories and other fields
The List<Category> is annotated with the @DBRef annotation to refer to the Category document. The Category document knows nothing about the LinguaSet document
The question is how to build a database query to return a List<LinguaSet> where will the LinguaSet document be located, which has a certain Category in the List<Category>?
There should be no more than three LinguaSets for each category.
That is, if there is a Category with id 1 and 5 LinguaSet whose List<Category> contains this Category with id 1, then only 3 LinguaSet should be returned.
You need to get it by Set<ObjectId> ids which will come to the function.

#3

Hey Yevhen,

Nice to see ya!

To build a database query to return a List where the LinguaSet document has a certain Category in the List, you can use the MongoDB aggregation pipeline with the $lookup and $match stages. Here is an example query:

Set<ObjectId> categoryIds = ... // set of category IDs to match
int limit = 3; // limit the number of LinguaSets per category to 3

List<Bson> pipeline = Arrays.asList(
        Aggregates.match(Filters.in("categories._id", categoryIds)),
        Aggregates.lookup("categories", "categories._id", "_id", "categories"),
        Aggregates.match(Filters.in("categories._id", categoryIds)),
        Aggregates.group("$_id", Accumulators.first("title", "$title"),
                Accumulators.first("categories", "$categories"),
                Accumulators.push("$$ROOT"), Accumulators.limit(limit)),
        Aggregates.project(Projections.fields(
                Projections.excludeId(), Projections.include("title", "categories"),
                Projections.computed("lingua_sets", "$$ROOT"))),
        Aggregates.unwind("$lingua_sets"),
        Aggregates.replaceRoot("$lingua_sets")
);

List<LinguaSet> result = linguaSetsCollection.aggregate(pipeline, LinguaSet.class).into(new ArrayList<>());

This query first matches LinguaSets that have at least one Category in the input set of category IDs. It then performs a $lookup stage to join the categories collection to the LinguaSet documents based on the _id and categories._id fields, and filters the results to only include Categories that match the input set of category IDs.

Next, the query groups the results by LinguaSet _id, and for each group, it keeps the title and categories fields from the first document, pushes the entire document onto an array, and limits the array to limit elements.

Then, the query projects the desired fields and renames the array to lingua_sets. Finally, it unwinds the lingua_sets array and replaces the root document with the array elements.

This query returns a list of up to limit LinguaSet documents per Category in the input set of category IDs that match the query criteria.

#4

Thanks for the answer
But as I can see Accumulators.push takes 2 parameters and Accumulators doesn’t have a limit method

#5

I am using spring boot 3.0.1 because some of the features are not available
How can they be replaced?

#6

My apologies for the mistake in my previous message. You are correct that the Accumulators.push method does not have a limit method, and it takes only one parameter. To limit the number of LinguaSets per category, which is what I had confused the Accumulators.push for, you can use the $slice operator instead. Here is the corrected query:

Set<ObjectId> categoryIds = ... // set of category IDs to match
int limit = 3; // limit the number of LinguaSets per category to 3

List<Bson> pipeline = Arrays.asList(
        Aggregates.match(Filters.in("categories._id", categoryIds)),
        Aggregates.lookup("categories", "categories._id", "_id", "categories"),
        Aggregates.match(Filters.in("categories._id", categoryIds)),
        Aggregates.group("$_id", Accumulators.first("title", "$title"),
                Accumulators.first("categories", "$categories"),
                Accumulators.push("$$ROOT"), Accumulators.first("count", "$count")),
        Aggregates.project(Projections.fields(
                Projections.excludeId(), Projections.include("title", "categories"),
                Projections.computed("lingua_sets", new Document("$slice", Arrays.asList("$lingua_sets", limit))))),
        Aggregates.unwind("$lingua_sets"),
        Aggregates.replaceRoot("$lingua_sets")
);

List<LinguaSet> result = linguaSetsCollection.aggregate(pipeline, LinguaSet.class).into(new ArrayList<>());

I used this on my local MBD in a similar way and it worked.

In this query, the $slice operator limits the lingua_sets array to contain only the first limit elements. The rest of the query remains the same as the previous example.

If this doesn’t work, please send whatever errors you’re getting etc.

**HOLD UP, I haven’t tested this with other versions of springboot, if it works let me know. If not, let me know but I’ll try it later with the version of boot you’re using.

#7

Thanks for the correction, but like I said earlier, Accumulators.push("$$ROOT") should have 2 parameters, not just one.
This is how it looks like in spring 3.0.1:

public static <TExpression> BsonField push(String fieldName, TExpression expression) {
         return accumulatorOperator("$push", fieldName, expression);
     }