How to map values from SQL Query to Series in a Pie Chart with Echarts

[Jorge]

Hi @mikhailvolkov.

I am using echart plugin in Grafana 9.3.1. I am trying to do a Pie Chart taking data from a SQL Query. The SQL query is:

SELECT
MODEL,
COUNT(1) AS NUMBER
FROM INVENTORY
GROUP BY 1

Model field has String values with 5 diferents categories
Number is the amount of rows for each model
The query name in grafana is “Inventory”

I wanted to map these two fields into a Pie Chart (Echarts). So, I code as follow:

let model = ,
number = ;

data.series.map((s) => {
if (s.refId === ‘Inventory’) {
model= s.fields.find((f) => f.name === ‘model’).values.buffer;
number = s.fields.find((f) => f.name === ‘number’).values.buffer;
}
});

return {
tooltip: {
trigger: ‘item’
},
legend: {
top: ‘5%’,
left: ‘center’
},
series: [
{
name: ‘Categories’,
type: ‘pie’,
radius: [‘40%’, ‘70%’],
avoidLabelOverlap: false,
itemStyle: {
borderRadius: 10,
borderColor: ‘#fff’,
borderWidth: 2
},
label: {
show: false,
position: ‘center’
},
emphasis: {
label: {
show: true,
fontSize: 40,
fontWeight: ‘bold’
}
},
labelLine: {
show: false
},
data: number
}
]
};

But it doesnt works because only show the number and not the categories.

How should i map the two fields into de Series.data? I was looking for an example in all internet but i cant find nothing.

Please help me.

Thanks

[rkhero1998]

I guess you want to generate graph like this

.
for this you have to assign

@Jorge

number

value like this
[
{ value: 1048, name: ‘Search Engine’ },
{ value: 735, name: ‘Direct’ },
{ value: 580, name: ‘Email’ },
{ value: 484, name: ‘Union Ads’ },
{ value: 300, name: ‘Video Ads’ }
]

. If you want something like this i can help you with that

[Jorge]

YESSS!!! Thank you.

That is the chart that i want to do. But the values in your example are defined manually in the same code. I would like to know how to map “value” into a series from a sql query and “name” into another series form the same sql query.

This is the output of sql query. There are two columns: “modelo” and “number”

the field “modelo” need to be mapped into “name” field (in your example)
the field “number” need to be mapped into “value” field (in your example)

Thanks in advance

BR
Jorge

[rkhero1998]

you can iterate through array and store in series.

[amanssnl490]

formatted_series=
for (index = 0; index <model.length ; index++) {
data_list = {};
data_list[“value”] = number[index];
data_list[“name”] =modelo[index];
formatted_series.push(data_list)
};
and replace this

@Jorge

number

with formatted_series

[yosiasz]

const koompa = data.series.map((s) => {
  const metrics = s.fields.find((f) => f.name === 'modelo').values.buffer;
  const values = s.fields.find((f) => f.name === 'number').values.buffer;

  return values.map((d, i) => {
    return { name: metrics[i], value: d };
  })
});

....

data: koompa[0]

[mikhailvolkov]

@yosiasz You nailed it!

Here is what it looks like using the Static Data Source:

Function:

const koompa = data.series.map((s) => {
  const metrics = s.fields.find((f) => f.name === 'modelo').values.buffer;
  const values = s.fields.find((f) => f.name === 'number').values.buffer;

  return values.map((d, i) => {
    return { name: metrics[i], value: d };
  })
})[0];

return {
  tooltip: {
    trigger: 'item'
  },
  legend: {
    top: '5%',
    left: 'center'
  },
  series: [
    {
      name: 'Pie Chart',
      type: 'pie',
      radius: ['40%', '70%'],
      avoidLabelOverlap: false,
      itemStyle: {
        borderRadius: 10,
        borderColor: '#fff',
        borderWidth: 2
      },
      label: {
        show: false,
        position: 'center'
      },
      emphasis: {
        label: {
          show: true,
          fontSize: '40',
          fontWeight: 'bold'
        }
      },
      labelLine: {
        show: false
      },
      data: koompa
    }
  ]
};

[Jorge]

Thanks to all!!! It works fine.
It’s not easy to me to understand how to map the values in each type of charts for this plugins. Thanks for your support.