How to extract data form a field and append to another field

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

Using Grafana Cloud version 9.3.6

  • What are you trying to achieve?

Following a tutorial online for using the JSON API plugin to use JSON data as a grafana datasource, I hit this endpoint GET

I would like to display unemployment data from a JSON datasource over time (months and years) but as you can see the way the endpoint serves the months field is in this string format: “M01” … “M12” at the JSON path $.Results.series[0].data[*].period. Here is an example of the data in table view to explain what i want to do:

As it stands at the moment, if I change the data type of the year field from String to Time then I can display this data as a time series but it only has data points every year, not every month.

I want to process the values in the period column, removing the leading “M” character from the string and append these to the year field with a hyphen so the end result would be something like this from the image above:
etc. which I then assume I can cast to the Time data type to display data points at the level of months not just years.

  • How are you trying to achieve it?

I tried using various of the Transform options documented here including Extract fields, rename by regex etc. but would not achieve the desired result.

  • What happened?

Am not able to use any of these Transformations to process the period field and append the output to the year field to create a new Time of the format YYYY-MM. Is this sort of processing not possible inside Grafana using the Transform tab?


Look at this jsonata query.
I will let you run with it.


1 Like