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

Hi,
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”: “Incident Test 1”,
“create_date”: 1699448036949,
“org_handle”: 205
}
],
“Denial of Service”: [
{
“id”: 5776,
“name”: “Incident Test 2”,
“create_date”: 1706789160891,
“org_handle”: 205
},
{
“id”: 5775,
“name”: “Incident Test 3”,
“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

Welcome @bnnlukaseviden

Yeah, I would say UQL is going to get you there, but being so specialized, asking on the Infinity plugin’s discussion page might be a better place to ask. If you do get the solution, please post back here so others can find it in the future.

Thanks @grant2. I asked the question in the Infinity forum and the problem was solved.

This is the solution as an UQL statement (Credits to yesoreyeram (Sriram) (github.com)):

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"
2 Likes