How to convert json array into columns with custom column header-value info

Question

I've got a kusto table that contains a number of columns and one column is dynamic. I'd like to expand this dynamic column to create extra columns in the result using one field as the header of the column.

Example dynamic column:

[
  {
    "code": "SenderID",
    "value": "XXXX"
  },
  {
    "code": "ReceiverID",
    "value": "YYYY"
  },
  {
    "code": "RecordNumber",
    "value": "00000012345"
  }
  {
    "code": "MachineName",
    "value": "Server1"
  }
]

I'd like to get my current columns from the table along with the expanded dynamic fields as columns like this:

I've tried using mv-expand and evaluate bag_unpack, but it just gives me multiple rows for each record (one row for every item in the json array):

transaction
| mv-expand identifier
| evaluate bag_unpack(identifier)
| where value == "00000012345"

Any ideas if it's possible to format the table in the way I like? I've been scouring different articles and haven't come across this question/answer.

Thank you!

Yoni L. · Accepted Answer · 2021-04-08 19:42:50Z

2

Below is one option

Please don't consider it efficient.
Ideally, you'll reformat the data at its source before ingesting it, to match how you with to query the data, and avoid the runtime overhead of reformatting it each time you run a query.
- If you can't, trying doing it at ingestion time, using an update policy: https://learn.microsoft.com/en-us/azure/data-explorer/kusto/management/updatepolicy

datatable(i:int, d:dynamic)
[
  1, dynamic([
      {    "code": "SenderID",    "value": "XXXX"  },
      {    "code": "ReceiverID",    "value": "YYYY"  },
      {    "code": "RecordNumber",    "value": "00000012345"  },
      {    "code": "MachineName",    "value": "Server1"  }]), 
  2, dynamic([
      {    "code": "SenderID",    "value": "ZZZZ"  },
      {    "code": "ReceiverID",    "value": "WWWW"  },
      {    "code": "RecordNumber",    "value": "000000456789"  },
      {    "code": "MachineName",    "value": "Server2"  }])
]
| mv-apply d on (
    extend key = tostring(d.code)
    | extend value = d.value
    | summarize b = make_bag(pack(key, value))
)
| evaluate bag_unpack(b)

i	MachineName	ReceiverID	RecordNumber	SenderID
1	Server1	YYYY	00000012345	XXXX
2	Server2	WWWW	000000456789	ZZZZ

answered Apr 8, 2021 at 19:42

Yoni L.

26.4k3 gold badges46 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

TechNikephoros Over a year ago

This works, thanks! We'll look into reformatting the data on the source before ingesting.

Collectives™ on Stack Overflow

How to convert json array into columns with custom column header-value info

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related