Changing the input data in logstash using a filter

Question

I have my input data from a table. The table data looks like <Customer_id> <Item_id> <Item name>. For every item brought by customer, there is a separate row in the table. For example, if c1 buys i1,i2,i3,i4,i5 It will have 5 rows in the table.

Now the data that I want to insert into elasticsearch is in this some way:

{
  "c1": [
    {
      "item_id": "i1",
      "item_name": "ABC"
    },
    {
      "item_id": "i2",
      "item_name": "XYZ"
    },
    .....
  ],
  "c2": [
    {
      "item_id": 4,
      "item_name": "PQR"
    }
  ]
}

How can I modify the input as above in logstash ?

Also my schema looks like this :

Item : item_id , item_name

Buy: cust_id, item_id

Also Can you please suggest the SQL query to be made in order to get the above output?

Answer 1

The way I would approach this is by creating an SQL query that groups those rows on Customer_ID together and uses GROUP_CONCAT to gather all items of the group.

Then, you can use the logstash jdbc input with the SQL query you came up with above and you should be good.

UPDATE

I've reworked your SQL query a little bit like this:

SELECT CONCAT('{"',cust_id,'": [',GROUP_CONCAT(CONCAT('{"item_id":',buy.item_id,','),CONCAT('"item_name": "',item.item_name,'"}')), ']}') 
FROM item, buy
WHERE buy.item_id = item.item_id 
GROUP BY cust_id

which produces rows like this, which are pretty close to what you need:

{"1": [{"item_id":1,"item_name": "abc"},{"item_id":2,"item_name": "xyz"}]}
{"2": [{"item_id":4,"item_name": "pqr"}]}