Filter on impossible to match values (null, empty)

[irevoire]

This discussion comes from this comment on the Meilisearch repository where a user would like to select the fields whose value is equal to null: meilisearch/meilisearch#2484 (comment).

{
  "id": "ignore_me",

  "null": null,
  "array": [],
  "empty": ""
}

Currently, all these fields can't be matched by our filters.

1. Is there more « impossible » values currently?
2. What API should we propose to match these?

@mech, since you're the first one asking for this feature, I would love to know how you would like to select these fields.
I was thinking of introducing a new keyword named IS that could be followed by EMPTY or NULL but if there is no other values it feels a little bit overkill 🤔

[mech]

I think if it can mimic SQL like gender IS NULL and gender IS NOT NULL will be much good enough. My search script actually fallback to Postgres if MeiliSearch instance somehow is offline, so having some parity will be nice. It definitely do not require any value.

If you think about it as a UI, when people select "Is empty", there will be no input field also.

I have another unrelated questions. Since we on the topic of filtering. I do have more filters in mind like:

• Contains
• Does not contain
• Starts with
• Ends with

All copied from Notion filter. I am not sure if you guys plan on that also?

[gmourier]

Hey @mech 👋

I've created a fresh discussion for the CONTAINS operator (also mentioning the Starts with, ends with).

[gmourier]

I was thinking of introducing a new keyword named IS that could be followed by EMPTY or NULL but if there is no other values it feels a little bit overkill 🤔

This is purely personal taste, I like the idea of being able to do that as in SQL. These are super common filters for someone having already used a SQL database.

What you are suggesting @irevoire, is to have NULL and EMPTY instead of IS NULL and IS EMPTY?

---

@mech Do you need to have both EMPTY and NULL?

I'm asking that because maybe having EMPTY could match null and '', "".

Having both brings more versatility but I wonder if it's not introducing some complexity.

[mech]

Having EMPTY to present just null is expected to me. Empty string seems to be presented as having a value traditionally.

To prevent empty string from being introduced, I would developer will use validation at frontend first, but I could be wrong.

[FrittenKeeZ]

I would say IS NULL for null checks, IS EMPTY for '', "", [], {}, and IS NIL for either null or empty

[mech]

Yup certainty make much sense. Like the way it include empty array and object also 👍

[FrittenKeeZ]

The ability to compare two fields could be a nice addition?
Like field1 > field2

Various date and time filters would also be very nice, like being able to select users having birthday today, without having to explicit add a field containing only the day and month.

[irevoire]

That's interesting; it would be nice if you could open two new product discussions for both features!

[ahmednfwela]

I think this is a very important feature, but using the = operator would be problematic since the user might expect field = null to be equivalent to field = 'null' and match {"field": "null"}.

I suggest simply introducing new operators NULL, EMPTY which would have the same syntax rules as EXISTS.

---

• NULL matches fields that EXISTS AND = NULL
• NOT NULL matches fields that NOT EXISTS OR != NULL

1. {"name":"A", "price": null}
2. {"name":"A", "price": 10}
3. {"name":"A"}

price NULL would match 1
price NOT NULL or NOT price NULL would match 2,3
price EXISTS would match 1, 2
price NOT EXISTS or NOT price EXISTS would match 3

common query : (price EXISTS) AND (price NOT NULL) would match 2

---

• EMPTY matches Array [ ] OR Object { } fields that EXISTS AND (= [] OR = {})
• NOT EMPTY matches fields that NOT EXISTS OR (!= [] AND != {})

1. {"name":"A", "tags": null}
2. {"name":"A", "tags": []}
3. {"name":"A", "tags": ["hello","world"]}
4. {"name":"A"}
5. {"name":"A", "tags": {}}
6. {"name":"A", "tags": {"t1":"v1"}}

