时间戳

首页 > 解决方案 > 猫鼬查询中的嵌套选择

sql - 猫鼬查询中的嵌套选择

问题描述

我正在尝试将 SQL 转换为 Mongoose 查询，我发现它很混乱。这是我的架构。基本上我想从今天开始获取过去 7 天的数据

var mongoose = require('mongoose');  
var RequestSchema = new mongoose.Schema({  
  pickup_person_name:String,
  dest_person_name:String,
  pickup_person_phone:String,
  dest_person_phone:String,
  tranx_status:String,
  order_status:String,
  pickup_longitude:String,
  pickup_latitude:String,
  pickup_location_name:String,
  pickup_date:Date,
  dest_longitude:String,
  dest_latitude:String,
  dest_location_name:String,
  price:String,
  item_to_deliver:String,
  rider_id:String,
  rider_name:String,
  order_number:String,
  payment_mode:String
},{ autoCreate: true});
mongoose.model('Request', RequestSchema);

这是我要转换为 Mongoose 的查询。

SELECT pickup_date, count(*) FROM (SELECT pickup_date as event_date FROM requests where DATE(pickup_date) >= (CURRENT_DATE -  '7 DAYS')   ) GROUP BY pickup_date ORDER BY pickup_date

I expect to have a list like 
[ { event_date:27/08/2020,count:8},{ event_date:26/08/2020,count:9},{ event_date:25/08/2020,count:9}]

标签： sqlnode.jsmongodbexpressmongoose

解决方案

你想使用这样的东西：

db.collection.aggregate([
  {
    $match: {
      $expr: {
        $gte: [
          "$pickup_date",
          {
            "$subtract": [
              "$$NOW",
              604800000
            ]
          }
        ]
      }
    }
  },
  {
    $group: {
      _id: {
        year: {
          $year: "$pickup_date"
        },
        month: {
          $month: "$pickup_date"
        },
        day: {
          "$dayOfMonth": "$pickup_date"
        }
      },
      count: {
        $sum: 1
      }
    }
  },
  {
    $project: {
      count: 1,
      _id: 0,
      event_date: {
        "$concat": [
          {
            "$toString": "$_id.day"
          },
          "/",
          {
            "$toString": "$_id.month"
          },
          "/",
          {
            "$toString": "$_id.year"
          }
        ]
      }
    }
  }
])

Mongo游乐场

推荐阅读