比较聚合内的多个值并在mongodb中的数组中显示结果 [英] compare multiple values inside aggregation and display result in array in mongodb

问题描述

我有下表:-

id order_number product_number order_status schedule_datetime
1  001          001.1          SUCCESS      20180103
2  001          001.2          SUCCESS      20180102
3  111          111.1          SUCCESS      20171225
4  111          111.2          SUCCESS      20171224
5  222          222.1          INPROGRESS   20171122
6  222          222.2          ON_HOLD      20171121
7  222          222.3          PARTLY_SUCCESS 20171121

我在这里尝试的操作，如果任何product_number匹配查询中给定的状态，它应该显示年份和order_numbers列表.

What i am trying here, if any of the product_number matches the status given in the query, it should display the year and list of order_numbers.

输入1:order_status = SUCCESS，下面的输出应该出现

Input 1: order_status = SUCCESS, below output should come

{
"order_statuses" : [
    "SUCCESS"
],
"year" : "2018",
"order_number" : "001"
},

{
 "order_statuses" : [
    "SUCCESS"
],
"year" : "2017",
"order_number" : "111"
}

输入2:order_status = PARTLY_SUCCESS，下面的输出应该出现

Input 2: order_status = PARTLY_SUCCESS, below output should come

{
"order_statuses" : [
    "PARTLY_SUCCESS",
    "ON_HOLD",
    "INPROGRESS"
],
"year" : "2017",
"order_number" : "222"

} 借助此链接转换mysql查询包含sum和group_concat到mongodb查询我试图获得所需的结果，但是下面的查询给出了错误字段名称'$ addFields'不能是运算符名称".由于我不想显示计数，因此我尝试将其添加到单独的组中，但这无济于事.

} With the help of this link convert mysql query contains sum and group_concat to mongodb query I have tried to get the desired result, but below query gives error "The field name '$addFields' cannot be an operator name". As i don't want to display count i tried adding to separate group but it didn't help.

db.order_summary.aggregate([

{"$project":{
    "schedule_datetime":1,
    "order_number ":1,
    "order_status":{"$ifNull":["$order_status",""]}
}},
{"$group":{
    "_id":{
    "order_number ":"$order_number ",
    "order_status":"$order_status"
    },
    "study_date":{"$first": "$study_date"}
}},
{"$sort":{"_id.order_status": 1}},
{"$group":{
    "_id":{
    "order_number ":"$_id.order_number "
    },
    "study_date":{"$first": "$study_date"},
    "order_status":{"$push": "$_id.order_status"}
}},
{"$group":{
    "_id":{
        "$substr":["$study_date",0,4]
    },
    "count":{
        "$sum":{
            "$cond": [
                {"$in": ["$order_status",[["SUCCESS"],["INPROGRESS","SUCCESS"]]]}, 

                1,0
             ]
        }
    },
    "order_numbers":{"$push":"$_id.order_number "},
    "$addFields":{"$size":"$order_numbers"},


}},
{"$match":{"order_numbers":{"$gt":0}}},
{"$sort":{"_id":-1}}

])

请帮助显示上面输出的年份和订单编号列表，谢谢！

Could you please help to display the year and list of order_numbers as per above output, Thanks!

推荐答案

您可以在下面的汇总查询中进行尝试.

You can try below aggregation query.

第一个$group获取不同的订单号和状态组合，并选择最新的时间戳.

First $group to get distinct order number and status combination and pick the latest time stamp.

第二个$group按年份和状态进行分组，并选择所有订单号.

Second $group to get the grouping by year and status and pick all the order numbers.

$match将状态组与输入状态进行比较.

$match to compare the status group against the input status.

db.order_summary.aggregate([
  {"$project":{
    "schedule_datetime":1,
    "order_number ":1,
    "order_status":{"$ifNull":["$order_status",""]
    }
  }},
  {"$sort":{"schedule_datetime":-1}},
  {"$group":{
    "_id":{
      "order_number":"$order_number",
      "order_status":"$order_status"
    },
    "schedule_datetime":{"$first":"$schedule_datetime"}
  }},
  {"$group":{
    "_id":{
       "year":{"$substr":["$schedule_datetime",0,4]},
       "order_number":"$_id.order_number"
      },
     "order_statuses":{"$push":"$_id.order_status"}
   }},
  {"$redact": {"$cond":[{"$in": ["SUCCESS","$order_statuses"]},"$$KEEP","$$PRUNE"]}},
  {"$sort":{"_id.year":-1}},
  {"$project":{"_id":0, "year":"$_id.year","order_number":"$_id.order_number", "order_statuses":1 }},
])

这篇关于比较聚合内的多个值并在mongodb中的数组中显示结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！