Jhon Q
Jhon Q

Reputation: 11

How to retrieve elements with join query in MongoDB?

I have a problem with sum detailed join with $graphLookUp and $map but the items retrieves double array :S, there is a way to group the elements in a sum?

This is an example of my collection:

/* 1 */
{

    "type" : "Fase1",
    "total" : 100,
    "key" : "A6E19D93-8546-4A3A-8887-E23C301A290F",
},

/* 2 */
{
    "type" : "Fase1",
    "total" : 340.80,
    "key": "85547A5B-623B-4408-9B3E-26F9B368B2C7"
},
/* 3 */
{
    "type" : "Fase2",
    "key": "F6307773-A6EF-41D4-A2F3-0FE55543846E"
    "complement" : {
        "payments" : [ 
            {
                "amount" : 20,
                "documentsRelated" : [ 
                    {
                        "keyId" : "85547A5B-623B-4408-9B3E-26F9B368B2C7",
                    }
                ]
            },
            {
                "amount" : 15,
                "documentsRelated" : [ 
                    {
                        "keyId" : "85547A5B-623B-4408-9B3E-26F9B368B2C7",
                    }
                ]
            }
        ],
    },
}

I need to get the information in a list with the sum of type Phase2

/* 1 */
{
    "type" : "Fase1",
    "total" : 100,
    "key" : "A6E19D93-8546-4A3A-8887-E23C301A290F",
},
/* 2 */
{
    "type" : "Fase1",
    "total" : 340.80,
    "key": "85547A5B-623B-4408-9B3E-26F9B368B2C7",
    //sum 20+35 from keyId related
    "totalFase2": 35
}

there is my example code, I'm confused sum data in 1 column:

query = [
      {
        $match: {
          type: 'Fase1',
        },
      }, {
        $graphLookup: {
          from: 'invoices',
          startWith: '$key',
          connectFromField: 'key',
          connectToField: 'complement.payments.documentsRelated.keyId',
          as: 'payments',
          restrictSearchWithMatch: {
            'type': 'Fase2',
          },
        },
      },
      {
        $project: {
          _id: 1,
          type: 1,
          total: 1,
          paymentData: '$payments.complement.payments'
        },
      },
];

db.getCollection('invoices').aggregate(query);

Upvotes: 1

Views: 34

Answers (1)

turivishal
turivishal

Reputation: 36134

Try $map to iterate loop of payments.complement.payments.amount array of array of amount, $sum array of amount, $map will return an array of number again $sum array of amount,

  {
    $project: {
      _id: 1,
      type: 1,
      total: 1,
      totalFase2: {
        $sum: {
          $map: {
            input: "$payments.complement.payments.amount",
            in: { $sum: "$$this" }
          }
        }
      }
    }
  }

Playground

Upvotes: 1

Related Questions