时间戳

首页 > 解决方案 > 给定 id 不重复的 MongoDB 计数

mongodb - 给定 id 不重复的 MongoDB 计数

问题描述

我是 Mongodb 的初学者，并在下面给出了我的数据

{ "client_id" : 1, "invoice":[{"invoice_date" : "20-07-2020", "invoice_month" : "July 2020","account_status" : "Active"}]}
{ "client_id" : 1, "invoice":[{"invoice_date" : "20-07-2020", "invoice_month" : "July 2020","account_status" : "Active"}]}
{ "client_id" : 1, "invoice":[{"invoice_date" : "30-07-2020", "invoice_month" : "July 2020","account_status" : "Active"}]}
{ "client_id" : 2, "invoice":[{"invoice_date" : "03-08-2020", "invoice_month" : "August 2020","account_status" : "Active"}]}
{ "client_id" : 2, "invoice":[{"invoice_date" : "05-05-2020", "invoice_month" : "August 2020","account_status" : "Active"}]}
{ "client_id" : 3, "invoice":[{"invoice_date" : "10-06-2020", "invoice_month" : "June 2020","account_status" : "Active"}]}

我想计算没有所有重复值并在下面提到预期结果

{"client_id": 2 , "invoice_month" : "August 2020", "count": 1}
{"client_id": 1 , "invoice_month" : "July 2020", "count": 1}
{"client_id": 3 , "invoice_month" : "June 2020", "count": 1}

在这里，我在下面使用聚合，但它计数所有重复

[
    {
        "$match": {
            "client_id": "1",
            "invoice.account_status": "Active"
        }
    },
    {
        "$unwind": "$invoice"
    },
    {
        "$group": {
            "_id": '$invoice.invoice_month',
            "count": {"$sum": 1}
        }
    }
]

标签： mongodbmongodb-querypymongo

解决方案

下面提到的阶段将帮助您达到预期的结果

操场

db.collection.aggregate([
  {
    "$match": { //Considering only active accounts
      "invoice.account_status": "Active"
    }
  },
  {
    "$unwind": "$invoice" // reshaping the invoices
  },
  {
    $project: { //Converting string to date
      "invoice_date": {
        $dateFromString: {
          dateString: "$invoice.invoice_date",
          "format": "%d-%m-%Y"
        }
      },
      "client_id": 1
    }
  },
  {
    $sort: { //getting the latest invoice
      "invoice_date": -1
    }
  },
  {
    "$group": { //reshaping again to group the result
      "_id": "$client_id",
      "data": { //Only first record
        $first: "$$ROOT"
      }
    }
  },
  {
    $project: {//Projecting what is needed
      "_id": 0,
      "client_id": "$data.client_id",
      "invoice_date": "$data.invoice_date"
    }
  }
])

如果您想在输出中保留日期作为字符串，您可以更改最后一个阶段，如下所示

{
    $project: {
      "_id": 0,
      "client_id": "$data.client_id",
      "invoice_date": {
        "$dateToString": {
          date: "$data.invoice_date"
        }
      }
    }
  }

推荐阅读