Group by terms and get count of nested array property?

Elastic Stack Elasticsearch
1

I would like to get the count from a document series where an array item matches some value.

I have documents like these:

{
    "Name": "jason",
    "Todos": [{
        "State": "COMPLETED"
        "Timer": 10
        },{
        "State": "PENDING"
        "Timer": 5
    }]
}

{
    "Name": "jason",
    "Todos": [{
        "State": "COMPLETED"
        "Timer": 5
        },{
        "State": "PENDING"
        "Timer": 2
    }]
}

{
    "Name": "martin",
    "Todos": [{
        "State": "COMPLETED"
        "Timer": 15
        },{
        "State": "PENDING"
        "Timer": 10
    }]
}

I would like to count how many documents I have where they have any Todos with COMPLETED State. And group by Name.

So from the above I would need to get: jason: 2 martin: 1

Usually I do this with a term aggregation for the Name, and an other sub aggregation for other items:

"aggs": {
    "statistics": {
        "terms": {
            "field": "Name"
        },
        "aggs": {
            "test": {
                "filter": {
                    "bool": {
                        "must": [{
                                "match_phrase": {
                                    "SomeProperty.keyword": {
                                        "query": "THEVALUE"
                                    }
                                }
                            }
                        ]
                    }
                },

But not sure how to do this here as I have items in an array.

2

What type of field is Todo, an object type or nested?

3

State has the following mapping:

"State": {
	"type": "text",
	"fields": {
		"keyword": {
			"type": "keyword",
			"ignore_above": 256
		}
	}
},

But I realized I made a mistake and put a filter into the query (not in the example) which resulted 0 result, and actually the above mentioned query works when I fix my time range filer. But according to what I have found online it should not work, because I should use nested type fields. I have a feeling it works only because I also group the aggregation by Name using the terms aggregation.

4

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