Multiple values in same graph

[afonsoc12]

Hello,
First of all, many thanks for this plugin. It has been working flawlessly for several JSON API's that I have already tested with.

However, there is this time-series API which I do not know how to plot multiple time-series in the same plot. This data comes from FireflyIII.

[
    {
        "label": "account 1",
        "currency_id": 3,
        "currency_code": "GBP",
        "currency_symbol": "£",
        "currency_decimal_places": 2,
        "start_date": "2020-12-01",
        "end_date": "2020-12-31",
        "type": "line",
        "yAxisID": 0,
        "entries": {
            "2020-12-01": 1,
            "2020-12-02": 2,
            "2020-12-03": 3,
            "2020-12-04": 4,
            "2020-12-05": 5,
            "2020-12-06": 6,
            "2020-12-07": 7,
            "2020-12-08": 8,
            "2020-12-09": 9,
            "2020-12-10": 10,
            "2020-12-11": 11,
            "2020-12-12": 12,
            "2020-12-13": 13,
            "2020-12-14": 14,
            "2020-12-15": 15,
            "2020-12-16": 16,
            "2020-12-17": 17,
            "2020-12-18": 18,
            "2020-12-19": 19,
            "2020-12-20": 20,
            "2020-12-21": 21,
            "2020-12-22": 22,
            "2020-12-23": 23,
            "2020-12-24": 24,
            "2020-12-25": 25,
            "2020-12-26": 26,
            "2020-12-27": 27,
            "2020-12-28": 28,
            "2020-12-29": 29,
            "2020-12-30": 30,
            "2020-12-31": 31
        }
    },
    {
        "label": "account 2",
        "currency_id": 3,
        "currency_code": "GBP",
        "currency_symbol": "£",
        "currency_decimal_places": 2,
        "start_date": "2020-12-01",
        "end_date": "2020-12-31",
        "type": "line",
        "yAxisID": 0,
        "entries": {
            "2020-12-01": 31,
            "2020-12-02": 30,
            "2020-12-03": 29,
            "2020-12-04": 28,
            "2020-12-05": 27,
            "2020-12-06": 26,
            "2020-12-07": 25,
            "2020-12-08": 24,
            "2020-12-09": 23,
            "2020-12-10": 20,
            "2020-12-11": 21,
            "2020-12-12": 20,
            "2020-12-13": 19,
            "2020-12-14": 18,
            "2020-12-15": 17,
            "2020-12-16": 16,
            "2020-12-17": 15,
            "2020-12-18": 14,
            "2020-12-19": 13,
            "2020-12-20": 12,
            "2020-12-21": 11,
            "2020-12-22": 10,
            "2020-12-23": 9,
            "2020-12-24": 8,
            "2020-12-25": 7,
            "2020-12-26": 6,
            "2020-12-27": 5,
            "2020-12-28": 4,
            "2020-12-29": 3,
            "2020-12-30": 2,
            "2020-12-31": 1
        }
    }
]

I have a Grafana Variable which is called asset_accounts which maps to the json label, such as account 1 and account 2

On the query field I have experimented with: $.[?(@.label =~"${asset_accounts}")].entries[*] but it just concatenates the points and does not put them into "another column".
Also, to have the time-stamps I have to use the queries: $.[?(@.label =~"${asset_accounts}")].entries[*~] (keys) and $.[?(@.label =~"${asset_accounts}")].entries[*], since $.[?(@.label =~"${asset_accounts}")].entries[*~, *] generates two grafana data-frames.

Any thoughts? Is this possible to achieve at all?

Thank you!

[marcusolsson]

Hi @afonsoc12, and thanks for sharing an interesting use case!

The plugin currently only supports one time series per Grafana query, so to have plot multiple time series, they'd have to be broken out into separate Grafana queries with filters:

To support your use case, I think we'd have to add some form of "Group by" feature, where you could tell the plugin to split the JSON response into multiple time series, and evaluate the JSON paths for each one.

[afonsoc12]

Thank you @marcusolsson for your answer.
I will keep an eye in the repo to check if this was implemented.
If you can, please tag me in the commit if this is implemented.

