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

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
  }
]
azure-data-explorerkql


Solution 1:[1]

AzLogsCoverage
| extend Logs = case(isnull(Logs) or isempty(Logs), 'N/A', Logs)
| extend Metrics = case(isnull(Metrics) or isempty(Metrics), 'N/A', Metrics)
| where Logs <> 'N/A'
| extend LogsDynamic = todynamic(Logs)
| extend MetricsDynamics = todynamic(Metrics)
| mv-expand LogsDynamic, MetricsDynamics
| project SubscriptionId, ResourceId, ResourceName, ResourceType, DiagnosticSettingStatus, DiagnosticSettingId, DiagnosticSettingName, DiagnosticSettingType, LAworkspaceId, LAworkspaceRetentionPeriod,   
LogsDynamic.category,LogsDynamic.enabled,LogsDynamic.categoryGroup, MetricsDynamics.category, MetricsDynamics.categoryGroup, MetricsDynamics.enabled, MetricsDynamics.retentionPolicy.enabled , MetricsDynamics.retentionPolicy.days

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Vinny

Related Questions