时间戳

首页 > 解决方案 > 如何在具有多个状态的集合中获得 mongodb 中的第二高状态

mongodb - 如何在具有多个状态的集合中获得 mongodb 中的第二高状态

问题描述

    /* 1 */
    {
        "_id" : ObjectId("5f0b6699486ea60a2a9c62fh"),
        "EventDatetime" : "2020-07-12T19:38:00.653",
        "Eventstatus" : "05",
        "DelNumber" : "0703676929",
        "DELIVERY_ITEM" : [ 
            {
                "Product_Code" : "123",
                "Product_Name" : "utensils",
                "Shipment_Quantity" : "12",
            }, 
            {
                "Product_Code" : "456",
                "Product_Name" : "clothes",
                "Shipment_Quantity" : "10",
            }
        ]
    }
    {
        "_id" : ObjectId("5f0b6699486ea60a2a9c62fi"),
        "EventDatetime" : "2020-07-12T19:38:00.653",
        "Eventstatus" : "03",
        "DelNumber" : "0703676929",,
        "DELIVERY_ITEM" : [ 
            {
                "Product_Code" : "123",
                "Product_Name" : "utensils",
                "Shipment_Quantity" : "12",
            }, 
            {
                "Product_Code" : "456",
                "Product_Name" : "clothes",
                "Shipment_Quantity" : "10",
            }
        ]
    }
    {
        "_id" : ObjectId("5f0b6699486ea60a2a9c62fg"),
        "EventDatetime" : "2020-07-12T19:38:00.653",
        "Eventstatus" : "02",
        "DelNumber" : "0703676929",
        "DELIVERY_ITEM" : [ 
            {
                "Product_Code" : "123",
                "Product_Name" : "utensils",
                "Shipment_Quantity" : "12",
            }, 
            {
                "Product_Code" : "456",
                "Product_Name" : "clothes",
                "Shipment_Quantity" : "10",
            }
        ]
    }
    /*2*/

    {
        "_id" : ObjectId("5f0b6699486ea60a2a9c62fa"),
        "EventDatetime" : "2020-07-12T19:38:00.653",
        "Eventstatus" : "04",
        "DelNumber" : "0703676928",
        "DELIVERY_ITEM" : [ 
            {
                "Product_Code" : "123",
                "Product_Name" : "utensils",
                "Shipment_Quantity" : "12",
            }, 
            {
                "Product_Code" : "456",
                "Product_Name" : "clothes",
                "Shipment_Quantity" : "10",
            }
        ]
    }
    {
        "_id" : ObjectId("5f0b6699486ea60a2a9c62fb"),
        "EventDatetime" : "2020-07-12T19:38:00.653",
        "Eventstatus" : "02",
        "DelNumber" : "0703676928",
        "DELIVERY_ITEM" : [ 
            {
                "Product_Code" : "123",
                "Product_Name" : "utensils",
                "Shipment_Quantity" : "12",
            }, 
            {
                "Product_Code" : "456",
                "Product_Name" : "clothes",
                "Shipment_Quantity" : "10",
            }
        ]
    }
    {
        "_id" : ObjectId("5f0b6699486ea60a2a9c62fc"),
        "EventDatetime" : "2020-07-12T19:38:00.653",
        "Eventstatus" : "01",
        "DelNumber" : "0703676928",
        "DELIVERY_ITEM" : [ 
            {
                "Product_Code" : "123",
                "Product_Name" : "utensils",
                "Shipment_Quantity" : "12",
            }, 
            {
                "Product_Code" : "456",
                "Product_Name" : "clothes",
                "Shipment_Quantity" : "10",
            }
        ]
    }

我正在收集订单。将其视为电子商务网站。现在，订单可以有多个状态 00,01,02,03,04 和 05 。05是指订单的实时跟踪。所以我的 delno.1 可以有状态 01,02,05,05,03,05,05。我的问题是如何获得我运行的查询将 03 作为最新状态而不是 05。此外，当状态达到 04 时，我不需要检索该记录。任何帮助都会很棒！

标签： mongodbaggregation-frameworkquery-optimization

解决方案

你可以试试，

  • $match您的条件，DelNumber 是“0703676929”并且 Eventstatus 不等于 4
  • $addFields使用在整数字段中添加 Eventstatus$toInt
  • $sort按状态字段降序排列
  • $skip在第二页 (1) 因为第一个是 5 状态
  • $limit单条记录
db.collection.aggregate([
  { 
    $match: { 
      DelNumber: "0703676929",
      Eventstatus: { $ne: "04" } 
    } 
  }, 
  { $addFields: { status: { $toInt: "$Eventstatus" } } },
  { $sort: { status: -1 } },
  { $skip: 1 },
  { $limit: 1 }
])

操场

第二种方式，您可以在第 5 种状态下使用修复条件，

  • $match您的条件，DelNumber 是“0703676929”并且 Eventstatus 不等于 4th 和 5th
  • $addFields使用在整数字段中添加 Eventstatus$toInt
  • $sort按状态字段降序排列
  • $limit单条记录
db.collection.aggregate([
  {
    $match: {
      DelNumber: "0703676929",
      Eventstatus: { $nin: ["05", "04"] }
    }
  },
  { $addFields: { status: { $toInt: "$Eventstatus" } } },
  { $sort: { status: -1 } },
  { $limit: 1 }
])

操场

推荐阅读