madjazz
madjazz

Reputation: 31

retrieving array from json as a table in Kusto

I am trying to retrieve all the rows from a Json array. The json is similar to the one shown below.

 {
  "messageId": "123",
  "fileName": "abc.json",
  "payload": {
    "routeStatus": "FINAL",
    "activities": [
      {
        "durationSeconds": 1800,
        "location": {
          "longitude": 151.2603,
          "latitude": -33.7644
        },
        "type": "DEPART",
        "slot": {
          "start": "2020-04-14T19:05:00.0000000Z",
          "cost": null,
          "end": "2020-04-15T03:30:00.0000000Z"
        }
      },
      {
        "durationSeconds": 1100,
        "type": "DRIVE"
      },
      {
        "durationSeconds": 360,
        "location": {
          "longitude": 151.21814,
          "latitude": -33.756319
        },
        "type": "SERVICE",
        "slot": {
          "start": "2020-04-14T20:00:00.0000000Z",
          "cost": null,
          "end": "2020-04-15T00:45:00.0000000Z"
        }
      },
      {
        "durationSeconds": 164,
        "type": "DRIVE"
      }
    ],
    "truck": "XYZ"
  }
}

I would like to get all the attributes under the activities in a table as I would need to filter and join to other tables. I am only able to retrieve one row from the array. Any pointers would be helpful.

Upvotes: 0

Views: 239

Answers (1)

Yoni L.
Yoni L.

Reputation: 25895

you can use mv-expand or mv-apply.

for example:

print d = dynamic({
  "messageId": "123",
  "fileName": "abc.json",
  "payload": {
    "routeStatus": "FINAL",
    "activities": [
      {
        "durationSeconds": 1800,
        "location": {
          "longitude": 151.2603,
          "latitude": -33.7644
        },
        "type": "DEPART",
        "slot": {
          "start": "2020-04-14T19:05:00.0000000Z",
          "cost": null,
          "end": "2020-04-15T03:30:00.0000000Z"
        }
      },
      {
        "durationSeconds": 1100,
        "type": "DRIVE"
      },
      {
        "durationSeconds": 360,
        "location": {
          "longitude": 151.21814,
          "latitude": -33.756319
        },
        "type": "SERVICE",
        "slot": {
          "start": "2020-04-14T20:00:00.0000000Z",
          "cost": null,
          "end": "2020-04-15T00:45:00.0000000Z"
        }
      },
      {
        "durationSeconds": 164,
        "type": "DRIVE"
      }
    ],
    "truck": "XYZ"
  }
})
| mv-expand d.payload.activities
| project durationSeconds = tolong(d_payload_activities.durationSeconds), type = tostring(d_payload_activities.type)

Upvotes: 1

Related Questions