azure-log-analytics - 如何在 kusto 查询语言中解析 json 数组
问题描述
如何在 kusto 查询语言中解析 json 数组。我有一个输出列,它具有 JSON 数组格式的值,如下所示。我需要解析它以获取两列形式的值。
{"count": 14
"value": [
{
"Total_Record_Count": 16608,
"date": "2021-03-01T00:00:00Z"
},
{
"Total_Record_Count": 27254,
"date": "2021-02-24T00:00:00Z"
},
{
"Total_Record_Count": 6,
"date": "2021-02-01T00:00:00Z"
},
{
"Total_Record_Count": 26964,
"date": "2021-01-15T00:00:00Z"
},
{
"Total_Record_Count": 134516,
"date": "2020-12-18T00:00:00Z"
},
{
"Total_Record_Count": 27345,
"date": "2020-12-16T00:00:00Z"
},
{
"Total_Record_Count": 521,
"date": "2020-12-01T00:00:00Z"
},
{
"Total_Record_Count": 4,
"date": "2020-11-02T00:00:00Z"
},
{
"Total_Record_Count": 6,
"date": "2020-10-01T00:00:00Z"
},
{
"Total_Record_Count": 1,
"date": "2020-09-01T00:00:00Z"
},
{
"Total_Record_Count": 3,
"date": "2020-08-03T00:00:00Z"
},
{
"Total_Record_Count": 18,
"date": "2020-07-01T00:00:00Z"
},
{
"Total_Record_Count": 18754,
"date": "2020-06-16T00:00:00Z"
},
{
"Total_Record_Count": 4451898,
"date": "2020-06-08T00:00:00Z"
}
]}
如何使用输出列名而不是使用完整的 json 数组来实现它。
解决方案
请参阅下面的示例,该示例使用 mv-expand 运算符将数组分成行。 https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/mvexpandoperator
print d = dynamic(
{"count": 14,
"value": [
{
"Total_Record_Count": 16608,
"date": "2021-03-01T00:00:00Z"
},
{
"Total_Record_Count": 27254,
"date": "2021-02-24T00:00:00Z"
},
{
"Total_Record_Count": 6,
"date": "2021-02-01T00:00:00Z"
},
{
"Total_Record_Count": 26964,
"date": "2021-01-15T00:00:00Z"
},
{
"Total_Record_Count": 134516,
"date": "2020-12-18T00:00:00Z"
},
{
"Total_Record_Count": 27345,
"date": "2020-12-16T00:00:00Z"
},
{
"Total_Record_Count": 521,
"date": "2020-12-01T00:00:00Z"
},
{
"Total_Record_Count": 4,
"date": "2020-11-02T00:00:00Z"
},
{
"Total_Record_Count": 6,
"date": "2020-10-01T00:00:00Z"
},
{
"Total_Record_Count": 1,
"date": "2020-09-01T00:00:00Z"
},
{
"Total_Record_Count": 3,
"date": "2020-08-03T00:00:00Z"
},
{
"Total_Record_Count": 18,
"date": "2020-07-01T00:00:00Z"
},
{
"Total_Record_Count": 18754,
"date": "2020-06-16T00:00:00Z"
},
{
"Total_Record_Count": 4451898,
"date": "2020-06-08T00:00:00Z"
}
]})
| project Value = d.['value']
| mv-expand Value
| project Count = tolong(Value.['Total_Record_Count']), Date = todatetime(Value.['date'])
数数 | 日期 |
---|---|
4451898 | 2020-06-08 00:00:00.0000000 |
18754 | 2020-06-16 00:00:00.0000000 |
18 | 2020-07-01 00:00:00.0000000 |
3 | 2020-08-03 00:00:00.0000000 |
1 | 2020-09-01 00:00:00.0000000 |
6 | 2020-10-01 00:00:00.0000000 |
4 | 2020-11-02 00:00:00.0000000 |
521 | 2020-12-01 00:00:00.0000000 |
27345 | 2020-12-16 00:00:00.0000000 |
134516 | 2020-12-18 00:00:00.0000000 |
26964 | 2021-01-15 00:00:00.0000000 |
6 | 2021-02-01 00:00:00.0000000 |
27254 | 2021-02-24 00:00:00.0000000 |
16608 | 2021-03-01 00:00:00.0000000 |
推荐阅读
- opentext - 简单 SQLTalk 的替代品
- xml - 西门子开放 XML 文件
- sql - SQL 聚合函数的数据类型(COUNT、SUM、AVG)
- git - 从已在本地删除的旧提交中删除已提交的文件
- clang - 有没有办法在没有 sudo 的情况下在 centOS 上获取 llvm/clang?
- vue.js - 未知的自定义元素:
使用 vuetify 组件时 - go - 覆盆子信标可以检索任何数据吗
- android - 在一个片段中设置 ViewModel,并使用 NavHost 在 SingleActivity 应用程序中获取其他片段中的新数据?
- mysql - 选择中的MySQL中值子查询
- python - 从 np 数组中删除随机行