MongoDB转换Select from UNION with Group

Question

我正在NodeJS中构建一个非常好的API，其中有关于我的国家的非常深入的细节。

由于巨额的帐单，我决定重写我的整个数据库，从MySQL到NoSQL，这是我更负担得起的。

基本上我要做的是，如果date_set为空，那么我将使用date_sampen作为代理。

我有以下MySQL查询，我需要将其转换为NoSQL。

SELECT count(a.cases) as cases, a.date FROM 
(SELECT date_specimen AS cases, date_specimen AS date from case_informations WHERE (date_specimen <> '' AND date_onset = '') 
UNION ALL
SELECT date_onset AS cases, date_onset AS date FROM case_informations WHERE date_onset <> '') AS a
GROUP BY a.date ORDER BY a.date ASC

文件:

{
    "_id": {
        "$oid": "5f29a6dcc4ce73be6be928ff"
    },
    "case_code": "C611583",
    "age": 20,
    "age_group": "20-24",
    "sex": "female",
    "date_specimen": "",
    "date_result_release": "2020-04-22",
    "date_rep_conf": "2020-04-24",
    "date_died": "",
    "date_recover": "",
    "removal_type": "recovered",
    "admitted": "no",
    "region_res": "NCR",
    "prov_res": "",
    "city_mun_res": "",
    "city_muni_psgc": "",
    "health_status": "recovered",
    "quarantined": "no",
    "date_onset": "",
    "pregnant_tab": "no",
    "validation_status": "Removal Type is \"Recovered\", but no Recovered Date is recorded\nRemoval Type is \"Recovered\", but no Recovered Date is recorded\nHealth Status is \"Recovered\", but no Date Recovered is recorded\nHealth Status is \"Recovered\", but no Date Recovered is recorded"
}

这是我能得到的最接近的结果:

collection.aggregate([
            {$project: {date_specimen: 1, date_onset: 1}},
            {$lookup:
                {
                  from: 'case_informations',
                  pipeline: [
                    {$match: {date_specimen: {$exists: true}, date_onset: ''}},
                    {$group: {_id: '$date_specimen', cases: {$sum: 1}}},
                    {$sort: {_id: 1}},
                  ],
                  as: 'a',
                },
            },
            {$lookup:
                {
                  from: 'case_informations',
                  pipeline: [
                    {$match: {date_onset: {$exists: true}}},
                    {$group: {_id: '$date_onset', cases: {$sum: 1}}},
                    {$sort: {_id: 1}},
                  ],
                  as: 'b',
                },
            },
            {$project: {'a': 1, 'b': 1}},
          ]).limit(1);

结果:

{
  _id: 5f29a6dcc4ce73be6be928fc,
a: [
    { _id: '2020-03-04', cases: 1 },
    { _id: '2020-03-06', cases: 8 },
    { _id: '2020-03-07', cases: 48 },
    {...}
   ],
b: [
    { _id: '2020-03-03', cases: 45 },
    { _id: '2020-03-04', cases: 32 },
    { _id: '2020-03-05', cases: 55 },
    {...}
]
}

应为:

{
  _id: 5f29a6dcc4ce73be6be928fc,
UnionOfAandC: [
    { _id: '2020-03-03', cases: 45 },
    { _id: '2020-03-04', cases: 33 }, // merge object with same date
    { _id: '2020-03-05', cases: 55 },
    { _id: '2020-03-06', cases: 8 },
    { _id: '2020-03-07', cases: 48 },
    {...},
   ],
}

Answer 1

经过反复试验，我最终使用$cond解决了这个问题

collection.aggregate([
            {
              $group: {
                _id: {
                  'date': {
                    $cond: {
                      if: {$eq: ['$date_onset', '']}, then: '$date_specimen', else: '$date_onset',
                    },
                  },
                },
                cases: {$sum: 1},
              },
            },
            {$sort: {'_id.date': 1}},
          ]);