CODEWITHSUNDEEP
mongodbmongodb-query
fraaa
fraaa

Reputation: 43

How to have multiple nested arrays in an array

I have the collection data from a csv file with header. When i run my query

db.ties.aggregate(
   [
     {
       $group:
         {
           _id: { "SHOP": "$SHOP" },
           isLinkedTo: { $push: { "PERSON": "$PERSON", "CITY": "$CITY", "ROOM": "$ROOM", "STYLE": "$STYLE", "hasDonated": {"DATE": "$DATE", "OBJECT": "$OBJECT", "COST": "$COST", "COLOR": "$COLOR", "PAYMENT": "$PAYMENT"}}}
         }
     },
     { $out: "ties"}
   ],
   { allowDiskUse: true }
)

I have like result:

{
        "_id": {
            "Shop": "FirstShopNameCovered"
        },
        "isLinkedTo": [{
            "PERSON": "Carleen",
            "CITY": "Rome",
            "ROOM": "Kitchen",
            "STYLEPREFERED": "Modern",
            "hasDonated": {
                "DATE": "2019-10-11",
                "OBJECT": "Set of dishes",
                "COST": 72,
                "COLOR": "White",
                "PAYMENT": "Credit card"
            }
        }, {
            "PERSON": "Carleen",
            "CITY": "Rome",
            "ROOM": "Kitcher",
            "STYLEPREFERED": "Modern",
            "hasDonated": {
                "DATE": "2018-10-26",
                "OBJECT": "Set of chairs",
                "COST": 353,
                "COLOR": "Grey",
                "PAYMENT": "Coupon"
            }
        }, {
            "PERSON": "Pernick",
            "CITY": "Venezia",
            "ROOM": "Bathroom",
            "STYLE": "Minimalist",
            "hasDonated": {
                "DATE": "2018-09-18",
                "OBJECT": "Mirror",
                "COST": 68,
                "COLOR": "Brown",
                "PAYMENT": "Credit card"
            }
        }

You can see that there is replicated the Person "PERSON": "Carleen" with all data with 2 different arrays hasDonated.

I wish have something like this result, with person not replicated that contains all hasDonated arrays where he is present:

    "_id": {
        "Shop": "NameCovered"
    },
    "isLinkedTo": [{
        "PERSON": "Carleen",
        "CITY": "Rome",
        "ROOM": "Kitchen",
        "STYLE": "Retrò",
        "hasDonated": {
            "DATE": "2019-10-11",
            "OBJECT": "Set of dishes",
            "COST": 72,
            "COLOR": "White",
            "PAYMENT": "Credit card"
        },
        {
            "DATE": "2018-10-26",
            "OBJECT": "Chair",
            "COST": 53,
            "COLOR": "Grey",
            "PAYMENT": "Coupon"
        }
    }, {
        "PERSON": "Pernick",
        "CITY": "Venezia",
        "ROOM": "Bathroom",
        "STYLE": "Minimalist",
        "hasDonated": {
            "DATE": "2018-09-18",
            "OBJECT": "Mirror",
            "COST": 68,
            "COLOR": "Brown",
            "PAYMENT": "Credit card"
        }

How can I do to have the result like this?

Upvotes: 2

Views: 55

Answers (1)

varman
varman

Reputation: 8894

First we need to $unwind to flat the array. Then group the hasDonated using $group where unique is found by combination of "_id" and "PERSON" as you mentioned.

[
  {
    "$unwind": "$isLinkedTo"
  },
  {
    $group: {
      _id: {
        _id: "$_id",
        per: "$isLinkedTo.PERSON"
      },
      isLinkedTo: {
        $first: {
          PERSON: "$isLinkedTo.PERSON",
          CITY: "$isLinkedTo.CITY",
          ROOM: "$isLinkedTo.ROOM",
          STYLEPREFERED: "$isLinkedTo.STYLEPREFERED"
        }
      },
      hasDonated: {
        $addToSet: "$isLinkedTo.hasDonated"
      }
    }
  },
  {
    $addFields: {
      _id: "$_id._id",
      "isLinkedTo.hasDonated": "$hasDonated"
    }
  },
  {
    $project: {
      hasDonated: 0
    }
  },
  {
    $group: {
      _id: "$_id",
      isLinkedTo: {
        $push: "$isLinkedTo"
      }
    }
  }
]

Working Mongo playground

Upvotes: 3

Related Questions