Elasticsearch where in

Question

Can I in elasticsearch express a query that is similar to the following sql query?

select * from data where data.uid in(select d2.uid from data d2 where d2.colX='val1');

@Amit-ESenthusiast You mean that it can't be answered in a generic expression? like with x, y variables? — user3532232, Sep 02 '22 at 09:06
Does this answer your question? [What is the ElasticSearch equivalent for an SQL subquery?](https://stackoverflow.com/questions/28734436/what-is-the-elasticsearch-equivalent-for-an-sql-subquery) — star67, Sep 02 '22 at 11:07
You can run sql queries by SQL Search API, look [this](https://www.elastic.co/guide/en/elasticsearch/reference/current/sql-limitations.html#_using_a_sub_select). — rabbitbr, Sep 02 '22 at 12:11

score 0 · Answer 1 · answered Sep 02 '22 at 15:21

There are two possible solutions.

You can first run a query to get a list of IDs and then use those IDs to run a second query (terms query). Use this approach if you know that the result of the first query will stay under 65,536 IDs/terms. Elasticsearch has a default limit on this amount. You shouldn't increase this limit, it's there for a reason!

You can use nested or a parent/child documents. The main difference is that nested are faster compared to parent/child, but, nested docs require reindexing the parent with all its children, while parent child allows to reindex / add / delete specific children. I don't have enough context to know which type of join will work best in your case.

If Elasticsearch is not a requirement, you might want to take a look at Clickhouse. It supports join queries out of the box (in an SQL way).

Elasticsearch where in

1 Answers1