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!

Answer 1

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