# Update array of object with jdbc

**URL:** <https://discuss.elastic.co/t/update-array-of-object-with-jdbc/177331>\
**Category:** Logstash\
**Created:** [April 17, 2019, 3:25pm UTC](https://discuss.elastic.co/t/update-array-of-object-with-jdbc/177331 "2019-04-17T15:25:16Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![Mathieu\_Lussier](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mathieu_lussier/32/42729_2.png) [@Mathieu\_Lussier](https://discuss.elastic.co/u/Mathieu_Lussier)\
**Post date:** [April 17, 2019, 3:25pm UTC](https://discuss.elastic.co/t/update-array-of-object-with-jdbc/177331/1 "2019-04-17T15:25:16Z")

</div>

Im facing a problem. Im using jdbc to synchronise elasticsearch with mariadb mysql.  
Im building a searching system with elasticsearch for my company for the intranet and the ticket.  
With the actual app we can reply to those ticket and all the reply is save in table name ticket\_msg.  
I know in the filter i can do jdbc stream and fetch data.  
But, i want all the reply to be save in an array of objects into the parent ticket. I have a statement that check if theres a new reply by checking the last id and the update\_timestamp. And in the ticket\_msg table i have a column called ticket\_id. When a new reply is added or reply is modified, i want it to add or update this reply in the ticket doc.  
In short: I want all reply to be added to the right ticket with the field called ticket\_id and push the reply in an array in the ticket.  
My File:  
input {  
jdbc {  
...  
jdbc\_driver\_library =\> "C:/mysql-connector-java-8.0.14/mysql-connector-java-8.0.14.jar"  
jdbc\_driver\_class =\> "com.mysql.cj.jdbc.Driver"  
statement =\> "SELECT `intranet_doc`._, `staff`.`first_name`, `staff`.`last_name`, `intranet_doc_paths`.`description` FROM `intranet_doc` LEFT JOIN `staff` ON `staff`.`id` = `intranet_doc`.`account_id` LEFT JOIN `intranet_doc_paths` ON `intranet_doc_paths`.`id` = `intranet_doc`.`path_id` WHERE `intranet_doc`.`id` \> :sql\_last\_value"  
use\_column\_value =\> true  
tracking\_column =\> "id"  
tracking\_column\_type =\> "numeric"  
clean\_run =\> true  
type =\> "intranet"  
}  
jdbc {  
...  
statement =\> "SELECT `intranet_doc`._, `staff`.`first_name`, `staff`.`last_name`, `intranet_doc_paths`.`description` FROM `intranet_doc` LEFT JOIN `staff` ON `staff`.`id` = `intranet_doc`.`account_id` LEFT JOIN `intranet_doc_paths` ON `intranet_doc_paths`.`id` = `intranet_doc`.`path_id` WHERE `intranet_doc`.`update_timestamp` \> :sql\_last\_value"  
use\_column\_value =\> true  
tracking\_column =\> "update\_timestamp"  
tracking\_column\_type =\> "timestamp"  
type =\> "intranet"  
clean\_run =\> true  
}  
jdbc {  
...  
jdbc\_driver\_library =\> "C:/mysql-connector-java-8.0.14/mysql-connector-java-8.0.14.jar"  
jdbc\_driver\_class =\> "com.mysql.cj.jdbc.Driver"  
statement =\> "SELECT `ticket`._, `assign`.`first_name` as 'assign\_fn', `assign`.`last_name` as 'assign\_ln', `openby`.`first_name` as 'openby\_fn', `openby`.`last_name` as 'openby\_ln', `department`.`name` as 'department', `account`.`customer_id`, `account`.`first_name` as `customer_fn`, `account`.`last_name` as `customer_ln`, `account`.`company` as `customer_cp` FROM `ticket` INNER JOIN `staff` as assign ON `assign`.`id` = `ticket`.`assign_to` INNER JOIN `staff` as openby ON `openby`.`id` = `ticket`.`open_by` INNER JOIN `ticket_dept` as `department` ON `department`.`id` = `ticket`.`dept_id` INNER JOIN `account` ON `account`.`id` = `ticket`.`account_id` INNER JOIN `account` as customer ON `customer`.`id` = `ticket`.`account_id` WHERE `ticket`.`id` \> :sql\_last\_value"  
use\_column\_value =\> true  
tracking\_column =\> "id"  
tracking\_column\_type =\> "numeric"  
clean\_run =\> true  
type =\> "ticket"  
}  
jdbc {  
...  
statement =\> "SELECT `ticket`._, `assign`.`first_name` as 'assign\_fn', `assign`.`last_name` as 'assign\_ln', `openby`.`first_name` as 'openby\_fn', `openby`.`last_name` as 'openby\_ln', `department`.`name` as 'department', `account`.`customer_id`, `account`.`first_name` as `customer_fn`, `account`.`last_name` as `customer_ln`, `account`.`company` as `customer_cp` FROM `ticket` INNER JOIN `staff` as assign ON `assign`.`id` = `ticket`.`assign_to` INNER JOIN `staff` as openby ON `openby`.`id` = `ticket`.`open_by` INNER JOIN `ticket_dept` as `department` ON `department`.`id` = `ticket`.`dept_id` INNER JOIN `account` ON `account`.`id` = `ticket`.`account_id` INNER JOIN `account` as customer ON `customer`.`id` = `ticket`.`account_id` WHERE `ticket`.`update_timestamp` \> :sql\_last\_value"  
use\_column\_value =\> true  
tracking\_column =\> "update\_timestamp"  
tracking\_column\_type =\> "timestamp"  
type =\> "ticket"  
clean\_run =\> true  
}  
jdbc {  
...  
statement =\> "SELECT `ticket_msg`._, `staff`.`first_name`, `staff`.`last_name` FROM `ticket_msg` INNER JOIN `staff` ON `staff`.`id` = `ticket_msg`.`staff_id` WHERE `ticket_msg`.`id` \> :sql\_last\_value"  
use\_column\_value =\> true  
tracking\_column =\> "id"  
tracking\_column\_type =\> "numeric"  
clean\_run =\> true  
type =\> "ticket\_msg"  
}  
jdbc {  
...  
statement =\> "SELECT `ticket_msg`._, `staff`.`first_name`, `staff`.`last_name` FROM `ticket_msg` INNER JOIN `staff` ON `staff`.`id` = `ticket_msg`.`staff_id` WHERE `ticket_msg`.`update_timestamp` \> :sql\_last\_value"  
use\_column\_value =\> true  
tracking\_column =\> "update\_timestamp"  
tracking\_column\_type =\> "timestamp"  
type =\> "ticket\_msg"  
clean\_run =\> true  
}  
}  
filter {}  
output {  
if [type] == "intranet" {  
stdout { codec =\> json\_lines }  
elasticsearch {  
index =\> "intranet"  
document\_id =\> "%{id}"  
hosts =\> ["127.0.0.1:9200"]  
document\_type =\> "intranet"  
}  
}  
if [type] == "ticket" {  
stdout { codec =\> json\_lines }  
elasticsearch {  
index =\> "ticket"  
document\_id =\> "%{id}"  
hosts =\> ["127.0.0.1:9200"]  
document\_type =\> "ticket"  
}  
}  
if [type] == "ticket\_msg" {  
stdout { codec =\> json\_lines }  
elasticsearch {  
index =\> "ticket"  
action =\> "update"  
doc\_as\_upsert =\> true  
document\_id =\> "%{ticket\_id}"  
document\_type =\> "ticket"  
hosts =\> ["127.0.0.1:9200"]  
}  
}  
stdout {  
codec =\> rubydebug  
}  
}  
P.S: im setting the ticket\_msg id to ticket\_id for updating the right ticket.  
I want reply to be added to an array for elasticsearch query.

---

<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:** [May 15, 2019, 3:25pm UTC](https://discuss.elastic.co/t/update-array-of-object-with-jdbc/177331/2 "2019-05-15T15:25:28Z")

</div>

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