Storing/Saving only a particular object from list of objects along with parent object in MongoDB

Question

In mongodb, I have a master table called category sample data as below:

{
    "_id" : "63d3e01f43aa4e0ee349f841",
    "subCategories" : [
        {
            "subCategoryId" : NumberLong(1), 
            "name": "Mobile phones" 
        },
        {
            "subCategoryId" : NumberLong(2), 
            "name": "XYZ Machine" 
        }
    ]
}

There is another table called product. Sample data as below:

{
    "_id" : "63d3e13b43aa4e0ee349f842",
    "productId" : NumberLong(1),
    "name" : "iphone 14",
    "category" : DBRef("category", "63d3e01f43aa4e0ee349f841")
}

While adding new product, only 1 category and 1 subcategory from that selected category can be selected. In my case, I am using @DbRef and I am struggling to find a way through which I can save only 1 subcategory within the product table. Right now it points to an entire object of the category table in which there can be x number of subcategories.

Is it possible to achieve this using @DbRef annotation without changing the database structure and without breaking the category table records in between separate category & subcategory tables ?

May be something like this:

{
    "_id" : "63d3e13b43aa4e0ee349f842",
    "productId" : NumberLong(1),
    "name" : "iphone 14",
    "category" : DBRef({"category", "63d3e01f43aa4e0ee349f841"},
                       "subCategoryId", 1)

}

Using MongoDb version 4+ with Java spring-data-mongo

Answer 1

I don't think it is possible to achieve your expected behaviour without changing the schema. From official doc of DBRef,

DBRefs are a convention for representing a document, rather than a specific reference type.

So DBRef will point to a specific document, instead of certain sub-document array entry.

This leaves us 2 options:

1. change the category collection to store document like this:

{
    "categoryId" : "63d3e01f43aa4e0ee349f841", // this is new
    "subCategoryId" : NumberLong(1), 
    "name": "Mobile phones" 
}

Unfortunately this is banned as changing schema is not allowed

2. add another field in product schema to store the subCategory Id and use it to locate subCategory entries when $lookup

{
      "_id": "63d3e13b43aa4e0ee349f842",
      "productId": NumberLong(1),
      "name": "iphone 14",
      "category": {
        "$ref": "category",
        "$id": "63d3e01f43aa4e0ee349f841"
      },
      "subCategoryId": NumberLong(1) // this is new
    }

the aggregation:

db.product.aggregate([
  {
    $match: {
      "_id": "63d3e13b43aa4e0ee349f842"
    }
  },
  {
    "$lookup": {
      "from": "category",
      "let": {
        categoryId: "$category.$id",
        subCategoryId: "$subCategoryId"
      },
      "pipeline": [
        {
          $match: {
            $expr: {
              $eq: [
                "$$categoryId",
                "$_id"
              ]
            }
          }
        },
        {
          $unwind: "$subCategories"
        },
        {
          $match: {
            $expr: {
              $eq: [
                "$$subCategoryId",
                "$subCategories.subCategoryId"
              ]
            }
          }
        }
      ],
      "as": "subCategoryLookup"
    }
  }
])

Mongo Playground

This is also kind of banned as it needs to add one more field to the product schema. But I would still suggest this as this involves a minimal change to the schema.