在Kusto查询中解析json [英] Parsing json in kusto query

问题描述

如何使用KUSTO查询从JSON提取单个值.

How can I extract individual values from a JSON using KUSTO query.

我希望能够读取SourceSystemId，Message的值并投影这些值.我还想在以下JSON中使用date作为过滤器.并且仅投影日期大于作为外部参数提供的日期的那些记录.

I want to be able to read the value for SourceSystemId, Message and project these values. I also want to use date in the following JSON as a filter. And project only those records where date greater than a date supplied as an external parameter.

{{
  "Status": 2,
  "SourceSystemId": "4",
  "RequisitionId": null,
  "Errors": [
    {
      "Code": "8002",
      "Message": "some message",
      "FieldName": "VendorNumber",
      "PartNumber": null
    },
    {
      "Code": "8003",
      "Message": "",
      "FieldName": "PartNumber",
      "PartNumber": ""
    }
  ],
  "SuppName": "SomeSupp",
 "Date":"22/2/2017"
}}

推荐答案

假设您有一个名为 T 的表，该表具有一个名为 MyString 的列，该表用于存储您的JSON值，其类型为 string (在下面的示例中定义了这样的表).

Let's assume you have a table named T, with a column named MyString, which stores your JSON values and is typed as string (such a table is defined below for the example).

1. 您首先需要在列上调用 parse_json()(除非已在其中键入了 dynamic 而不是 string )在这种情况下，您可以跳过此步骤.)
2. 然后，您可以在JSON值中访问 Date 属性，并使用 todatetime()将其强制转换为 datetime .
>
3. 然后，您可以按外部参数(在下面的示例中为 dateTimeLowerBound )进行过滤.
4. 最后-您可以 project 感兴趣的相关属性( Message 位于 Errors 数组的第一个元素中，然后 SourceSystemId )，您可以使用 tolong()将它们转换为它们的预期类型(例如 long 和 string 分别是 tostring()).

1. You'll first need to invoke parse_json() on your column (unless it's already typed as dynamic and not as string, in which case you can skip this step).
2. Then you can access the Date property in your JSON value and use todatetime() to cast it to type datetime.
3. Afterwards, you can filter by your external parameter (dateTimeLowerBound in the example below).
4. Lastly - you can project the relevant properties you're interested in (Message is in the first element in the Errors array, and SourceSystemId), and you can cast them to their expected types (e.g. long and string using tolong() and tostring() respectively).

这里是例子:

let dateTimeLowerBound = datetime(2017-01-21);
let T = datatable(MyString:string) // this table is just for the example
[
'{"Status": 2, "SourceSystemId": "4", "RequisitionId": null, "Errors": [ { "Code": "8002", "Message": "some message", "FieldName": "VendorNumber", "PartNumber": null }, { "Code": "8003", "Message": "", "FieldName": "PartNumber", "PartNumber": "" } ], "SuppName": "SomeSupp", "Date":"2017-02-22"}'
];
T 
| project MyJson = parse_json(MyString)
| where todatetime(MyJson.Date) > dateTimeLowerBound
| project SourceSystemId = tolong(MyJson.SourceSystemId), Message = tostring(MyJson.Errors[0].Message)

此示例的输出应该是一个表，该表包含2列，分别为 long 和 string类型的名称为 SourceSystemId 和 Message 的表，并且值分别为 4 和 some message .

The output of this example should be a table with 2 columns, named SourceSystemId and Message, of types long and string, and with the values 4 an some message, respectively.

这篇关于在Kusto查询中解析json的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！