How to unwind a nested array of objects

I’m working on project using nesjts graphQl and mongoDb I made $lookup between three collection. the collection which is I want to return has 226 records every record contains a field of type array of object I want to get the data within this array so I used $unwind the problem here when I use $unwind to read the field the number of records increased to be 524 records and when I remove the $unwind I couldn’t return the data and I got this error

 "message": "String cannot represent value: [\"Debit Mastercard\"]",
      "locations": [
        {
          "line": 14,
          "column": 7
        }
      ],

Data I want to return :

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

Data inside the field settledTransactions

[
          {
            "appName": "Debit Mastercard",
            "cardInputMethod": "INPUT_CHIP_CONTACT",
            "cardPAN_PCI": "XXXXXXXXXXXX5545",
            "networkName": "MASTERCARD",
            "onlineApprovalCode": "846022",
            "onlineRetrievalReferenceNumber": "000102846022",
            "transactionAmount": "000000069000",
            "transactionDate": "220613",
            "transactionTime": "110016",
            "transactionType": "TRANSACTION_TYPE_PURCHASE"
          }
        ],
       

Here is the function :

async getSettlementsByUser(role: string, name: string) {

      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);

      return settlement;
  

Anyone could help me in this please ?

Hi @skander_lassoued and welcome to the MongoDB community forum!!

To replicate the query in my local environment, it would be very helpful if you could share the following information:

  1. A sample documents from all the relevant collections
  2. MongoDB version

Thanks
Aasawari

I guess that other collection doesn’t matter I just want to unwind a field contains array of objects without duplicating the main collection but for your order I’m going to share with you

1 - Sample of other collection :

{
    _id: new ObjectId("62cd33dda9b59d77cffef126"),
    sn: 'xxx',
    terminalId: 'xxx',
    merchantId: 'xxx',
    merchantName: 'xxx',
    networkType: '',
    binded: true,
    longitude: 'xx',
    latitude: 'xx',
    bindedBank: 'xx',
    bindedClient: 'xx',
    bindedSite: 'xx',
    region: 'xx'
  }

{
    _id: new ObjectId("632c711adc4f85eba1b74911"),
    nameUser: '**',
    contactName: '**',
    phone: '***',
    email: "**",
    password: '**',
    role: '**',
    city: '**',
    postCode: '**',
    address: '**',
    creationDate: **,
    bindedSuperAdmin: '**',
    bindedBanque: '**',
    bindedClient: '**',
    longitude: 0,
    latitude: 0,
    localisation: '**',
    region: '**'
  }

2 - mongoose version ": “^9.0.2”

Hi @skander_lassoued

Thank you for sharing the above collection information. However, I do not have all the details
I would need to reproduce the issue in my local environment.
For instance, the fields mentioned in the expected response are not available in the above two collection.
Also, I think it’s best if you post the actual documents from all collections required for the aggregation to work, so we can provide a more effective help

Thanks
Aasawari

@Aasawari Thanks for replying :love_letter:
But I provided you all you need but I will arrange all information for you.

This is collection device :

{
    _id: new ObjectId("62cd33dda9b59d77cffef126"),
    sn: 'xxx',
    terminalId: 'xxx',
    merchantId: 'xxx',
    merchantName: 'xxx',
    networkType: '',
    binded: true,
    longitude: 'xx',
    latitude: 'xx',
    bindedBank: 'xx',
    bindedClient: 'xx',
    bindedSite: 'xx',
    region: 'xx'
  }

this is a collection user

{
    _id: new ObjectId("632c711adc4f85eba1b74911"),
    nameUser: '**',
    contactName: '**',
    phone: '***',
    email: "**",
    password: '**',
    role: '**',
    city: '**',
    postCode: '**',
    address: '**',
    creationDate: **,
    bindedSuperAdmin: '**',
    bindedBanque: '**',
    bindedClient: '**',
    longitude: 0,
    latitude: 0,
    localisation: '**',
    region: '**'
  }

