# Aggregate multiple records to single chart

**URL:** <https://discuss.elastic.co/t/aggregate-multiple-records-to-single-chart/86561>\
**Category:** Kibana\
**Created:** [May 21, 2017, 9:56am UTC](https://discuss.elastic.co/t/aggregate-multiple-records-to-single-chart/86561 "2017-05-21T09:56:14Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![avivc](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/avivc/32/16501_2.png) [@avivc](https://discuss.elastic.co/u/avivc)\
**Post date:** [May 21, 2017, 9:56am UTC](https://discuss.elastic.co/t/aggregate-multiple-records-to-single-chart/86561/1 "2017-05-21T09:56:14Z")

</div>

Hi,  
The API I'm using holds nested json data (i.e, base-derived nodes relation)  
When index it in ES, it's flat-json index  
What I'm trying to do simply is to aggregate base object with derived object into single chart

E.g.  
Base object - **x**  
derived object - **y (nested in object x)**  
`Display in bar chart all records for objects as single column. Not multiple column`  
I want to use Lucene's syntax in my dashboard

Any idea?  
10x in advance

---

<div class="post-metadata">

**Author:** ![LeeDr](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/leedr/32/9289_2.png) [@LeeDr](https://discuss.elastic.co/u/LeeDr)\
**Post date:** [May 26, 2017, 2:24pm UTC](https://discuss.elastic.co/t/aggregate-multiple-records-to-single-chart/86561/2 "2017-05-26T14:24:36Z")

</div>

Hi Avivc,

Could you please post a very simple example data set here (just a couple of docs) in the form that can be pasted into the Kibana dev console so we could load it and try to create the chart you're looking for?

Thanks,  
Lee

---

<div class="post-metadata">

**Author:** ![avivc](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/avivc/32/16501_2.png) [@avivc](https://discuss.elastic.co/u/avivc)\
**Post date:** [May 28, 2017, 6:19am UTC](https://discuss.elastic.co/t/aggregate-multiple-records-to-single-chart/86561/3 "2017-05-28T06:19:53Z")

</div>

Hi @LeeDr ! Yes of course  
See the next:

 ![](https://us1.discourse-cdn.com/elastic/original/3X/b/2/b264cce6b024464cb98d2603618280ad7a00b386.png)

The 2nd bar in the base object and all other bars are the derived bars. **I want to display single bar**  
My syntax in the search bar is;

> Project.name: ("base object" OR "derived objects")

`ES + KIbana: 5.4.0 (maybe create scripted field? )`

Tnx !

---

<div class="post-metadata">

**Author:** ![LeeDr](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/leedr/32/9289_2.png) [@LeeDr](https://discuss.elastic.co/u/LeeDr)\
**Post date:** [May 30, 2017, 5:22pm UTC](https://discuss.elastic.co/t/aggregate-multiple-records-to-single-chart/86561/4 "2017-05-30T17:22:44Z")

</div>

Maybe this post will help you. In this example, the user has data that might be in 3 different fields but they want one field they can search on. So if you create a scripted field that gets either the `Project.name: base object` or `Project.name: dereved object` into one string field you can aggregate on that.

> [@Fields aggregation or field formatter?](https://discuss.elastic.co/t/fields-aggregation-or-field-formatter/87023/2):
>
> Hi Chris, I think a scripted field might help. Here's an experiment I did. I put this data using Kibana dev console. Each doc has a name but in a different field; PUT /discuss/test/1 { "date" : "2017-05-26T00:01:00", "name" : "Lee" } PUT /discuss/test/2 { "date" : "2017-05-26T00:02:00", "firstName" : "Bob" } PUT /discuss/test/3 { "date" : "2017-05-26T00:02:30", "fullName" : "Bart Simpson" } It looks like this in Discover; Obviously I can already sea…

---

<div class="post-metadata">

**Author:** ![avivc](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/avivc/32/16501_2.png) [@avivc](https://discuss.elastic.co/u/avivc)\
**Post date:** [June 1, 2017, 12:52pm UTC](https://discuss.elastic.co/t/aggregate-multiple-records-to-single-chart/86561/5 "2017-06-01T12:52:44Z")

</div>

Hi @LeeDr  
I'm trying to create query to aggregate data (bucket nested aggregation) to put in the scripted field but without success.  
The syntax in **painless**

Any suggestions?

---

<div class="post-metadata">

**Author:** ![LeeDr](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/leedr/32/9289_2.png) [@LeeDr](https://discuss.elastic.co/u/LeeDr)\
**Post date:** [June 1, 2017, 3:28pm UTC](https://discuss.elastic.co/t/aggregate-multiple-records-to-single-chart/86561/6 "2017-06-01T15:28:02Z")

</div>

Hi Avivc,

If I understand you correctly, you can't do that. Scripted fields only access data in individual documents, not aggregations across multiple documents.

Regards,  
Lee

---

<div class="post-metadata">

**Author:** ![avivc](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/avivc/32/16501_2.png) [@avivc](https://discuss.elastic.co/u/avivc)\
**Post date:** [June 3, 2017, 4:16pm UTC](https://discuss.elastic.co/t/aggregate-multiple-records-to-single-chart/86561/7 "2017-06-03T16:16:32Z")

</div>

Tnx @LeeDr  
What I'm trying is to create is the next script (using Dev Tools) :

> GET kpi/Defect/\_search  
> {  
> "aggs": {  
> "NAME": {  
> "AGG\_TYPE": {}  
> }  
> }  
> }

I want to create this script to aggregate data for the base object and it's derived (by key). With that, I want create my scripted field.  
What do you mean by **individual documents**?

---

<div class="post-metadata">

**Author:** ![avivc](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/avivc/32/16501_2.png) [@avivc](https://discuss.elastic.co/u/avivc)\
**Post date:** [June 5, 2017, 11:27am UTC](https://discuss.elastic.co/t/aggregate-multiple-records-to-single-chart/86561/8 "2017-06-05T11:27:25Z")

</div>

Hi @LeeDr  
If it's not possible via scripted fields, are there other solutions?  
Does **value count aggregation** can be efficient in this case?

---

<div class="post-metadata">

**Author:** ![LeeDr](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/leedr/32/9289_2.png) [@LeeDr](https://discuss.elastic.co/u/LeeDr)\
**Post date:** [June 5, 2017, 1:01pm UTC](https://discuss.elastic.co/t/aggregate-multiple-records-to-single-chart/86561/9 "2017-06-05T13:01:23Z")

</div>

Hi Avivc,  
I'm sorry but I don't think I can help without some kind of example data we can talk about. Can you post some code we could paste into the dev console to create a few documents that would represent your data to use as an example?

Thanks,  
Lee

---

<div class="post-metadata">

**Author:** ![avivc](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/avivc/32/16501_2.png) [@avivc](https://discuss.elastic.co/u/avivc)\
**Post date:** [June 5, 2017, 1:13pm UTC](https://discuss.elastic.co/t/aggregate-multiple-records-to-single-chart/86561/10 "2017-06-05T13:13:55Z")

</div>

Hi @LeeDr  
I have a field in my index pattern named : **Project.\_refObjectName**  
I wish to aggregate various values refer to this field

**E.g.:**  
Values name: field1, field2, field3

I want to aggregate this values like described [here](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-metrics-valuecount-aggregation.html#search-aggregations-metrics-valuecount-aggregation)  
Something like this:

> POST /myIndex/\_search?size=0  
> {  
> "aggs" : {  
> "grades\_count" : {  
> "value\_count" : {  
> "script" : {  
> "params" : {  
> "field" : "type"  
> }  
> }  
> }  
> }  
> }  
> }

I'm not sure how to set the aggregation I wish to have using this script

**Edit:**  
If I do the simple query:

> GET myIndex/\_search  
> {  
> "query": {  
> "match": {  
> "Project.\_refObjectName": {  
> "query": "myValue"  
> }  
> }  
> }  
> }

I get the total count for this specific value. All I want it to aggregate multiple values (or using wlid card if possible) and fetch the total count

**Is it enough data to move on ? is this code is sufficient?**

10x a lot !

---

<div class="post-metadata">

**Author:** ![LeeDr](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/leedr/32/9289_2.png) [@LeeDr](https://discuss.elastic.co/u/LeeDr)\
**Post date:** [June 6, 2017, 4:48pm UTC](https://discuss.elastic.co/t/aggregate-multiple-records-to-single-chart/86561/11 "2017-06-06T16:48:00Z")

</div>

Hi Avivc,

In the `Fields aggregation or field formatter?` link I posted above I included commands to `put` a couple of documents using the dev console, and then I used those documents to answer a question. Can you create a couple of sample documents like that and post it here and then what you're trying to aggregate out of that? I can't tell what you're data looks like from your searches.

Thanks,  
Lee

---

<div class="post-metadata">

**Author:** ![avivc](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/avivc/32/16501_2.png) [@avivc](https://discuss.elastic.co/u/avivc)\
**Post date:** [June 7, 2017, 4:58pm UTC](https://discuss.elastic.co/t/aggregate-multiple-records-to-single-chart/86561/12 "2017-06-07T16:58:07Z")

</div>

Hi @LeeDr

I'm trying too - but regarding to your link, which is great and very helpful, I need to use `PUT` for specific field in my documents and not new fields  
This field is under a specific type. So when I'm trying to do:

> PUT myIndex/myType/1  
> {  
> "Project.\_refObjectName" : "baseObejct/ derivedObject"  
> }

I'm not getting what I wish for.  
Maybe I should add another key:value pair/s to my query?

---

<div class="post-metadata">

**Author:** ![LeeDr](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/leedr/32/9289_2.png) [@LeeDr](https://discuss.elastic.co/u/LeeDr)\
**Post date:** [June 7, 2017, 7:57pm UTC](https://discuss.elastic.co/t/aggregate-multiple-records-to-single-chart/86561/13 "2017-06-07T19:57:20Z")

</div>

I used PUT in my examples but you don't have to.

POST can be used to achieve auto-generation of ids whereas a PUT is used when you want to specify an id.

---

<div class="post-metadata">

**Author:** ![avivc](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/avivc/32/16501_2.png) [@avivc](https://discuss.elastic.co/u/avivc)\
**Post date:** [June 13, 2017, 6:31pm UTC](https://discuss.elastic.co/t/aggregate-multiple-records-to-single-chart/86561/14 "2017-06-13T18:31:09Z")

</div>

Hi @LeeDr  
I want to simplify it - just taking couple of values for existing key and create scripted field that'll aggregate it to single Object so I can store it in single bar chart. Not related to nested objects

I've tried to follow the steps from your example but no success.

Tnx

---

<div class="post-metadata">

**Author:** ![avivc](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/avivc/32/16501_2.png) [@avivc](https://discuss.elastic.co/u/avivc)\
**Post date:** [June 18, 2017, 11:53am UTC](https://discuss.elastic.co/t/aggregate-multiple-records-to-single-chart/86561/15 "2017-06-18T11:53:32Z")

</div>

@LeeDr any idea please ?  
tnx 👍

---

<div class="post-metadata">

**Author:** ![LeeDr](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/leedr/32/9289_2.png) [@LeeDr](https://discuss.elastic.co/u/LeeDr)\
**Post date:** [June 19, 2017, 5:14pm UTC](https://discuss.elastic.co/t/aggregate-multiple-records-to-single-chart/86561/16 "2017-06-19T17:14:23Z")

</div>

Hi Avivc,

I'm sorry but I still don't understand the data in your docs. I suggested you provide sample data but your single `PUT myIndex/myType/1` isn't enough for me to understand your problem.

Can you please provide a couple of sample docs and then explain which field you're trying to aggregate?

Thanks,  
Lee

---

<div class="post-metadata">

**Author:** ![avivc](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/avivc/32/16501_2.png) [@avivc](https://discuss.elastic.co/u/avivc)\
**Post date:** [June 20, 2017, 11:04am UTC](https://discuss.elastic.co/t/aggregate-multiple-records-to-single-chart/86561/17 "2017-06-20T11:04:04Z")

</div>

Hi @LeeDr  
The next is part of my json input data that's relevant for my aggregation:

> "Project": {  
> "\_type": "Project",  
> "\_rallyAPIMinor": "0",  
> "\_rallyAPIMajor": "2",  
> "\_refObjectName": "Alt-st-aviv avivScrum",  
> "\_ref": "[https://data](https://data)",  
> "\_refObjectUUID": "uniqueGuid"  
> },

The relevant field is `Project._refObjectName`  
I want to take couple of values for this field into single scripted field

In the Discover I use to do:

> Project.\_refObjectName: ("value1" OR "value2")

But, as the above image display, it gives me more than 1 bar, which I don't want in some cases

**Is this the data you need?**  
Tnx a lot 👍

---

<div class="post-metadata">

**Author:** ![LeeDr](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/leedr/32/9289_2.png) [@LeeDr](https://discuss.elastic.co/u/LeeDr)\
**Post date:** [June 20, 2017, 3:13pm UTC](https://discuss.elastic.co/t/aggregate-multiple-records-to-single-chart/86561/18 "2017-06-20T15:13:18Z")

</div>

I think you're saying that the \_refObjName field can have some set of values and you want to create a scripted field that combines some of those. So maybe you can create a scripted field like this;  
(I just copied this from another post and changed the field name)

**NOTE:** I broke it into multiple lines to make it easier to read, but it might have to all be on one line for the scripted field.

```auto
if (doc["_refObjName"].value == 'Windows Server' || 
    doc["_refObjName"].value == 'Linux Server' || 
    doc["_refObjName"].value == 'PCI-Devices') {
   return "OS";
} else if (doc["_refObjName"].value == 'Palo Alto Firewall Group' || 
    doc["_refObjName"].value == 'ASA Firewall') {
    return "Firewall";
}

```

When creating a new scripted field, I would always start with a very simple case and make sure it works in Discover first. And then go back and add more to it. So maybe something like this to start with;

```auto
if (doc["_refObjName"].value == 'Alt-st-aviv avivScrum') {
   return "matches";
} else {
   return "no match";
}

```

---

<div class="post-metadata">

**Author:** ![avivc](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/avivc/32/16501_2.png) [@avivc](https://discuss.elastic.co/u/avivc)\
**Post date:** [June 29, 2017, 6:34am UTC](https://discuss.elastic.co/t/aggregate-multiple-records-to-single-chart/86561/19 "2017-06-29T06:34:34Z")

</div>

Tnx @LeeDr  
When I'm creating a very slim scripted field with 1 item (for testing in Discover), I'm getting the next error:  
`"type":"illegal_argument_exception","reason":"Fielddata is disabled on text fields by default. Set fielddata=true on [Project._refObjectName] in order to load fielddata in memory by uninverting the inverted index. Note that this can however use significant memory. Alternatively use a keyword field instead."`

I can see that this field is analyze field (text) in Kibana's management  
I found [this](https://stackoverflow.com/questions/38145991/how-to-set-fielddata-true-in-kibana/38156296#38156296) tutorial, but here too, when trying to modify the field like this:

> PUT my\_index/\_mapping/Defect  
> {  
> "Defect": {  
> "properties": {  
> "Project.\_refObjectName": {  
> "type": "text",  
> "fielddata": true  
> }  
> }  
> }  
> }

I'm getting the next error:  
`"type": "illegal_argument_exception", "reason": "Mapper for [Project._refObjectName] conflicts with existing mapping in other types:\n[mapper [Project._refObjectName] is used by multiple types. Set update_all_types to true to update [fielddata] across all types.]"`

Should I add **update\_all\_types == true**? If so, where? or I'm not in the right direction for solution ?

10x in advance 👍

---

<div class="post-metadata">

**Author:** ![LeeDr](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/leedr/32/9289_2.png) [@LeeDr](https://discuss.elastic.co/u/LeeDr)\
**Post date:** [June 30, 2017, 4:07pm UTC](https://discuss.elastic.co/t/aggregate-multiple-records-to-single-chart/86561/20 "2017-06-30T16:07:48Z")

</div>

Are you setting the `my_index` mapping after docs have been indexed already? If so, you might have to delete the docs, set the mapping, and then load the docs again.

In the console, you can just do `DELETE my_index`. This is assuming this is test data that you can reload.

Regards,  
Lee

[Next page](https://discuss.elastic.co/t/aggregate-multiple-records-to-single-chart/86561.md?page=2)