# Trying to mimic a where clause
**URL:** https://discuss.elastic.co/t/trying-to-mimic-a-where-clause/93773
**Category:** Elasticsearch
**Created:** [July 19, 2017, 1:31pm UTC](https://discuss.elastic.co/t/trying-to-mimic-a-where-clause/93773 "2017-07-19T13:31:44Z")
**Posts on this page:** 10
**Page:** 1
### Author:  [@boldfish](https://discuss.elastic.co/u/boldfish)
#### Post date: [July 19, 2017, 1:31pm UTC](https://discuss.elastic.co/t/trying-to-mimic-a-where-clause/93773/1 "2017-07-19T13:31:44Z")
Hi say I have two fields "catA" and "catB" respectively indexed. A has possible values ["1" ,"2", "n"] and B has possible values ["i" ,"ii" ,-"n"] within the document set. I want to be able to know for what values of catB , the count of distinct catA values is \>1
any help would be graciously appreciated.
Thanks in advance
---
### Author:  [@PaulieMac](https://discuss.elastic.co/u/PaulieMac)
#### Post date: [July 19, 2017, 2:12pm UTC](https://discuss.elastic.co/t/trying-to-mimic-a-where-clause/93773/2 "2017-07-19T14:12:28Z")
Hello mate....
What you are looking for will be a combination of Aggregations, like this (in pseudocode, I don't have an elastic instance handy sorry):
A terms aggregation on "catB" to 'group by' those values, with a sub-aggregation of type 'value\_count' to count the 'catA' values within the enclosing catB.
Then, as a sibling agg to the 'terms' you'll want a 'bucket\_selector' to filter the results...
So at a rough stab, I _think_ it would be something like this... Apologies if it doesn't run, just doing it off the top of my head!
```auto
"aggs" : {
"summary" : {
"terms" : { "field" : "catB", "size" : "200"},
"aggs": {
"catAcount" : { "value_count" : { "field" : "catA" } }
}
},
"having" : "bucket_selector": {
"buckets_path": {
"catAcounts": "summary>catAcount"
},
"script": "params.catAcounts > 1"
}
}
```
---
### Author:  [@boldfish](https://discuss.elastic.co/u/boldfish)
#### Post date: [July 20, 2017, 9:11am UTC](https://discuss.elastic.co/t/trying-to-mimic-a-where-clause/93773/3 "2017-07-20T09:11:13Z")
Thanks so much for the answer . Its almost there.. I can get this to work
"aggs" : {
"summary" : {
"terms" : { "field" : "catB", "size" : "200"},
"aggs": {
"catAcount" : { "cardinality" : { "field" : "catA" } }
}
}}}
Which changes the "value\_count" to "cardinality" this will tell me the number of values for catA in each document that contains the CatB field. I could not get the bucket aggregator to work tho. Something to do with the brackets as I am getting errors about sibling queries not allowed etc. If anyone has anything to offer on getting the above working with the bucket aggregator so as to get a list of the catA values it would be great... either way thanks for the help. Im halfway there 🙂
---
### Author:  [@PaulieMac](https://discuss.elastic.co/u/PaulieMac)
#### Post date: [July 20, 2017, 9:22am UTC](https://discuss.elastic.co/t/trying-to-mimic-a-where-clause/93773/4 "2017-07-20T09:22:40Z")
Hello mate -sorry my first attempt didn't work! Looking at it, perhaps I put the 'bucket\_selector' in the wrong spot? What if it is shifted to be contained by the 'terms' structure - i.e. like this:
(I should note here, I haven't used the bucket selector before, but I do tend to use other pipeline aggs a fair bit, so I think it should work ok with the right tweak 🙂
"aggs" : {
"summary" : {
"terms" : { "field" : "catB", "size" : "200"},
"aggs": {
"catAcount" : { "value\_count" : { "field" : "catA" } },
"having" : "bucket\_selector": {
"buckets\_path": {
"catAcounts": "catAcount"
},
"script": "params.catAcounts \> 1"
}
}
}
}
---
### Author:  [@boldfish](https://discuss.elastic.co/u/boldfish)
#### Post date: [July 20, 2017, 9:43am UTC](https://discuss.elastic.co/t/trying-to-mimic-a-where-clause/93773/5 "2017-07-20T09:43:08Z")
"error": {
"root\_cause": [
{
"type": "parsing\_exception",
"reason": "Aggregation definition for [having starts with a [VALUE\_STRING], expected a [START\_OBJECT].",
Thanks for trying again, alas it doesnt work though. I have twiddled with it little myself but no luck so far.. thanks again.
---
### Author:  [@boldfish](https://discuss.elastic.co/u/boldfish)
#### Post date: [July 20, 2017, 9:48am UTC](https://discuss.elastic.co/t/trying-to-mimic-a-where-clause/93773/6 "2017-07-20T09:48:09Z")
actually i got this error now :{
"error": {
"root\_cause": [],
"type": "search\_phase\_execution\_exception",
"reason": "",
"phase": "fetch",
"grouped": true,
"failed\_shards": [],
"caused\_by": {
"type": "script\_exception",
"reason": "runtime error",
"script\_stack": [
"params.catAcount \> 1",
" ^---- HERE"
],
"script": "params.catAcount \> 1",
"lang": "painless",
"caused\_by": {
"type": "null\_pointer\_exception",
"reason": null
}
}
},
"status": 503
}
---
### Author:  [@boldfish](https://discuss.elastic.co/u/boldfish)
#### Post date: [July 20, 2017, 10:05am UTC](https://discuss.elastic.co/t/trying-to-mimic-a-where-clause/93773/7 "2017-07-20T10:05:14Z")
hey the error was I my end. Thanks It worked Im marking this as solved . All the very best and thanks again for the help.
---
### Author:  [@PaulieMac](https://discuss.elastic.co/u/PaulieMac)
#### Post date: [July 20, 2017, 11:21am UTC](https://discuss.elastic.co/t/trying-to-mimic-a-where-clause/93773/8 "2017-07-20T11:21:41Z")
Ha ha sorry been out grabbing a coffee - you got it working though? Sorry if my syntax was off... Getting these aggs working right can be finicky - I usually seem to need a few goes to get it right 🙂
---
### Author:  [@boldfish](https://discuss.elastic.co/u/boldfish)
#### Post date: [July 24, 2017, 10:12am UTC](https://discuss.elastic.co/t/trying-to-mimic-a-where-clause/93773/9 "2017-07-24T10:12:29Z")
I know this query is marked complete but I don have an additional related question which I think would be misplaced in a new post. So here goes. If the final code is like this:
"summary" : {
"terms" : { "field" : "catB", "size" : "200"},
"aggs": {
"catAcount" : { "value\_count" : { "field" : "catA" } },
"having" : "bucket\_selector": {
"buckets\_path": {
"catAcounts": "catAcount"
},
"script": "params.catAcounts \> 1"
}
}
}
}
how do I do a sub aggregation on the buckets? as in where do I nest the sub aggregation ? I found the documentation particularly vague and nebulous on this matter... anyway if you have any idea it would be great. Also thanks a million for the help.
---
### Author:  [@system](https://discuss.elastic.co/u/system)
#### Post date: [August 21, 2017, 10:12am UTC](https://discuss.elastic.co/t/trying-to-mimic-a-where-clause/93773/10 "2017-08-21T10:12:44Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.