I created data structure for internet shop.

I have decided to separate product into 3 object documents, because I have complicated situation where I need 30 sub products with different colors and sizes and qt.

For example:

We have main product, it has images, and some common stuff (maybe). And sub-products with filters.

Schema for main:

{
    id: MongoId,
    images: ['', ''],
    ...,
    ...,
}

Schema for sub:

{
    id: MongoId,
    pid: MongoId, // Parent item ID
    image: '',
    features: {
        color: ['red'],
        size: 42
    },
    qt: 5
}

{
    id: MongoId,
    pid: MongoId, // Parent item ID
    image: '',
    features: {
        color: ['red'],
        size: 43
    },
    qt: 1
}

..... others red with different sizes and qt


{
    id: MongoId,
    pid: MongoId, // Parent item ID
    image: '',
    features: {
        color: ['yellow'],
        size: 44
    },
    qt: 10
}

..... others yellow with different sizes and qt

It could be 30 sub-products, but it should in my opinion has main product in order to show in the list, just this main product with its images and not sub-product (sub just for single product page and filters).

All the sub-products have parentId.

Also I have schema for description that I want in different collection:

{
    cid: MongoId, // Parent item ID
    content: {
        ru: {
            title: '',
            description: ''
        },
        en: {
            title: '',
            description: ''
        }
    }
}

All the stuff connected with parentId (it's ID of main product)

Why I have decided to make this separation main product / sub product, and why I do not include sub product inside main product like children? Because of search by filters!

I want to search items by color red and size 42, I will search this items in collection of sub products that I put above and group by parent ID. This collection (sub products) I need just for 2 cases when I search something and for single product page.

Question is, for example I found in table 3 products, group them by parent ID I have received 2 product, then I need pick just main product respectively with parent ID that sub products have.

Examples:

Get sub by filter:

[{ parentId: 1, .... }, { parentId: 1, .... }, { parentId: 2, .... }]

Group by parent ID

[{ parentId: 1, .... }, { parentId: 2, .... }]

I need to receive just main products from 'main product' collection and get rid of 'subs' or combine with 'main', and also with 'content' form different collection.

[{ id: 1, content: { title: {} } }, { id: 2, content: { title: {} } }]

I can receive sub products and then in next request to db get main products with lookup for 'content' schema and combine them in 'for' loop for example, but I don't think so it is correct way.

How can I do it? Or maybe someone has more efficient and fancy way to create such data model without separation for 3 collection?

CodePudding user response：

I think it will be good request, isn't it? Find all sub items group them by parent id, join with main product, join with content, replace root structure and extend object with main item and content and remove empty arrays of main and content from response

db.sub.aggregate([
    {
      "$match": {
        "features.color": "red"
      }
    },
    {
      "$group": {
        "_id": "$pid"
      }
    },
    {
      "$lookup": {
        "from": "products",
        "localField": "_id",
        "foreignField": "_id",
        "as": "main"
      }
    },
    {
      "$lookup": {
        "from": "content",
        "localField": "_id",
        "foreignField": "pid",
        "pipeline": [
          {
            "$match": {
              "lang": "ru"
            }
          },
          {
            $project: {
              _id: 0,
              pid: 0,
              lang: 0
            }
          }
        ],
        "as": "content"
      }
    },
    {
      $replaceRoot: {
        newRoot: {
          $mergeObjects: [
            {
              $arrayElemAt: [
                "$main",
                0
              ],
              
            },
            {
              $arrayElemAt: [
                "$content",
                0
              ],
              
            },
            "$$ROOT"
          ]
        }
      }
    },
    {
      $project: {
        main: 0,
        content: 0
      }
    }
  ])