how to represent this ohlc mongo query using spring mongodb aggregation function

Question

I'm wring an ohlc query for the stock, and the data is like:

{ "_id" : "52f97cba03646d746011199a" , "tradedAmount" : { "amount" : 1050000000 , "currency" : "USD"} , "tradedPrice" : { "amount" : 10500 , "currency" : "CNY"} , "orderBookIdentifier" : "1024d9ab-3c2a-4c06-a127-9ab717aa2474" , "tradeTime" : ISODate("2011-11-30T04:12:12.120Z") , "tradeType" : "BUY" , "audit" : { "created_by" : null , "created_on" : ISODate("2014-02-11T01:28:26.720") , "last_modified_by" : null } , "version" : 0})

aggregation js script is:

db.tradeExecutedEntry.aggregate(
[
    { "$match" : { "orderBookIdentifier" : "1024d9ab-3c2a-4c06-a127-9ab717aa2474" ,
        "tradeTime" : { "$gte" : ISODate("2006-12-12T04:12:12.120Z") , "$lt" : ISODate("2015-12-12T04:12:04.120Z")}}} ,
    { "$project" :
        {"tradeTime" : 1 , "tradedPrice" : "$tradedPrice.amount" , "priceCurrency" : "$tradedPrice.currency" ,
        "tradedAmount" : "$tradedAmount.amount" , "tradeCurrency" : "$tradedAmount.currency" ,
        "year" : { "$year" : [ "$tradeTime"]} ,
        "month" : { "$month" : [ "$tradeTime"]} ,
        "day" : { "$dayOfMonth" : [ "$tradeTime"]} ,
        "hour" : { "$hour" : [ "$tradeTime"]}}},
    { $sort : { tradeTime : 1, "tradedPrice.amount":-1} },
    {"$group":
        {"_id" : {"year": "$year", "month": "$month", "day": "$day", "hour": "$hour", "minute": "$minute" },
        "open": {"$first": "$tradedPrice"},
        "high": {"$max": "$tradedPrice"},
        "low": {"$min": "$tradedPrice"},
        "close": {"$last": "$tradedPrice"},
        "volume" : { "$sum" : "$tradedAmount"}}}

]
);

how can I use the aggregation framework to represent it? the below does not work properly(it returns a empty entry):

TypedAggregation<TradeExecutedEntry> aggregation = newAggregation(TradeExecutedEntry.class,
              match(where("orderBookIdentifier").is(orderBookIdentifier)
                      .and("tradeTime").gte(start)
                      .and("tradeTime").lt(end)),
              project("tradeTime", "tradedPrice", "tradedAmount")
                      .and("tradeTime").project("year").as("year")
                      .and("tradeTime").project("month").as("month")
                      .and("tradeTime").project("dayOfMonth").as("day")
                      .and("tradeTime").project("hour").as("hour")                ,
              sort(DESC, "tradeTime", "tradedPrice.amount"),
              group(Fields.from(Fields.field("priceCurrency", "tradedPrice.currency"))
                      .and(Fields.field("amountCurrency", "tradedAmount.currency"))
                      .and(Fields.field("year", "year"))
                      .and(Fields.field("month", "month"))
                      .and(Fields.field("day", "day"))
                      .and(Fields.field("hour", "hour"))
              )
                      .first("tradedPrice").as("open")
                      .max("tradedPrice").as("high")
                      .min("tradedPrice").as("low")
                      .last("tradedPrice").as("close")
                      .sum("tradedAmount").as("volume"),
              skip(pageable.getOffset()),
              limit(pageable.getPageSize())
      );
AggregationResults<OpenHighLowCloseVolume> result = mongoTemplate.aggregate(aggregation, OpenHighLowCloseVolume.class);

Can someone please help me out?

score 1 · Answer 1 · edited Jul 25 '16 at 04:00

I tried many times, and this works for me:

Aggregation chartAgg = Aggregation.newAggregation(
    Aggregation.match(Criteria.where("investorId").is(user.getId()).and("status").is(Constant.Terms.Status.RETURNING )),
    Aggregation.project("profit", "status").and("date").project("month").as("key"),
    Aggregation.group("key", "status").sum("profit").as("profit"),
    Aggregation.project("key", "profit"));

Good luck!

how to represent this ohlc mongo query using spring mongodb aggregation function

1 Answers1