4 ms·
Using OpenSearch/Elasticsearch "exists" is on the face of it a sensible choice. But it has a couple of big pitfalls. One potential pitfall here is if your sche
by atombender 2y ago
Using OpenSearch/Elasticsearch "exists" is on the face of it a sensible choice. But it has a couple of big pitfalls.
One potential pitfall here is if your schema supports nested objects. For example:
{
"user": {
"name": "bob",
"age": 20
}
}
A condition such as this:
user != null
would get translated to:
{"exists": "user"}
But this is a "macro" that OS/ES internally expands at query time:
{"boolean": {"should": [
{"exists": "user.name"},
{"exists": "user.age"}
]}
As you can imagine, this can cause an explosion of field names if you have many nested fields. OS/ES has a global limit on the number of boolean clauses a single query can have, and it can cause a query to suddenly fail. Such queries are also very much not performant.
Another pitfall is that empty values do not get indexed. This includes empty objects and empty arrays. Empty values aren't null, yet they will match here because "exists" evaluates to false for them. So "exists" don't work for this edge case.
You have at least two decent choices here. One is to index a special adjacent field of type "boolean" for every field. For any data you index, you also index this metadata field:
{
"user": {},
"user$notNull": true
}
However, you end up with many fields, and OS/ES performance degrades quite a bit the more fields you have.
Another option is to have a top-level field listing the names of all fields that are empty. So something like:
{
"user": {},
"$notNullFields": ["user"]
}
Now you can use a boolean match on this special field rather than "exists", and it will be performant.
Of course, the list has to include all parent key paths as well. So for a non-empty object:
{
"user": {
"name": "bob",
"age": 20
},
"$notNullFields": [
"user", "user.name", "user.age"
]
}
But you have the option of only including empty objects/arrays in this list, and using "exists" for all value types, assuming you know which fields can be which.
- jillesvangurp 2y agoWorth pointing out that what you outline here is not an actual nested object (in the sense of using the nested mapping) but simply field flattening. Nested is a specific thing in opensearch intended to work with lists of sub documents to model one to many relation ships. It's intended to work around the limitation that opensearch does not implement any joins. One big limitation is that child documents need to have the same mapping as their parent. Another is that there's an upper limit on the number of nested documents. Nested of course further complicates things because you need to know to use the nested query construct as well in the dsl. So you get something like { "query": { "nested": { "path": "users", "query": { "bool": { "must": [ { "exists": { "field": "users" } } ] } } } } } (if you map users as nested instead of object) I'm not sure how they handle this exactly, but Elasticsearch recently added sql support in their until recently proprietary and now somewhat open source again if you can live with AGPL and contributor licenses. Their documentation is a bit light on the exists semantics but I suspect it might have similar issues. There are also some limitations on the use of nested: https://www.elastic.co/guide/en/elasticsearch/reference/current/sql-limitations.html https://www.elastic.co/guide/en/elasticsearch/reference/curr... along with a few other limitations. Adding SQL support has been a topic for a long time. It's hard because Lucene simply is not designed to be a relational database. It's optimized for text search. So there are going to be all sorts of issues where you end up shooting yourself into the foot in terms of performance or run into things that it simply does not support at all. Another product worth mentioning here is cratedb, which started out as a product that built ontop of Elasticsearch to add SQL support. Like Opensearch, they forked when Elastic changed the license. And since then they have changed what it does architecturally. I have no experience with their product though. So, I can't comment on the semantics.
- atombender 2y agoNo, I'm not referring to the "nested" mapping type, but nested in the sense of JSON object structures. The "nested" mapping type, in addition to its limitations, comes with a huge performance hit, and is therefore not usable as a general-purpose way of representing arbitrary nested structures.