Primary Key Inference

[gmourier]

Hi everyone 👋

We'd love to hear your opinion about our mechanism for inferring the primary key of documents during the first indexing process. We have thoughts about it, but we would love to verify them from your impressions and feedback.

We have prepared a small survey so that you can help us to see more clearly on that particular subject. 🔮

Survey link

Thank you very much!

[gmourier]

As we haven't had any significant feedback on this call for feedback, I think we should keep the primary key inference but in a different way to better guide users to prevent unwanted consequences.

Today the inference will be automatically triggered and choose the first field containing the id substring. For example, this means that if a field named reviews_id is located higher than the id field of a document representing a product, it will be chosen as the identifier when the first document will be indexed.

In order to prevent this problem, the engine could search a field called id or _id first, and if it doesn't find them take the first field containing id or _id.

The primaryKey can always be specified to override this behavior.

From our analytics, we realize that the majority of users specify the primary key set id or _id.

We could also rename primaryKey to a more suited term like primaryField or documentIdentifier, a discussion is dedicated to that here.

[curquiza]

I like this idea of checking id first and then inferring. The primary key declaration can still be accepted.

However, we should think about a potential addition of the "auto-generated document identifier", cf this discussion for people who are interested. How to handle the feature with our current primary key inference? (not a real question, but a thought)

[gmourier]

Mentioned by @GregoryConrad here meilisearch/milli#695 (comment)

I'm pasting the issue content.

Hi! I noticed this issue when writing some test cases for my side project over at https://github.com/GregoryConrad/embedded_meilisearch

The Problem

I've noticed Milli's behavior in identifying a primary key to use (based on the field names) does not always produce the correct results. Take the following JSON document:

{
  "id": "1234",
  "video": "https://some-video-provider.com/some-video"
}

This works ok; "id" comes before "video" in the document, and it appears milli does the contains("id") check through the fields in-order.

Now take the following document.

{
  "video": "https://some-video-provider.com/some-video",
  "id": "1234"
}

Milli fails to index such a document, even with an id field.

Possible Solutions

Having to manually set a primary key for documents that already provide an id seems fairly unintuitive; thus, I propose a few other solutions:

1. Manually check to see if there is an id field before doing the contains("id") check in-order. This option requires the least change. Also, an id key is much more likely to occur earlier in a document, and this approach respects that.
2. For all fields that contains("id"), choose the one with the smallest string length. This retains consistency with the docs (not a breaking change), but also makes a smarter guess as to which field to use (assuming users have field names like pid or fid, for instance).
3. For all fields that contains("id"), choose the one with the smallest Levenshtein distance from "id". This might actually return the same results as solution 2; I would need to read into the algorithm more. Either way, it will not be a breaking change, as per the docs.
4. Choose the field with smallest levenshtein distance from "id". Here, the field closest to id will be chosen as the primary key. I think this is somewhat a smarter approach than the others. However, this is obviously a breaking change and would require doc updates.

[GregoryConrad]

Hi! Thanks for tagging me here. I also thought of a 5th solution today, in addition to the above 4 that I gave. This is my preferred solution at the moment:

Recasing

The idea is re-casing the given fields in a document to a specific type (say, PascalCase) and then selecting the first (or shortest in length--up to the implementation) field that matches a given regex. For PascalCase, this regex would probably be Id($|[^a-z]). Care would have to be taken with the regex to allow for special characters & numbers (I believe the one I just gave for PascalCase would be ok).

Here are some examples:

• some_id would be recased to SomeId, which would match.
• SomeId would be recased to SomeId, which would match.
• id would be recased to Id, which would match.
• video would be recased to Video, which would not match (the problem example I gave in the issue I filed).
• idiom would be recased to Idiom, which would not match (a tricky example I included because it starts with id but still shouldn't match)

Recasing can be done with recase in Rust.

Regex emulation of recasing

We can also emmulate the recasing above with something like the following regex:

((^|(.*[\d-_ ]))(id|ID)($|([\d-_ ].*)))|((^|(.*[\d-_ A-Za-z]))Id($|([\d-_ A-Z].*)))

This looks like a mouthful, but here's a quick breakdown. We look for "id", "Id", or "ID".

(
  (^|(.*[\d-_ ]))
  (id|ID)
  ($|([\d-_ ].*))
)
|
(
  (^|(.*[\d-_ A-Za-z]))
  Id
  ($|([\d-_ A-Z].*))
)

If we see "id" or "ID", we use the following to match before and after the "id"/"ID":

• (^|(.*[\d-_ ])) = match the start of the line, or any preceding characters followed by some separating character
• ($|([\d-_ ].*)) = match the end of the line, or some separating character followed by any succeeding characters

If we see "Id", we use the following to match before and after:

• (^|(.*[\d-_ A-Za-z])) = match the start of the line, or any preceding characters followed by some separating character (including all letters)
• ($|([\d-_ A-Z].*)) = match the end of the line, or some separating character (including uppercase letters)

This solution was included in case you do not want to add recase as an additional dependency, but in reality it would be best to just use recase.

[GregoryConrad]

Sorry to nag, but is there any update on this discussion internally? I'd be happy to submit a PR to milli for the simple case of checking for id/_id existing (at least to start). Although this is not a blocking issue for my project, it can cause some unexpected behavior.

[gmourier]

Hello @GregoryConrad 👋

We have decided to work on it for v1.0, and we will start next week! I will keep you updated as soon as possible.

[gmourier]

Hi @GregoryConrad!

For v1.0, Meilisearch will stop inferring a pk when there is an ambiguity.

The inference process will be as follows:

• Meilisearch looks for fields ending with id. e.g pid, _id, id, cupid
• If there are more than two fields ending with id (ambiguity state is reached), an asynchronous error will be reported indicating that the desired primary key should be specified manually by the user.

You can find more details here 🙇‍♂️

Thank you!

[gmourier]

Hi everyone 👋

I'm locking that discussion since it will be released with v1

Feel free to open a new discussion to share feedback or a feature request.

Thank you 🙇‍♂️