Parse array & json to right numbers of column

Question

I'm think this is maybe basic question but any advise will be helpful.

One of my table has a field with following value :

[{"key":"app1","name":"Application 1","value":false,"group":"dashboard"},{"key":"app2","name":"Application 2","value":true,"group":"support"}]

I do understand it's an array with JSON value.

I would like to get the following "conversion" + that automatically adapt to json data. If I have 3 applications, it creates 3 columns etc etc.

Application 1	Application 2
false	true

I suppose I have to use mv-expand and parse_json but not success for now :D any advise is welcome

Answer 1

I suppose I have to use mv-expand and parse_json but not success for now:

To achieve your requirement, you need to use mv-expand like below. It splits multi-value arrays or data into multiple records further.

Once it's done, I have used pivot as it shifts the unique values from one column in the input table into multiple columns in the output table and it is clearly detailed in the given MS Doc.

let table =
datatable(field: dynamic)
[
    dynamic([{"key":"app1","name":"Application 1","value":false,"group":"dashboard"},
             {"key":"app2","name":"Application 2","value":true,"group":"support"},
             {"key":"app2","name":"Application 3","value":true,"group":"support"},
             {"key":"app2","name":"Application 4","value":false,"group":"dashboard"}])
];
table
| mv-expand data = field
| project name = tostring(data.name), value = tostring(data.value)
| evaluate pivot(name, any(value))