tl;dr:
- It seems like unique indexes that explicitly include identity refs (refs to the given doc) in the values (e.g.
values: [{field: ["ref"]}]and also bindings that include identity refs) don’t work. Perhaps indexes include that identity doc in the uniqueness calculation, making the uniqueness constraint useless? It would be great if those refs could be excluded from the uniqueness constraints. If not, documenting this would be useful (maybe I missed it). - It seems like null values are not indexed. That makes sense, and can be useful to have indexes only pertinent to a filtered subset of docs (especially if
uniqueworked in that context, per the above)! I didn’t see that in the docs. If it’s actually not there, I’d recommend adding a note to that effect. - Open source might be helpful for community debugging.
Details
Hi. I have a few collections that should be unique across two or more fields. With some small surprises, it seems to work as expected when I don’t set values that are the indexed doc.
I’ve spent some time identifying decent examples, simplifying from my actual needs. I could take it further to see if I can find even simpler test cases, but I’m hoping this is enough to communicate reasonably quickly.
In all cases, I’m verifying that the index has active: true before testing (and in fact my test data is small enough that the indexes are active as soon as I create them).
In most cases, I’ve performed these experiments creating indexes both in the shell (CreateIndex) and in the Console’s “Indexes” UI. The results are the same, except when I create bindings, which I can’t do in the UI.
First, here’s an index that does work as expected.
{
name: "unique_test_Sync",
unique: true,
serialized: true,
source: "Sync",
terms: [
{field: ["data", "start"]},
{field: ["data", "name"]},
{field: ["data", "conversation"]}
]
}
If I make a Sync doc that has no start date (null in the index), a “test” name, and a conversation (a ref to a doc in another collection), then I get expected behavior: a search in the index returns the doc I made, and the index rejects attempts to create another doc with the same three key values.
Now we go into unexpected or buggy behavior. First, let’s delete the index and then make a new one that explicitly specifies a ref as a value.
{
name: "unique_test2_Sync",
unique: true,
serialized: true,
source: "Sync",
terms: [
{field: ["data", "start"]},
{field: ["data", "name"]},
{field: ["data", "conversation"]}
],
values: [
{field: ["ref"]}
]
}
Obviously this would be more useful if I had more values there, or a filtering binding, but this is the simplest story that demonstrates the behavior I want to highlight.
Good news: searches still work as expected. Bad news: the unique limitation no longer works. I can create a new document with the same three key values, without the database stopping me.
One hypothesis I had was that you hadn’t built in logic to exclude identity doc refs from the unique logic that looks across terms and values. That’s understandable that it’s not there yet, even if disappointing. (BTW, open source would help folks like me debug issues like this!)
For one other smaller surprise, let’s look at another variant. In this one, we’ll remove the ref and move the start date from a term to a value.
{
name: "unique_test3_Sync",
unique: true,
serialized: true,
source: "Sync",
terms: [
{
field: ["data", "name"]
},
{
field: ["data", "conversation"]
}
],
values: [
{
field: ["data", "start"]
}
]
}
Remember–the start date is empty (effectively a null, per index docs). In that case, searches (now only of the name and the reference) don’t get any results at all. I’m assuming that this is because null values are not indexed, which makes sense. (I didn’t see that documented, but maybe I missed it.)
I did a number of other side experiments, but they basically confirm that everything is working as expected, with the above two caveats on explicit values and null values.
Thanks for reading!
.
