Map is not expanded on timeseries format - [object Object]

[sbengo]

Hi,

We have a CH schema with a field defined as Map(String,String). When we try to perform a simple query from Grafana on the TimeSeries panel and as TimeSeries format, the Map value is not expanded and not included as part of the serie. It is working as "Table format" mode.

The idea is to be able to retrieve the value as a dict (or map[string]string) and use the Extract Field Grafana transformation to generate the desired columns, but the generated dataframe seems to concat the string values with an object Object result, being unable to use it.

The following snippet is the "Support" feature that extracts the result Dataframe processed by the backend, with no transformations. There you can check that the generated dataframe is built with Name - Value - Time, with the non-expanded map is set as [object Object] on the dataframes name

Key	Value
Panel	timeseries @ 10.0.3 (eb8dd72637)
Grafana	10.0.3 (eb8dd72637) // Open Source

Panel debug snapshot dashboard

{
  "panels": [
    {
      "datasource": {
        "type": "grafana",
        "uid": "grafana"
      },
      "fieldConfig": {
        "defaults": {
          "custom": {
            "drawStyle": "line",
            "lineInterpolation": "linear",
            "barAlignment": 0,
            "lineWidth": 1,
            "fillOpacity": 0,
            "gradientMode": "none",
            "spanNulls": false,
            "showPoints": "auto",
            "pointSize": 5,
            "stacking": {
              "mode": "none",
              "group": "A"
            },
            "axisPlacement": "auto",
            "axisLabel": "",
            "axisColorMode": "text",
            "scaleDistribution": {
              "type": "linear"
            },
            "axisCenteredZero": false,
            "hideFrom": {
              "tooltip": false,
              "viz": false,
              "legend": false
            },
            "thresholdsStyle": {
              "mode": "off"
            }
          },
          "color": {
            "mode": "palette-classic"
          },
          "mappings": [],
          "thresholds": {
            "mode": "absolute",
            "steps": [
              {
                "color": "green",
                "value": null
              },
              {
                "color": "red",
                "value": 80
              }
            ]
          }
        },
        "overrides": []
      },
      "gridPos": {
        "h": 13,
        "w": 15,
        "x": 0,
        "y": 0
      },
      "id": 2,
      "options": {
        "tooltip": {
          "mode": "single",
          "sort": "none"
        },
        "legend": {
          "showLegend": true,
          "displayMode": "list",
          "placement": "bottom",
          "calcs": []
        }
      },
      "targets": [
        {
          "refId": "A",
          "datasource": {
            "type": "grafana",
            "uid": "grafana"
          },
          "queryType": "snapshot",
          "snapshot": [
            {
              "schema": {
                "name": "id1, [object Object]",
                "fields": [
                  {
                    "name": "Time",
                    "type": "time",
                    "config": {}
                  },
                  {
                    "name": "Value",
                    "type": "number",
                    "config": {}
                  }
                ]
              },
              "data": {
                "values": [
                  [
                    1694517223000,
                    1694517237000
                  ],
                  [
                    1,
                    null
                  ]
                ]
              }
            },
            {
              "schema": {
                "name": "id2, [object Object]",
                "fields": [
                  {
                    "name": "Time",
                    "type": "time",
                    "config": {}
                  },
                  {
                    "name": "Value",
                    "type": "number",
                    "config": {}
                  }
                ]
              },
              "data": {
                "values": [
                  [
                    1694517223000,
                    1694517237000
                  ],
                  [
                    null,
                    1
                  ]
                ]
              }
            },
            {
              "schema": {
                "name": "id3, [object Object]",
                "fields": [
                  {
                    "name": "Time",
                    "type": "time",
                    "config": {}
                  },
                  {
                    "name": "Value",
                    "type": "number",
                    "config": {}
                  }
                ]
              },
              "data": {
                "values": [
                  [
                    1694517223000,
                    1694517237000,
                    1694517250000
                  ],
                  [
                    null,
                    null,
                    1
                  ]
                ]
              }
            }
          ]
        }
      ],
      "title": "Reproduced with embedded data",
      "transformations": [],
      "type": "timeseries"
    },
    {
      "gridPos": {
        "h": 7,
        "w": 9,
        "x": 15,
        "y": 0
      },
      "id": 5,
      "options": {
        "content": "<table width=\"100%\">\n    <tr>\n      <th width=\"2%\">Panel</th>\n      <td >timeseries @ 10.0.3 (eb8dd72637)</td>\n    </tr>\n    <tr>\n      <th>Queries</th>\n      <td>A[vertamedia-clickhouse-datasource]</td>\n    </tr>\n    <tr>\n      <th>Transform</th>\n      <td></td>\n  </tr>\n    <tr><th>Data</th><td> 3 frames, 6 fields, 7 rows </td></tr>\n    \n    <tr>\n      <th>Grafana</th>\n      <td>10.0.3 (eb8dd72637) // Open Source</td>\n    </tr>\n  </table>",
        "mode": "html"
      },
      "title": "Debug info",
      "type": "text"
    },
    {
      "id": 6,
      "title": "Original Panel JSON",
      "type": "text",
      "gridPos": {
        "h": 13,
        "w": 9,
        "x": 15,
        "y": 7
      },
      "options": {
        "content": "{\n  \"datasource\": {\n    \"type\": \"vertamedia-clickhouse-datasource\",\n    \"uid\": \"caf89ca7-12da-4a25-9974-5fc93dc4e57a\"\n  },\n  \"fieldConfig\": {\n    \"defaults\": {\n      \"custom\": {\n        \"drawStyle\": \"line\",\n        \"lineInterpolation\": \"linear\",\n        \"barAlignment\": 0,\n        \"lineWidth\": 1,\n        \"fillOpacity\": 0,\n        \"gradientMode\": \"none\",\n        \"spanNulls\": false,\n        \"showPoints\": \"auto\",\n        \"pointSize\": 5,\n        \"stacking\": {\n          \"mode\": \"none\",\n          \"group\": \"A\"\n        },\n        \"axisPlacement\": \"auto\",\n        \"axisLabel\": \"\",\n        \"axisColorMode\": \"text\",\n        \"scaleDistribution\": {\n          \"type\": \"linear\"\n        },\n        \"axisCenteredZero\": false,\n        \"hideFrom\": {\n          \"tooltip\": false,\n          \"viz\": false,\n          \"legend\": false\n        },\n        \"thresholdsStyle\": {\n          \"mode\": \"off\"\n        }\n      },\n      \"color\": {\n        \"mode\": \"palette-classic\"\n      },\n      \"mappings\": [],\n      \"thresholds\": {\n        \"mode\": \"absolute\",\n        \"steps\": [\n          {\n            \"color\": \"green\",\n            \"value\": null\n          },\n          {\n            \"color\": \"red\",\n            \"value\": 80\n          }\n        ]\n      }\n    },\n    \"overrides\": []\n  },\n  \"gridPos\": {\n    \"h\": 8,\n    \"w\": 12,\n    \"x\": 0,\n    \"y\": 0\n  },\n  \"id\": 1,\n  \"options\": {\n    \"tooltip\": {\n      \"mode\": \"single\",\n      \"sort\": \"none\"\n    },\n    \"legend\": {\n      \"showLegend\": true,\n      \"displayMode\": \"list\",\n      \"placement\": \"bottom\",\n      \"calcs\": []\n    }\n  },\n  \"targets\": [\n    {\n      \"database\": \"test\",\n      \"datasource\": {\n        \"type\": \"vertamedia-clickhouse-datasource\",\n        \"uid\": \"caf89ca7-12da-4a25-9974-5fc93dc4e57a\"\n      },\n      \"dateColDataType\": \"\",\n      \"dateLoading\": false,\n      \"dateTimeColDataType\": \"time\",\n      \"dateTimeType\": \"DATETIME\",\n      \"datetimeLoading\": false,\n      \"extrapolate\": true,\n      \"format\": \"time_series\",\n      \"formattedQuery\": \"SELECT $timeSeries as t, count() FROM $table WHERE $timeFilter GROUP BY t ORDER BY t\",\n      \"intervalFactor\": 1,\n      \"query\": \"SELECT\\n    $timeSeries as t,\\n    count(),\\n    id,\\n    attributes\\nFROM $table\\n\\nWHERE $timeFilter\\n\\nGROUP BY\\n    t,\\n    id,\\n    attributes\\nORDER BY t\\n\",\n      \"rawQuery\": \"SELECT\\n    (intDiv(toUInt32(time), 1) * 1) * 1000 as t,\\n    count(),\\n    id,\\n    attributes\\nFROM test.map_table\\n\\nWHERE time >= toDateTime(1694516084) AND time <= toDateTime(1694517884)\\n\\nGROUP BY\\n    t,\\n    id,\\n    attributes\\nORDER BY t\",\n      \"refId\": \"A\",\n      \"round\": \"0s\",\n      \"skip_comments\": true,\n      \"table\": \"map_table\",\n      \"tableLoading\": false\n    }\n  ],\n  \"title\": \"Panel Title\",\n  \"transformations\": [],\n  \"type\": \"timeseries\"\n}",
        "mode": "code",
        "code": {
          "language": "json",
          "showLineNumbers": true,
          "showMiniMap": true
        }
      }
    },
    {
      "id": 3,
      "title": "Data from panel above",
      "type": "table",
      "datasource": {
        "type": "datasource",
        "uid": "-- Dashboard --"
      },
      "gridPos": {
        "h": 7,
        "w": 15,
        "x": 0,
        "y": 13
      },
      "options": {
        "showTypeIcons": true
      },
      "targets": [
        {
          "datasource": {
            "type": "datasource",
            "uid": "-- Dashboard --"
          },
          "panelId": 2,
          "withTransforms": true,
          "refId": "A"
        }
      ]
    }
  ],
  "schemaVersion": 37,
  "title": "Debug: Panel Title // 2023-09-12 13:26:31",
  "tags": [
    "debug",
    "debug-timeseries"
  ],
  "time": {
    "from": "2023-09-12T10:56:31.582Z",
    "to": "2023-09-12T11:26:31.582Z"
  }
}

