In an index, we store information for example student and their department information.
Looking for query to get all the students belonging to the same department as a given student name passed in the search clause.

In normal RDBMS, this can be achieved

select * from student_dept where dept_name in (select dept_name from student_dept where student_name='John')

Do we have an option to achieve similar result in ES?

Note: The search and nested query as well happens on the same index.

Expecting a Elastic search query that can provide the needed result