Merge a JSON array into a JSON object in Kusto

Question

Let say I have a log analytics table like this:

datatable(Computer:string, TimeGenerated:datetime, data:dynamic)
[
    "comp1",  datetime(2019-02-07 16:31:15), dynamic('[{prop1: 2}, {prop2: 0.8}, {prop3: 12.204532}, {prop4: 0}]'),
    "comp2",  datetime(2019-02-07 16:31:15), dynamic('[{prop1: 2}, {prop2: 0.8}]'),
    "comp3",  datetime(2019-02-07 16:31:15), dynamic('[{prop2: 0.8}, {prop3: 12.204532}, {prop4: 0}]'),
]

When querying, is there a way to merge the json array contained on the data column into a single object to have something as follows:

[{prop1: 2}, {prop2: 0.8}, {prop3: 12.204532}, {prop4: 0}] Becomes {prop1: 2, prop2: 0.8, prop3: 12.204532, prop4: 0}.

[{prop1: 2}, {prop2: 0.8}] becomes {prop1: 2, prop2: 0.8}.

and [{prop2: 0.8}, {prop3: 12.204532}, {prop4: 0}] becomes {prop2: 0.8, prop3: 12.204532, prop4: 0}.

I don't need that array of objects, I need it to be merged on a single object.

Thanks in advance.

score 1 · Accepted Answer · answered Feb 15 '22 at 14:05

mv-apply for the rescue

P.S.
A JSON textual key should be qualified.
Note the changes I made for data column.

datatable(Computer:string, TimeGenerated:datetime, data:dynamic)
[
    "comp1",  datetime(2019-02-07 16:31:15), dynamic([{"prop1": 2}, {"prop2": 0.8}, {"prop3": 12.204532}, {"prop4": 0}]),
    "comp2",  datetime(2019-02-07 16:31:15), dynamic([{"prop1": 2}, {"prop2": 0.8}]),
    "comp3",  datetime(2019-02-07 16:31:15), dynamic([{"prop2": 0.8}, {"prop3": 12.204532}, {"prop4": 0}]),
]
| mv-apply e = data on (summarize data = make_bag(e))

+----------+----------------------+-----------------------------------------------------+
| Computer |    TimeGenerated     |                        data                         |
+----------+----------------------+-----------------------------------------------------+
| comp1    | 2019-02-07T16:31:15Z | {"prop1":2,"prop2":0.8,"prop3":12.204532,"prop4":0} |
| comp2    | 2019-02-07T16:31:15Z | {"prop1":2,"prop2":0.8}                             |
| comp3    | 2019-02-07T16:31:15Z | {"prop2":0.8,"prop3":12.204532,"prop4":0}           |
+----------+----------------------+-----------------------------------------------------+

Merge a JSON array into a JSON object in Kusto

1 Answers1