Filter: Add IN[] operator

[loiclec]

Description

We had a few requests to add a IN filter which could provide a more efficient way to write filters containing too many OR or AND. This would reduce the size of the request (in bytes) due to the syntax being terser and it would avoid running into filter complexity limits.

Related discussions:

• Meilisearch crashes when using a lot of filter clauses meilisearch#2163
• IN operator #407
• NOT IN (x, x, x) filter #274

I propose the following syntax:

in_filter  = value "IN" "[" value_list "]"
value_list = (value ("," value)* ","?)?

For example:

colour IN [green, red, blue]
colour IN[]
id IN [1, 2,3,]
NOT colour in [green]
NOT id IN []
NOT id IN[35, 23, 2,]

IN[...] selects all documents whose attribute contains at least one of the values given inside the brackets. In case there are no values, then the filter selects no documents.

NOT colour IN[...] selects all documents with a colour attribute which contains values that are all different from those in the brackets.

I wrote a basic implementation of this filter on the branch filter/field-in of the milli repo.

Examples

With the following documents:

[
  {
    "id": 0,
    "colour": "green"
  },
  {
    "id": 1,
    "colour": "blue"
  },
  {
    "id": 2,
    "colour": "red"
  },
  {
    "id": 3,
  },
  {
    "id": 4,
    "colour": []
  },
  {
    "id": 5,
    "colour": [
      "blue",
      "green"
    ]
  }
]

Here are a couple of examples of the ids that the filter would select:

colour IN[]
=> []
colour IN[blue]  
=> [1, 5]
colour IN[blue, green]  
=> [0, 1, 5]

NOT colour IN[blue]
=> [0, 2]
NOT colour IN[red]
=> [0, 1, 5]
NOT colour IN[]
=> [0, 1, 2, 5]

The requirement that the attribute contains a value for the NOT IN filter can be considered surprising, but it follows from this equivalence:

NOT colour IN[blue, green]  
<=>  
NOT (colour = blue OR colour = green)
<=>
colour != blue AND colour != green

But someone could argue that

colour IN[]
NOT colour IN[]

should behave differently or be disallowed.

Syntax details

I chose to use square brackets for the syntax instead of pqrentheses because parentheses are already used to group expressions together. Futhermore, square brackets denote lists in JSON and other programming languages, so it makes sense to use them here when we expect a list of values.

Trailing commas are allowed:

colour IN [blue,]

But a comma in an empty bracket pair is NOT allowed:

colour IN [,]     is invalid

Other

Typesense also offers this functionality. From https://typesense.org/docs/0.22.2/api/documents.html#search-parameters :

A field can be matched against one or more values.

Examples:
- country: USA
- country: [USA, UK] returns documents that have country of USA OR UK.

The syntax is therefore similar but a colon is used instead of IN.

[irevoire]

Hey, great explanation.
Personally, I find the feature perfect as-is 👍

[gmourier]

Hey @loiclec!

I have no particular opinion about the syntax personally.

The main thing is that it is usable and solves the case of the user having to multiply the filter clauses which seems to be the case with the examples you give.

Maybe the @meilisearch/integration-team will have a different opinion between IN[]or IN().

Maybe it deserves another discussion about the position of the NOT, IMO it seems much more natural to be able to write color NOT IN(...) rather than NOT color IN(...).

1. Does it seems relevant to you?
2. Technically, what does it mean?

[loiclec]

1. Yes, that definitely seems relevant.

2. Technically, there are a couple of things to note:

Currently, the syntax for NOT is the same regardless of the filter it applies to: it's simply the word NOT followed by a filter. So if we write x NOT IN[...], that would be a major change in the syntax. But we could potentially allow both forms:

NOT x IN [...]
// is equal to
x NOT IN [...]

Allowing both forms is easier to implement than to allow only x NOT IN[]. But I am slightly against it. It may be confusing that the following is allowed:

NOT x NOT IN [1, 2, ...]

One thing to keep in mind is that the NOT operator is not a “real” filter. It is more of a filter-modifier: it transforms the filter that follows it to its negated form. For example:

