# Transforms: How to aggregate multiple events into one event based on shared field?

**URL:** <https://discuss.elastic.co/t/transforms-how-to-aggregate-multiple-events-into-one-event-based-on-shared-field/345055>\
**Category:** Elasticsearch\
**Tags:** transforms\
**Created:** [October 15, 2023, 6:28pm UTC](https://discuss.elastic.co/t/transforms-how-to-aggregate-multiple-events-into-one-event-based-on-shared-field/345055 "2023-10-15T18:28:57Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![mohsin106](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mohsin106/32/65203_2.png) [@mohsin106](https://discuss.elastic.co/u/mohsin106)\
**Post date:** [October 15, 2023, 6:28pm UTC](https://discuss.elastic.co/t/transforms-how-to-aggregate-multiple-events-into-one-event-based-on-shared-field/345055/1 "2023-10-15T18:28:57Z")

</div>

Hi, I have the following events writing to the same index in ES:

```auto
{
  "@timestamp": "2023-10-15T17:06:05.137039490Z",
  "ssn": null,
  "z4date": "1697207822",
  "criminalnotes": null,
  "reason": null,
  "notes": null,
  "lname": "LASTNAME",
  "fullnotes": "",
  "icn": null,
  "zid": 391695,
  "fname": "FIRSTNAME",
  "mname": null,
  "status": "new",
  "workingdate": null,
  "criminal": "",
  "email": null,
  "nname": null,
  "fbname": null,
  "@version": "1",
  "city": "CITY"
}

```

```auto
{
  "@timestamp": "2023-10-15T17:05:29.274347904Z",
  "zid": 391695,
  "skill": "Engineer",
  "@version": "1"
}

```

```auto
{
  "@timestamp": "2023-10-15T17:05:29.274347904Z",
  "zid": 391695,
  "skill": "HelpDesk",
  "@version": "1"
}

```

`zid` is the unique key between all events. How can I use transforms to aggregate all the fields for a specific `zid`? I'm hoping to have an output similar to this:

```auto
{
  "@timestamp": "2023-10-15T17:06:05.137039490Z",
  "ssn": null,
  "z4date": "1697207822",
  "criminalnotes": null,
  "reason": null,
  "notes": null,
  "lname": "LASTNAME",
  "fullnotes": "",
  "icn": null,
  "zid": 391695,
  "fname": "FIRSTNAME",
  "mname": null,
  "status": "new",
  "workingdate": null,
  "criminal": "",
  "email": null,
  "nname": null,
  "fbname": null,
  "@version": "1",
  "city": "CITY",
  "skill": ["Engineer","HelpDesk"]
}

```

Timestamp aggregation can be set to `1d`.  
I tried creating this transform but its not showing me the output that I want to see:

```auto
POST _transform/_preview
{
  "source": {
    "index": [
      "z4_db*"
    ]
  },
  "pivot": {
    "group_by": {
      "zid": {
        "terms": {
          "field": "zid"
        }
      },
      "@timestamp": {
        "date_histogram": {
          "field": "@timestamp",
          "calendar_interval": "1d"
        }
      },
      "fname": {
        "terms": {
          "field": "fname"
        }
      },
      "lname": {
        "terms": {
          "field": "lname"
        }
      }
    },
    "aggregations": {
      "skill.terms": {
        "filter": {
          "exists": {
            "field": "skill"
          }
        },
        "aggs": {
          "skill.terms": {
            "terms": {
              "field": "skill",
              "size": 10
            }
          }
        }
      }
    }
  }
}

```

In the preview output I'm seeing column headers `@timestamp`, `fname`, `lname`, and `zid`. I'm not seeing a `skill` column header.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [November 12, 2023, 6:29pm UTC](https://discuss.elastic.co/t/transforms-how-to-aggregate-multiple-events-into-one-event-based-on-shared-field/345055/2 "2023-11-12T18:29:10Z")

</div>

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