Kibana query filter by result of another saved search object field data


I am migrating the log reports from direct search with oracle SQL to Kibana,

and I am creating the dashboard to show the existing log reports.

May I know is it possible to get the result like the following query and how can I do.

select profile, status, count (*) from tableA
where  trx_id in (select distinct trx_id from tableB where ... )
group by profile, status;

p.s. My Kibana version is 6.2.3

Thank you.

Kibana SQL does not support sub-selects like the one you're showing, and Elasticsearch does not support joins across indices. You can use the aggregations in Elasticsearch to do very powerful grouping like you've shown, but all the relevant data needs to be part of the same document. This is the standard way that a document store like Elasticsearch needs denormalized data.