How to retrieve the value from multiple json data

[max420max]

Hi , I am using Infinite Plugin for Grafana , i have JSON file as below , I tried two approach, 1. UQL query and 2. adding the columns.
How to retrieve the value from multiple array?

{
  "Results": [
    {
      "Name": "device-bff-pdb",
      "Namespace": "default-release-name-deviceportal",
      "Kind": "PodDisruptionBudget",
      "Results": {
        "pdbDisruptionsIsZero": {
          "ID": "pdbDisruptionsIsZero",
          "Message": "Voluntary evictions are possible",
          "Details": null,
          "Success": true,
          "Severity": "warning",
          "Category": "Reliability",
          "Mutations": null
        }
      },
      "PodResult": null,
      "CreatedTime": "0001-01-01T00:00:00Z"
    },
    {
      "Name": "ca-certificates-configmap",
      "Namespace": "default-release-name-aops",
      "Kind": "ConfigMap",
      "Results": {
        "sensitiveConfigmapContent": {
          "ID": "sensitiveConfigmapContent",
          "Message": "The ConfigMap does not contain potentially sensitive content in its keys and values",
          "Details": null,
          "Success": true,
          "Severity": "danger",
          "Category": "Security",
          "Mutations": null
        }
      },
      "PodResult": null,
      "CreatedTime": "0001-01-01T00:00:00Z"
    }
  ]
}

======================

Results.Results.pdbDisruptionsIsZero.Message

Results.Results.sensitiveConfigmapContent.Message

how to retrive those two value ?

[yesoreyeram]

You can use the backend parser and Results.*.* (jsonata selector) as your root selector.

[yesoreyeram]

In the rows/Root selector, you can write a proper jsonata selector instead of just Results

[max420max]

I am new in JSON query , not sure where is wrong . i tried two different query but not getting expected output.

Results.{
Name: Name,
Namespace: Namespace,
Kind: Kind,
Message: Results.pdbDisruptionsIsZero.Message & Results.sensitiveConfigmapContent.Message,
Severity: Results.pdbDisruptionsIsZero.Severity & Results.sensitiveConfigmapContent.Severity,
Category: Results.pdbDisruptionsIsZero.Category & Results.sensitiveConfigmapContent.Category,
Success: Results.pdbDisruptionsIsZero.Success & Results.sensitiveConfigmapContent.Success
}

Results.{
"Name": Name,
"Namespace": Namespace,
"Kind": Kind,
"Results": Results.*{
"ID": ID,
"Message": Message,
"Success": Success,
"Severity": Severity,
"Category": Category
}
}

The same query gives the correct output in https://try.jsonata.org/ but when same query i am trying to use in Grafana Row/Root it does not give output.

However Results.. gives the result but not fetching the data of three columns(Kind , Namespace , Name)

[yesoreyeram]

Can you share the query you tried which was working jsonata?

[max420max]

Query shared in below thread !

[max420max]

json-data.json
For reference, i am attaching the complete JSON data,
The below query gives me the CORRECT result except for the few arrays (automountServiceAccountToken, cpuLimitsMissing)''

