[SERVER-1176] Unique index on array keys only Created: 01/Jun/10  Updated: 06/Dec/22  Resolved: 29/Jun/19

Status: Closed
Project: Core Server
Component/s: Index Maintenance, Write Ops
Affects Version/s: None
Fix Version/s: None

Type: Improvement Priority: Major - P3
Reporter: phpMoAdmin Assignee: Backlog - Query Team (Inactive)
Resolution: Won't Do Votes: 1
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Related
related to SERVER-30191 Add JSON Schema support for document ... Closed
Assigned Teams:
Query
Participants:

 Description   

As far as I can tell this is not possible with the current unique index, but it would be great to be able to unique-index just the keys of an array. This would really help in de-normalization as the following example could act as both a user table and products table if the key of the products array were unique indexed so that keys are unique between all documents in the table.

array(
'_id' => 1,
'name' => 'Rita',
'userdata' => array(...),
'products' => array(
1 => array('name' => 'cube', 'style' => 'polka dots'),
2 => array('name' => 'tiles', 'style' => 'checkerboard')
)
)

array(
'_id' => 2,
'name' => 'Joe',
'userdata' => array(...),
'products' => array(
3 => array('name' => 'circle', 'style' => 'round'),
4 => array('name' => 'triangle', 'style' => '3-sided')
)
)

With the current Mongo unique index I could still add the following even though the key already exists within another document:

array(
'_id' => 3,
'products' => array(
3 => array('name' => 'duplicate key', 'style' => 'key is not unique...')
)
)



 Comments   
Comment by Asya Kamsky [ 29/Jun/19 ]

Is this actually asking for keys (field names rather than values) to be unique? In the description it's 1, 2, 3, 4 keys that are unique. And then 3 is added again. I don't see how that fits into the document model.

Comment by Asya Kamsky [ 04/Dec/17 ]

For arrays of scalars or cases where entire subdocument must be unique, $jsonSchema (available in 3.6.0) will allow constraining that all array elements must be unique.

This does not resolve the case where a single field of the subdocuments of an array must be unique so this ticket is still relevant to tracking that.

Comment by Ioannis Chouklis [ 24/May/15 ]

Yes, the suggested method by Aaron works. Unfortunately, it doesn't work when you are using the $push operation

e.g.

c.update({_id : 2}, {$push : {products : {name:'circle'}}});

The element will be pushed in to the products array.

Comment by Aaron Staple [ 26/Nov/12 ]

Hi,

Can you do something like this?

c = db.c;
c.drop();
 
c.save( {
        '_id': 1,
            'name': 'Rita',
            'userdata': [],
            'products': [
                              { 'name': 'cube', 'style': 'polka dots' },
                              { 'name': 'tiles', 'style': 'checkerboard' }
                          ]
            } );
 
c.save( {
        '_id': 2,
            'name': 'Joe',
            'userdata': [],
            'products': [
                         { 'name': 'circle', 'style': 'round' },
                         { 'name': 'triangle', 'style': '3-sided' }
                          ]
            } );
 
c.ensureIndex( { 'products.name':1 }, { unique:true } );
 
c.save( { _id:3, products:[ { name:'circle' } ] } );
printjson( db.getLastError() );

Generated at Thu Feb 08 02:56:17 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.