Is Self Inner Join Supported in ES?

Elastic Stack Elasticsearch
1

is possible to the below DB in query in Elasticsearch in SIngle query.

SELECT * 
FROM ADSMFolderDetails a 
INNER JOIN ADSMFolderDetails b 
  ON a.parent_path = b.path 
 AND a.perm = b.perm 
 AND a.sid = b.sid
2

Welcome to the forum !!

Maybe, more likely not.

INNER JOIN do not translate that well, in part as elasticsearch is not really a "database" in same way as traditional "databases".

But close to impossible to say without knowing something about the data in ADSMFolderDetails, and how that is mapped to elasticsearch documents. Show us some sample data from ADSMFolderDetails, and how that is represented in Elasticsearch docs (usually a few json documents would suffice) and someone can maybe help, suggest other ideas to return same results as your SQL query.

3

It's not possible to use JOINs using the Query DSL. But you can use them in ES|QL, the new query language for Elasticsearch.

Check this blog post for more details.