时间戳

首页 > 解决方案 > 我正在尝试将复杂的 SQL 查询转换为 MongoDB 查询，但是在为连接的结果集添加条件时失败

sql - 我正在尝试将复杂的 SQL 查询转换为 MongoDB 查询，但是在为连接的结果集添加条件时失败

问题描述

我编写了一个 SQL 查询，显示我想要的结果集。我想为 MongoDB 写等效的东西。我检查了文档和其他来源以使其实现，但我坚持添加条件。

这是我的 SQL 代码；

--MSSQL AND POSTGRESQL QUERY
SELECT
A.*,
B.SYMPTOM1,
B.SYMPTOM2,
B.SYMPTOM3,
B.SYMPTOM4,
B.SYMPTOM5,
C.RECVDATE,
CASE C.STATE WHEN NULL THEN 'Unknown' ELSE C.STATE END AS STATE,
CASE C.AGE_YRS WHEN NULL THEN 0 ELSE C.AGE_YRS END AS AGE_YRS,
(CAST(COALESCE(C.CAGE_YR,0) AS float) + COALESCE(C.CAGE_MO,0.0)) AS VAX_DATE_BIRTHDATE,
C.SEX,
C.RPT_DATE,
C.SYMPTOM_TEXT,
CASE C.DIED WHEN NULL THEN 'N' ELSE C.DIED END AS DIED,
C.DATEDIED,
CASE C.L_THREAT WHEN NULL THEN 'N' ELSE C.L_THREAT END AS L_THREAT,
CASE C.ER_VISIT WHEN NULL THEN 'N' ELSE C.ER_VISIT END AS ER_VISIT,
CASE C.HOSPITAL WHEN NULL THEN 'N' ELSE C.HOSPITAL END AS HOSPITAL,
CASE C.HOSPDAYS WHEN NULL THEN 0 ELSE C.HOSPDAYS END AS HOSPDAYS,
CASE C.X_STAY WHEN NULL THEN 'N' ELSE C.X_STAY END AS X_STAY,
CASE C.DISABLE WHEN NULL THEN 'N' ELSE C.DISABLE END AS DISABLE,
CASE C.RECOVD WHEN NULL THEN 'U' ELSE C.RECOVD END AS RECOVD,
C.VAX_DATE,
C.ONSET_DATE,
CASE C.NUMDAYS WHEN NULL THEN 0 ELSE C.NUMDAYS END AS NUMDAYS,
C.LAB_DATA,
CASE C.V_ADMINBY WHEN NULL THEN 'UNK' ELSE C.V_ADMINBY END AS V_ADMINBY,
CASE C.V_FUNDBY WHEN NULL THEN 'UNK' ELSE C.V_FUNDBY END AS V_FUNDBY,
C.OTHER_MEDS,
C.CUR_ILL,
C.HISTORY,
C.PRIOR_VAX,
C.SPLTTYPE,
C.FORM_VERS AS VAERS_FORM_VERSION,
C.TODAYS_DATE,
CASE C.BIRTH_DEFECT WHEN NULL THEN 'N' ELSE C.BIRTH_DEFECT END AS BIRTH_DEFECT,
CASE C.OFC_VISIT WHEN NULL THEN 'N' ELSE C.OFC_VISIT END AS OFC_VISIT,
CASE C.ER_ED_VISIT WHEN NULL THEN 'N' ELSE C.ER_ED_VISIT END AS ER_ED_VISIT,
CASE C.ALLERGIES WHEN NULL THEN 'none' ELSE C.ALLERGIES END AS ALLERGIES
    FROM VAERSVAX AS A
        INNER JOIN VAERSSYMPTOMS AS B ON
        A.VAERS_ID = B.VAERS_ID
        INNER JOIN VAERSDATA AS C ON
        A.VAERS_ID = C.VAERS_ID
            WHERE (B.SYMPTOM1 LIKE '%COVID%' OR B.SYMPTOM2 LIKE '%COVID%' OR B.SYMPTOM3 LIKE '%COVID%' OR B.SYMPTOM4 LIKE '%COVID%' OR B.SYMPTOM5 LIKE '%COVID%')
            AND A.VAX_TYPE = 'COVID19'

这是我对 MongoDB 等价物的尝试；

    db.VaersVax.aggregate([
   {
       $match: { "VAX_TYPE" : "COVID19" }
   },
   {
      $lookup:
      {
        from: "VaersSymptoms",
        localField: "VAERS_ID",
        foreignField: "VAERS_ID", 
        as: "VaersSymptoms" 
      },
   },
   {
      $lookup:
      {
        from: "VaersData",
        localField: "VAERS_ID",
        foreignField: "VAERS_ID", 
        as: "VaersData" 
      }
   },
   {
       $project:
       {
           VaersSymptoms:
           {
               $filter:
               {
                   input: "$VaersSymptoms",
                   as: "a",
                   cond: { $in: [ "$$a.SYMTOM1", [db.VaersSymptoms.find({"SYMPTOM1": /COVID/})]]}
               }
           }
       }
   }
]);

我对 SQL 没问题，但我是 MongoDB 的新手。所以我卡在这里。任何帮助，将不胜感激。

此致

标签： sqlregexmongodbjoinaggregation-framework

解决方案

我可以在比我更了解 mongo 的朋友的支持下解决它。总结一下，mongo脚本最后的状态是这样的；

db.VaersVax.aggregate([
   {
      $lookup:
      {
        from: "VaersSymptoms",
        localField: "VAERS_ID",
        foreignField: "VAERS_ID", 
        as: "VaersSymptoms" 
      }
   },
   {
      $lookup:
      {
        from: "VaersData",
        localField: "VAERS_ID",
        foreignField: "VAERS_ID", 
        as: "VaersData" 
      }
   },
   { $unwind: "$VaersSymptoms" },
   {
        "$match": {
            "$and":[
                {"VAX_TYPE":"COVID19"},
                {"$or":[
                {"VaersSymptoms.SYMPTOM1": /.*COVID.*/}, 
                {"VaersSymptoms.SYMPTOM2": /.*COVID.*/}, 
                {"VaersSymptoms.SYMPTOM3": /.*COVID.*/},
                {"VaersSymptoms.SYMPTOM4": /.*COVID.*/},
                {"VaersSymptoms.SYMPTOM5": /.*COVID.*/},
            ]}
                ]
        }
    }
]);

推荐阅读