每列存在MongoDb [英] MongoDb Exists per column

问题描述

运行如下聚合：

[ 
   { 
      "$match":{ 
         "datasourceName":"Startup Failures",
         "sheetName":"Data",
         "Cost":{ 
            "$exists":true
         },
         "Status":{ 
            "$exists":true
         }
      }
   },
   { 
      "$group":{ 
         "Count of Cost":{ 
            "$sum":1
         },
         "Count of Status":{ 
            "$sum":1
         },
         "_id":null
      }
   },
   { 
      "$project":{ 
         "Count of Cost":1,
         "Count of Status":1
      }
   }
]

存在过滤器的结果实际上是过滤掉不存在成本或状态的整个文档。这样成本和状态的预测（计数）是相同的。我不想只过滤单个列中的整个文档，这样我得到的投影是存在成本的文档数（成本计数），而另一个投影是存在状态的文档数。对于我的数据，这些将给出两个单独的数字。

The result of the exists filters actually filters out the whole documents where "Cost" or "Status" do not exist. Such that the projection (Count) of both Cost and Status are the same. I don't want to filter the whole document only the individual columns such that the projection I get is the number of documents where Cost exists (Count of Cost) and the other projection is the number of documents where Status exists. In the case of my data these would give two separate numbers.

推荐答案

我使用 $进行汇总方面；这允许并行查询每个文档传递。因此，我们将成本和状态进行查询并计为同一查询的两个方面。

I have an aggregation using $facet; this allows do queries in parallel for each document pass. So, we query and count the Cost and Status as two facets of the same query.

db.test.aggregate( [
  {
      $match: { fld1: "Data" }
  },
  { 
      $facet: {
          cost: [
              { $match: { cost: { $exists: true } } },
              { $count: "count" }
          ],
          status: [
              { $match: { status: { $exists: true } } },
              { $count: "count" }
          ],
      }
  },
  { 
      $project: { 
          costCount: { $arrayElemAt: [ "$cost.count" , 0 ] },
          statusCount: { $arrayElemAt: [ "$status.count" , 0 ] }
      } 
  }
] )

我得到的结果是 { costCount：4，4， statusCount：3} ，使用以下命令文档：

I get a result of { "costCount" : 4, "statusCount" : 3 }, using the following documents:

{ _id: 1, fld1: "Data", cost: 12, status: "Y" },
{ _id: 2, fld1: "Data", status: "N" },
{ _id: 3, fld1: "Data" },
{ _id: 4, fld1: "Data", cost: 90 },
{ _id: 5, fld1: "Data", cost: 44 },
{ _id: 6, fld1: "Data", cost: 235, status: "N" },
{ _id: 9, fld1: "Stuff", cost: 0, status: "Y" }

注意：这是一个使用方面的类似查询： MongoDB自定义排序基于两个字段。

NOTE: Here is a similar query using the facets: MongoDB Custom sorting on two fields.

这篇关于每列存在MongoDb的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！