How to do subqueries in Elastic search

Elastic Stack Elasticsearch
1

Hi, I have a sql query which has subquery, can any one help me how this could be done in Elastic search 5 and above.

> select sum(a.netamt)  ,   
>  (select sum(total_amount)
>         from (
>                select distinct number, total_amount
>             from Table1 
>             where id=6 
>            )  a
>         ) total_amount
> from Table1 a
> where a.id=6

Suppose my sql Table1 looks like this.

number net amt total amount
229W0330014229 0.69 11.07
229W0330014229 0.05 11.07
21380365288472 26.25 44.02
21380365288472 1.2 44.02
64950353437733 19.95 57.39
64950353437733 2.53 57.39
64950352648123 11.55 37.6
64950352648123 1.76 37.6
65410310307858 1.12 16.56
65410310307849 1.12 16.56
800W0346263038 0.8 11.81
Total: 11 67.02 195.01

Here, In my ES query iam trying to avoid terms & cardinality because both have limitations when it comes to large dataset.Kindly let me know the best way to implement it. Thanks in advance.

2

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.