N1QL vs ElasticSearch Join

Other
1

Hi @davido,

I need your help, Would you give me a hand with this?

My documents are:

Iwp::1::Porcentaje::Period::1
{
  "id": null,
  "period": 1,
  "type": "IwpCumulative",
  "category": "Porcentaje",
  "sumEarn": 0,
  "sumActual": 0.2248520710059172,
  "sumForecast": 0,
  "sumPlanned": 0,
  "sumValue": 0,
  "parent": "Iwp::1"
}

Iwp::1
{
  "name": "Iwp 1",
  "description": "Iwp 1 Description",
  "manyPeriods": 50,
  "type": "Iwp",
  "countCC": 0,
  "costCode": [
    "CostCode::3",
    "CostCode::4"
  ],
  "iwpCumulatives": [
    "Iwp::1::Porcentaje::Period::1",
     .......
    "Iwp::1::Porcentaje::Period::50",
    "Iwp::1::Qty::Period::1",
........
    "Iwp::1::Qty::Period::50",
  ]
}

How I Could do this query at ElasticSearch?

N1QL:

select 
t.category,
t.period,
sum(t.sumActual) 
from 
default as q 
inner join default as p on keys q.parent 
inner join default as t on keys p.iwpCumulatives 
where
q.type = 'IwpCumulative' 
and q.period = 50
and q.sumActual > 0 
and q.category = 'Porcentaje' 
group by t.category,t.period
order by t.period,t.category;

I have this querys at ElasticSearch:

{
    "query":{
    "filtered":{
           "query":{
               "bool":{
                    "must":[
                        {"term":{"period":"5"}},
                        {"term":{"type":"iwpcumulative"}},
                        {"range":{"sumActual":{"gt":"0"}}},
                        {"term":{"category":"porcentaje"}}
                        ]
                }
            }
    }
    }
}

and this:

{
   "size":0,
   "aggs":{
      "group_by_state":{
         "terms":{
            "field":"category"
         },
         "aggs":{
            "costars":{
               "terms":{
                  "field":"period"
               },
               "aggs":{
                  "Suma":{
                     "sum":{
                        "field":"earn"
                     }
                  }
               }
            }
         }
      }
   }
}

Now, I need to use the first result with their Id so I will to use at the second query.

Inside the document, the iwpCumulatives array was save this: Is it correct? How I could set analyzer to the iwpCumulatives field?

https://mega.nz/#!BxYFUTxB!oYESUvcFldg6e6mi2x75rKpkjqYCVgN2mz2uYLS2QBA

Thanks in advance.