I joined device with user to get devices affected to user in this stage

 {
          $lookup: {
            from: 'tpes',
            localField: 'nameUser',
            foreignField: 'merchantName',
            as: 'tpesBySite',
          },
        },

then I got a this result

{
    _id: new ObjectId("632c711adc4f85eba1b74911"),
    nameUser: 'TEST MERCHANT',
    contactName: 'user',
    phone: '85747485',
    email: 'Monoprixd@Monoprixd.Monoprixd',
    password: '$2b$10$.UfsvqSSoEU1PodF1g4OZ.vSgzRgp4VTTIfzbXlqPRSySNjfoHfj.',
    role: 'SITE',
    city: 'test',
    postCode: '7844',
    address: 'test',
    creationDate: 2022-09-22T00:00:00.000Z,
    bindedSuperAdmin: 'Ms-Techsoft',
    bindedBanque: 'Attijeri',
    bindedClient: 'Monoprix',
    longitude: 0,
    latitude: 0,
    localisation: 'test',
    region: 'Tunis',
    isEmailConfirmed: true,
    tpesBySite: {
      _id: new ObjectId("62bd66313cd6fe2410d47770"),
      sn: 'N300W150726',
      terminalId: '05000002',
      merchantId: '458742236657711',
      merchantName: 'TEST MERCHANT',
      networkType: 'wifi',
      binded: true,
      model: 'N3',
      firmWareVer: 'v1.7.1',
      emvKernelVersion: 'com.nexgo.oaf.apiv3.EmvKernelVersionInfo@28c6fd25',
      sdkVer: '3.02.001',
      vendor: 'nexgo',
      addr: 'TunisTunis city',
      longitude: '10.302438',
      latitude: '36.835339',
      __v: 0,
      inUse: true
    },

I want to join this result with collection called settlements that looks like

{
    _id: new ObjectId("632c711adc4f85eba1b74911"),
    bReconcileError: string,
    batchNumber: string,
    btransfered: string,
    countryCode: string,
    currencyCode:string',
    merchantID: string,
    nSettlementAmount: string,
    onlineMessageMACerror:string,
    reconciliationAdviceRRN:string,
    reconciliationApprovalCode: '',
    settledTransactions: [ {
            "appName": string,
            "cardInputMethod": string,
            "cardPAN_PCI": string,
            "networkName":string,
            "onlineApprovalCode": string,
            "onlineRetrievalReferenceNumber": string,
            "transactionAmount": string,
            "transactionDate": string,
            "transactionTime":string,
            "transactionType":string
          } ],
    settlementAmount:string,
    settlementDate: string,
    settlementTime: string,
    terminalID:string',
    traceNumber: string,
    uniqueID:string
  },

to get settlement related to user depends devices so I did this stage

  {
          $lookup: {
            from: 'settlements',
            localField: 'tpesBySite.terminalId',
            foreignField: 'terminalID',
            as: 'settlementsByUser',
            pipeline: [
              {
                $sort: {
                  transactionDate: -1,
                },
              },
            ],
          },
        },

I got result like this

    settlementsByUser: [
      [Object], [Object], [Object], [Object], [Object], [Object],
      [Object], [Object], [Object], [Object], [Object], [Object],
      [Object], [Object], [Object], [Object], [Object], [Object],
      [Object], [Object], [Object], [Object], [Object], [Object],
      [Object], [Object], [Object], [Object], [Object], [Object],
      [Object], [Object], [Object], [Object], [Object], [Object],
      [Object], [Object], [Object], [Object], [Object], [Object],
      [Object], [Object], [Object], [Object], [Object], [Object],
      [Object], [Object], [Object], [Object], [Object], [Object],
      [Object], [Object], [Object], [Object], [Object], [Object],
      [Object], [Object], [Object], [Object], [Object], [Object],
      [Object], [Object], [Object], [Object], [Object], [Object],
      [Object], [Object], [Object], [Object], [Object], [Object],
      [Object], [Object], [Object], [Object], [Object], [Object],
      [Object], [Object], [Object], [Object], [Object], [Object],
      [Object], [Object], [Object], [Object], [Object], [Object],
      [Object], [Object], [Object], [Object],
      ... 126 more items
    ]

I need to use { $unwind: ‘$settlementsByUser’ }, to see what’s inside every object, so after $unwing i got 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
    }