tags EMPTY would match 2,5
tags NOT EMPTY or NOT tags EMPTY would match 1,3,4,6
tags NULL would match 1
tags NOT NULL or NOT tags NULL would match 2,3,4,5,6
tags EXISTS would match 1,2,3,5,6
tags NOT EXISTS or NOT tags EXISTS would match 4

common query : (tags EXISTS) AND (tags NOT NULL) AND (tags NOT EMPTY) would match 3,6

[Kerollmops]

I published a new prototype version for the IS NULL syntax that is available on Docker.
You can use the syntax described in the concerned PR here (it differs from the one in the main message above, it uses an IS NULL and IS NOT NULL syntax instead).

docker run -it --rm -p 7700:7700 -v $(pwd)/meili_data:/meili_data getmeili/meilisearch:prototype-null-filter-1

Please let me know here if anyone has any issue with it 🙏

⚠️ Prototypes are not recommended for production

[Kerollmops]

I published a new prototype version for the IS NULL and IS EMPTY syntax that is available on Docker.
You can use the syntax described in the concerned PR here.

It differs from the one in the main message above:

• It uses an IS NULL and IS NOT NULL syntax.
• The IS EMPTY and IS NOT EMPTY matches empty strings "" too.

docker run -it --rm -p 7700:7700 -v $(pwd)/meili_data:/meili_data getmeili/meilisearch:prototype-null-empty-filters-0

Please let me know here if anyone has any issue with it 🙏

⚠️ Prototypes are not recommended for production

[mech]

Just to confirm, this is not in the recent v1.1 releases right.

[ahmednfwela]

@mech no I think this will be in v1.2

[gmourier]

Yes! @ahmednfwela is right, the release candidate should be available next week

[macraig]

Hello everyone 👋

We've just released the first RC (release candidate) of Meilisearch containing this new feature!
You can test it by using

• the release assets
• the Meilisearch Docker image

docker run -it --rm -p 7700:7700 -v $(pwd)/meili_data:/meili_data getmeili/meilisearch:v1.2.0-rc.0

You are welcome to leave your feedback in this discussion.

If you encounter any bugs, please report them here.
Thanks in advance for your help and your involvement in Meilisearch ❤️

🎉 Official and stable release containing this change will be available on June 5th, 2023

⚠️ RC (release candidates) are not recommended for production

[macraig]

Hey folks 👋

v1.2 has been released! 🐻‍❄️ We've introduced two new filters:

• IS EMPTY matches existing fields with a valid, but empty value

• IS NULL matches existing fields with an explicit null value

📚 https://www.meilisearch.com/docs/learn/advanced/filtering#is-null
📚 https://www.meilisearch.com/docs/learn/advanced/filtering#is-empty

[cklogs]

@Kerollmops

I know this is a closed thread, forgive me if this is the wrong spot to ask. I did dig through documentation, github issues and Q&A and could not find exactly this issue being discussed--though it's entirely possible I missed something.

If you'd prefer that I direct the question elsewhere or open a separate discussion, please let me know and I'll promptly update.

For the sake of space efficiency, some documents in my index are intentionally missing fields for which there is no, and will never be a, value.

Initializing all fields for every document (with null or empty) will consume a great deal of extra space, which I would prefer to avoid.

[
    {
        "id": "id-01",
        "name": "product 1",
        "specialProductAttribute": [
            "value 1",
            "value 2"
        ]
    },
    {
        "id": "id-02",
        "name": "product 2"
        // specialProductAttribute not defined
    }
]

Every single one of these filter expressions will return the product with "id-02".

"filter": "NOT specialProductAttribute CONTAINS 'value 1'"

"filter": "specialProductAttribute != 'value 1'"

"filter": "specialProductAttribute IS NOT NULL"

"filter": "specialProductAttribute IS NOT EMPTY"

Is there at all a way to filter out documents that are simple "missing" the targeted field?

Thanks for your help.

[ahmednfwela]

I think you are looking for EXISTS filter

[cklogs]

@ahmednfwela Yes, you are absolutely right. Thank you.

I don't know how that escaped me. Appreciate it.