"ContainerResults": [
{
"Name": "macma-configuration-operator-pod",
"Results": {
"cpuLimitsMissing": {
"ID": "cpuLimitsMissing",
"Message": "CPU limits are set",
"Details": null,
"Success": true,
"Severity": "warning",
"Category": "Efficiency",
"Mutations": null
},
"PodResult": {
"Name": "",
"Results": {
"automountServiceAccountToken": {
"ID": "automountServiceAccountToken",
"Message": "The ServiceAccount will be automounted",
"Details": null,
"Success": false,
"Severity": "warning",
"Category": "Security",
"Mutations": null
},

How to retrive those two array data? I tried to adding those two array ( cpuLimitsMissing , automountServiceAccountToken) in query but it does not work.

Below query too long , how to improve here ?

parse-json
| project "Results"
| project-away "CreatedTime"
| extend "Kind", "Name","Namespace", "ID"=strcat("Results.pdbDisruptionsIsZero.ID","Results.sensitiveConfigmapContent.ID","Results.rolebindingClusterAdminClusterRole.ID","Results.rolebindingClusterAdminRole.ID","Results.rolebindingClusterRolePodExecAttach.ID","Results.rolebindingRolePodExecAttach.ID","Results.rolePodExecAttach.ID"),"Message"=strcat("Results.pdbDisruptionsIsZero.Message","Results.sensitiveConfigmapContent.Message","Results.rolebindingClusterAdminClusterRole.Message","Results.rolebindingClusterAdminRole.Message","Results.rolebindingClusterRolePodExecAttach.Message","Results.rolebindingRolePodExecAttach.Message","Results.rolePodExecAttach.Message"), "Severity"=strcat("Results.pdbDisruptionsIsZero.Severity","Results.sensitiveConfigmapContent.Severity", "Results.rolePodExecAttach.Severity"),"Category"=strcat("Results.pdbDisruptionsIsZero.Category","Results.sensitiveConfigmapContent.Category","Results.rolebindingClusterAdminClusterRole.Category","Results.rolebindingClusterAdminRole.Category","Results.rolebindingClusterRolePodExecAttach.Category","Results.rolebindingRolePodExecAttach.Category","Results.rolePodExecAttach.Category"), "Success"=strcat("Results.pdbDisruptionsIsZero.Success","Results.sensitiveConfigmapContent.Success","Results.rolebindingClusterAdminClusterRole.Success","Results.rolebindingClusterAdminRole.Success","Results.rolebindingClusterRolePodExecAttach.Success","Results.rolebindingRolePodExecAttach.Success","Results.rolePodExecAttach.Success")
| project-away "PodResult", "Results"

[max420max]

json-data.json
For reference, i am attaching the complete JSON data,
The below query gives me the CORRECT result except for the few arrays (automountServiceAccountToken, cpuLimitsMissing)''

"ContainerResults": [
{
"Name": "macma-configuration-operator-pod",
"Results": {
"cpuLimitsMissing": {
"ID": "cpuLimitsMissing",
"Message": "CPU limits are set",
"Details": null,
"Success": true,
"Severity": "warning",
"Category": "Efficiency",
"Mutations": null
},
"PodResult": {
"Name": "",
"Results": {
"automountServiceAccountToken": {
"ID": "automountServiceAccountToken",
"Message": "The ServiceAccount will be automounted",
"Details": null,
"Success": false,
"Severity": "warning",
"Category": "Security",
"Mutations": null
},

How to retrive those two array data? I tried to adding those two array ( cpuLimitsMissing , automountServiceAccountToken) in query but it does not work.

Below query too long , how to improve here ?

parse-json
| project "Results"
| project-away "CreatedTime"
| extend "Kind", "Name","Namespace", "ID"=strcat("Results.pdbDisruptionsIsZero.ID","Results.sensitiveConfigmapContent.ID","Results.rolebindingClusterAdminClusterRole.ID","Results.rolebindingClusterAdminRole.ID","Results.rolebindingClusterRolePodExecAttach.ID","Results.rolebindingRolePodExecAttach.ID","Results.rolePodExecAttach.ID"),"Message"=strcat("Results.pdbDisruptionsIsZero.Message","Results.sensitiveConfigmapContent.Message","Results.rolebindingClusterAdminClusterRole.Message","Results.rolebindingClusterAdminRole.Message","Results.rolebindingClusterRolePodExecAttach.Message","Results.rolebindingRolePodExecAttach.Message","Results.rolePodExecAttach.Message"), "Severity"=strcat("Results.pdbDisruptionsIsZero.Severity","Results.sensitiveConfigmapContent.Severity", "Results.rolePodExecAttach.Severity"),"Category"=strcat("Results.pdbDisruptionsIsZero.Category","Results.sensitiveConfigmapContent.Category","Results.rolebindingClusterAdminClusterRole.Category","Results.rolebindingClusterAdminRole.Category","Results.rolebindingClusterRolePodExecAttach.Category","Results.rolebindingRolePodExecAttach.Category","Results.rolePodExecAttach.Category"), "Success"=strcat("Results.pdbDisruptionsIsZero.Success","Results.sensitiveConfigmapContent.Success","Results.rolebindingClusterAdminClusterRole.Success","Results.rolebindingClusterAdminRole.Success","Results.rolebindingClusterRolePodExecAttach.Success","Results.rolebindingRolePodExecAttach.Success","Results.rolePodExecAttach.Success")
| project-away "PodResult", "Results"