# Index parent child relation using Logstash (one to many) Configuration issue
**URL:** https://discuss.elastic.co/t/index-parent-child-relation-using-logstash-one-to-many-configuration-issue/93517
**Category:** Logstash
**Created:** [July 18, 2017, 7:31am UTC](https://discuss.elastic.co/t/index-parent-child-relation-using-logstash-one-to-many-configuration-issue/93517 "2017-07-18T07:31:17Z")
**Posts on this page:** 15
**Page:** 1
### Author:  [@malhotras](https://discuss.elastic.co/u/malhotras)
#### Post date: [July 18, 2017, 7:31am UTC](https://discuss.elastic.co/t/index-parent-child-relation-using-logstash-one-to-many-configuration-issue/93517/1 "2017-07-18T07:31:17Z")
I am using parent-child relation between 2 entities. They are fetched from the database where they come from 2 different tables. I use the SQL join statement for them in logstash config file. Details of the config file:
```
input {
jdbc {
jdbc_connection_string => "url"
jdbc_user => "user"
jdbc_password => "pswd"
jdbc_driver_library => "./ifxjdbc-3-50-JC7.jar"
jdbc_driver_class => "com.informix.jdbc.IfxDriver"
statement => ["SELECT st1.name as s_name, st1.pnumber, st1.mnumber, st2.name as comp_name, zen.s_id, zen.comp_id, zen.conc_1, zen.conc_2 FROM sub_zen zen join sub st1 on st1.id = zen.s_id join sub st2 on st2.id = zen.comp_id"]}}
filter {mutate {remove_field =>["@timestamp"]}}
output {
stdout { codec => json_lines }
elasticsearch {
"manage_template" => "false"
"hosts" => "url"
"index" => "test-migrate"
"parent" => "%{s_id}"}}
```
So, what I am doing is I am joining 2 tables first on "id" which is primary key in Table sub (st1 & st2) with table sub\_zen first on "s\_id" and then "comp\_id". So, id from sub can be either s\_id or comp\_id in sub\_zen.
I define the mapping in ES to have dynamic strict mapping and set manage\_template false in logstash. Now, I would like to index the data into ES where I will have 2 different types.
Sub with fields: s\_id,s\_name,mnumber and pnumber
Comp with fields: comp\_id, comp\_name, conc\_1,conc\_2
I have defined my mapping in ES already:
```
curl -GET url:9200/test-migrate/_mapping?pretty
{
"test-migrate" : {
"mappings" : {
"sub" : {
"dynamic" : "strict",
"properties" : {
"mnumber" : {
"type" : "long"
},
"pnumber" : {
"type" : "long"
},
"s_id" : {
"type" : "long"
},
"s_name" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
}
}
},
"comp" : {
"dynamic" : "strict",
"_parent" : {
"type" : "sub"
},
"_routing" : {
"required" : true
},
"properties" : {
"comp_id" : {
"type" : "long"
},
"comp_name" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},
"conc_1" : {
"type" : "float"
},
"conc_2" : {
"type" : "float"
}
}
}
}
}
}
```
The current logstash config file does not index the data correctly as i would like, what shall i change? Any pointers? How can i guide logstash to index fields (id,name,pnumber,mnumber) to sub and (id,name,conc\_1,conc\_2) to comp and also respect parent child relation. Here sub is a parent which can have many comp child. Any pointers?
---
### Author:  [@fbaligand](https://discuss.elastic.co/u/fbaligand)
#### Post date: [July 19, 2017, 5:43pm UTC](https://discuss.elastic.co/t/index-parent-child-relation-using-logstash-one-to-many-configuration-issue/93517/2 "2017-07-19T17:43:16Z")
Hi,
Have you tried to use aggregate plugin, using this sample use case ?
[https://www.elastic.co/guide/en/logstash/current/plugins-filters-aggregate.html#plugins-filters-aggregate-example4](https://www.elastic.co/guide/en/logstash/current/plugins-filters-aggregate.html#plugins-filters-aggregate-example4)
Then, could you give an example of the target document (or target documents) you wish in elasticsearch, for one zen related to 1-n st1 and 1-m st2 ?
Please format it in json or rubydebug format.
---
### Author:  [@malhotras](https://discuss.elastic.co/u/malhotras)
#### Post date: [July 20, 2017, 4:41am UTC](https://discuss.elastic.co/t/index-parent-child-relation-using-logstash-one-to-many-configuration-issue/93517/3 "2017-07-20T04:41:27Z")
Hi,
I have solved my problem yesterday already (was short on time). But, thanks for your help and suggestion.
---
### Author:  [@fbaligand](https://discuss.elastic.co/u/fbaligand)
#### Post date: [July 20, 2017, 4:54am UTC](https://discuss.elastic.co/t/index-parent-child-relation-using-logstash-one-to-many-configuration-issue/93517/4 "2017-07-20T04:54:39Z")
Ok, Nice to see you solved your problem !
For info, did you use aggregate plugin ?
---
### Author:  [@malhotras](https://discuss.elastic.co/u/malhotras)
#### Post date: [July 20, 2017, 5:19am UTC](https://discuss.elastic.co/t/index-parent-child-relation-using-logstash-one-to-many-configuration-issue/93517/5 "2017-07-20T05:19:11Z")
Yeah, I did. I used nested structure. Used the elasticsearch template provided by logstash. Took hints from your
reply to other posts..😃 The output works and gives me results. But, I am still wondering if there is better way to organize the mapping. So, I have say sub as a parent with many comp childs. Right now, I have structure like this:
[
{
"parent": {
"child": [
{
"conc\_1": 1,
"conc\_2": 2,
"comp\_id": 129,
"comp\_name": "abc"
},
{
"conc\_2": 1,
"conc\_1": 15,
"comp\_id": 11,
"comp\_name": "abc1"
}
]
},
"pnumber": 200,
"@timestamp": "abc",
"sub\_name": "Eb",
"@version": "1",
"mnumber": 21,
"s\_id": 22
},
{
"parent": {
"child": [
{
"conc\_2": 6,
"conc\_1": 5,
"comp\_id": 25,
"comp\_name": "Water"
},
{
"conc\_2": 1,
"con2\_1": 14,
"comp\_id": 129,
"comp\_name": "cdf"
},
{
"conc\_2": 10,
"conc\_1": 7,
"comp\_id": 1686,
"comp\_name": "Mag"
}
]
},
"pnumber": 207,
"@timestamp": "2017-07-19T08:33:30.890Z",
"s\_name": "KMW",
"@version": "1",
"mnumber": 21,
"s\_id": 22913
}
And so on....
now, I just did a quick search using Kibana. When i search for say give me all comp\_id:129, I get all the records along with other comp\_ids too as they are in array. Is there any better way to search or organize mapping??
---
### Author:  [@fbaligand](https://discuss.elastic.co/u/fbaligand)
#### Post date: [July 20, 2017, 5:57am UTC](https://discuss.elastic.co/t/index-parent-child-relation-using-logstash-one-to-many-configuration-issue/93517/6 "2017-07-20T05:57:57Z")
If you use nested type for your parent/child need, that is normal.
Kibana queries elasticsearch which returns all documents which match your query.
But for all matches, the whole elasticsearch document is returned, so you see also other array entries (in the same document).
If you want only children which match your query, you maybe can use parent/child elasticsearch mapping.
It implies that parents and children are different documents. It implies that in your ES response, you get only child info and not parent info.
If you want both, you have to use has\_parent query with inner\_hits :
[https://www.elastic.co/guide/en/elasticsearch/reference/current/search-request-inner-hits.html#parent-child-inner-hits](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-request-inner-hits.html#parent-child-inner-hits)
---
### Author:  [@malhotras](https://discuss.elastic.co/u/malhotras)
#### Post date: [July 20, 2017, 7:00am UTC](https://discuss.elastic.co/t/index-parent-child-relation-using-logstash-one-to-many-configuration-issue/93517/7 "2017-07-20T07:00:16Z")
Yeah, i guessed so. Just wanted to have a working backend before I jump into the ES complex queries. I guess, Kibana (discover) is not yet there to have refined queries.
My goal is to have parent-child-grandchild relationship in the end. Thanks for your suggestions so far. Will keep you updated!
---
### Author:  [@fbaligand](https://discuss.elastic.co/u/fbaligand)
#### Post date: [July 20, 2017, 8:33am UTC](https://discuss.elastic.co/t/index-parent-child-relation-using-logstash-one-to-many-configuration-issue/93517/8 "2017-07-20T08:33:10Z")
In kibana, You can do has\_parent queries with inner\_hits.
But to see parent data in kibana, you have to expand a result document and click on "JSON" view to see parent inner hit
---
### Author:  [@malhotras](https://discuss.elastic.co/u/malhotras)
#### Post date: [July 20, 2017, 9:53am UTC](https://discuss.elastic.co/t/index-parent-child-relation-using-logstash-one-to-many-configuration-issue/93517/9 "2017-07-20T09:53:15Z")
Ok! I need to ask you something with aggregate plugin. Can we make parent-child-grandchild structure? I tried it, but, does not work yet. This is what i have tried so far.
```
map['parent'] ||= {}
map['parent]['child'] ||= []
map['parent']['child']['grandchild'] ||= []
map['parent']['child']['grandchild'] << {'conc_1' => event.get('conc_1'),'conc_2' => event.get('conc_2')}
```
---
### Author:  [@fbaligand](https://discuss.elastic.co/u/fbaligand)
#### Post date: [July 21, 2017, 8:30am UTC](https://discuss.elastic.co/t/index-parent-child-relation-using-logstash-one-to-many-configuration-issue/93517/10 "2017-07-21T08:30:01Z")
Sure, you can do this using aggregate plugin.
The problem in your code is this line :
`map['parent]['child'] ||= []`
If you set an array, then in the next line, you can't set a 'grandchild' field as if 'child' was a structure {}.
So either you set `map['parent]['child'] ||= {}`
either you insert 'grandchild' as an array entry : `map['parent']['child'] << {'grandchild': []}`
---
### Author:  [@malhotras](https://discuss.elastic.co/u/malhotras)
#### Post date: [August 11, 2017, 11:55am UTC](https://discuss.elastic.co/t/index-parent-child-relation-using-logstash-one-to-many-configuration-issue/93517/11 "2017-08-11T11:55:39Z")
Hi again,
So, now I have extensively tested the parent-child-grandchild relationship etc. Works great! I want to ask something additionally and dont want to open new issue for it.
When we map the attributes to array, is it possible to do it for all the attributes automatically without writing the name individually. so what i mean...
```
map['parent']['child']['grandchild'] << {'conc_1' => event.get('conc_1'),'conc_2' => event.get('conc_2')}
```
Here instead of writing name of conc\_1 and conc\_2, individually i can map all the attributes automatically
---
### Author:  [@fbaligand](https://discuss.elastic.co/u/fbaligand)
#### Post date: [August 11, 2017, 4:47pm UTC](https://discuss.elastic.co/t/index-parent-child-relation-using-logstash-one-to-many-configuration-issue/93517/12 "2017-08-11T16:47:20Z")
This should do the job :
map['parent']['child']['grandchild'] \<\< event.to\_hash
---
### Author:  [@malhotras](https://discuss.elastic.co/u/malhotras)
#### Post date: [August 18, 2017, 6:55am UTC](https://discuss.elastic.co/t/index-parent-child-relation-using-logstash-one-to-many-configuration-issue/93517/13 "2017-08-18T06:55:18Z")
Thanks again. I have some other issue to resolve. Posted it here [https://stackoverflow.com/questions/45750086/change-null-value-from-database-for-one-field-using-logstash-and-insert-into-ela](https://stackoverflow.com/questions/45750086/change-null-value-from-database-for-one-field-using-logstash-and-insert-into-ela)
If you can help...
---
### Author:  [@fbaligand](https://discuss.elastic.co/u/fbaligand)
#### Post date: [August 18, 2017, 7:43am UTC](https://discuss.elastic.co/t/index-parent-child-relation-using-logstash-one-to-many-configuration-issue/93517/14 "2017-08-18T07:43:51Z")
As it is a very different issue, I invite you to open a new topic for that, and notify me, with @fbaligand
---
### Author:  [@system](https://discuss.elastic.co/u/system)
#### Post date: [September 15, 2017, 7:43am UTC](https://discuss.elastic.co/t/index-parent-child-relation-using-logstash-one-to-many-configuration-issue/93517/15 "2017-09-15T07:43:52Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.