Why aggregation doesn't return exact data in my DB

Working with Data
, , ,
1

I made a lookup with two collections existing in my db, result of this lookup I want to join with another collection in my db, So I could implement this query with success but returned docs are more than data existing in my db for example I have 266 records, After the query I got 524 I don’t know where they come .
HINT : when I use simple find() I got correct result but with this aggregation I got more records
Here is my function shown below :

 let settlement = await this.profileModel.aggregate([
        {
          $match: {
            bindedSuperAdmin: name,
          },
        },
        {
          $lookup: {
            from: 'tpes',
            localField: 'nameUser',
            foreignField: 'merchantName',
            as: 'tpesBySite',
          },
        },
        {
          $lookup: {
            from: 'settlements',
            localField: 'tpesBySite.terminalId',
            foreignField: 'terminalID',
            as: 'settlementsByUser',
            pipeline: [
              {
                $sort: {
                  transactionDate: -1,
                },
              },
            ],
          },
        },

        { $unwind: '$tpesBySite' },

        { $unwind: '$settlementsByUser' },
        { $unwind: '$settlementsByUser.settledTransactions' },
        {
          $project: {
            bReconcileError: '$settlementsByUser.bReconcileError',
            batchNumber: '$settlementsByUser.batchNumber',
            btransfered: '$settlementsByUser.btransfered',
            countryCode: '$settlementsByUser.countryCode',
            currencyCode: '$settlementsByUser.currencyCode',
            merchantID: '$settlementsByUser.merchantID',
            nSettlementAmount: '$settlementsByUser.nSettlementAmount',
            onlineMessageMACerror: '$settlementsByUser.onlineMessageMACerror',
            reconciliationAdviceRRN:
              '$settlementsByUser.reconciliationAdviceRRN',
            reconciliationApprovalCode:
              '$settlementsByUser.reconciliationApprovalCode',
            settledTransactions: [
              {
                appName: '$settlementsByUser.settledTransactions.appName',
                cardInputMethod:
                  '$settlementsByUser.settledTransactions.cardInputMethod',
                cardPAN_PCI:
                  '$settlementsByUser.settledTransactions.cardPAN_PCI',
                networkName:
                  '$settlementsByUser.settledTransactions.networkName',
                onlineApprovalCode:
                  '$settlementsByUser.settledTransactions.onlineApprovalCode',
                onlineRetrievalReferenceNumber:
                  '$settlementsByUser.settledTransactions.onlineRetrievalReferenceNumber',
                transactionAmount:
                  '$settlementsByUser.settledTransactions.transactionAmount',
                transactionDate:
                  '$settlementsByUser.settledTransactions.transactionDate',
                transactionTime:
                  '$settlementsByUser.settledTransactions.transactionTime',
                transactionType:
                  '$settlementsByUser.settledTransactions.transactionType',
              },
            ],

            settlementAmount: '$settlementsByUser.settlementAmount',
            settlementDate: '$settlementsByUser.settlementDate',
            settlementTime: '$settlementsByUser.settlementTime',
            terminalID: '$settlementsByUser.terminalID',
            traceNumber: '$settlementsByUser.traceNumber',
            uniqueID: '$settlementsByUser.uniqueID',
          },
        },
      ]);
      console.log('settlement from service ', settlement.length);

      return settlement;

the result :

settlementsByUser: {
      _id: new ObjectId("62ac9732c36810454f8f3822"),
      bReconcileError: 'true',
      batchNumber: '1',
      btransfered: 'true',
      countryCode: '788',
      currencyCode: '788',
      merchantID: '458742236657711',
      nSettlementAmount: '159800',
      onlineMessageMACerror: 'false',
      reconciliationAdviceRRN: '000104246913',
      reconciliationApprovalCode: '',
      settledTransactions: [Array],
      settlementAmount: 'C000159800',
      settlementDate: '220617',
      settlementTime: '114110',
      terminalID: '05000002',
      traceNumber: '13',
      uniqueID: '363bc047-4cff-4013-aaad-e608a59bbd4c',
      __v: 0
    }

I need to unwind the settledTransaction to read what’s inside it but the unwind made the duplicating of data

I’m really confused, it’s my first time I face this kind of error anyone could help me please ?

2

Hi @skander_lassoued ,

Unwinding arrays is the probable reason.

When you unwind an array you basically multiply all root occurrences with each element of the unwinded array forming a sort of a Cartesian join…

Ty
Pavel

3

Thanks for replying me, could you provide me solution how can I resolve this problm ?

4

Hi @skander_lassoued ,

This is not a problem this is how unwind works…

If you need a specefic help, first explain what is the sample documents you use? What is the purpose of the query? And the expected output?

Thanks
Pavel

5

I will update my question to explain more about my problem I wish you help me

6

I updated my question take a look please :slight_smile:

7

To solve this I will need source document from all the involved collection corresponding to the result you are trying to achieve

8

I’m curious about this statement. When you join the data from another collection it ends up as an array because you may have more than one matching value in the collection you are looking up data from. When you unwind you’re now saying you want to have a separate record for each such array element.

If you just want original documents with joined data inside them, don’t do any $unwind stages and that’s exactly what you will get. If you think you need to use $unwind you’ll have to explain why you think you need it…

Asya