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.
CodePudding user response:
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} |
---------- ---------------------- -----------------------------------------------------