retrieving only a single element of an second level nested array in mongodb

Question

In my collection each user has an array of shops and each shop has an array of products. I need to retrieve a single product by its productId property. Here is my simplified collection:

[
    {
        "_id": "60e66b70da2439232e330415",
        "name": "User1 Name",
        "shops": [
            {
                "_id": "60e7c9e2be0d8f03544a03b8",
                "shopName": "User1 Shop1 Name",
                "products": [
                    {
                        "_id": "60e9e9e8105d6021a2e91535",
                        "title": "User1 Shop1 Product1 Title"
                    },
                    {
                        "_id": "60e9f4a0105d6021a2e91536",
                        "title": "User1 Shop1 Product2 Title"
                    }
                ]
            },
            {
                "_id": "60e8e8c00f3986577cb968c9",
                "shopName": "User1 Shop2 Name",
                "products": [
                    {
                        "_id": "60e9f4fe105d6021a2e91537",
                        "title": "User1 Shop2 Product1 Title"
                    },
                    {
                        "_id": "60e9f515105d6021a2e91538",
                        "title": "User1 Shop2 Product2 Title"
                    }
                ]
            }
        ]
    },
    {
        "_id": "60e66b93da2439232e330416",
        "name": "User2 Name",
        "shops": [
            {
                "_id": "60e69698e76cad44e49e1fc8",
                "shopName": "User2 Shop1 Name",
                "products": [
                    {
                        "_id": "60e9f588105d6021a2e91539",
                        "title": "User2 Shop1 Product1 Title"
                    },
                    {
                        "_id": "60e9f59c105d6021a2e9153a",
                        "title": "User2 Shop1 Product2 Title"
                    }
                ]
            },
            {
                "_id": "60e902b441e9df63c7fbcb49",
                "shopName": "User2 Shop2 Name",
                "products": [
                    {
                        "_id": "60e9f5c9105d6021a2e9153b",
                        "title": "User2 Shop2 Product1 Title"
                    },
                    {
                        "_id": "60e9f5de105d6021a2e9153c",
                        "title": "User2 Shop2 Product2 Title"
                    }
                ]
            }
        ]
    }
]

I searched and tried a lot with elemMatch and aggregate and searched a lot. Found some answers for 1st level nested array but not for 2nd level nested array. As an example, I am trying to get the following output for the productId of 60e9e9e8105d6021a2e91535 this is my intended output:

{
        "_id": "60e9e9e8105d6021a2e91535",
        "title": "User1 Shop1 Product1 Title"
}

Thank you very much.

Rajdeep D · Accepted Answer · 2021-07-21 15:01:11Z

2

You can use aggregate framework, please find the query below and

working Playground is here.

db.collection.aggregate([
  {
    $match: {
      "shops.products._id": "60e9e9e8105d6021a2e91535"
    }
  },
  {
    "$unwind": "$shops"
  },
  {
    "$unwind": "$shops.products"
  },
  {
    $match: {
      "shops.products._id": "60e9e9e8105d6021a2e91535"
    }
  },
  {
    $project: {
      "_id": "$shops.products._id",
      "title": "$shops.products.title"
    }
  }
])

answered Jul 21, 2021 at 15:01

Rajdeep D

3,9201 gold badge12 silver badges18 bronze badges

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

2 Comments

Omid Over a year ago

Thank you so much for the great help. Your code is exactly what I want and works well in the playground. But it does not work on my web app. I am sharing a simplified code of my webapp where I am using your code. I am sharing the code in a separate question as the scope of that code changes a little. Please kindly take a look at the following post and provide me with your kind advice. Thank you very much. Post Link: stackoverflow.com/questions/68473463/…

Rajdeep D Over a year ago

yup, I saw the question, I shall comment there

Collectives™ on Stack Overflow

retrieving only a single element of an second level nested array in mongodb

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related