I need help with lookup aggregation

Working with Data
,
1

I have 2 collections that are in the same DB.
See examples of documents:

  1. A collection called cavity and the second collection called test
    These are the documents for example:

***cavity-

{
“_id”: {
“$oid”: “656965e7a4353114cceaf4de”
},
“rmt_srv”: {
“ip”: “192.168.14.247”,
“host”: “”,
“pn”: “1185_MINI_GW_GSM_M_B02”,
“os”: “”,
“location”: “afula”
},
“develop”: true,
“time”: {
“$date”: “2023-12-01T06:49:43.489Z”
},
“pn_cfg”: “7602480”,
“cavity”: “1”,
“type”: “finish”,
“_exec_st”: {
“$oid”: “65696593a4353114cceaf499”
},
“_log”: “e9d1f95f-9591-446b-a7f5-faa1f3993079”,
“cavity_st”: {
“$date”: “2023-12-01T06:48:19.924Z”
},
“batch”: “”,
“pass”: true,
“_cav_st”: {
“$oid”: “65696593a4353114cceaf49a”
},
“user”: “tst”,
“faulty_test_name”: null,
“_cfg”: “647c4e796a3de000018dfb4a”,
“duration”: 83565,
“pn_cfg_rev”: “4762”,
“serial”: “11161804”,
“dut”: {
“pn”: “0634480”,
“model”: “PROG GATEWAY-S5”,
“rev”: “4A2”,
“desc”: “PROG CUBE SAS-Security-Gateway Fibocom”
},
“production_step”: “debug”
}

***test-

{
“_id”: {
“$oid”: “65684c158d00894df459145b”
},
“rmt_srv_pn”: “TF_1197_FW2_GBD_B”,
“fail”: [
“11209845”,
“11211548”,
“11209882”,
“11076777”,
“11076768”,
“11209903”,
“11211528”
],
“pass”: [
“11238822”,
“11076764”,
“10569751”
],
“total”: 10,
“stage_id”: “06-Board level”,
“serial_combined”: {
“10569751”: “pass”,
“11076764”: “pass”,
“11076768”: “fail”,
“11076777”: “fail”,
“11209845”: “fail”,
“11209882”: “fail”,
“11209903”: “fail”,
“11211528”: “fail”,
“11211548”: “fail”,
“11238822”: “pass”
},
“serials_return_period”:
}

****I need help writing an aggregation that will do the following:

  1. You will receive a time range from date to date and perform a scan of all the serial_combined and search in the collection of the cavity if those serials exist, if so write the one that exists in a new field

All my attempts didn’t work

2

Hello, welcome to the MongoDB community. I don’t know if I understand the idea 100%, but try to see if this helps you:

db.test.aggregate([
     {
         // Step 1: Match documents in 'test' collection based on a time range
         $match: {
             // Assuming you have a 'date' field in the 'test' collection
             // Replace with your actual date field and desired date range
             gives you: {
                 $gte: ISODate("2023-01-01T00:00:00.000Z"),
                 $lte: ISODate("2023-12-31T23:59:59.999Z")
             }
         }
     },
     {
         // Step 2: Unwind the 'serial_combined' field
         $unwind: "$serial_combined"
     },
     {
         // Step 3: Lookup from 'cavity' collection
         $lookup: {
             from: "cavity",
             localField: "serial_combined",
             foreignField: "serial",
             as: "cavity_match"
         }
     },
     {
         // Step 4: Filter matches (keep documents with a non-empty 'cavity_match')
         $match: {
             cavity_match: { $ne: [] }
         }
     },
     {
         // Step 5: Project or add fields (optional)
         $project: {
             // Define the fields you want in the output
             serial: "$serial_combined",
             cavity_data: "$cavity_match",
             // Include other fields as needed
         }
     }
]);
3

See identical or related post Help with aggregation - #2 by steevej

@Amit_Faibish, please update your posts after reading

4

hi samuel,
tnx its works…
I have another question if possible?
I have 2 collections.
One named “test” and the other named “return”
I am attaching an example for your information:
“test”****

{
  "_id": {
    "$oid": "656d92878d00894df4a17a9b"
  },
  "rmt_srv_pn": "1738_RMT_SECTOR_M_V02",
  "fail": [
    "10705689",
    "10705668",
    "10705573",
    "10705065",
    "10701339",
    "10706051",
    "10701247",
    "10706023",
    "10705465"
  ],
  "pass": [
    "10706217",
    "10700962",
    "10704946",
    "10705440",
    "10706181",
    "10706030",
    "10706022",
    "10706245",
    "10705666",
    "10705664",
    "10701313",
    "10706037",
    "10705657",
    "10706015",
    "10701335",
    "10705436",
    "10701328",
    "10705893",
    "10705900",
    "10705904",
    "10705449",
    "10705630",
    "10705629",
    "10705453",
    "10706043",
    "10705462",
    "10701344"
  ],
  "total": 36,
  "stage_id": "Verification_level",
  "serials_return_period": [],
  "serial_combined": [
    "10700962",
    "10701247",
    "10701313",
    "10701328",
    "10701335",
    "10701339",
    "10701344",
    "10704946",
    "10705065",
    "10705436",
    "10705440",
    "10705449",
    "10705453",
    "10705462",
    "10705465",
    "10705573",
    "10705629",
    "10705630",
    "10705657",
    "10705664",
    "10705666",
    "10705668",
    "10705689",
    "10705893",
    "10705900",
    "10705904",
    "10706015",
    "10706022",
    "10706023",
    "10706030",
    "10706037",
    "10706043",
    "10706051",
    "10706181",
    "10706217",
    "10706245"
  ]
}