NOT x = 5   
	=> // (is transformed to) 
x != 5


NOT (x < 1 OR x = 2)
	=> 
x >= 1 AND x != 2

// etc.

So there isn't a single explanation for what NOT means. Every time we have a new filter, we need to think about what its negation is.

One idea I'd like to put out there is to define NOT {filter} as a filter that selects whatever is not selected by {filter}. I would find it easier to document and understand. But I am not 100% convinced that it would be a great change. For one, it would be a breaking change:

	NOT x = 5 
// currently means x != 5, which implies x exists.
// but with the proposed change, it would mean:
	(x != 5) OR (NOT x EXISTS)

// as a result, we would have that 
	NOT x < 5
// is a different filter than
	x >= 5
	
// And also:
	x = 5
// would be different from:
	NOT x != 5

So to go back to my light objection to x NOT IN [..]. If we ever adopt this new definition of the NOT operator, then the confusing filter I mentioned earlier becomes even more confusing:

NOT x NOT IN [...]

In short:

1. it's ugly to disallow NOT x IN[..]
2. allowing both NOT x IN[..] and x NOT IN[..] is possible but it may lead to confusion about the difference (if any) between the two of them, especially when they are combined. This would be even worse of a problem if we simplify the definition of NOT as I suggested above.
3. When we consider how a filter behaves with non-existing values and what its negated form means, we run into some confusing cases. Maybe we should have a conversion about these nuances before agreeing on the IN filter?

[gmourier]

Thanks! It's very well detailed.

When we consider how a filter behaves with non-existing values and what its negated form means, we run into some confusing cases. Maybe we should have a conversion about these nuances before agreeing on the IN filter?

Sure!

My 2 cents here, when you express this notation NOT x NOT IN [1, 2, ...], I find that it could make sense. I'm simplifying it to x IN [1,2, ...].

[bidoubiwa]

Just out of curiosity, would x = [1, 2] be a possibility? Not sure I want it but I would like your opinion on it

[bidoubiwa]

NOT x IN [...] or X NOT IN [...] or both

I'm mot against the following as indeed @gmourier pointed out, it does make sense that it would work.

NOT x NOT IN [1, 2, ...]

Nonetheless, it introduces double negations. Is there a use case where it would make sense that we use double negation?

If we had to choose between one of them, Loic suggestion more in line with programming languages syntax