[supportdesk-si]

We just started to play with your plugin. Very nicely done, thank you. Yes, multiple time-series is practically "a must" in modern charting applications. BTW, Infinity JSON, CSV plugin is supporting and processes series very nicely. We used it in CSV but assumption it will render JSON in a similar way.

One issue we noticed, when using plotly chart, selecting part of the chart to create time period to zoom in, it can't forward the from and to time stamps.

[marcusolsson]

Experimented a bit with this today. Other data sources have a way to configure a Metrics field, which AFAIK essentially does two things:

• grouping the data into multiple data frames
• using the name of the data frame as the display name for the Metrics field

I'm leaning towards configuring these to things separately. Here's how we could enable grouping into mulitple time series.

I've received several requests to bring back Aliases for fields, in which case we could support meta aliases like $__dataFrameName, to replicate the Metrics field feature.

Unfortunately, this wouldn't solve the issue in the original post, where the JSON is nested. Since each field query executes separately from the others, there's currently no way of knowing what entry belongs to which label in the original example.

[marcusolsson]

I've just published v1.1.0 to the marketplace, which includes experimental support for this. Would you mind checking it out and letting me know whether it addresses your use case?

[supportdesk-si]

Sure thing. Loading up now ;) Thank you.

[supportdesk-si]

Well, the grouping works rather strange. We will have more time later today and post some examples. Thank you for working on this.

[afonsoc12]

Hi @marcusolsson,

Just tested this, and as you said it yourself, it doesn't support my original case.

I was thinking if I could have these 3 queries
$.[?(@.label =~ /^(account 1|account 2)$/)].entries[*~]
$.[?(@.label =~ /^(account 1|account 2)$/)].entries[*]
$.[?(@.label =~ /^(account 1|account 2)$/)].label

Where the last one would repeat for the number of times within (...).entries[*], and group by this label?

Also, I am not being able to use Grafana variables in the query itself. I have a variable $accounts, where I select one or more accounts. The idea is to have something like this $.[?(@.label =~ /^$accounts$/)].entries[*] to replace $.[?(@.label =~ /^(account 1|account 2)$/)].entries[*]

But it could be me who didn't get the JS expression right!

Note that these variables do not seem to be working on Alias field of the plugin either.

Thanks,
Afonso

[supportdesk-si]

Marcus,

We are comparing your implementation with something that works very well in data representation, however we do not have some of the features your plugin provides. This is grouping we are currently getting from another plugin

It aligns data per language of the audience with time stamp and value.

When we use your current version, the grouping is not performed

Notice how selecting the item does not transform the data. That's the issue that prevents it to load correctly, resulting in garbage chart.

[marcusolsson]

@afonsoc12

Where the last one would repeat for the number of times within (...).entries[*], and group by this label?

This is the tricky part about getting this to work. Since the JSON paths are evaluated separately, I don't believe there's any way of knowing what entries should have what label.

One possible solution would be to support nested JSON paths, where you'd define a JSON path to return all the objects first, and then define "relative" JSON paths on that object. You could then potentially add a configuration to repeat a value on the object, for all the rows generated by the relative paths. For example:

$.[*] // returns an array of objects
  .entries[*~]
  .entries[*]
  .label // "Repeat" is set to true

There would be performance implications of running evaluating several JSON paths, but I think it could be a natural next step for the plugin.

[marcusolsson]

@supportdesk-si

Your first screenshot is indeed how the Group by feature should work. For you second screenshot, can you confirm that you've set the Group by to name?

If you have, and it's not working, is there any way you could provide a sample JSON that demonstrates the issue?

[supportdesk-si]

Yes indeed the settings are set to be grouped by name. I believe you are on the (better/right) path. JSON should come packaged as series. To keep your current code untouched, you can have a checkbox that will force array vs series data path traversal.

