Parsing json in kusto query

Question

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

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"
}}

Answer 1

Try |extend Status = extractjson("$.",toString([column]))

Answer 2

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. 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).

Here's the example:

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)

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.