Grafana - Plotting XY Charts and Gantt Charts with JSON and multiple array data

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

  • What are you trying to achieve?

I am using Elasticsearch Datasource with Grafana. I have one index in ELK and in that I will be having multiple records as documents in the Kibana Index.
Below is the sample NDJSON format I am using to upload documents to Kibana index. Below is the format:

{“@timestamp”: “2023-08-16T08:38:38.105+05:30”, “id”: 1, “seconds”:[1,2,3,4,5], “a”:[0,1,1,0,1],“b”:[1,0,0,0,0],“c”:[0,1,1,0,1],“d”:[0,1,1,0,0],“e”:[0,1,1,0,0],“f”:[0,0,0,1,0]}
{“@timestamp”: “2023-09-16T08:38:38.105+05:30”, “id”: 2, “seconds”:[1,2,3,4,5], “a”:[0,1,1,0,1],“b”:[1,0,0,0,0],“c”:[0,1,1,0,1],“d”:[0,1,1,0,0],“e”:[0,1,1,0,0],“f”:[0,0,0,1,0]}

I am calling this Index in Grafana, and I am trying to extract the data from one document in the Index as below let’s say with id = 1:

{“@timestamp”: “2023-08-16T08:38:38.105+05:30”, “id”: 1, “seconds”:[1,2,3,4,5], “a”:[0,1,1,0,1],“b”:[1,0,0,0,0],“c”:[0,1,1,0,1],“d”:[0,1,1,0,0],“e”:[0,1,1,0,0],“f”:[0,0,0,1,0]}

This above JSON data with arrays or it may have some nested array formats like - {“@timestamp”: “2023-08-16T08:38:38.105+05:30”, “matrix”: [[0,1,2,0,5], [1,2,0,0,4], [1,0,5,6,0]]}

I want to extract the data from these arrays/ nested arrays to create a whole new table in Grafana from it. So that I can plot various charts like XY-chart or Gantt charts with this kind of data. (Time vs Values of multiple fields in single chart)

  • How are you trying to achieve it?
    I have tried various transformations in Grafana like Extract fields (JSON, Key-Value pairs etc.), Organize fields, Group by, Rows to fields, Filter by query, Filter by values etc. etc.

I want to plot the Gantt charts (task durations over time) as well as XY plots from this data.

  • What happened?
  1. I am able to create separate table with one array field, but I want to add new rows to my table by using array values from other fields as well. I can’t add new row to my table after I extract fields.

  2. I have tried Rows to fields transform. This Rows to fields transform did not change my rows to columns or columns to rows (transpose operation).

  3. I have tried plotting Gantt charts, but the coloring option was not available for the Gantt plugin for Grafana.

  4. After field extraction, XY plots are not showing up the time axis values and Y axis values properly for multiple field selection when I go to the manual mode to select axis fields.

  • What did you expect to happen?
    I expected that I could covert this records array data to a new table format so that I can plot XY plots as well as Gantt plots. I will be dealing with more complex data than the sample data which I have shared. There will be hundreds of records with such kind of data that I need plot Graphs for.

  • Can you copy/paste the configuration(s) that you are having problems with?
    Grafana Rows to fields option did not transpose my data after transformation. Below is the screenshot:

I cannot add new rows or columns to the fields after extracting the existing array fields. Below is the screenshot:

  • Did you receive any errors in the Grafana UI or in related logs? If so, please tell us exactly what they were.
    Whenever I am trying the elastic search query, I get an error. Below is the screenshot:

I cheched any missing Braces, I changed this “type”:“array” to “type”:“long” similar to as it is mapped in actual Datasource. Still, I am having “Failed to parse query” errors. Below is the error screenshot:

I used this to install Grafana and other plugins and then I have experimented with available features.

Please suggest some proper way to plot XY charts and Gantt charts with such data source.