Aggregate-filter to create nested arrays

I'm new to logstash and have been browsing the web for a couple of day for a solution for my problem to create an nested array for a company structure to load into elasticsearch. I want to create an array of companys with a nested array of departments and for each department also an array of groups/sections. I've been copying a lot of code-snippets from the web but I haven't been able to put everything together to get what i want ...

My original source is a MySQL database but I've been captured the JDBC output into a json file. I'll include the wanted output as json files and then the aggregate filter section of my conf-file

It will be much appreciated if someone had the time to help me on my way.

input.json
{
"company_id":1,
"department_id":1,
"departments_section_id":4,
"director":"catdoy",
"department_name":"Support",
"name":"Millenium Inc",
"departments_section_manager":"thotom",
"departments_section_name":"Facilities",
"department_manager":"jenjon"
}
{
"company_id":1,
"department_id":1,
"departments_section_id":5,
"director":"catdoy",
"department_name":"Support",
"name":"Millenium Inc",
"departments_section_manager":"bilbre",
"departments_section_name":"IT",
"department_manager":"jenjon"
}
{
"company_id":1,
"department_id":2,
"departments_section_id":null,
"director":"catdoy",
"department_name":"Consumer",
"name":"Millenium Inc",
"departments_section_manager":null,
"departments_section_name":null,
"department_manager":"marsmi"
}
{
"company_id":1,
"department_id":3,
"departments_section_id":1,
"director":"catdoy",
"department_name":"Corporate",
"name":"Millenium Inc",
"departments_section_manager":"petlar",
"departments_section_name":"Finance",
"department_manager":"petlar"
}
{
"company_id":1,
"department_id":3,
"departments_section_id":2,
"director":"catdoy",
"department_name":"Corporate",
"name":"Millenium Inc",
"departments_section_manager":"esmeas",
"departments_section_name":"Legal",
"department_manager":"petlar"
}
{
"company_id":1,
"department_id":3,
"departments_section_id":3,
"director":"catdoy",
"department_name":"Corporate",
"name":"Millenium Inc",
"departments_section_manager":"linpow",
"departments_section_name":"Human Resources",
"department_manager":"petlar"
}
{
"company_id":1,
"department_id":4,
"departments_section_id":null,
"director":"catdoy",
"department_name":"Goverment",
"name":"Millenium Inc",
"departments_section_manager":null,
"departments_section_name":null,
"department_manager":"thotom"
}
{

    "company_id":2,
    "department_id":null,
    "departments_section_id":null,
    "director":"annsma",
    "department_name":null,
    "name":"Nebulose Corp",
    "departments_section_manager":null,
    "departments_section_name":null,
    "department_manager":null
}

wanted-output.json
{
"company_id":1,
"director":"catdoy",
"name":"Millenium Inc",
"departments": [
{
"name":"Support",
"id":1,
"manager":"jenjon",
"sections": [
{
"name":"Facilities",
"id":4,
"manager":"thotom"
},
{
"name":"IT",
"id":5,
"manager":"bilbre"
}
]
},
{
"name":"Consumer",
"id":2,
"manager":"marsmi",
"sections": []
},
{
"name":"Corporate",
"id":3,
"manager":"petlar",
"sections":[
{
"name":"Finance",
"id":1,
"manager":"petlar"
},
{
"name":"Legal",
"id":2,
"manager":"esmeas"
},
{
"name":"Human Resources",
"id":3,
"manager":"linpow"
}
]
},
{
"name":"Goverment",
"id":4,
"manager":"thotom",
"sections":[]
}
]
}
{
"company_id":2,
"director":"annsma",
"name":"Nebulose Corp",
"departments":[]
}

and the filter part from my logstash.conf
filter {
aggregate {
task_id => "%{company_id}"
code => "
map['company_id'] = event.get('company_id')
map['name'] = event.get('name')
map['director'] = event.get('director')
map['departments_list'] ||= []
map['departments'] ||= []
if (event.get('department_id') != nil)
if !( map['departments_list'].include? event.get('departments_id') )
map['departments_list'] << event.get('department_id')
if( event.get('departments_section_id') != nil)
map['departments'] << {
'id' => event.get('department_id'),
'name' => event.get('department_name'),
'manager' => event.get('department_manager'),
'sections' => [] << {
'id' => event.get('departments_section_id'),
'name' => event.get('departments_section_name'),
'manager' => event.get('departments_section_manager')
}
}
else
map['departments'] << {
'id' => event.get('department_id'),
'name' => event.get('department_name'),
'manager' => event.get('department_manager'),
'sections' => []
}
end
end
end
event.cancel()
"
push_previous_map_as_event => true
timeout => 5
}
mutate {
remove_field => ["departments_list"]
}
}

1 Like

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