Term aggregation ordering within a pivot transform

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?

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!

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" } }
                    }
                }
            }
        }
    }
}

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!

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!