Extracting values from JSON column using KQL (Azure Data Explorer)

Question

Can you please tell me how to extract values of category, enabled and categoryGroup from the below JSON column in KQL(Azure Data Explorer).

Below JSON value is exactly what I see in the column called "Logs". I see that the Column Logs is defined as string datatype in table

AzLogsCoverage 
| extend Logs = case(isnull(Logs) or isempty(Logs), 'N/A', Logs)
| where Logs <> 'N/A'
| project Logs
| extend LogsCategory = parse_json(Logs).category

[
  {
    "category": "Administrative",
    "enabled": true,
    "categoryGroup": null
  },
  {
    "category": "Security",
    "enabled": false,
    "categoryGroup": null
  },
  {
    "category": "ServiceHealth",
    "enabled": false,
    "categoryGroup": null
  },
  {
    "category": "Alert",
    "enabled": false,
    "categoryGroup": null
  },
  {
    "category": "Recommendation",
    "enabled": false,
    "categoryGroup": null
  },
  {
    "category": "Policy",
    "enabled": false,
    "categoryGroup": null
  },
  {
    "category": "Autoscale",
    "enabled": false,
    "categoryGroup": null
  },
  {
    "category": "ResourceHealth",
    "enabled": false,
    "categoryGroup": null
  }
]

Answer 1

if the input is of type string, you first need to invoke parse_json() on it, to make it of type dynamic.

Then, you can use mv-expand/mv-apply to expand elements in the array, and then you can explicitly project properties of interest for each element.

for example:

print input = ```[
  {
    "category": "Administrative",
    "enabled": true,
    "categoryGroup": null
  },
  {
    "category": "Security",
    "enabled": false,
    "categoryGroup": null
  },
  {
    "category": "ServiceHealth",
    "enabled": false,
    "categoryGroup": null
  },
  {
    "category": "Alert",
    "enabled": false,
    "categoryGroup": null
  },
  {
    "category": "Recommendation",
    "enabled": false,
    "categoryGroup": null
  },
  {
    "category": "Policy",
    "enabled": false,
    "categoryGroup": null
  },
  {
    "category": "Autoscale",
    "enabled": false,
    "categoryGroup": null
  },
  {
    "category": "ResourceHealth",
    "enabled": false,
    "categoryGroup": null
  }
]```
| extend d = parse_json(input)
| mv-apply d on (
    project Category = tostring(d.category),
            Enabled = tobool(d.enabled),
            CategoryGroup = tostring(d.categoryGroup)
)
| project-away input

Category	Enabled	CategoryGroup
Administrative	True
Security	False
ServiceHealth	False
Alert	False
Recommendation	False
Policy	False
Autoscale	False
ResourceHealth	False