CODEWITHSUNDEEP
azure
namrata
namrata

Reputation: 2545

Parsing json in kusto query

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

Upvotes: 16

Views: 44956

Answers (4)

englebart
englebart

Reputation: 573

| extend newColumn_s = extract_json("$.jsonFieldName", tostring(JsonBlobField), typeof(string))

Then I could use newColumn_s in subsequent filters. I had to coerce the json blob field via the tostring() function.

Upvotes: 0

viveksharma
viveksharma

Reputation: 715

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

Upvotes: 2

gpkarnik
gpkarnik

Reputation: 39

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

Upvotes: 0

Yoni L.
Yoni L.

Reputation: 25895

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.

Upvotes: 20

Related Questions