Re:Mastering Null Semantics: Translating SQL Expressions to ...
by atombender on Sunday September 15, 2024 @08:04AM
(#41546981)
No, 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.
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
(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/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.