How to use spring-data-mongodb to implement complex expressions under $substr in Aggregation.project

Question

I have a mongodb query that should be converted to spring-data code.

db.collection.aggregate([
  {
    "$project": {
      "partnerid": 1,
      "market": {
        "$toUpper": [
          {
            "$substr": [
              "$pseudocitycode",
              0,
              3
            ]
          }
        ]
      },
      "office": {
        $toUpper: [
          {
            $substr: [
              "$pseudocitycode",
              {
                $subtract: [
                  {
                    $strLenCP: "$pseudocitycode"
                  },
                  2
                ]
              },
              2
            ]
          }
        ]
      },
      
    }
  },
  {
    "$group": {
      "_id": {
        "partnerid": "$partnerid",
        "market": "$market",
        "office": "$office"
      },
      "clickCount": {
        "$sum": 1
      }
    }
  }
])

I have tried using the following code to get started, but stuck how to get the office value.

//ArithmeticOperators.Subtract subtract = ArithmeticOperators.valueOf(StringOperators.valueOf("pseudocitycode").lengthCP()).subtract(2);
StringOperators.Substr market = StringOperators.valueOf("pseudocitycode").substring(0, 3);
ProjectionOperation projectionOperation = Aggregation.project("partnerid")
                    .and(StringOperators.valueOf(market).toUpper()).as("market")
                    .andExpression("{$toUpper:[{$substr:['$pseudocitycode',{$subtract:[{$strLenCP:'$pseudocitycode'},2]},2]}]}").as("office"); 

Can anyone help implement the query code above? Thanks in advance.

Answer 1

One simple way is you can parse JSON to Document and provide it to MongoCollection:

Document projectStage = Document.parse("{\"$project\":...}"); // your project stage JSON
Document groupStage = Document.parse("{\"$group\":...}"); // your group stage JSON

// pass to MongoCollection
List<Document> stages = List.of(projectStage, groupStage);
MongoCollection collection = database.getCollection(collectionName);
AggregateIterable<Document> result = collection.aggregate(stages);

---

You can still use the StringOperators and ArithmeticOperators in spring-data-mongodb to build your query.
But since there is no method that can accept AggregationExpression as the start or length in StringOperators.Substr, you may need to write your implementation about this. For example:

// similar design to the origin StringOperators.SubStr
public static class NewSubStr implements AggregationExpression {
    private final List<?> value;

    private NewSubStr(List<?> value) {
        this.value = value;
    }

    public static NewSubStr valueOf(String fieldReference) {
        return new NewSubStr(Fields.fields(fieldReference).asList());
    }

    private String getMongoMethod() {
        return "$substr";
    }

    public NewSubStr substring(AggregationExpression start) {
        return new NewSubStr(this.append(Arrays.asList(start, -1)));
    }

    private List<Object> append(List value) {
        List<Object> clone = new ArrayList<>(this.value);
        clone.addAll(value);
        return clone;
    }

    @Override
    public Document toDocument(AggregationOperationContext context) {
        return new Document(this.getMongoMethod(), this.unpack(value, context));
    }

    private Object unpack(Object value, AggregationOperationContext context) {
        if (value instanceof AggregationExpression) {
            return ((AggregationExpression)value).toDocument(context);
        } else if (value instanceof Field) {
            return context.getReference((Field)value).toString();
        } else if (value instanceof List) {
            List<Object> sourceList = (List)value;
            List<Object> mappedList = new ArrayList(sourceList.size());
            sourceList.stream().map((item) -> {
                return this.unpack(item, context);
            }).forEach(mappedList::add);
            return mappedList;
        } else {
            return value;
        }
    }
}

Then use your new SubStr like:

AggregationExpression strLenCP = StringOperators.valueOf("$pseudocitycode").lengthCP();
AggregationExpression subtract = ArithmeticOperators.Subtract.valueOf(strLenCP).subtract(2);
AggregationExpression substr = NewSubStr.valueOf("$pseudocitycode").substring(subtract);
AggregationExpression toUpper = StringOperators.valueOf(substr).toUpper();

ProjectionOperation projection = Aggregation.project().and(toUpper).as("pseudocitycode_upper");

Aggregation aggregation = Aggregation.newAggregation(projection);