# Merge information from separated databases into a single document

**URL:** <https://discuss.elastic.co/t/merge-information-from-separated-databases-into-a-single-document/201078>\
**Category:** Logstash\
**Created:** [September 25, 2019, 3:45pm UTC](https://discuss.elastic.co/t/merge-information-from-separated-databases-into-a-single-document/201078 "2019-09-25T15:45:54Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![ricardo.almeida](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ricardo.almeida/32/54871_2.png) [@ricardo.almeida](https://discuss.elastic.co/u/ricardo.almeida)\
**Post date:** [September 25, 2019, 3:45pm UTC](https://discuss.elastic.co/t/merge-information-from-separated-databases-into-a-single-document/201078/1 "2019-09-25T15:45:54Z")

</div>

I would like to merge information from two different tables in different database.  
In table t\_a from database db\_a I have information about the users, like username, Name, Phone Number, etc.  
In table t\_b from database db\_b I have system-specific information about users. Eg, for that system the user X is of Type Y.  
The table t\_b has a field indicating the id of the user on table t\_a, and this is how I can link the two tables.  
I want to group users by Username in Elastic and inside each document there will be an array of users that have specific user information for each existing user with that username.  
Eg.  
{  
"\_index" : "users",  
...  
"source" : {  
"user\_id" : "sysadmin",  
"username" : "sysadmin"  
"users": {  
{  
"name" : null, \ From t\_a  
"email" : "fabio.lp@abcd.com", \ From t\_a  
"tenantid" : "e118af1f-6674-4348-bb12-xxxxxxxx27a6", \ From t\_a  
"user\_type" : 1050 \ From t\_b  
},  
{  
"name" : "Administrator", \ From t\_a  
"email" : "joao.lapa@abcd.com", \ From t\_a  
"tenantid" : "e118af1f-6674-4348-bb12-9032bd2xxxxx", \ From t\_a  
"user\_type" : 1050 \ From t\_b  
}  
}  
}  
}

Here is what I am trying to do at the moment. Unfortunately is not working:

```
input {
  jdbc {
    jdbc_connection_string => "jdbc:sqlserver://db_a;databaseName=t_a;user=xx;password=xxx;"
    jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
    jdbc_user => "xx"
	jdbc_password => "xxx"

    statement => "SELECT * FROM Users order by username"
  }
}

filter {
  jdbc_streaming {
		jdbc_connection_string => "jdbc:sqlserver://db_b;databaseName=t_b;user=bbb;password=aaa;"
		jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
		jdbc_user => "aaa"
		jdbc_password => "bbb"
		statement => "select ExternalID, UserTypeID from [User] where ExternalID = :userid"
		parameters => { "userid" => "id" }
		target => "extra"
		add_field => {
			"userType" => "%{[extra][0][UserTypeID]}"
		}
		remove_field => ["extra"]
	}

  aggregate {
    task_id => "%{username}"
	code => "
         map['username'] = event.get('username')
         map['users'] ||= []
         map['users'] << {'name' => event.get('name'), 
						'email' => event.get('email'),
						'tenantid' => event.get('tenantid')}
         event.cancel()
       "
	push_previous_map_as_event => true
	timeout_task_id_field => "user_id"
	timeout => 3600
	inactivity_timeout => 300
	timeout_tags => ['_aggregatetimeout']
	}
}

output {
  elasticsearch {
    hosts => ["localhost:9200"]
    index => "users"
	}
}

```

I tried to explain the best I could =/  
I want to know if this is possible and if it is, how can I achieve it.

---

<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 23, 2019, 4:00pm UTC](https://discuss.elastic.co/t/merge-information-from-separated-databases-into-a-single-document/201078/2 "2019-10-23T16:00:56Z")

</div>

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