# Group field from multiple rows based on common key

**URL:** <https://discuss.elastic.co/t/group-field-from-multiple-rows-based-on-common-key/194501>\
**Category:** Logstash\
**Created:** [August 8, 2019, 7:49pm UTC](https://discuss.elastic.co/t/group-field-from-multiple-rows-based-on-common-key/194501 "2019-08-08T19:49:02Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![shri\_palchuri](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/shri_palchuri/32/51976_2.png) [@shri\_palchuri](https://discuss.elastic.co/u/shri_palchuri)\
**Post date:** [August 8, 2019, 7:49pm UTC](https://discuss.elastic.co/t/group-field-from-multiple-rows-based-on-common-key/194501/1 "2019-08-08T19:49:02Z")

</div>

**spotlight.csv:**

ENTITY\_ID,ENTITY\_TYPE,SPN\_FIRM\_ID,IA\_ID,BRANCH\_ID  
1323575,AC,101,10503,11002  
1323575,AC,101,10503,359006

**Expecting Output JSON:**  
{  
"keyInformation": {  
"entityId": 1323575,  
"entityType": "AC",  
"firmId": 101  
},  
"entitlements": {  
**"branchId": [11002,359006],**  
"iaId": 10503  
}  
}

**spotlight.conf:**

input {  
file {  
path =\> "C:/ELK/logstash-7.2.0/config/spotlight.csv"  
start\_position =\> "beginning"  
sincedb\_path =\> "NUL"  
}  
}

filter {  
csv {  
separator =\> ","  
columns =\> ["ENTITY\_ID","ENTITY\_TYPE","SPN\_FIRM\_ID",  
"IA\_ID","BRANCH\_ID"]  
}

mutate {

```
    rename => {
		"ENTITY_ID"=> "[keyInformation][entityId]"
		"ENTITY_TYPE"=> "[keyInformation][entityType]"
		"SPN_FIRM_ID"=> "[keyInformation][firmId]"
		"IA_ID"=> "[entitlements][iaId]"
		"BRANCH_ID"=> "[entitlements][branchId]"
	}

```

}

}  
output {  
stdout { codec =\> rubydebug }  
elasticsearch{  
hosts =\> ["localhost:9200"]  
index =\> "spotlight"  
document\_id =\> "%{[keyInformation][entityId]}"  
document\_type =\> "\_doc"  
user =\> elastic  
password =\> test01  
action =\> "update"  
doc\_as\_upsert =\> "true"  
}  
}

Branch Id is different for the same key, need to create a list of branch Id's for the key. Can some help me how to achieve using logstash?

---

<div class="post-metadata">

**Author:** ![Badger](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/badger/32/25190_2.png) [@Badger](https://discuss.elastic.co/u/Badger)\
**Post date:** [August 8, 2019, 8:29pm UTC](https://discuss.elastic.co/t/group-field-from-multiple-rows-based-on-common-key/194501/2 "2019-08-08T20:29:33Z")

</div>

Use an [aggregate](https://www.elastic.co/guide/en/logstash/current/plugins-filters-aggregate.html) filter.

```
    csv { autodetect_column_names => true }
    aggregate {
        task_id => "%{ENTITY_ID}"
        code => '
            map["keyInformation"] ||= {}
            map["keyInformation"]["entityId"] = event.get("ENTITY_ID")
            map["keyInformation"]["entityType"] = event.get("ENTITY_TYPE")
            map["keyInformation"]["firmId"] = event.get("SPN_FIRM_ID")
            map["entitlements"] ||= {}
            map["entitlements"]["branchId"] ||= []
            map["entitlements"]["iaId"] = event.get("IA_ID")
            map["entitlements"]["branchId"] << event.get("BRANCH_ID")
            event.cancel
        '
        push_map_as_event_on_timeout => true
        timeout => 2
    }

```

Make sure you set --pipeline.workers 1

---

<div class="post-metadata">

**Author:** ![shri\_palchuri](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/shri_palchuri/32/51976_2.png) [@shri\_palchuri](https://discuss.elastic.co/u/shri_palchuri)\
**Post date:** [August 9, 2019, 1:11pm UTC](https://discuss.elastic.co/t/group-field-from-multiple-rows-based-on-common-key/194501/3 "2019-08-09T13:11:53Z")

</div>

> [@Badger](#):
>
> csv { autodetect\_column\_names =\> true }

Thanks Badger, this is really helpful.

---

<div class="post-metadata">

**Author:** ![shri\_palchuri](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/shri_palchuri/32/51976_2.png) [@shri\_palchuri](https://discuss.elastic.co/u/shri_palchuri)\
**Post date:** [August 9, 2019, 4:06pm UTC](https://discuss.elastic.co/t/group-field-from-multiple-rows-based-on-common-key/194501/4 "2019-08-09T16:06:52Z")

</div>

One more question.

I need output as below

**Output:**

"associatedAccounts": [  
{  
"accountId": 728006,  
"accountNumber": "5BOMD9Q5WMCAYNM",  
"iaId": 903751,  
"branchId": 426176,  
"areaId": 572198,  
"regionId": 942753,  
"modelOwnerId": 925453,  
"programId": "805554",  
"lastUpdated": "Jun 21, 2019 12:09:33 PM"  
},  
{  
"accountId": 646757,  
"accountNumber": "EQ5GFVQO3MYYQFX",  
"iaId": 305899,  
"branchId": 337585,  
"areaId": 582687,  
"regionId": 438242,  
"modelOwnerId": 906315,  
"programId": "805554",  
"lastUpdated": "Jun 21, 2019 12:09:33 PM"  
}  
]

**Config:**

filter {  
csv { autodetect\_column\_names =\> true }

if [message] =~ /^ENVIRONMENT\_ID/ {  
drop {}  
}  
aggregate {  
task\_id =\> "%{ENTITY\_ID}"  
code =\> '  
map["keyInformation"] ||= {}  
map["keyInformation"]["entityId"] = event.get("ENTITY\_ID")  
map["keyInformation"]["entityType"] = event.get("ENTITY\_TYPE")  
map["keyInformation"]["firmId"] = event.get("SPN\_FIRM\_ID")

```
		map["descriptive"] ||= {}
		map["descriptive"]["householdTitle"] = event.get("HOUSEHOLDTITLE")
		map["descriptive"]["tagType"] = event.get("TAG_TYPE")
		
	
		map['associatedAccounts'] ||= [] 
		map['associatedAccounts'] << { 
		 'accountNumber' => event.get("ACCOUNTNUMBER")
		 'accountId' => event.get("ACCT_ID")
		 'modelOwnerId' => event.get("MGR_ID")
		 'programId' => event.get("PROGRAM_ID")
		 'iaId' => event.get("IA_ID")
                     'branchId' => event.get("BRANCH_ID")
		 'areadId' => event.get("AREAD_ID")
		 'regionId' => event.get("REGION_ID")
		}			
        event.cancel
    '
    push_map_as_event_on_timeout => true
    timeout => 2
}

```

}

I am getting logstash config error, can you help me?

---

<div class="post-metadata">

**Author:** ![Badger](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/badger/32/25190_2.png) [@Badger](https://discuss.elastic.co/u/Badger)\
**Post date:** [August 9, 2019, 4:29pm UTC](https://discuss.elastic.co/t/group-field-from-multiple-rows-based-on-common-key/194501/5 "2019-08-09T16:29:32Z")

</div>

What is the error?

---

<div class="post-metadata">

**Author:** ![shri\_palchuri](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/shri_palchuri/32/51976_2.png) [@shri\_palchuri](https://discuss.elastic.co/u/shri_palchuri)\
**Post date:** [August 9, 2019, 4:34pm UTC](https://discuss.elastic.co/t/group-field-from-multiple-rows-based-on-common-key/194501/6 "2019-08-09T16:34:22Z")

</div>

Failed to execute action {:action=\>LogStash::PipelineAction::Create/pipeline\_id:main, :exception=\>"LogStash::ConfigurationError", :message=\>"Expected one of #, {, } at line 28, column 9 (byte 783) after filter {\r\n csv { autodetect\_column\_names =\> true }\r\n \r\n if [message] =~ /^ENVIRONMENT\_ID/ {\r\n\tdrop {}\r\n }\r\n aggregate {\r\n task\_id =\> "%{ENTITY\_ID}"\r\n code =\> '\r\n map["keyInformation"] ||= {}\r\n map["keyInformation"]["entityId"] = event.get("ENTITY\_ID")\r\n map["keyInformation"]["entityType"] = event.get("ENTITY\_TYPE")\r\n map["keyInformation"]["firmId"] = event.get("SPN\_FIRM\_ID")\r\n \r\n\t\t\tmap["descriptive"] ||= {}\r\n\t\t\tmap["descriptive"]["householdTitle"] = event.get("HOUSEHOLDTITLE")\r\n\t\t\tmap["descriptive"]["tagType"] = event.get("TAG\_TYPE")\r\n\t\t\t\r\n\t\t\r\n\t\t\tmap['", :backtrace=\>["C:/ELK/logstash-7.2.0/logstash-core/lib/logstash/compiler.rb:41:in `compile_imperative'", "C:/ELK/logstash-7.2.0/logstash-core/lib/logstash/compiler.rb:49:in`compile\_graph'", "C:/ELK/logstash-7.2.0/logstash-core/lib/logstash/compiler.rb:11:in `block in compile_sources'", "org/jruby/RubyArray.java:2577:in`map'", "C:/ELK/logstash-7.2.0/logstash-core/lib/logstash/compiler.rb:10:in `compile_sources'", "org/logstash/execution/AbstractPipelineExt.java:151:in`initialize'", "org/logstash/execution/JavaBasePipelineExt.java:47:in `initialize'", "C:/ELK/logstash-7.2.0/logstash-core/lib/logstash/java_pipeline.rb:24:in`initialize'", "C:/ELK/logstash-7.2.0/logstash-core/lib/logstash/pipeline\_action/create.rb:36:in `execute'", "C:/ELK/logstash-7.2.0/logstash-core/lib/logstash/agent.rb:325:in`block in converge\_state'"]}

---

<div class="post-metadata">

**Author:** ![Badger](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/badger/32/25190_2.png) [@Badger](https://discuss.elastic.co/u/Badger)\
**Post date:** [August 9, 2019, 4:38pm UTC](https://discuss.elastic.co/t/group-field-from-multiple-rows-based-on-common-key/194501/7 "2019-08-09T16:38:33Z")

</div>

The code block is surrounded by single quotes, so you should be using double quotes inside it (I do this so that I can use string magic when I need to).

---

<div class="post-metadata">

**Author:** ![shri\_palchuri](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/shri_palchuri/32/51976_2.png) [@shri\_palchuri](https://discuss.elastic.co/u/shri_palchuri)\
**Post date:** [August 9, 2019, 5:27pm UTC](https://discuss.elastic.co/t/group-field-from-multiple-rows-based-on-common-key/194501/8 "2019-08-09T17:27:53Z")

</div>

Thanks.

---

<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 6, 2019, 5:27pm UTC](https://discuss.elastic.co/t/group-field-from-multiple-rows-based-on-common-key/194501/9 "2019-09-06T17:27:58Z")

</div>

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