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

Yoni L. · Answer 1 · 2019-02-18T16:39:50.130

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

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).
Then you can access the Date property in your JSON value and use todatetime() to cast it to type datetime.
Afterwards, you can filter by your external parameter (dateTimeLowerBound in the example below).
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.

suppose he had an array of object with the same properties, how can you deal with that ? I posted a SO question about this here : https://stackoverflow.com/questions/54746111/how-to-transform-a-json-array-of-objects-to-a-kusto-table — , Feb 18 '19 at 11:42

score 1 · Answer 2 · answered Sep 22 '22 at 04:50

Its is simple when you want to parse JSON that is in column and want to have where clause on JSON properties -

        tablename 
        | extend prop= parse_json(jsonColumn) 
        | where prop.description == 'My Description' 
        | sort by nameCoumn desc | take 1

score 0 · Answer 3 · answered May 14 '21 at 09:25

0

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

answered May 14 '21 at 09:25

gpkarnik

29
1
3

Parsing json in kusto query

3 Answers3

Linked