Array [{data},{data},{data}]
Series [{ser_a:[{data},{data},{data}]},{[ser_b:[{data},{data},{data}]}]

Naturally, data structure must be identical for any branch to traverse along the path..

That way there is a huge saving on traffic and data arrives in a larger chunk. We are using custom charts that allow zooming without reload. If data is loaded for 30 days you can zoom onto 1 day without sending a single bit of data. Your caching works perfectly and keeps data available to user.

While we are talking about options, it would be incredible to have color as an optional field built into the plugin. Our server has color schemes and users can preset the options they prefer. Right now we have to add block of code to recolor the charts, We recently started to adjust colors for color blind users. If the series can have color sent as data it opens up incredible flexibility and helps users see dashboards in their customized environment.

[{ser_a:[{data},{data},{data}],color_fg:'#fff'},{ser_b:[{data},{data},{data}],color_fg:'#333'}]

[Satyabhama-Reddy]

@marcusolsson ,
I am trying to plot multiple time-series, but it seems to plot the same values for both names. For instance,
at time epoch 1607565300, the value for "319556" is 3282189 and the value for "319557" is 56964. But the graph plots value 3282189 for both "319556" & "319557".

The json data has an array of data:

{
    "records": [
      {
        "time": 1607565300,
        "value": 3282189,
        "name": 319556
      },
      {
        "time": 1607565300,
        "value": 56964,
        "name": 319557
      },
      {
        "time": 1607565600,
        "value": 35199090,
        "name": 319556
      },
      {
        "time": 1607565600,
        "value": 57147,
        "name": 319557
      },
      {
        "time": 1607565900,
        "value": 22253010,
        "name": 319556
      },
      {
        "time": 1607565900,
        "value": 30975,
        "name": 319557
      },
      {
        "time": 1607566200,
        "value": 10304925,
        "name": 319556
      },
      {
        "time": 1607566200,
        "value": 35028,
        "name": 319557
      },
      {
        "time": 1607566500,
        "value": 6447159,
        "name": 319556
      },
      {
        "time": 1607566500,
        "value": 30117,
        "name": 319557
      },
      {
        "time": 1607566800,
        "value": 3543957,
        "name": 319556
      },
      {
        "time": 1607566800,
        "value": 20286,
        "name": 319557
      },
      {
        "time": 1607567100,
        "value": 1007703,
        "name": 319556
      },
      {
        "time": 1607567100,
        "value": 132,
        "name": 319557
      },
      {
        "time": 1607567400,
        "value": 179370,
        "name": 319556
      },
      {
        "time": 1607567400,
        "value": 18,
        "name": 319557
      },
      {
        "time": 1607567700,
        "value": 12963,
        "name": 319556
      },
      {
        "time": 1607567700,
        "value": 3,
        "name": 319557
      }
    ]
}

Screenshots of the output:

Any solution for this?

Thanks,
Satya

[marcusolsson]

@Satyabhama-Reddy

Thank you for the test data! It makes things a lot easier for me. I can reproduce this as well so I can start troubleshooting this today.

[marcusolsson]

I found the bug causing the issue both of you are seeing. I'll be releasing a fix for this later today.

[marcusolsson]

I've just published 1.1.1 which should fix the grouping issue.

@afonsoc12 Added the missing variables support for Aliases as well

[supportdesk-si]

Confirming the plugin works as expected. Thank you very much.

[Turnerj]

Had a look at the experimental Group By and Metric options and they are working for my use case too. When you do move them out of experimental, it might be useful to point the user towards configuring both. At first I was confused and only configured the Group By option so it didn't label the series correctly (each series was called "value", the data in the graph was fine). Once I set the Metric field, it worked once I saved the panel and re-opened it.

Great work on this plugin by the way! It has been super useful for me!

[marcusolsson]

Sounds like it's working well for most so far, so I'll make sure to move them out of experimental for the next version.

[renofa]

hi, is there a way to use a calculated column (im calculating latency from two time stamps) as metric?

[Uzama]

Is this feature still in experimental?. My Grafana still showing the experimental warning.

Grafana v10.2.3
Plugin v1.3.12

[zoltanbedi]

@Uzama It is not but we likely won't change anything around it. We are not adding new features to the plugin and encouraging everyone to migrate to infinity data source instead.