Lens or Aggs query to group by having count > X

Elastic Stack Kibana
1

I'm drawing a blank on how to accomplish this in Kibana or Elastic.
Prefer to have this in a Lens visual so I can add to a dashboard, but minimally I can do it with an aggregate API query in Kibana dev tools.

I'm dumbing down my documents to something super simple for the example.

[
  {
    "parentID": 123,
    "score": 65
  },
  {
    "parentID": 123,
    "score": 45
  },
  {
    "parentID": 123,
    "score": 94
  },
  {
    "parentID": 456,
    "score": 87
  },
  {
    "parentID": 456,
    "score": 67
  }
]

What I need to see is group by parentID and all the counts, then only see those with counts > 2.

In SQL it would be

select parentID, count(*) 
from docs
group by parentID
having count(*) > 2

I think I'm close with this aggs query, but I don't know how to limit to only those with count > 2

GET test-data/_search
{
  "size": 0,
  "aggs" : {
    "parents" : {
      "terms" : {
        "field" : "parentID",
        "size": 400
      }
    }
  }
}

Lastly, is there any way to do this same thing in a Lens visualization?
I struggle to translate these API calls to something in Lens.

Thank you!

2

@qd What version are you on?

3

version 8.11

4

You should try ESQL :slight_smile: Pretty sure you can do that.

I do think you can do that with lens ... I would have to try...

So in the above example only parent ID and account for number 123 would be returned. Is that correct?

5

Ooooh - ES|QL - good point. I forgot about that and haven't used it yet. Will try. Thank you.

Yes that's correct. I would like the parentID and the COUNT of records for that value (in this case 3).
When you extend this across a large data set (and lots more data in each document) I'm still just wanting the parentID values that have more than 2 documents.

6

Just posting the solution to this in case anyone else needs it.

The ES|QL suggestion was great - thank you @stephenb.

from docs
| stats numResults = COUNT(*) by parentID

I kind of hacked my way through a Lens visual for this. Only shows the last bunch of results so it's not a complete solution. I may be missing some other visual that would show it better.

  • vertical bar stacked
  • horizontal axis: parentID
    • number values: big enough to show all the groups (my real data has 500+ of these)
    • rank ascending
    • group remaining as other is OFF
  • vertical axis: count
  • breakdown by: parentID

Some drawbacks of this visual:

  • only shows highest or lowest N groups depending on rank direction
  • have to guess at the number of groups to show, so might not see all the results

Thanks again for the help

1 Like
7

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