How to have multiple nested arrays in an array

Question

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?

varman · Accepted Answer · 2020-09-23 12:14:38Z

3

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

edited Sep 23, 2020 at 12:14

answered Sep 22, 2020 at 16:08

varman

8,9226 gold badges25 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

17 Comments

varman Over a year ago

open your monog shell and type use myfirsttry;

varman Over a year ago

then copy and paste below code

db.test.insert({ "_id": { "SHOP": "SecondShop" }, "isLinkedTo": [ { "PERSON": "Sissy", "CITY": "Rome", "ROOM": "Bedroom", "STYLEDPREFERED": "Normal", "hasDonated": { "DATE": "2019-07-28", "OBJECTS": "Clothes", "COST": 30, "COLOR": "Blue", "PAYMENT": "Coupons" } } ] })

varman Over a year ago

I found the problem, gimme some time, i will correct it

varman Over a year ago

I have updated the code, you can check it now. Just need to put last $group as I wrote

varman Over a year ago

Wew, thank you, I forgot to add group stage. If any problem, feel free to contact me

|

Collectives™ on Stack Overflow

How to have multiple nested arrays in an array

1 Answer 1

17 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

17 Comments

Your Answer

Sign up or log in

Post as a guest

Related