Using the categories of different records in json format from a REST API as an attribute after using infinity datasource

[bnnlukasEviden]

Hi,
i have already addressed this topic in the Grafana forum, but was referred here.

i have a Rest API i am getting the data from in a JSON-format via the Infinity Plugin. The format of the input looks like this:

{
  "TBD": [
    {
      "id": 5374,
      "name": "IncidentTest1",
      "create_date": 1699448036949,
      "org_handle": 205
    }
  ],
  "DenialofService": [
    {
      "id": 5776,
      "name": "IncidentTest2",
      "create_date": 1706789160891,
      "org_handle": 205
    },
    {
      "id": 5775,
      "name": "IncidentTest3",
      "create_date": 1706785738252,
      "org_handle": 205
    }
  ]
}

My goal is to get all the three records as rows in a table to do further transformations. The problem is that i want the top categories (“TBD”, “Denial of Service”) to be added as a feature to the records. So for the first record it should become:

{
  "category": "TBD",
  "id": 5374,
  "name": "Incident Test 1",
  "create_date": 1699448036949,
  "org_handle": 205
}

And the same for the other two records, so the resulting table looks like this:

How can i do this? Maybe with an UQL statement? I am using the Grafana Version 10.3.1.

Thanks a lot in advance

[yesoreyeram]

you may need project kv() and mv-expand commands. Refer UQL docs for more details.

parse-json
| project kv()
| mv-expand "item"="value"
| project "category"="key", "id"="item.id", "name"="item.name", "create_date"=unixtime_milliseconds_todatetime("item.create_date"), "org_handle"="item.org_handle"

[bnnlukasEviden]

Many thanks for your help. It works