# A typical case of SQL relation: aggregating results from jdbc input plugin (nested)

**URL:** <https://discuss.elastic.co/t/a-typical-case-of-sql-relation-aggregating-results-from-jdbc-input-plugin-nested/214597>\
**Category:** Logstash\
**Created:** [January 10, 2020, 12:28pm UTC](https://discuss.elastic.co/t/a-typical-case-of-sql-relation-aggregating-results-from-jdbc-input-plugin-nested/214597 "2020-01-10T12:28:08Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![Ahmed\_Ahmed](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ahmed_ahmed/32/44720_2.png) [@Ahmed\_Ahmed](https://discuss.elastic.co/u/Ahmed_Ahmed)\
**Post date:** [January 10, 2020, 12:28pm UTC](https://discuss.elastic.co/t/a-typical-case-of-sql-relation-aggregating-results-from-jdbc-input-plugin-nested/214597/1 "2020-01-10T12:28:08Z")

</div>

I would like to ask With reference to the following where there is a parent-child relation is pipelined  
[Logstash aggregate-example4](https://www.elastic.co/guide/en/logstash/current/plugins-filters-aggregate.html#plugins-filters-aggregate-example4)  
I wonder how to achieve a scenario where event elements have a hierarchal relationship like following  
parent (n)  
---child (n)  
------ grand-child (n)  
------------great-grand-child (n)

For events, I am using JDBC and a SQL query. works fine and giving me events as expected  
I filter aggregate them with the following code.

```
filter {
    aggregate {
        task_id => "%{region_name}"
        code => "
            map['region_name'] = event.get('region_name')
            map['countryz_lst'] ||= []
            map['countryz'] ||= []

            if (event.get('country_name') != nil)
                if !( map['countryz_lst'].include? event.get('country_name') )
                map['countryz_lst'] << event.get('country_name')
                map['countryz'] << {
                    'country_name' => event.get('country_name')
                    }
                    end

                map['cityz_lst'] ||= []
                map['cityz'] ||= []
                if (event.get('city') != nil)
                                if !( map['cityz_lst'].include? event.get('city') )
                                map['cityz_lst'] << event.get('city')
                                map['cityz'] << {
                                    'city' => event.get('city'),
                                    'postal_code' => event.get('postal_code')
                                    }
                                    end
                            end
                            event.cancel()

            end

            event.cancel()
        "
        
        push_map_as_event_on_timeout => true
        timeout => 123
    }
    mutate {
        remove_field => ["countryz_lst","cityz_lst","@version","@timestamp"]
    }
}

output {
    # Output to the console.
    stdout {
        codec => json_lines
    }
}

```

It produces the following json

```
{
"region_name": "Europe",
"countryz": [
    {
        "country_name": "United Kingdom"
    },
    {
        "country_name": "Netherlands"
    },
    {
        "country_name": "Italy"
    },
    {
        "country_name": "France"
    },
    {
        "country_name": "Denmark"
    },
    {
        "country_name": "Germany"
    },
    {
        "country_name": "Switzerland"
    },
    {
        "country_name": "Belgium"
    }
],
"cityz": [
    {
        "postal_code": "09629850293",
        "city": "Stretford"
    },
    {
        "postal_code": "OX9 9ZB",
        "city": "Oxford"
    },
    {
        "postal_code": null,
        "city": "London"
    },
    {
        "postal_code": "3029SK",
        "city": "Utrecht"
    },
    {
        "postal_code": "10934",
        "city": "Venice"
    },
    {
        "postal_code": "00989",
        "city": "Roma"
    },
    {
        "postal_code": "80925",
        "city": "Munich"
    },
    {
        "postal_code": "3095",
        "city": "Bern"
    },
    {
        "postal_code": "1730",
        "city": "Geneva"
    }
]

```

}

```
REGION
         COUNTRY
         CITY

```

I want related cities nested into related countries.  
Like

```
Region
           COUNTRY
                           CITY

```

I hope i could explain.  
Thanks in advance

---

<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:** [January 10, 2020, 5:25pm UTC](https://discuss.elastic.co/t/a-typical-case-of-sql-relation-aggregating-results-from-jdbc-input-plugin-nested/214597/2 "2020-01-10T17:25:52Z")

</div>

What does the input data look like?

---

<div class="post-metadata">

**Author:** ![Ahmed\_Ahmed](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ahmed_ahmed/32/44720_2.png) [@Ahmed\_Ahmed](https://discuss.elastic.co/u/Ahmed_Ahmed)\
**Post date:** [January 10, 2020, 6:08pm UTC](https://discuss.elastic.co/t/a-typical-case-of-sql-relation-aggregating-results-from-jdbc-input-plugin-nested/214597/3 "2020-01-10T18:08:23Z")

</div>

Hello,  
Thanks for the response,

Input is like the following dataset

```
REGION_NAME COUNTRY_NAME CITY STATE_PROVINCE POSTAL_CODE  
---------------------- ------------------------ ------------------- ----------------- -----------  
Americas Argentina (null) (null) (null)       
Americas Brazil Sao Paulo Sao Paulo 01307-002    
Americas Canada Toronto Ontario M5V 2L7      
Americas Canada Whitehorse Yukon YSW 9T2      
Americas Mexico Mexico City Distrito Federal, 11932        
Americas United States of America Seattle Washington 98199        
Americas United States of America South Brunswick New Jersey 50090        
Americas United States of America South San Francisco California 99236        
Americas United States of America Southlake Texas 26192        
Asia Australia Sydney New South Wales 2901         
Asia China Beijing (null) 190518       
Asia India Bombay Maharashtra 490231       
Asia Japan Hiroshima (null) 6823         
Asia Japan Tokyo Tokyo Prefecture 1689         
Asia Malaysia (null) (null) (null)       
Asia Singapore Singapore (null) 540198       
Europe Belgium (null) (null) (null)       
Europe Denmark (null) (null) (null)       
Europe France (null) (null) (null)       
Europe Germany Munich Bavaria 80925        
Europe Italy Roma (null) 00989        
Europe Italy Venice (null) 10934        
Europe Netherlands Utrecht Utrecht 3029SK       
Europe Switzerland Bern BE 3095         
Europe Switzerland Geneva Geneve 1730         
Europe United Kingdom London (null) (null)       
Europe United Kingdom Oxford Oxford OX9 9ZB      
Europe United Kingdom Stretford Manchester 09629850293  
Middle East and Africa Egypt (null) (null) (null)       
Middle East and Africa Israel (null) (null) (null)       
Middle East and Africa Kuwait (null) (null) (null)       
Middle East and Africa Nigeria (null) (null) (null)       
Middle East and Africa Zambia (null) (null) (null)       
Middle East and Africa Zimbabwe (null) (null) (null)    

```

I hope this explains the scenario now.  
Thanks

---

<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:** [January 10, 2020, 7:10pm UTC](https://discuss.elastic.co/t/a-typical-case-of-sql-relation-aggregating-results-from-jdbc-input-plugin-nested/214597/4 "2020-01-10T19:10:59Z")

</div>

Does this help? If you really want countryz to be an array it will require a lot more work.

```
    code => "
        map['region_name'] = event.get('region_name')
        map['countryz_lst'] ||= []
        map['countryz'] ||= {}

        country_name = event.get('country_name')
        if country_name
            if !( map['countryz_lst'].include? country_name)
                map['countryz_lst'] << country_name
                map['countryz'][country_name] = {}
                map['countryz'][country_name]['cityz_lst'] ||= []
                map['countryz'][country_name]['cityz'] ||= []
            end

            city = event.get('city')
            if city
                if !( map['countryz'][country_name]['cityz_lst'].include? city )
                    map['countryz'][country_name]['cityz_lst'] << city
                    map['countryz'][country_name]['cityz'] << {
                        'city' => city,
                        'postal_code' => event.get('postal_code')
                    }
                end
            end
        end
        event.cancel()
    "

```

That code will produce

```
 "region_name" => "Europe",
"countryz_lst" => [
    [0] "Belgium",
    [1] "Denmark",
    [2] "France",
    [3] "Germany",
    [4] "Italy",
    [5] "Netherlands",
    [6] "Switzerland",
    [7] "United Kingdom"
],
    "countryz" => {
             "Italy" => {
            "cityz" => [
            [0] {
                       "city" => "Roma",
                "postal_code" => "00989"
            },
            [1] {
                       "city" => "Venice",
                "postal_code" => "10934"
            }
        ],
        "cityz_lst" => [
            [0] "Roma",
            [1] "Venice"
        ]
    },
       "Switzerland" => {
```

---

<div class="post-metadata">

**Author:** ![Ahmed\_Ahmed](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ahmed_ahmed/32/44720_2.png) [@Ahmed\_Ahmed](https://discuss.elastic.co/u/Ahmed_Ahmed)\
**Post date:** [January 10, 2020, 8:38pm UTC](https://discuss.elastic.co/t/a-typical-case-of-sql-relation-aggregating-results-from-jdbc-input-plugin-nested/214597/5 "2020-01-10T20:38:54Z")

</div>

I appreciate your response. It can be a direction for some needed solutions. But in my case, It still needs some more effort.

Can there be Aggregate(s) based on multiple fields, can there be multiple task\_ids if yes what is the use case(s)  
Is there anything Like the following

```
aggregate {
        task_id => "%{region_name}%{country_name}"

```

Thanks

---

<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:** [January 10, 2020, 9:22pm UTC](https://discuss.elastic.co/t/a-typical-case-of-sql-relation-aggregating-results-from-jdbc-input-plugin-nested/214597/6 "2020-01-10T21:22:53Z")

</div>

Yes, you could use that as a task\_id.

---

<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:** [February 7, 2020, 9:23pm UTC](https://discuss.elastic.co/t/a-typical-case-of-sql-relation-aggregating-results-from-jdbc-input-plugin-nested/214597/7 "2020-02-07T21:23:01Z")

</div>

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