How to combine values in different JSON areas in Kusto?

Question

I am currently struggling with Kusto to get the data projected in the way I need it.

I found a solution for the following example already:

print sampleData = dynamic({
    "url": "/api/rest/v2/services/bacnet/remote/devices",
    "status": 200,
    "headers": {
        "content-length": "2514",
        "content-type": "application/json"
    },
    "body": {
        "1111": {
            "objects": {
                "analog-inputs": {
                    "0": {
                        "units": "DegreesCelsius",
                        "present-value": 21.25
                    },
                    "1": {
                        "units": "PercentRelativeHumidity",
                        "present-value": 46.2
                    }
                }
            }
        },
        "2222": {
            "objects": {
                "analog-inputs": {
                    "0": {
                        "units": "DegreesCelsius",
                        "present-value": 22.18
                    },
                    "1": {
                        "units": "PercentRelativeHumidity",
                        "present-value": 46.7
                    }
                }
            }
        }
    }
})
| mv-expand kind=array l=sampleData.body
| project deviceId = tostring(l[0]), objectValue = l[1]
| mv-expand kind=array objectValue.objects
| extend inputType = tostring(objectValue_objects[0])
| mv-expand kind=array sensorReading = objectValue_objects[1]
| extend objectId = tostring(sensorReading[0]), units = tostring(sensorReading[1].units), sensorValue = sensorReading[1].['present-value']
| project-away objectValue, objectValue_objects, sensorReading
| extend twinId = strcat(deviceId, inputType, objectId)
| lookup kind=leftouter (
    datatable (twinId: string, modelId:string) [
        "1111analog-inputs0", "modelid123"
    ]
    ) on twinId

but I am not able to find a solution for this one:

print sampleData = dynamic({
  "url": "/api/rest/v2/services/bacnet",
  "status": 200,
  "headers": {
    "content-length": "1067",
    "content-type": "application/json"
  },
  "body": {
    "local": {
      "objects": {
        "devices": {
          "5000": {
            "object-name": "ABC-LDN-DEF-GHIJ"
          },
          "4194303": {
            "object-name": "ABC-LDN-DEF-GHIJ"
          }
        },
        "analog-values": {
          "1": {
            "units": "NoUnits",
            "present-value": 0
          },
          "2": {
            "units": "NoUnits",
            "present-value": 122
          }
        }
      }
    }
  }
})

The challenge for me is, to print each analog-values element as one row as did above and also add a substring(['object-name'], 4, 3) of the "object-name" of the first "devices" element to it. The reason is, that inside the object-name is the location configured (LDN) what I need.

My expected output would look like:

Can someone help me out?

Answer 1

Try with the below code to print analog-values element as one row and location as LDN and also to get the required output.

print sampleData = dynamic({
  "url": "/api/rest/v2/services/bacnet",
  "status": 200,
  "headers": {
    "content-length": "1067",
    "content-type": "application/json"
  },
  "body": {
    "local": {
      "objects": {
        "devices": {
          "5000": {
            "object-name": "ABC-LDN-DEF-GHIJ"
          },
          "4194303": {
            "object-name": "ABC-LDN-DEF-GHIJ"
          }
        },
        "analog-values": {
          "1": {
            "units": "DegreesCelsius",
            "present-value": 0
          },
          "2": {
            "units": "PercentRelativeHumidity",
            "present-value": 122
          }
        }
      }
    }
  }
})

| extend device_id = tostring(bag_keys(sampleData.body.local.objects.devices)[0])
| extend object_name = tostring(sampleData.body.local.objects.devices[device_id]["object-name"])
| extend location = tostring(split(object_name, "-")[1])
| extend analog_1_units = tostring(sampleData.body.local.objects["analog-values"]["1"].units)
| extend analog_2_units = tostring(sampleData.body.local.objects["analog-values"]["2"].units)
| extend presentValue_0 = tostring(sampleData.body.local.objects["analog-values"]["1"]["present-value"])
| extend presentValue_1 = tostring(sampleData.body.local.objects["analog-values"]["2"]["present-value"])
| project location, datapointId = tostring(bag_keys(sampleData.body)[0]), inputType = 'analog-values', 
  presentValue = pack_array(presentValue_0, presentValue_1),
  units = pack_array(analog_1_units, analog_2_units)
| mv-apply presentValue, units on (
    extend objectId = row_number()
    | extend twinId = strcat(datapointId, ".analog-values.", presentValue),
           units = tostring(units)
)

Output:

Answer 2

You have to split devices and analog-values first into two colums:

| extend objects=objectValue.objects, sensorReading=objectValue.objects['analog-values'] 

extract the device location and finally do the mv-expand to get the sensorReadings.

Final KQL looks like:

| mv-expand kind=array l=sampleData.body
| project datapointId = tostring(l[0]), objectValue = l[1]
| extend objects=objectValue.objects, sensorReading=objectValue.objects['analog-values'] 
| extend device_id = tostring(bag_keys(objects.devices)[0])
| extend object_name = tostring(objects.devices[device_id]["object-name"]) 
| extend location = tostring(split(object_name, "-")[1])
| project-away objects, objectValue
| mv-expand kind=array sensorReading
| extend units = tostring(sensorReading[1].units), sensorValue = sensorReading[1].['present-value']
| project-away sensorReading