How to map multiple values into one value

[sadhanm]

• What Grafana version and what operating system are you using?
  Grafana v10.0.0

• What are you trying to achieve?
  I’m trying to map multiple values into one value.
  

  

The column route contains 4 unique values and appearing many times, they are A100, A110, A120, A140. I want to convert A100 and A110 to group A and A120 and A140 to group B. Then I want to group by route and want to have 2 rows only, A and B and their aggregated value.

• How are you trying to achieve it?
  by using value mapping. This is what I have done, followed by ‘group by’ transformation.
  
  

• What happened?
  It’s grouping the data, but A and B both appearing 2 times. This is what I’m getting-
  

• What did you expect to happen?
  I’m expecting both A and B to be appeared one time each with corresponding aggregated value as group by transformation applied. the expected output should be as below-
  

  

• Can you copy/paste the configuration(s) that you are having problems with?
  N/A

• Did you receive any errors in the Grafana UI or in related logs? If so, please tell us exactly what they were.
  N/A

• Did you follow any online instructions? If so, what is the URL?
  I followed Grafana value mapping documentation and here is the link:
  VALUE MAPPING

[yosiasz]

Welcome @sadhanm

What is your data source?

[sadhanm]

@yosiasz, Thank you for taking time to look into it and replying.
My data source is Infinity, getting data through a json API.

[yosiasz]

please proper sample data from your api? as inline json?

for example

{
  "1701234000": "65.1",
  "1701234300": "65.1",
  "1701234600": "65.1",
  "1701234900": "65.0",
  "1701235200": "64.9"
}

[sadhanm]

My data source is INFINITY

[sadhanm]

Here is the data sample from the api

[{
"Code": "A442",
"Description": "ALL-REPAIR-W100-PAINTED PANEL REQ",
"count": "49",
"route": "A100"
},
{
"Code": "B259",
"Description": "ALL-REPAIR-W110-VQA OVERCHECK",
"count": "3",
"route": "A110"
}]

[joachimschiewek]

Hello,

you can use MIxed as Datasource and map a second table to your data

Use a static datasource as second query to map your values

Then you can join these querys: Mode outer(tabular)

And group them together

So you can always expand your route values on the fly…

Jo

[yosiasz]

I personally try to avoid grafana transformation if at all possible (it can get very hairy and unmaintainable) and try to do what I need in the data source itself

Here is one approach using jsonata which is a feature of infinity plugin. Nice and clean and readable. You can even take it further and do your counts in jsonata itself to get your final data as you want it to look like.

Doing counts (added one more A110 to the json)

[sadhanm]

@joachimschiewek, Thank you for replying and your answers seems to be very straightforward. However, I’m only allowed to use infinity as data source, and I don’t have permission to install ‘Static DS’ as I’m on editor role.

[sadhanm]

Hi @yosiasz,
Thank you for replying. Since I’m very new to Grafana, I’m trying to figure out how to use jsonata without any luck as I haven’t seen any option for jsonata.

[yosiasz]

Searxh through this forum for thr word jsonata and tjere are tons of examples

[sadhanm]

@yosiasz , I was able to use jsonata in a very basic way as i’m very new to it and as I have just have started learning. While a single line of code is working in jsonata, but that very same code (copied and pasted) in uql editor is not working? Please see the image for reference.

using Jsonata:

using UQL editor

I have also tried to put the whole code into one line.But its not getting any data either. Am I not supposed to use exact query that worked in jsonata?

The data has been taken from Grafana playground, however, you can have it here.

Thank you

[sadhanm]

Thanks @yosiasz , it is working now and it gives exactly what I was looking for. Thank you for supporting.

parse-json
| jsonata “[{ ‘warehouse’: ‘A’,‘total’: $sum(ahs.ahsd[country=‘USA’ or country=‘UK’].$number(salary))},{ ‘warehouse’: ‘B’,‘total’: $sum(ahs.ahsd[country=‘Canada’ or country=‘Germany’].$number(salary))}]”