I would like to get all products and categories it belong to. I have this full text search query sample data

db.products.find(
{ $text: { $search: 'IPhone 6' } },
{ score: { $meta: "textScore" } })
.sort({ score:{$meta: "textScore" } })
.limit(100)

results are next [![enter image description here][2]][2]

It gives me all the products and it's main and sub categories, but since we have a limit Frontend is not able to show all the main and sub categories

Somehow I need to add distinct main and sub categories

Looks like aggregation is a way to go but I can not figure out how to do it.

Would be grateful for any help!

I expect output to be like this

{
        "main_category_name": "Lights & Lighting",
        "main_category_id": 39,
        "sub_categories": [
            {
                "sub_сategory_name_1": "Portable Lighting",
                "sub_сategory_id_1": 390503,
                "sub_сategory_name_2": [
                    {
                        "sub_сategory_name_2": "Flashlights & Torches",
                        "sub_сategory_id_2": 150410,
                        "sub_сategory_name_3": []
                    }
                ]
            }
        ]
    }

CodePudding user response：

You can do this, but it gets really gnarly after 3 layers - I'd probably recommend a schema change, or possibly just getting the distinct values and re-structuring them at the app layer, that being said - it is possible:

db.test1.aggregate([{
   $group: { _id: {
     main_category_name: "$main_category_name",
     main_category_id: "$main_category_id",
     sub_category_name_1: "$sub_category_name_1",
     sub_category_id_1: "$sub_category_id_1",
     sub_category_name_2: "$sub_category_name_2",
     sub_category_id_2: "$sub_category_id_2",
   },
   sub_categories: { $addToSet: { sub_category_name_3: "$sub_category_name_3", sub_category_id_3: "$sub_category_id_3" } }
  }
},
{
  $group: {
    _id: {
     main_category_name: "$_id.main_category_name",
     main_category_id: "$_id.main_category_id",
     sub_category_name_1: "$_id.sub_category_name_1",
     sub_category_id_1: "$_id.sub_category_id_1",
   },
   sub_categories: {
     $addToSet: {
       sub_category_id_2: "$_id.sub_category_id_2",
       sub_category_name_2: "$_id.sub_category_name_2",
       sub_categories: "$sub_categories"
     }
   }
 }
},
{
  $group: {
    _id: {
     main_category_name: "$_id.main_category_name",
     main_category_id: "$_id.main_category_id",
   },
   sub_categories: {
     $addToSet: {
       sub_category_id_1: "$_id.sub_category_id_1",
       sub_category_name_1: "$_id.sub_category_name_1",
       sub_categories: "$sub_categories"
     }
   }
 }
}

])

Couple small things:

1. It wasn't clear what naming convention you wanted (sub_categories vs sub_category_name_n) - so I kept it consistent at sub_categories
2. Every aggregation pipeline needs an _id - so your main_category_name and main_category_id got pushed in.

The result is:

{
    "_id" : {
        "main_category_name" : "test",
        "main_category_id" : 1
    },
    "sub_categories" : [
        {
            "sub_category_id_1" : 2,
            "sub_category_name_1" : "test 1",
            "sub_categories" : [
                {
                    "sub_category_id_2" : 2,
                    "sub_category_name_2" : "test 2",
                    "sub_categories" : [
                        {
                            
                        }
                    ]
                }
            ]
        },
        {
            "sub_category_id_1" : 3,
            "sub_category_name_1" : "test 3",
            "sub_categories" : [
                {
                    "sub_category_id_2" : 5,
                    "sub_category_name_2" : "test 5",
                    "sub_categories" : [
                        {
                            
                        }
                    ]
                },
                {
                    "sub_category_id_2" : 4,
                    "sub_category_name_2" : "test 4",
                    "sub_categories" : [
                        {
                            
                        }
                    ]
                }
            ]
        }
    ]

Note the {} in the final layer is because my dummy data didn't have sub_category_*_3 fields - if you need to filter these out, you can do so easily at the app layer (it would be possible with an aggregation stage too)