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

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

I guess you want to generate graph like this
image
.
for this you have to assign

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

1 Like

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

you can iterate through array and store in series.

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

with formatted_series

1 Like
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]

image

1 Like

@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
    }
  ]
};
1 Like

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.

1 Like