How to unwind a nested array of objects

Working with Data
, , , ,
1

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 ?

3

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

4

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”

5

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

6

@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

7

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

2 Likes
8

Thanks for replying @Aasawari :smiley: ,
Yes Even graphql Does not show data until I $unwind the ‘$settlementsByUser.settledTransactions’

9

@Aasawari could you reply me what’s the final result of your test I’m suck :confused:

10

Hi Skander,

I believe this is in reference to Aasawari’s below question but please correct me if I am wrong here:

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

I believe the data does exist within the [Array] but is just not being expanded (at least for mongosh depending on the 'inspectDepth' value). As an example:

DB> db.depth.find() /// output shows field `a` array elements
[
  {
    _id: ObjectId("6392b2170a04333a461fec1b"),
    a: [
      1, 2, 3, 4,  5,
      6, 7, 8, 9, 10
    ]
  }
]

DB> config.get('inspectDepth') /// inspectDepth of 100
100

DB> config.set('inspectDepth',1) /// inspectDepth changed to 1
Setting "inspectDepth" has been changed

DB> db.depth.find()
[ { _id: ObjectId("6392b2170a04333a461fec1b"), a: [Array] } ] /// field 'a' value now shows as [Array] for same document

I understand you have mentioned this output [Array] is what you are also seeing in GraphQL however I am not too familiar with GraphQL but would you be able to provide some steps to replicate the output you’re achieving via GraphQL? Perhaps any documentation you have followed would be good here.

Lastly, my understanding here is that the [Array] contents or data does exist but is just not expanded. Is the use case to expand this just for troubleshooting purposes?

Regards,
Jason

1 Like
11

@Jason_Tran thanks for your time,
It’s not for troubleshooting purpose, I want to get the data to show it in my application I really stuck :confused:

12

Let’s take a step back and determine the main issue here.

I believe the main facts are:

  1. You are using NestJS and installed the GraphQL module GitHub - nestjs/graphql: GraphQL (TypeScript) module for Nest framework (node.js) 🍷
  2. You need to do a $lookup across three different collections
  3. You need to display the result of these $lookup queries in NestJS, but the output so far is showing [Object] or [Array] instead of the actual data
  4. To get into these [Objects] or [Array], you used multiple $unwind to flatten the structure of the documents

Now the issues are:

  1. Multiple $unwind creates many duplicates
  2. The only way to not show the [Object] is to $unwind
  3. Not using these series of $unwind resulted in error String cannot represent value

Am I understanding this correctly so far?

If this is correct, then:

  1. I don’t think the issue is mainly related to MongoDB
  2. Rather, it’s an issue when the GraphQL module in NestJS cannot properly display highly nested JSON (note that I’m not too familiar with GraphQL to say this definitively, so I might be wrong here). Since $lookup can have the potential to create highly nested JSON, this sounds to me like a limitation on NestJS (or at least its GraphQL module)
  3. If the main issue is the error String cannot represent value, then perhaps there’s an issue in the GraphQL query specification. I found this link node.js - TypeError: String cannot represent value: graphql Query not working - Stack Overflow and [GraphQL error]: Message: String cannot represent value: { name: “Bar”, ordinal: 1 } · Issue #2093 · graphql/graphql-js · GitHub that may be helpful

As a first step toward resolving this, may I suggest you to experiment with your GraphQL query when the result is expected to have nested objects, and work one nesting level at a time, to ensure that the query involved is behaving correctly when nested JSON is to be returned.

Regards,
Jason

2 Likes
13

Thanks for your efforts @Jason_Tran I really appreciate, I’m going to check then come back to give results :love_letter:

14

Finally I got it :smiley:

 settledTransactions: {
              $map: {
                input: '$settlementsByUser.settledTransactions',
                as: 'transaction',
                in: {
                  appName: '$$transaction.appName',
                  cardInputMethod: '$$transaction.cardInputMethod',
                  cardPAN_PCI: '$$transaction.cardPAN_PCI',
                  networkName: '$$transaction.networkName',
                  onlineApprovalCode: '$$transaction.onlineApprovalCode',
                  onlineRetrievalReferenceNumber:
                    '$$transaction.onlineRetrievalReferenceNumber',
                  transactionAmount: '$$transaction.transactionAmount',
                  transactionDate: '$$transaction.transactionDate',
                  transactionTime: '$$transaction.transactionTime',
                  transactionType: '$$transaction.transactionType',
                },
              },
            },

This how it works perfectly Thank you all

1 Like