# Pivot transform copying values

**URL:** <https://discuss.elastic.co/t/pivot-transform-copying-values/297919>\
**Category:** Elasticsearch\
**Tags:** transforms\
**Created:** [February 22, 2022, 3:14pm UTC](https://discuss.elastic.co/t/pivot-transform-copying-values/297919 "2022-02-22T15:14:36Z")\
**Posts on this page:** 12\
**Page:** 1

<div class="post-metadata">

**Author:** ![iamtheschmitzer](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/iamtheschmitzer/32/96988_2.png) [@iamtheschmitzer](https://discuss.elastic.co/u/iamtheschmitzer)\
**Post date:** [February 22, 2022, 3:14pm UTC](https://discuss.elastic.co/t/pivot-transform-copying-values/297919/1 "2022-02-22T15:14:36Z")

</div>

I'm attempting my first pivot transform with a goal of calculating duration between timestamps. In the `group_by` clause, it results in a two documents, each with a timestamp. I have been able to calculate the duration using a `min` and `max` aggregation, along with a `bucket_script` like this:

```auto
  "pivot": {
    "group_by": { 
      "linkId": { "terms": { "field": "linkId" }},
      "string_metadata_hash": { "terms": { "field": "string_metadata_hash" }}
    },
    "aggregations": {
      "start": {
        "min": {
          "field": "timestamp"
        }
      },
      "stop": {
        "max": {
          "field": "timestamp"
        }
      },
      "duration_sec": {
        "bucket_script": {
          "buckets_path": {
            "start": "start.value",
            "stop": "stop.value"
          },
          "script": "return (params.stop - params.start)/1000;"
        }
      }
    }
  }
}

```

The result documents contain duration calculation I want, and the aggregation fields, but nothing else:

```auto
 "preview" : [
    {
      "linkId" : "<uuid>",
      "string_metadata_hash" : <hash value>",
      "stop" : "2022-02-18T20:21:32.842Z",
      "start" : "2022-02-18T20:19:39.934Z",
      "duration_sec" : 112.908
    },
   ...
  ]

```

There are a number of other fields, nested within a sub-object `metadata` for example `foo` a string and `bar` an integer, and several others. How can I include them in the result? Note that they are guaranteed to be identical between the two documents I just need any copy of the values.

Do I need a second transformation, combining this destination index and the original source? Or are there aggregations I can use? Ideally it can copy the whole `metadata` without enumerating the individual fields. Or must I enumerate these fields in the `group_by` clause?

---

<div class="post-metadata">

**Author:** ![sophie\_chang](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/sophie_chang/32/18008_2.png) [@sophie\_chang](https://discuss.elastic.co/u/sophie_chang)\
**Post date:** [February 22, 2022, 4:20pm UTC](https://discuss.elastic.co/t/pivot-transform-copying-values/297919/2 "2022-02-22T16:20:15Z")

</div>

For the `pivot` function, the destination index output fields must either be specified in the `group_by` section or the `aggregations` section. It would be best to experiment against your local data as to which is most appropriate.

If using aggregations, then the Top metrics aggregation is likely what you'll need. [Create transform API | Elasticsearch Guide [8.0] | Elastic](https://www.elastic.co/guide/en/elasticsearch/reference/current/put-transform.html)

Hope this helps

---

<div class="post-metadata">

**Author:** ![Tomo\_M](https://avatars.discourse-cdn.com/v4/letter/t/848f3c/32.png) [@Tomo\_M](https://discuss.elastic.co/u/Tomo_M)\
**Post date:** [February 22, 2022, 5:40pm UTC](https://discuss.elastic.co/t/pivot-transform-copying-values/297919/3 "2022-02-22T17:40:34Z")

</div>

I suppose adding Scripted metric to the transform is the possible way. You may create custom metric just retrieving the first document and discard the rest.  
If you only contain numeric fields, Top Metrics aggregation could be another option while you need list up all fields needed.

---

<div class="post-metadata">

**Author:** ![Tomo\_M](https://avatars.discourse-cdn.com/v4/letter/t/848f3c/32.png) [@Tomo\_M](https://discuss.elastic.co/u/Tomo_M)\
**Post date:** [February 22, 2022, 5:54pm UTC](https://discuss.elastic.co/t/pivot-transform-copying-values/297919/4 "2022-02-22T17:54:29Z")

</div>

Something like this:

```auto
"aggs":{
    "one_document":{
      "scripted_metric": {
        "init_script": "state.doc = new HashMap()",
        "map_script": "if (state.doc.isEmpty()){state.doc = new HashMap(params['_source'])}",
        "combine_script": "return state.doc",
        "reduce_script": "return states[0]"
      }
    }
  }

```

---

<div class="post-metadata">

**Author:** ![sophie\_chang](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/sophie_chang/32/18008_2.png) [@sophie\_chang](https://discuss.elastic.co/u/sophie_chang)\
**Post date:** [February 22, 2022, 6:05pm UTC](https://discuss.elastic.co/t/pivot-transform-copying-values/297919/5 "2022-02-22T18:05:40Z")

</div>

Top metrics does actually support keywords (naming is hard) and I suspect it is more performant than a scripted metric -- but it depends on your data and both should work.

---

<div class="post-metadata">

**Author:** ![Tomo\_M](https://avatars.discourse-cdn.com/v4/letter/t/848f3c/32.png) [@Tomo\_M](https://discuss.elastic.co/u/Tomo_M)\
**Post date:** [February 22, 2022, 6:28pm UTC](https://discuss.elastic.co/t/pivot-transform-copying-values/297919/6 "2022-02-22T18:28:41Z")

</div>

Thank you for correcting my imprecise post. Top metrics could also be used for keywords fields.

@iamtheschmitzer  
Supported field types for Top metrics aggregation is discribed [here](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-metrics-top-metrics.html#_metrics).

---

<div class="post-metadata">

**Author:** ![iamtheschmitzer](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/iamtheschmitzer/32/96988_2.png) [@iamtheschmitzer](https://discuss.elastic.co/u/iamtheschmitzer)\
**Post date:** [February 22, 2022, 6:54pm UTC](https://discuss.elastic.co/t/pivot-transform-copying-values/297919/7 "2022-02-22T18:54:54Z")

</div>

Thank you this did the job, but I added `.metadata` to the `map_script`

---

<div class="post-metadata">

**Author:** ![iamtheschmitzer](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/iamtheschmitzer/32/96988_2.png) [@iamtheschmitzer](https://discuss.elastic.co/u/iamtheschmitzer)\
**Post date:** [February 22, 2022, 6:56pm UTC](https://discuss.elastic.co/t/pivot-transform-copying-values/297919/8 "2022-02-22T18:56:15Z")

</div>

I will look at that next, thank you

---

<div class="post-metadata">

**Author:** ![iamtheschmitzer](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/iamtheschmitzer/32/96988_2.png) [@iamtheschmitzer](https://discuss.elastic.co/u/iamtheschmitzer)\
**Post date:** [February 22, 2022, 7:33pm UTC](https://discuss.elastic.co/t/pivot-transform-copying-values/297919/9 "2022-02-22T19:33:52Z")

</div>

> [@sophie\_chang](#):
>
> Top metrics does actually support keywords (naming is hard) and I suspect it is more performant than a scripted metric -- but it depends on your data and both should work.

I attempted `top_metrics` but it gave me null results. Not sure if this is related, but the context-sensitive autocomplete did not provide `top_metrics` as a suggestion, only `top_hits`. Not sure if this is due to it being within a pivot transform or not.

---

<div class="post-metadata">

**Author:** ![Tomo\_M](https://avatars.discourse-cdn.com/v4/letter/t/848f3c/32.png) [@Tomo\_M](https://discuss.elastic.co/u/Tomo_M)\
**Post date:** [February 23, 2022, 1:05am UTC](https://discuss.elastic.co/t/pivot-transform-copying-values/297919/10 "2022-02-23T01:05:18Z")

</div>

I suppose top hits aggregation is not supported by pivot transform. Could you share the whole settings of that transform?

---

<div class="post-metadata">

**Author:** ![iamtheschmitzer](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/iamtheschmitzer/32/96988_2.png) [@iamtheschmitzer](https://discuss.elastic.co/u/iamtheschmitzer)\
**Post date:** [February 23, 2022, 11:05pm UTC](https://discuss.elastic.co/t/pivot-transform-copying-values/297919/11 "2022-02-23T23:05:21Z")

</div>

Sure, appreciate the help

```auto
  "source": {
    "index": "source-name"
  },
  "dest" : { 
    "index" : "dest-name"
  },
  "pivot": {
    "group_by": { 
      "linkId": { "terms": { "field": "linkId" }},
      "string_metadata_hash": { "terms": { "field": "string_metadata_hash" }},
    },
    "aggregations": {
      "metadata": {
        "scripted_metric": {
          "init_script": "state.doc = new HashMap()",
          "map_script": "if (state.doc.isEmpty()){state.doc = new HashMap(params['_source'].metadata)}",
          "combine_script": "return state.doc",
          "reduce_script": "return states[0]"
        }
      },
      "start": {
        "min": {
          "field": "timestamp"
        }
      },
      "complete": {
        "max": {
          "field": "timestamp"
        }
      },
      "duration_sec": {
        "bucket_script": {
          "buckets_path": {
            "start": "start.value",
            "complete": "complete.value"
          },
          "script": "return (params.complete - params.start)/1000;"
        }
      }
    }
  },
   "sync": {
    "time": { 
      "field": "timestamp",
      "delay": "60s"
    }
  }

```

---

<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:** [March 23, 2022, 11:05pm UTC](https://discuss.elastic.co/t/pivot-transform-copying-values/297919/12 "2022-03-23T23:05:22Z")

</div>

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