Mongo db unique index on sub document problem

Question

I have a collection estates with a unique index on the name field, created thus:

db.estates.createIndex ({ "name": 1 }, { unique: true })

This works - attempts to insert a duplicate name cause an error.

I try to create a per estate unique index on the buildings sub-document (an array of different buildings):

db.estates.createIndex ({ "name": 1, "buildings.name": 1 }, { unique: true })

This does not work; I can have numerous name: 'Building 1' on my buildings sub document for my name: 'Estate 1' document.

Sample document:

{
_id: ObjectId('6661f5ec5236ce287fa26a17'),
name: 'Estate 1',
code: '12345',
buildings: [
  { name: 'Building 3' },
  {
    name: 'Building 4',
    code: '4444',
    function: '',
    floor_area: '',
    number_of_floors: '',
    _id: ObjectId('66673d5ae5d0c8f632280614')
  },
  {
    name: 'All buildings',
    _id: ObjectId('66ae5a9fc029cb808ff01784')
  },
  {
    name: 'Building 2',
    code: '222222',
    function: '',
    floor_area: '',
    number_of_floors: '',
    _id: ObjectId('66afaaee36c6939cbbe20611')
  },
  {
    name: 'Building 1',
    code: '',
    function: '',
    floor_area: '',
    number_of_floors: '',
    _id: ObjectId('6739f3d0858859fdada218ea')
  },
  {
    name: 'Building 1',
    code: '',
    function: '',
    floor_area: '',
    number_of_floors: '',
    _id: ObjectId('6739f3e3858859fdada218eb')
  }
],
building_systems: [
  {
    name: 'VS01',
    category: 'Heating - radiator',
    building: 'Building one',
    room: '4320',
    floor: '04',
    control_cabinet: 'AS01',
    year: 2008,
    components: [
      { name: 'P1', category: 'Pump - circulation' },
      { name: 'ST21', category: 'Actuator - valve' }
    ]
  },
  {
    _id: ObjectId('6661f5ec5236ce287fa26a15'),
    name: 'VV01',
    category: 'Hot water',
    building: 'Building one',
    room: '4320',
    floor: '04',
    year: 2008
  },
  { name: 'LB01', _id: ObjectId('66675021e5d0c8f632280615') }
],
control_cabinets: [
  {
    _id: ObjectId('6661f5ec5236ce287fa26a16'),
    name: 'AS01',
    building: 'Building one',
    room: '4320',
    floor: '04',
    year: 2008,
    plc_manufacturer: 'Beckhoff',
    ip_address: '198.192.0.28',
    port: 183,
    protocol: 'ADSL'
  },
  { name: 'AS02', _id: ObjectId('66675442e5d0c8f632280618') }
],

What am I missing?

Answer 1

The index works the way it was intended but not the way you expected.

The index is per document so the estates collection can only have 1 document with name: 'Estate 1' and buildings.name: 'Building 1'. When you insert a new document a check is made for another document that would have that combination, not if the document with name: 'Estate 1' has another array element containing buildings.name: 'Building 1'.

So these documents can't both exist in the same collection:

// estates
{
    "_id": ObjectId("65494baa21b99c8cff188a11"),
    "name": "Estate 1",
    "buildings": [
       { "name": "Building 1" }, 
    ],
},
{
    "_id": ObjectId("65494baa21b99c8cff188a12"),
    "name": "Estate 1",
    "buildings": [
       { "name": "Building 1" }, 
    ],
}

But these documents can:

// estates
{
    "_id": ObjectId("65494baa21b99c8cff188a11"),
    "name": "Estate 1",
    "buildings": [
       { "name": "Building 1" }, 
       { "name": "Building 1" },
       { "name": "Building 1" }
    ],
},
{
    "_id": ObjectId("65494baa21b99c8cff188a12"),
    "name": "Estate 2",
    "buildings": [
       { "name": "Building 2" }, 
       { "name": "Building 2" },
       { "name": "Building 2" }
    ],
},
{
    "_id": ObjectId("65494baa21b99c8cff188a13"),
    "name": "Estate 1",
    "buildings": [
       { "name": "Building 2" }, 
       { "name": "Building 3" },
       { "name": "Building 4" },
       { "name": "Building 4" }
    ],
}

You might want to consider storing the buildings in another collection with the parent Estate._id stored as a reference. Then you could do an index on that new collection like:

db.buildings.createIndex({ "estateId": 1, "name": 1 }, { unique: true });

When you create a new document in the new buildings collection, this new index would ensure that only one document exists with name: 'Building 1' and the estateId being a reference to the estates document _id value that has the name: 'Estate 1'.

// estates
{
    "_id": ObjectId("65494baa21b99c8cff188a11"),
    "name": "Estate 1",
    "buildings": [
       ObjectId("65494baa21b99c8cff188a14"), 
    ],
},

// buildings
{
    "_id": ObjectId("65494baa21b99c8cff188a14"),
    "estateId": ObjectId("65494baa21b99c8cff188a11"),
    "name": "Building 1"
},
{
    "_id": ObjectId("65494baa21b99c8cff188a15"),
    "estateId": ObjectId("65494baa21b99c8cff188a11"), // ❌ E11000 duplicate key error
    "name": "Building 1" // ❌ E11000 duplicate key error
},

That would mean your Estate.buildings array would just be an array of ObjectIds instead of sub-documents. You can do a $lookup to join them like this.