Repro case:

1. Create DB and table with Map(String,String) as column

> CREATE DATABASE test;

> CREATE TABLE test.map_table
(
    `time` DateTime,
    `id` String,
    `attributes` Map(String, String)
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(time)
PRIMARY KEY (toDate(time), id)
ORDER BY (toDate(time), id)
TTL toStartOfMonth(time) + toIntervalMonth(12 * 5)
SETTINGS index_granularity = 8192, ttl_only_drop_parts = 1;

2. Insert values to mock a TimeSeries:

> INSERT INTO test.map_table values (now(), 'id1', {'key1': 'value1', 'key2':'value2'})
> INSERT INTO test.map_table values (now(), 'id2', {'key1': 'value1', 'key2':'value2'})
> INSERT INTO test.map_table values (now(), 'id2', {'key1': 'value1', 'key2':'value2'})

3. Create a simple Grafana graph:

SELECT
    $timeSeries as t,
    count(),
    id,
    attributes
FROM $table
WHERE $timeFilter
GROUP BY
    t,
    id,
    attributes
ORDER BY t

4. Check that the attributes field is being set as an string: object Object and part of the name (as dataframe):

[sbengo]

Thanks @Slach for reading it and setting up as 2.6.0 milestone.

I think the current issue, is also related with the current dataframes: the plugin is using and old implementation (Name/Time/Value), which seems a bit abandoned by the Grafana team... (#478 ) and we cannot take profit of the different "labels" features,

Sorry about the following question, but it is an stopper for us and our internal roadmap:
Do you have an estimated release date for 2.6.0?

[Slach]

no we don't have ETA

you could try to use Grafana Labs plugin for ClickHouse https://grafana.com/grafana/plugins/grafana-clickhouse-datasource/

[Slach]

As a quick workaround you could use:

SELECT
    $timeSeries as t,
    count() AS value,
    concat(toString(id),' ', toString(attributes)) AS label
FROM $table
WHERE $timeFilter
GROUP BY
    t,
    label
ORDER BY t

[Slach]

similar with #189

maybe we need to make toString on plugin-side on JavaScript and Golang alerts parts