Converting Un-Pivoted SQL Data to Nested Datatype using Logstash JDBC Input

Elastic Stack Logstash
1

Hi,

I have a table in SQL Server which looks like this:

The data is in an un-pivoted format:

CustomerID, AddressNumber, AddressValue
1, 1, a
1, 2, b
1, 3, c
1, 4, d
1, 5, e
2, 1, f
2, 2, g
2, 2, h
2, 2, i
2, 2, k
...

I can easily pivot the table to look like:
CustomerID, Address1, Address2, Address3, Address4, Address5
1, a, b, c, d, e
2, f, g, h, i, k
...

I was wondering if there is a way while using logstash, to convert each row in this pivoted table into a document which looks like:

{
  "CustomerID":1,
  "Address": 
  [
    {
      "AddressID":1,
      "AddressValue": "a" 
    },
    {
      "AddressID":2,
      "AddressValue": "b"
    },
    {
      "AddressID":3,
      "AddressValue": "c"
    },
    {
      "AddressID":4,
      "AddressValue": "d"
    },
    {
      "AddressID":5,
      "AddressValue": "e"
    }
  ]
}

Thanks.

2

Bump.

3

Is it possible to achieve this using Logstash?

Thanks.

4

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