# Merge values in JSON (jdbc events logstash)

**URL:** <https://discuss.elastic.co/t/merge-values-in-json-jdbc-events-logstash/99367>\
**Category:** Logstash\
**Created:** [September 5, 2017, 5:28am UTC](https://discuss.elastic.co/t/merge-values-in-json-jdbc-events-logstash/99367 "2017-09-05T05:28:17Z")\
**Posts on this page:** 15\
**Page:** 1

<div class="post-metadata">

**Author:** ![malhotras](https://avatars.discourse-cdn.com/v4/letter/m/a8b319/32.png) [@malhotras](https://discuss.elastic.co/u/malhotras)\
**Post date:** [September 5, 2017, 5:28am UTC](https://discuss.elastic.co/t/merge-values-in-json-jdbc-events-logstash/99367/1 "2017-09-05T05:28:18Z")

</div>

I have a JSON structure like this:

```
 {
  "details": [
{
 "place": "abc",
 "group": 3,
  "year": 2006,
  "id": 1304,
  "street": "xyz 14",
  "lf_number": "0118",
 "code": 4433,
 "name": "abc coorperation",
 "group2": 3817"
 "group1": 32"
 "postal_code": "22926",
 "status": 2
 },
 {
 "place": "cbc",
  "group": 2,
  "year": 2007,
 "id": 4983,
 "street": "mnc 14",
 "lf_number": "0145",
 "code": 4433,
 "name": "abc coorperation", 
 "group2": 3817,
 "group1": 32"
 "postalcode": "22926",
 "status": 2
 }
 ],
 "@timestamp": "2017-09-04",
"parent": {
 "child": [
 {
 "w_2": 0.5,
 "w_1": 0.1,
 "id": 14226,
 "name": "air"
  },
 {
  "w_2": null,
 "w_1": 91,
  "id": 25002,
 "name": "Water"
 }]
 },
 "p_name": "anacin",
 "@version": "1",
 "id": 28841
 }

```

I want to edit the **details**. This is the restructured output from logstash JDBC plugin using aggregate filter to create parent-child relation. So, this JSON is outcome of JDBC filter and aggregate filter from Logstash. Now, I want to merge the information in details here. I want to construct new fields.

```
  Field 1) coorperations: (details.name | details.postal_code details.street ; details.name | details.postal_code details.street)
 
 Output:
   Coorperations: (abc coorperation |22926 xyz 14; abc coorperation | 22926 mnc 14)

 Field 2) code: (details.code; details.code)
 
 Output:
 code: (4433,4433)

 Field 3) access_code: (details.status-details.id-details.group1-details.group2-details.group(always two digit)/details.year(only last two digits); details.status-details.id-details.group1-details.group2-details.group(always two digit)/details.year(only last two digits))

 Output: access_code (2-32-3817-03-06; 2-32-3817-02-07)

```

How can I achieve this for all the values in **details**. Without touching other parts of the JSON.

---

<div class="post-metadata">

**Author:** ![malhotras](https://avatars.discourse-cdn.com/v4/letter/m/a8b319/32.png) [@malhotras](https://discuss.elastic.co/u/malhotras)\
**Post date:** [September 13, 2017, 6:55am UTC](https://discuss.elastic.co/t/merge-values-in-json-jdbc-events-logstash/99367/2 "2017-09-13T06:55:56Z")

</div>

No hints from anyone. I am still looking for answer. Just a clue. @fbaligand any input from your side.

---

<div class="post-metadata">

**Author:** ![fbaligand](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/fbaligand/32/5657_2.png) [@fbaligand](https://discuss.elastic.co/u/fbaligand)\
**Post date:** [September 13, 2017, 7:32am UTC](https://discuss.elastic.co/t/merge-values-in-json-jdbc-events-logstash/99367/3 "2017-09-13T07:32:29Z")

</div>

As your input is a array containing structured objects (details), I don't see a way to do it with standard filters (like mutate).

I invite you to use ruby filter.  
And in code, you make a "for" loop on all details.  
For each detail, you get each field you need and put it in a result string. Then you set the result string as a new global field using event.set()

---

<div class="post-metadata">

**Author:** ![malhotras](https://avatars.discourse-cdn.com/v4/letter/m/a8b319/32.png) [@malhotras](https://discuss.elastic.co/u/malhotras)\
**Post date:** [September 13, 2017, 12:38pm UTC](https://discuss.elastic.co/t/merge-values-in-json-jdbc-events-logstash/99367/4 "2017-09-13T12:38:43Z")

</div>

Thanks for your feedback again. I have guessed already that mutate might not work. But the ruby code i have tried so far also is not working. I am very new to it. Like, I just tried to test if I could rename a field in all the members of "details". This is not working. What is the problem?

```
filter {
 if ([details]) {
ruby {
 code => "event.get('details').each {|k|
  k.rename('code') = k('secode')
   k.delete('code')
}"} } }
```

---

<div class="post-metadata">

**Author:** ![fbaligand](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/fbaligand/32/5657_2.png) [@fbaligand](https://discuss.elastic.co/u/fbaligand)\
**Post date:** [September 13, 2017, 1:18pm UTC](https://discuss.elastic.co/t/merge-values-in-json-jdbc-events-logstash/99367/5 "2017-09-13T13:18:05Z")

</div>

Two things :

- first, what you try to do here is totally different from what you ask in this topic. Right ?
- then, finally, what you ask is precisely answered in this topic : [Aggregate fields based on nested filter with custom separator](https://discuss.elastic.co/t/aggregate-fields-based-on-nested-filter-with-custom-separator/98008/15)

Why don't you use the solution I provided to you here ?

---

<div class="post-metadata">

**Author:** ![malhotras](https://avatars.discourse-cdn.com/v4/letter/m/a8b319/32.png) [@malhotras](https://discuss.elastic.co/u/malhotras)\
**Post date:** [September 13, 2017, 1:33pm UTC](https://discuss.elastic.co/t/merge-values-in-json-jdbc-events-logstash/99367/6 "2017-09-13T13:33:03Z")

</div>

yes, I am trying to do a different thing here to learn the ruby code syntax. As, I am trying to see if i loop-over correctly.

The solution you have mentioned earlier used aggregate filter syntax. Therefore, i am bit confused. It does not work with this case now.

I have used jdbc filter, jdbc streaming filter and then aggregate plugin. In the end, this is my output. Now, I want to aggregate again. Using the second aggregate filter does not help here. How can i access only array "details" here.

---

<div class="post-metadata">

**Author:** ![fbaligand](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/fbaligand/32/5657_2.png) [@fbaligand](https://discuss.elastic.co/u/fbaligand)\
**Post date:** [September 13, 2017, 1:57pm UTC](https://discuss.elastic.co/t/merge-values-in-json-jdbc-events-logstash/99367/7 "2017-09-13T13:57:09Z")

</div>

I guess "details" info is built using jdbc + aggregate filter, right ?  
So in the previous topic, I gave you a solution to create a field named "all" that combines in one string, all informations details.  
That is precisely what you need here.

The aggregate job is to do just after jdbc input, where each detail is a seperate event.  
So in one aggregate filter, you can aggregate all details into one event, and also create this global aggregated string field with all details info.

---

<div class="post-metadata">

**Author:** ![malhotras](https://avatars.discourse-cdn.com/v4/letter/m/a8b319/32.png) [@malhotras](https://discuss.elastic.co/u/malhotras)\
**Post date:** [September 13, 2017, 2:20pm UTC](https://discuss.elastic.co/t/merge-values-in-json-jdbc-events-logstash/99367/8 "2017-09-13T14:20:58Z")

</div>

```
filter {
aggregate {
task_id => "%{s_id}"
code => "
map['parent'] ||= {}
map['s_id'] = event.get('s_id')
map['s_name'] = event.get('s_name')
map['details'] = event.get('details')
map['parent']['child'] ||= []
map['parent']['child'] << {'id' => event.get('id'),'name' => event.get('name'),'w_1' => event.get('w_1'),'w_2' => event.get('w_2')}
event.cancel()"
push_previous_map_as_event => true
timeout => 5
}
}

```

This is my aggregate filter definition at the moment. And the fields i want to combine are in details. How can i define here to operate only on "details"

---

<div class="post-metadata">

**Author:** ![fbaligand](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/fbaligand/32/5657_2.png) [@fbaligand](https://discuss.elastic.co/u/fbaligand)\
**Post date:** [September 13, 2017, 3:27pm UTC](https://discuss.elastic.co/t/merge-values-in-json-jdbc-events-logstash/99367/9 "2017-09-13T15:27:05Z")

</div>

OK, so if you add here my suggested code with `sprintf` method, you could do what you need.

---

<div class="post-metadata">

**Author:** ![malhotras](https://avatars.discourse-cdn.com/v4/letter/m/a8b319/32.png) [@malhotras](https://discuss.elastic.co/u/malhotras)\
**Post date:** [September 14, 2017, 5:29am UTC](https://discuss.elastic.co/t/merge-values-in-json-jdbc-events-logstash/99367/10 "2017-09-14T05:29:18Z")

</div>

It is not working this is the problem. I am not able to find out how can i access the values inside "details" properly.

This is what i tried so far:

```
filter {
 aggregate {
  task_id => "%{s_id}"
  code => "
map['parent'] ||= {}
map['id'] = event.get('id')
map['p_name'] = event.get('p_name')
map['details'] = event.get('details')
map['details']['cooperations'] ||= ''
map['details']['cooperations'] += ' ; ' unless map['details']['cooperations'].empty?
map['details']['cooperations'] += event.sprintf('%{name} | %{postal_code} %{street}')
event.set('cooperations', map['details']['cooperations'])
     map['parent']['child'] ||= []
     map['parent']['child'] << {'id' => event.get('id'),'name' => event.get('name'),'w_1' => event.get('w_1'),'w_2' => event.get('w_2')}
     event.cancel()

  "
push_previous_map_as_event => true
timeout => 5} }

```

Can you help in correcting the code here. This is what i am trying to figure out. How can i access only values inside the "details" and make new field inside it.

---

<div class="post-metadata">

**Author:** ![fbaligand](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/fbaligand/32/5657_2.png) [@fbaligand](https://discuss.elastic.co/u/fbaligand)\
**Post date:** [September 17, 2017, 7:52am UTC](https://discuss.elastic.co/t/merge-values-in-json-jdbc-events-logstash/99367/11 "2017-09-17T07:52:46Z")

</div>

Could you provide event json content that arrives just before aggregate filter ?

---

<div class="post-metadata">

**Author:** ![malhotras](https://avatars.discourse-cdn.com/v4/letter/m/a8b319/32.png) [@malhotras](https://discuss.elastic.co/u/malhotras)\
**Post date:** [September 19, 2017, 5:05am UTC](https://discuss.elastic.co/t/merge-values-in-json-jdbc-events-logstash/99367/12 "2017-09-19T05:05:11Z")

</div>

{  
"@timestamp" =\> 2017-09-19T06:51:37.659Z,  
"details" =\> [  
[0] {  
"place" =\> "abc",  
"group" =\> 2,  
"year" =\> 2004,  
"id" =\> 753,  
"street" =\> "Kaiser Str. 30",  
"lf\_number" =\> "1511",  
"code" =\> 200,  
"name" =\> "abc coorperation",  
"group2" =\> 3817,  
"group1" =\> 8,  
"postal\_code" =\> "90763",  
"status" =\> 2  
},  
[1] {  
"place" =\> "abc",  
"group" =\> 2,  
"year" =\> 2005,  
"id" =\> 2142,  
"street" =\> "Kaiser Str. 30",  
"lf\_number" =\> "1628",  
"code" =\> 200,  
"name" =\> "abc cooperation",  
"group2" =\> 3817,  
"group1" =\> 32,  
"postal\_code" =\> "90763",  
"status" =\> 2  
}  
],  
"p\_name" =\> "anacin",  
"w\_2" =\> nil,  
"w\_1" =\> 1.9,  
"@version" =\> "1",  
"s\_id" =\> 24051,  
"id" =\> 23225,  
"name" =\> "water"  
}  
{  
"@timestamp" =\> 2017-09-19T06:51:37.659Z,  
"melder\_details" =\> [  
[0] {  
"place" =\> "abc",  
"group" =\> 2,  
"year" =\> 2004,  
"id" =\> 753,  
"street" =\> "Kaiser Str. 30",  
"lf\_number" =\> "1511",  
"code" =\> 200,  
"name" =\> "abc coorperation",  
"group2" =\> 3817,  
"group1" =\> 8,  
"postalcode" =\> "90763",  
"status" =\> 2  
},  
[1] {  
"place" =\> "abc",  
"group" =\> 2,  
"year" =\> 2005,  
"id" =\> 2142,  
"street" =\> "Kaiser Str. 30",  
"lf\_number" =\> "1628",  
"code" =\> 200,  
"name" =\> "abc cooperation",  
"group2" =\> 3817,  
"group1" =\> 32,  
"postalcode" =\> "90763",  
"status" =\> 2  
}  
],  
"p\_name" =\> "anacin",  
"w\_2" =\> nil,  
"w\_1" =\> 0.2,  
"@version" =\> "1",  
"s\_id" =\> 24051,  
"id" =\> 23319,  
"name" =\> "air"  
}

Here is how it looks like before aggregate filter. I aggregate on "s\_id". So, this is parent (p\_name, s\_id) and child (w\_1,w\_2, id, name). The fields in details remain as such and i want to do "sprintf" on them.

---

<div class="post-metadata">

**Author:** ![malhotras](https://avatars.discourse-cdn.com/v4/letter/m/a8b319/32.png) [@malhotras](https://discuss.elastic.co/u/malhotras)\
**Post date:** [September 19, 2017, 9:46am UTC](https://discuss.elastic.co/t/merge-values-in-json-jdbc-events-logstash/99367/13 "2017-09-19T09:46:07Z")

</div>

I managed to print a new field to combine the postalcode from "details" using ruby filter. But, this does not work when i want to combine more fields.

```
  filter{
    ruby {
 code => "if(!event.get('[details][0]').nil?)
 event.set('[postalcode]',event.get('[details]').collect { |m| m['postalcode'] }.join(' '))
end"}
}
```

---

<div class="post-metadata">

**Author:** ![malhotras](https://avatars.discourse-cdn.com/v4/letter/m/a8b319/32.png) [@malhotras](https://discuss.elastic.co/u/malhotras)\
**Post date:** [October 2, 2017, 6:49am UTC](https://discuss.elastic.co/t/merge-values-in-json-jdbc-events-logstash/99367/14 "2017-10-02T06:49:48Z")

</div>

@fbaligand, can you suggest a solution?

---

<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:** [October 30, 2017, 6:50am UTC](https://discuss.elastic.co/t/merge-values-in-json-jdbc-events-logstash/99367/15 "2017-10-30T06:50:47Z")

</div>

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