// The ! is in front of the condition
if (!([1,2].includes(x)) {
    // ...
}

It also gives the possibility to create a list of conditions and ask form them all to be NOT

if (!([1,2].includes(x) || y = 2)) {
    // ...
}

Whereas SQL languages prefers adding the NOT after the fields name:

SELECT Column(s) FROM table_name WHERE Column NOT IN (value1, value2... valueN);

But it results in the case of multiple conditions being longer to express.

The question is, do we want to go in the direction of SQL syntax or programming syntax?

I'm not against accepting both. If I had to chose one I would go with @loiclec . Maybe we could do a poll?

NOT x IN [...] or X NOT IN [...] or both

In favor of NOT x IN [..] 👍
in favor if X NOT IN [...] 🚀
In favor of both NOT x NOT IN [..] 🎉

[gmourier]

I would favor (not saying we have to enforce it) a SQL expression if it were up to me. SQL is an almost "universal" logical expression language. I think that developers are quite used to it or have already encountered it several times.

[bidoubiwa]

x IN [...] or x IN (...)

(sorry for all the answers I try to separate every point that was brought)

If we continue in the line of programming language this suggestion makes more sense to me:

colour IN [blue, red]

If we want to go in the direction of SQL this one makes more sense.

colour IN (blue,red)

I have a preference for the first option.

Please vote with the appropriate emoji:

In favor of x IN [...] 👍
In favor of x IN (...) 🚀
In favor of x IN {...} ❤️
In favor of accepting both 🎉

This impacts the other poll:

[irevoire]

The last option would actually be to use { }.
First it's what we use in C / C++ to create arrays: int[] bob = {1, 2, 3 };. And as we all know, 99% of our community are C web developers.

And then, the second argument is that {} are often used for set/dictionary. It implies that you should not duplicate any entry (you can, but it'll have no effect), which is what we want.

Personally, I would rank everything like that; [] > {} > ()

[bidoubiwa]

I have updated the poll

[gmourier]

"99% of our community are C web developers." this is a joke right? If so, well done!

[bidoubiwa]

x IN[...] or x IN [...]

don't mind the bracket choice, this is another poll

I don't like the IN being attached to the condition. It makes it look like a function whereas it is an operator like AND and OR that uses white space between expressions.

// yes
colour IN [blue]
// no
colour IN[blue]

In favor of IN[..] or IN(...) 👍
In favor of IN [..] or IN (...) 🚀
In favor of accepting both 🎉

This impacts the other poll

[gmourier]

I think we don't have to forbid the spaced/not spaced notations if they can be resolved to the same thing. It seems easier to accept both. It's nicely resolved itself if a user omits a space or adds one, plus it may seem technically easier but I'm not the subject matter expert about nom here 🥷.

[irevoire]

Yep, I'm in favour of accepting both too. That's what happens with every other operators I think; price<3 vs price < 3

[cparkinsonMYCM]

Will this work with the inverse arrangement of parameters, such as 'example' IN attribute where attribute is an array data type in the index?

The Documentation says:

Meilisearch flattens arrays and concatenates them into strings.

and that

we are fully committed to the philosophy of prefix search.

So I would assume not, but if this could be in consideration, that would be greatly appreciated.

[gmourier]

Hello dear community, 👋

We have published a docker image allowing you to test the addition of the IN operator.

IN operator

Instead of writing a cumbersome clause like user_id = 1 OR user_id = 2 OR user_id = 3 .... It's possible to write user_id IN [1, 2, 3] and voila!

• user_id IN [...] selects all documents whose attribute contains at least one of the values given inside the brackets. In case there are no values, then the filter selects no documents.
• NOT user_id IN[...] selects all documents whose attribute contains values that are all different from those in the brackets. (It includes documents missing the attribute - See EXISTS operator for more granularity).

---

We have also made some other changes that may be of interest to you.

NOT / != behavior

NOT /!= operator has been enhanced following this discussion.

tags != adult now returns documents having tags: null or tags: [] but also documents without the tags property. It's equivalent to NOT tags = adult.

The NOT operator selects all documents not selected by the filter that follows it.

EXISTS operator

We took the opportunity to add the operator EXISTS. This operator allows you to granularly require that the selected documents have a tag attribute regardless of its value.

In this case, tags != adult and tags EXISTS to retrieve documents that do not contain the adult value in the tag attribute and only those for which the tag attribute exists.

• tags EXISTS selects all documents having the tags attribute.
• NOT tags EXISTS selects all documents missing the tags attribute.

---

We would like to have your feedback before deciding whether to introduce the given changes in a future version.

To do so you have to pull the docker image we prepared.

docker run -it --rm \
    -p 7700:7700 \
    -v $(pwd)/meili_data:/meili_data \
    getmeili/meilisearch:v0.29.0-filter.beta.0

Thank you so much for being part of our community ✨

[gmourier]

Hello 👋

We have just released v0.29.0rc1, which is a release candidate of v0.29.0 🔥

You can test the just-introduced IN operator!
More information in this issue

Binaries are attached to the release, or you can use the docker image:

docker run -it --rm \
    -p 7700:7700 \
    getmeili/meilisearch:v0.29.0rc1

Let us know about any bugs or feedback! 😄 It would be really helpful.

FYI, the official v0.29.0 release will be available on 3rd October.

[gmourier]

Hey everyone 👋

Locking this discussion since the IN operator has been released.

IN [valueA, valueB, …, valueN] selects all documents whose chosen field contains at least one of the specified values. The expression genres IN [horror, comedy] returns all documents whose genres includes either horror, comedy, or both

📚 https://docs.meilisearch.com/learn/advanced/filtering_and_faceted_search.html#using-filters

Please open a new discussion if you have any feedback to share 🙇‍♂️