Logstash jdbc plugin input for mutliple table joins of one to many relationships

Elastic Stack Logstash
1

HI All,
I have two tables linked in a one to many relationship , i.e for each row in first table there will be multiple rows in second table .
One employee can have many orders assigned to them .
ex :

how can i achieve the data upload to elastic for a single index to have all orders in the form of an array of orders inside the main JSON object?

like..

t2.png819×494 9.41 KB

I also want to add that i have read about custom mappings and templates for elastic , but i need the logstash way of pushing data in the format i have shown .

Thanks !

1 Like
2

@magnusbaeck Can you please offer some assistance on this ?

3

############## Nested Objects ###
PUT employee_orders
{
"mappings": {
"employee": {
"properties": {
"_id": { "type": "long" },
"name": { "type": "string" },
"orders": { "type": "nested",
"properties": {
"orderid": { "type": "string" },
"description": { "type": "string" }
}
}
}
}
}
}

GET employee_orders

POST employee_orders/employee/1
{
"name": "Suresh"
}

PUT employee_orders/employee/2
{
"name": "Sudha"
}

GET employee_orders/employee/1
GET employee_orders/employee/2

DELETE employee_orders/employee/1
GET employee_orders/_search

POST employee_orders/employee/1/_update
{
"doc":{
"orders":{
"orderid": "0001",
"description": "Leather Jacket"
}
}
}

########Dynamic Mapping #######

GET emporders/_search

PUT /emporders
{
"mappings": {
"empid": {
"dynamic": "strict",
"properties": {
"orderid": { "type": "string"},
"orderdetails": {
"type": "object",
"dynamic": true
}
}
}
}
}

PUT /emporders/empid/1
{
"orderid": "order1",
"orderdetails": {
"item1": "Jacket",
"item2": "Shaving kit"
}
}

PUT /emporders/empid/2
{
"orderid": "order2",
"orderdetails": {
"item1": "Cooking Gas Stove"
}
}

4

I know this is an older question and you probably have resolved it by now. But I came across a similar issue and took me a while to figure out.
One way is to use a JOIN in your sql query and then use the aggregate filter. Please see this reference example #4
https://www.elastic.co/guide/en/logstash/current/plugins-filters-aggregate.html#_example_4_no_end_event_and_tasks_come_one_after_the_other

5

Hi @Syed_Ather : I am having a similar requirement and would like to know if this was resolved and if so, through what approach ? Please explain.
Thanks,