“return”***

{
  "_id": {
    "$oid": "656d82f38d00894df494a1f1"
  },
  "serial": "11115711",
  "cavity_data": [
    {
      "_id": {
        "$oid": "653674d81e3a310cc0f4b4d3"
      },
      "rmt_srv": {
        "ip": "192.168.12.105",
        "host": "",
        "pn": "1985_CP_MINI_GW_V12_A_B01",
        "os": "",
        "location": "afula"
      },
      "develop": false,
      "time": {
        "$date": "2023-10-23T16:27:52.719Z"
      },
      "pn_cfg": "7620940",
      "cavity": "1",
      "type": "finish",
      "_exec_st": {
        "$oid": "653673e71e3a310cc0f4b3e1"
      },
      "_log": "509d4bfa-72eb-4479-bac1-679cb926c393",
      "cavity_st": {
        "$date": "2023-10-23T16:23:51.496Z"
      },
      "batch": "094189793",
      "pass": true,
      "_cav_st": {
        "$oid": "653673e71e3a310cc0f4b3e2"
      },
      "user": "tst",
      "faulty_test_name": null,
      "_cfg": "64ec2f7dc65e12000153652b",
      "duration": 241223,
      "pn_cfg_rev": "4900",
      "serial": "11115711",
      "dut": {
        "pn": "0694940",
        "model": "PROG ORG-CONTROL PANEL V12",
        "rev": "2C1",
        "desc": "PROG ORG-CONTROL PANEL V12"
      },
      "production_step": "094189793"
    },
    {
      "_id": {
        "$oid": "65380d961e3a3113a0d4d66d"
      },
      "rmt_srv": {
        "ip": "192.168.12.108",
        "host": "",
        "pn": "1185_CP_MINI_GW_IMP_V03(1)",
        "os": "",
        "location": "afula"
      },
      "develop": false,
      "time": {
        "$date": "2023-10-24T21:31:50.653Z"
      },
      "pn_cfg": "8609940",
      "cavity": "1",
      "type": "finish",
      "_exec_st": {
        "$oid": "65380d481e3a3113a0d4d622"
      },
      "_log": "575e1129-e396-4770-9dd2-3e93630f3047",
      "cavity_st": {
        "$date": "2023-10-24T21:30:32.146Z"
      },
      "batch": "094189350",
      "pass": true,
      "_cav_st": {
        "$oid": "65380d481e3a3113a0d4d623"
      },
      "user": "tst",
      "faulty_test_name": null,
      "_cfg": "6534d0e17911e800010ab2cd",
      "duration": 78507,
      "pn_cfg_rev": "4900",
      "serial": "11115711",
      "dut": {
        "pn": "220094940",
        "model": "ORG-220094940",
        "rev": "2C1-06",
        "desc": "ORG-CONTROL PANEL 4G V3"
      },
      "production_step": "094189350"
    },
    {
      "_id": {
        "$oid": "6553e7821e3a3110d88d1f8b"
      },
      "rmt_srv": {
        "ip": "192.168.12.228",
        "host": "",
        "pn": "1985_CP_MINI_GW_V12_A_B02",
        "os": "",
        "location": "afula"
      },
      "develop": false,
      "time": {
        "$date": "2023-11-14T23:32:50.189Z"
      },
      "pn_cfg": "7694940",
      "cavity": "1",
      "type": "finish",
      "_exec_st": {
        "$oid": "6553e6581e3a3110d88d1e9c"
      },
      "_log": "c4ba50a8-fb09-45b4-b422-4e5c0c5c9e81",
      "cavity_st": {
        "$date": "2023-11-14T23:27:52.164Z"
      },
      "batch": "104229463",
      "pass": true,
      "_cav_st": {
        "$oid": "6553e6581e3a3110d88d1e9d"
      },
      "user": "tst",
      "faulty_test_name": null,
      "_cfg": "6550cd7cacc7ca0001dd424c",
      "duration": 298025,
      "pn_cfg_rev": "4980",
      "serial": "11115711",
      "dut": {
        "pn": "0694940",
        "model": "PROG ORG-CONTROL PANEL V12",
        "rev": "2C1",
        "desc": "PROG ORG-CONTROL PANEL V12"
      },
      "production_step": "104229463"
    }
  ],
  "Board_level": 2,
  "Verification_level": 1,
  "rmt_srv_pn": [
    "1985_CP_MINI_GW_V12_A_B02",
    "1185_CP_MINI_GW_IMP_V03(1)",
    "1985_CP_MINI_GW_V12_A_B01"
  ]
}
type or paste code here

I want to write an aggregation that will perform a test:
In the documents of the “return” collection there are serials on which tests were performed in the past and they were recorded in the document, I need to locate the same serial in the documents of the “test” collection under the same condition of border and verification and it is enough that there is one or more in the “return” documents, then it is necessary Subtract the numbers from fail or pass and also subtract from the total.

5

Hey, try this:

db.test.aggregate([
    {
        $unwind: "$serials_return_period" // Adjust based on the actual field you want to unwind
    },
    {
        $lookup: {
            from: "return", // The collection to join
            localField: "serials_return_period", // Field from the `test` collection
            foreignField: "serial", // Field from the `return` collection
            as: "matched_return" // The output array field
        }
    },
    {
        $match: {
            // Your conditions go here
            // For example: "stage_id": "YourCondition"
        }
    },
    {
        $group: {
            _id: "$_id", // Group by the original document id or any other field
            // Include other fields as needed
        }
    }
]);