时间戳

首页 > 解决方案 > 展平 kusto 列中的嵌套 json

json - 展平 kusto 列中的嵌套 json

问题描述

我有一个 kusto 表，其中一列是带有嵌套 json 的动态类型，如何在 kusto 中展平？mv-expand 只做一个级别。

column1 : timetsamp
column2 : id
column3 : json object

timestamp   id  value
2020-10-13 22:42:05.0000000 d0  "{
  ""value"": ""0"",
  ""max"": ""0"",
  ""min"": ""0"",
  ""avg"": ""0""
}"
2020-10-13 22:42:05.0000000 d0  "{
  ""sid"": ""a0"",
  ""data"": {
    ""x"": {
      ""a"": {
        ""t1"": ""2020-10-13T22:46:50.1310000Z"",
        ""m1"": 446164,
        ""m4"": {
          ""m41"": ""abcd"",
          ""m42"": 1234
        }
      }
    }
  }
}"

#update2 ：我可以弄错键，但不能弄错值

let testJson = datatable(timestamp : datetime, id : string, value : dynamic )
    [datetime(2020-10-13T22:42:05Z), 'd0', dynamic({"value":"0","max":"0","min":"0","avg":"0"}),
     datetime(2020-10-13T22:42:05Z), 'd1', dynamic({"sid":"a0","data":{"x":{"a":{"t1":"2020-10-13T22:46:50.131Z","m1":446164,"m4":{"m41":"abcd","m42":1234}}}}})];
testJson
| extend key=treepath(value)
| mv-expand key
| extend value1 = value[tostring(key)]

标签： jsonflattenazure-data-explorer

解决方案

您可以多次调用 mv-expand：

let _data = datatable (column1:datetime , column2:string , column3:dynamic )
[
datetime(2020-10-13 22:42:05.0000000), 'd0', dynamic({
  "value": "0",
  "max": "0",
  "min": "0",
  "avg": "0"
}),
datetime(2020-10-13 22:42:05.0000000), 'd0', dynamic({
  "sid": "a0",
  "data": {
    "x": {
      "a": {
        "t1": "2020-10-13T22:46:50.1310000Z",
        "m1": 446164,
        "m4": {
          "m41": "abcd",
          "m42": 1234
        }
      }
    }
  }
})
];
_data
| mv-expand column3
| mv-expand more_data=column3.data.x.a
| mv-expand more_data_m4=more_data.m4

您还可以使用以下方法将动态字段提升到列中evaluate bag_unpack()： https ://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/bag-unpackplugin

let _data = datatable (column1:datetime , column2:string , column3:dynamic )
[
datetime(2020-10-13 22:42:05.0000000), 'd0', dynamic({
  "value": "0",
  "max": "0",
  "min": "0",
  "avg": "0"
}),
datetime(2020-10-13 22:42:05.0000000), 'd0', dynamic({
  "sid": "a0",
  "data": {
    "x": {
      "a": {
        "t1": "2020-10-13T22:46:50.1310000Z",
        "m1": 446164,
        "m4": {
          "m41": "abcd",
          "m42": 1234
        }
      }
    }
  }
})
];
_data
| mv-expand column3
| extend expanded_data = column3.data.x.a
| evaluate bag_unpack(expanded_data)

推荐阅读