as you can see settled transaction still not appeared so I need to unwind this field settledTransactions which allows me to return all data correctly
Finally once I do { $unwind: ‘$settlementsByUser.settledTransactions’ }, it duplicates settlementsByUser depends on number of object inside that field :confused:
I wish you appreciate my effort to explain my efforts and help me
Thanks

Hi @skander_lassoued

Thank you for providing the detailed information.

I ran the following pipeline on my test environment using the sample documents you provided (with slightly altered values so that the $lookup stages joined the sample documents from each collection):

[
  {
    '$lookup': {
      from: 'device',
      localField: 'nameUser',
      foreignField: 'merchantName',
      as: 'tpesBySite'
    }
  },
  {
    '$lookup': {
      from: 'settlements',
      localField: 'tpesBySite.terminalId',
      foreignField: 'terminalID',
      as: 'settlementsByUser'
    }
  },
  { '$unwind': '$settlementsByUser' }
]

Note: The above does not include the final $unwind on "settlementsByUser.settledTransactions"

as you can see settled transaction still not appeared so I need to unwind this field settledTransactions which allows me to return all data correctly

After this first $unwind on "settledTransactions", you write that "settled transacation still not appeared. Could you clarify what you mean here? (i.e. Do you mean the [Array] value that is shown in the output is not what you are expecting and that you are expecting to see all the objects within) The output from the above pipeline I used is shown below as reference:

[
  {
    _id: ObjectId("632c711adc4f85eba1b74911"),
    nameUser: 'xxx',
    contactName: 'xxx',
    phone: 'xxx',
    email: 'xxx',
    password: 'xxx',
    role: 'xxx',
    city: 'xxx',
    postCode: 'xxx',
    address: 'xxx',
    creationDate: 'xxx',
    bindedSuperAdmin: 'xxx',
    bindedBanque: 'xxx',
    bindedClient: 'xxx',
    longitude: 0,
    latitude: 0,
    localisation: 'xxx',
    region: 'xxx',
    tpesBySite: [
      {
        _id: ObjectId("62cd33dda9b59d77cffef126"),
        sn: 'xxx',
        terminalId: 'xxx',
        merchantId: 'xxx',
        merchantName: 'xxx',
        networkType: '',
        binded: true,
        longitude: 'xx',
        latitude: 'xx',
        bindedBank: 'xx',
        bindedClient: 'xx',
        bindedSite: 'xx',
        region: 'xx'
      }
    ],
    settlementsByUser: {
      _id: ObjectId("632c711adc4f85eba1b74912"),
      bReconcileError: 'string',
      batchNumber: 'string',
      btransfered: 'string',
      countryCode: 'string',
      currencyCode: 'string',
      merchantID: 'string',
      nSettlementAmount: 'string',
      onlineMessageMACerror: 'string',
      reconciliationAdviceRRN: 'string',
      reconciliationApprovalCode: '',
      settledTransactions: [
        {
          appName: 'string',
          cardInputMethod: 'string',
          cardPAN_PCI: 'string'
        },
        {
          appName: 'string2',
          cardInputMethod: 'string',
          cardPAN_PCI: 'string'
        },
        {
          appName: 'string3',
          cardInputMethod: 'string',
          cardPAN_PCI: 'string'
        }
      ],
      settlementAmount: 'string',
      settlementDate: 'string',
      settlementTime: 'string',
      terminalID: 'xxx',
      traceNumber: 'string',
      uniqueID: 'string'
    }
  }
]

Note: Some fields in the "settledTransaction" array’s elements were redacted for readability

Finally once I do { $unwind: ‘$settlementsByUser.settledTransactions’ }, it duplicates settlementsByUser depends on number of object inside that field :confused:

This is expected behaviour of $unwind. Are you wanting to $unwind '$settlementsByUser.settledTransactions' because it is showing as {Array]?

Regards
Aasawari

1 Like