# Merge search results into single document?

**URL:** <https://discuss.elastic.co/t/merge-search-results-into-single-document/196238>\
**Category:** Elasticsearch\
**Created:** [August 22, 2019, 5:54am UTC](https://discuss.elastic.co/t/merge-search-results-into-single-document/196238 "2019-08-22T05:54:02Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![Sjaak01](https://avatars.discourse-cdn.com/v4/letter/s/73ab20/32.png) [@Sjaak01](https://discuss.elastic.co/u/Sjaak01)\
**Post date:** [August 22, 2019, 5:54am UTC](https://discuss.elastic.co/t/merge-search-results-into-single-document/196238/1 "2019-08-22T05:54:02Z")

</div>

Hi,

I have data the looks like this:

```
{
"name":"John"
"limit":"100"
}

{
"name":"John"
"Spent":150"
}

{
"name":"Ray"
"limit":"200"
}

{
"name":"Ray"
"Spent":250"
}

```

But those are four separate documents. Is it possible to merge documents where the `name` is the same into a single document to create something like this?

```
{
"name":"John"
"limit":"100"
"Spent":150"
}

```

I want to pipe the output to Logstash and perform an action based on a comparison between `limit` and `spent` so the data will have to be in the same document.

---

<div class="post-metadata">

**Author:** ![abdon](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/abdon/32/9195_2.png) [@abdon](https://discuss.elastic.co/u/abdon)\
**Post date:** [August 22, 2019, 2:46pm UTC](https://discuss.elastic.co/t/merge-search-results-into-single-document/196238/2 "2019-08-22T14:46:09Z")

</div>

There is a brand new feature in Elasticsearch (introduced in version 7.2) called "[Data frame transforms](https://www.elastic.co/guide/en/elastic-stack-overview/current/ml-dataframes.html)" that will allow you to do this. With data frame transforms you can write the results of an aggregation to another index. In your case you could aggregate the values of `limit` and `Spent`, and group the results by `name`.

The [docs](https://www.elastic.co/guide/en/elastic-stack-overview/current/ml-dataframes.html) are quite extensive, with some great [examples](https://www.elastic.co/guide/en/elastic-stack-overview/current/dataframe-examples.html), but let me walk you through an example.

Let's say you had indexed your data like this:

```auto
PUT my_index
{
  "mappings": {
    "properties": {
      "name": {
        "type": "keyword"
      },
      "limit": {
        "type": "long"
      },
      "Spent": {
        "type": "long"
      }
    }
  }
}

PUT my_index/_doc/1
{
  "name": "John",
  "limit": 100
}

PUT my_index/_doc/2
{
  "name": "John",
  "Spent": 150
}

PUT my_index/_doc/3
{
  "name": "Ray",
  "limit": 200
}

PUT my_index/_doc/4
{
  "name": "Ray",
  "Spent": 250
}

```

You can define a transform that groups your original data by `name` and writes the results to a new index `my_index2`. The values of `limit` and `Spent` will be the sum of their original values:

```auto
PUT _data_frame/transforms/my_transform
{
  "source": {
    "index": "my_index"
  },
  "pivot": {
    "group_by": {
      "name": {
        "terms": {
          "field": "name"
        }
      }
    },
    "aggregations": {
      "limit": {
        "sum": {
          "field": "limit"
        }
      },
      "Spent": {
        "sum": {
          "field": "Spent"
        }
      }
    }
  },
  "dest": {
    "index": "my_index2"
  }
}

```

Next, you can start the transform :

```auto
POST _data_frame/transforms/my_transform/_start

```

You can now check whether the data in `my_index2` is in your desired format:

```auto
GET my_index2/_search

```

Resulting in:

```auto
    "hits" : [
      {
        "_index" : "my_index2",
        "_type" : "_doc",
        "_id" : "SpghpfNeEBZG2jy3kqf61UMAAAAAAAAA",
        "_score" : 1.0,
        "_source" : {
          "name" : "John",
          "limit" : 100.0,
          "Spent" : 150.0
        }
      },
      {
        "_index" : "my_index2",
        "_type" : "_doc",
        "_id" : "UtFxVzAGuj71aOPpxpuKJWsAAAAAAAAA",
        "_score" : 1.0,
        "_source" : {
          "name" : "Ray",
          "limit" : 200.0,
          "Spent" : 250.0
        }
      }
    ]

```

---

<div class="post-metadata">

**Author:** ![Sjaak01](https://avatars.discourse-cdn.com/v4/letter/s/73ab20/32.png) [@Sjaak01](https://discuss.elastic.co/u/Sjaak01)\
**Post date:** [August 23, 2019, 5:29am UTC](https://discuss.elastic.co/t/merge-search-results-into-single-document/196238/3 "2019-08-23T05:29:05Z")

</div>

@abdon thank you for the very detailed explanation. Looks like that might work, I will try it out.

---

<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:** [September 20, 2019, 5:36am UTC](https://discuss.elastic.co/t/merge-search-results-into-single-document/196238/4 "2019-09-20T05:36:17Z")

</div>

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