Dup_key error when trying to index an array field in mongodb

Working with Data
,
1

Hello all,

Short intro for my question:
In our product we have a Step object that identify a specific step inside a bigger Run.
Step is identified uniquely by 3 fields : run_id, step_name(string), scopes(array).

So here is the thing - “scopes” is an array that describes the scope of the specific step.
These 2 objects are should be able to live together:

# A lives under  "x" scope
{
  run_id: 123,
  step_name: "A",
  scopes: [x:{<x_details>}] 
}

# A lives under "x" and specific "y" scope.
{
  run_id: 123,
  step_name: "A",
  scopes: [{"x": <x_data>}, {"y": <y1_data>} ] 
}

# A lives under "x" and a specific (defferent y) "y" scope.
{
  run_id: 123,
  step_name: "A",
  scopes: [{"x": <x_data>}, {"y": <y2_data>} ] 
}

My question: I need my index to be unique by the 3 keys - run_id, step_name, scopes ,
BUT mongodb will raise a WriteError (code=11000 "dup key" ) when trying to insert any 2 of the 3 in the exmaple, when the collection is indexed with unique=True.

How can I get the effect I want ?

Thank you in advance. :pray:

2

Hi @Uri_Grinberg ,

A multikey index that created on arrays create an index entry per array element combination, meaning document 2,3 will both have an entry for :

run_id: 123,
  step_name: "A",
  scopes: [{"x": <x_data>}

Therefore this will break the uniqueness. Can you enforce uniqueness by doing check on application side? If not perhaps change the data model to be:

scopes : {
   x : ... ,
   y : ....
}

This will not create multikey index…

Thanks
Pavel

3

First, thank you very much for the answer, @Pavel_Duchovny

  1. I really prefer the uniqueness to be kept by the schema iteself.

  2. Your suggestion of:

won’t work for us, as order of scopes will be lost this way. some like:

scopes : {
   0: {x : ... },
   1: {y : ....}
}

could solve the order issue - but smart queries on scopes won’t be possible, I afraid.

That led me to the inevitable solution - keeping a scopes_hash field next to the scopes field - to be used only for unique indexing purpose.

I will love to have a review and other suggestions. :thought_balloon: :thought_balloon: