Find a nested object field inside an array in mongodb aggregate

Question

I have this object as below.

{
    "_id" : ObjectId("5ec80a981e89a84b19934039"),
    "status" : "active",
    "organizationId" : "1",
    "productId" : "1947",
    "name" : "BOOKEND & PAPER WEIGHT SET – ZODIAC PIG – RED COPPER + PLATINUM",
    "description" : "This global exclusive Zodiac bookend and paperweight set from Zuny will stand auspiciously on your bookcase and table, spreading good luck and fortune throughout your home just in time for the Year of the Pig.",
    "brand" : "ZUNY",
    "created" : "2018-09-28 00:00:00",
    "updated" : "2020-05-22 09:19:07",
    "mainImage" : "https://",
    "availableOnline" : true,
    "colors" : [ 
        {
            "images" : [ 
                {
                    "type" : "studio",
                    "url" : "https://"
                }, 
                {
                    "type" : "studio",
                    "url" : "https://"
                }, 
                {
                    "type" : "studio",
                    "url" : "https://"
                }
            ],
            "extraInfo" : [ 
                {
                    "type" : "text-tag",
                    "title" : "CATEGORY",
                    "tags" : [ 
                        "HOME FURNISHING & DÉCOR", 
                        "LIFESTYLE"
                    ]
                }, 
                {
                    "type" : "text-tag",
                    "title" : "BRAND",
                    "tags" : [ 
                        "ZUNY"
                    ]
                }, 
                {
                    "type" : "text-tag",
                    "title" : "COLOUR",
                    "tags" : [ 
                        "GOLD", 
                        "ROSE GOLD"
                    ]
                }, 
                {
                    "type" : "text-tag",
                    "title" : "SEASON",
                    "tags" : [ 
                        "AW(2018)"
                    ]
                }, 
                {
                    "type" : "text-tag",
                    "title" : "HASHTAG",
                    "tags" : [ 
                        "BOOKCASES", 
                        "BOOKEND", 
                        "COLOUR", 
                        "EXCLUSIVE", 
                        "GLOBAL EXCLUSIVE", 
                        "HOME", 
                        "LEATHER", 
                        "MOTIF", 
                        "OBJECTS", 
                        "PAPER", 
                        "PAPERWEIGHT", 
                        "PLATINUM", 
                        "SET", 
                        "SYNTHETIC", 
                        "ZODIAC", 
                        "HANDMADE", 
                        "time"
                    ]
                }
            ],
            "_id" : ObjectId("5ec80a981e89a84b1993403a"),
            "colorId" : "1",
            "color" : "ROSE GOLD",
            "status" : "active",
            "sizes" : [ 
                {
                    "extraInfo" : [ 
                        {
                            "type" : "text-block",
                            "title" : "Size And Fit",
                            "text" : ""
                        }, 
                        {
                            "type" : "text-block",
                            "title" : "Information",
                            "text" : "Global exclusive. Colour: Copper/Platinum. Set includes: Zodiac Pig bookend (x 1), Zodiac Pig paperweight (x 1). Metallic copper- and platinum-tone synthetic leather. Pig motif. Iron pellet filling. Handmade"
                        }
                    ],
                    "_id" : ObjectId("5ec80a981e89a84b1993403b"),
                    "sizeId" : "1",
                    "neo" : "0210111790664",
                    "size" : "*",
                    "originalPrice" : "1060.00",
                    "sellingPrice" : "1060.00",
                    "discountPercent" : "0.00",
                    "url" : "https://",
                    "status" : "active",
                    "currency" : "HK$",
                    "stores" : [ 
                        {
                            "storeId" : "1",
                            "quantity" : 70,
                            "_id" : ObjectId("5ec80a981e89a84b1993403c"),
                            "available" : 70,
                            "reserved" : 0,
                            "name" : "Park Street",
                            "status" : "active"
                        }, 
                        {
                            "storeId" : "2",
                            "quantity" : 95,
                            "_id" : ObjectId("5ec80a981e89a84b1993403d"),
                            "name" : "Rashbehari",
                            "status" : "active"
                        }
                    ]
                }
            ]
        }
    ],
    "__v" : 0
}

I want the output as follows

{
        "name": "Mock Collection",
        "collectionId": "92",
        "products": [
            {
                "title": "GLOBAL EXCLUSIVE OFF-SHOULDER SHIRT DRESS",
                "imageUrl": "https://",
                "productId": "21174",
                "currency": "" // This should be this.colors[0].sizes[0].currency
            },
        ]
    }

How to get the nested field. I tried using arrayElemAt by which I was able to get to colors[0]. But I am confused how to get inside the nested object of sizes from there. Also the currency node should have the exact value. It comes like currency:{currency: value} which I don't want.

Please help!

whoami - fakeFaceTrueSoul · Accepted Answer · 2020-06-18 17:58:54Z

3

Not sure how you've got that output but to extract currency from first object of sizes then you need to try this :

db.collection.aggregate([
  {
    $project: {
      currency: {
        $arrayElemAt: [
          {
            $arrayElemAt: [ "$colors.sizes.currency", 0 ] // gives an array of currency values, in your case since you've only one object just an array of one value
          },
          0
        ]
      }
    }
  }
])

Test : mongoplayground

answered Jun 18, 2020 at 17:58

whoami - fakeFaceTrueSoul

18.1k6 gold badges36 silver badges51 bronze badges

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

4 Comments

Visakh Vijayan Over a year ago

I used this "currency":{$let:{vars:{"color1":{$arrayElemAt:["$$product.colors",0]}},in:{$let:{vars:{"size1":{$arrayElemAt:["$$color1.sizes",0]}},in:"$$size1.currency"}}}}, Don't know if it's the right way but go it done.

whoami - fakeFaceTrueSoul Over a year ago

@VisakhVijayan : You don't need to use add-on $let operator for this instead you can simply get it done like above, not sure for what reason it was chosen :-)

Visakh Vijayan Over a year ago

This will select the first size of the first color?

whoami - fakeFaceTrueSoul Over a year ago

@VisakhVijayan : again not sure how it differs from simple structure what this answer provides. My intention is usage of additional operator is not needed & $let make it look clumsy..

Collectives™ on Stack Overflow

Find a nested object field inside an array in mongodb aggregate

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related