Term aggregation ordering within a pivot transform

Elastic Stack Elasticsearch
,
1

I'm running a pivot transform in which I have a terms aggregation that I'm trying to order, roughly:

            "items": {
                "terms": { "field": "item_id", "order": { "value": "desc" } },
                "aggregations": {
                    "value": { "sum": { "field": "value_a" } }
                }

        }

When I run this aggregation in a normal query within a terms aggregation that's the equivalent of the pivot, it works fine.

When I run it inside the pivot transform, there is no apparent ordering to the results.

What gives? What am I doing wrong?

2

Hi! Are you trying to order by document count per term or by term value?
Would you be up for sharing the entire transform config? Thanks!

3

I'm trying to order the item aggregation by descending value rather than count

there's not much to the whole transform - the relevant portions are (in transform form, which produces unordered results):

{
    "source": { "index": [ "..." ] },
    "pivot": {
        "group_by": { "group_id": { "terms": { "field": "group_id" } } },
        "aggregations": {
            "items": {
                "terms": { "field": "item_id", "order": { "value": "desc" },
                "aggregations": {
                    "value": { "sum": { "field": "item_value" } }
                }
            }
        }
    }
}

in aggregation form (which produces ordered results):

{
    "aggregations": {
        "groups": {
            "terms": { "field": "group_id" },
            "aggregations": {
                "items": {
                    "terms": { "field": "item_id", "order": { "value": "desc" } },
                    "aggregations": {
                        "value": { "sum": { "field": "item_value" } }
                    }
                }
            }
        }
    }
}
4

First of all, it seems you need to use "_key" rather than "value" inside "order" stanza.
At least that's what the documentation for terms is saying.

After I replaced "value" with "_key" I was indeed able to reproduce the issue, i.e.: pivot did not sort the terms in descending order whereas _search request with aggregations did.

I'll try to debug the code after the weekend.
Thanks for reporting!

5

I was able to reproduce and debug the problem.
Created GH issue: [Transform] Terms ordering specified via `"order"` parameter is ignored. · Issue #104847 · elastic/elasticsearch · GitHub

Once again, thanks for reporting!

6

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