[FIXED] Can't filter embedded document already sliced in mongodb in spring boot

Issue

I have an entity called Category with a embedded collection of Products, as you can see bellow.

{
_id: 1,
name: 'smartphones',
products: [
    {
        _id: '14',
        name: 'name',
        visibility: 'VISIBLE'
        ...
        
    },
    {
        _id: '12',
        name: 'name',
        visibility: 'NOT_VISIBLE'
        ...
    },
],
}

I am implementing a feature where I am getting a category and filtering the products from it by page and size (for pagination), using $slice operator, as you can see bellow (argument id is categoryId, page and size are used in $slice operator):

@Query(value = "{ 'id' : ?0}", fields = "{name: 1, size: 1, products: {$slice: [?1, ?2]}}")
Optional<Category> findCategoryProducts(Long id, int page, int size);

Product have a parameter called visibility and I want to filter sliced products by visibility equals to 'VISIBLE'. I already read about $filter, but I still haven't been able to implement with it in spring boot.

UPDATE: I tried first filter the products when visibility equals to 'VISIBLE'. I don't understand why, but the code bellow is not filtering, it gives an exception: "org.bson.BsonInvalidOperationException: readEndDocument can only be called when ContextType is DOCUMENT or SCOPE_DOCUMENT, not when ContextType is ARRAY"

@Query(value = "{ 'id' : ?0}", fields = "{'name': 1, 'size': 1, 'products': {$filter: {'input': $products, 'as': 'prod', 'cond': {$eq: [$$prod.visibility, 'VISIBLE']}}}}")
Optional<Category> findCategoryProducts(Long id, int page, int size);

After the filter works, the next step will be to put the $slice operator, but I don't know how?

[SOLVED]

Varman solved the problem. The final solution to the problem is bellow.

@Override
    public Optional<Category> findCategoryProducts(Long id, int page, int productsPerPage) {
        Aggregation aggregation = Aggregation.newAggregation(
                match(new Criteria("_id").is(id)),    //    first get the category by id
                project().and(
                        filter("products")
                                .as("pro")
                                .by(valueOf("pro.visibility").equalToValue(Visibility.VISIBLE.name())))
                        .as("products"),
                project().and("products").slice(page*productsPerPage, (page*productsPerPage) - productsPerPage).as("products")
        ).withOptions(AggregationOptions.builder().allowDiskUse(Boolean.TRUE).build());
        AggregationResults<Category> res = mongoTemplate.aggregate(aggregation, mongoTemplate.getCollectionName(Category.class), Category.class);
        // other code ...
    }

Solution

Hope you need to use aggregations

The the query is

db.collection.aggregate([
  {
    $project: {
      products: {
        "$filter": {
          "input": "$products",
          "as": "pro",
          "cond": { $eq: [ "$$pro.visibility", "VISIBLE" ] }
        }
      }
    }
  },
  {
    "$project": {
      products: {
        "$slice": [ "$products", 0, 2 ]
      }
    }
  }
])

Working Mongo playground

@Autowired
private MongoTemplate mongoTemplate;

public List<YOUR_CONVERTER_CLASS> test(String questionId,List<Integer> ratingList) {

    Aggregation aggregation = Aggregation.newAggregation(   
        project().and(
            filter("products")
                .as("pro")
                .by(valueOf("pro.visibility").equalToValue("VISIBLE")))
            .as("products"),
        project().and("products").slice(0, 10).as("products")
    ).withOptions(AggregationOptions.builder().allowDiskUse(Boolean.TRUE).build());

    return mongoTemplate.aggregate(aggregation, mongoTemplate.getCollectionName(YOUR_COLLECTION.class), YOUR_CONVERTER_CLASS.class).getMappedResults();

}

static imports

import static org.springframework.data.mongodb.core.aggregation.Aggregation.*;
import static org.springframework.data.mongodb.core.aggregation.ArrayOperators.Filter.filter;
import static org.springframework.data.mongodb.core.aggregation.ComparisonOperators.Eq.valueOf;

Note : The above java code was not tested. But it was written from working mongo playground

Answered By - varman