Agg count only once per parent in nested objects

Elastic Stack Elasticsearch
1

Hi there,

I have a mapping with nested objects, that we use to combine variants to a main product. we do nested aggregations on the variants to find values of certain keys.

The agg counts are incremented by one, for each variant that hits the value.
I would like to get only one count for the agg for each main product.

here you find an example of our mapping:

    {
"products": {
    "mappings": {
        "dynamic": "false",
        "properties": {
            "product_id": {
                "type": "keyword"
            },
            "variants": {
                "type": "nested",
                "properties": {
                    "brand": {
                        "type": "keyword"
                    },
                    "color": {
                        "type": "keyword"
                    }
                }
            }
        }
    }
}

this is an example of our product-structure:

{
        "_index": "products",
        "_type": "_doc",
        "_id": "5e42f759de235d5b42e6e35524141bd2cfcbc5d0",
        "_score": 1.0,
        "_source": {
            "product_id": "5e42f759de235d5b42e6e35524141bd2cfcbc5d0",
            "variants": [
                {

                    "color": [
                        "Black"
                    ]
                    "brand": "Fackelmann"
                },
                {
                    "color": [],
                    "brand": "Fackelmann"
                },
                {
                    "color": [],
                    "brand": "Fackelmann",
                },
                {
                    "color": [],
                    "brand": "Fackelmann"
                }
            ]
        }
    },
    {
        "_index": "products",
        "_type": "_doc",
        "_id": "768af68018654b04f20d32003348ee9bd81d9f65",
        "_score": 1.0,
        "_source": {
            "product_id": "768af68018654b04f20d32003348ee9bd81d9f65",
            "variants": [
                {

                    "color": [
                        "Grey"
                    ]
                    "brand": "Fackelmann"
                },
                {
                    "color": [],
                    "brand": "Fackelmann"
                },
                {
                    "color": [],
                    "brand": "Fackelmann",
                },
                {
                    "color": [],
                    "brand": "IKEA"
                }
            ]
        }
    }

in this example we get those counts for aggs:

brand:Fackelmann[7]
brand:IKEA[1]
color:Grey[1]
color:Black[1]

What i expect to get is:

brand:Fackelmann[2]
brand:IKEA[1]
color:Grey[1]
color:Black[1]

we already tried reverse nested aggregations but without luck :frowning:

2

How about storing the distinct set of brands in an own keyword field outside of the variants array like "brands" : [ "Fackelmann", "IKEA" ] and then running a terms aggregation on that one?

You could add an indexing pipeline to do that.

3

I suppose, reverse nested aggregation is the only an option.

4

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