Number of records per hour JSON

Grafana Developers & API
, ,
1
  • What Grafana version and what operating system are you using?
    v8.3.5
  • What are you trying to achieve?
    To build time series from JSON formatted data. I would like to use one base table panel with almost “real-time” orders data. I use Infinity plugin for getting the metrics from API. Data is provided by API every 10 minutes which returns JSON . Example JSON:
[
    {
        "order_id": "US-123",
        "store": "USA-1",
        "total_amount": "12.25",
        "items_ordered": "1",
        "timestamp": "1648119600"
    },
    {
        "order_id": "US-235",
        "store": "USA-1",
        "total_amount": "24.25",
        "items_ordered": "2",
        "timestamp": "1648119600"
    },
    {
        "order_id": "DE-212",
        "store": "Germany-1",
        "total_amount": "43.25",
        "items_ordered": "2",
        "timestamp": "1648634400"
    },
    {
        "order_id": "AT-452",
        "store": "Austria-1",
        "total_amount": "125.12",
        "items_ordered": "5",
        "timestamp": "1648641600"
    },    
{
        "order_id": "AT-245",
        "store": "Austria-1",
        "total_amount": "62.23",
        "items_ordered": "3",
        "timestamp": "1648641600"
    },
    {
        "order_id": "CA-321",
        "store": "Canada-1",
        "total_amount": "90.55",
        "items_ordered": "3",
        "timestamp": "1648846800"
    }
]

I would like to use this panel as a parent for all other time series panels like:
hourly order count by stores, hourly order total amount by stores and etc.

  • How are you trying to achieve it?
    I successfully created a filled table from API. Now I try to inherit the results in other panel “orders hourly”. In “orders hourly” panel I used “Organize fields”
    transformation and hide unnecessary fields for this panel: order_id, total_amount, items_ordered. Also I used “Convert Field Type” transformation to format the timestamp to hourly based format like (YYYY-MM-DD HH). So only “timestamp” and “store” left in the “orders hourly” panel.

  • What happened?
    I can’t seem to find how to group by “timestamp” AND “store” to have a total orders count by store in each hour. I tried to use “Group By” transformation for timestamp field, and it grouped the timestamp, but I lose the store count information.

  • What did you expect to happen?
    To have a matrix from timestamp and store. Count orders by store for every hour.

The question can confuse, so please let me know if you need more information.

2

Welcome

Which kind of dashboard visualization are you using?

3

I would say “table”? I am trying to use time series, but I don’t have number value for it, and I don’t know how to build a matrix from given JSON (timestamp and store values).

4

sorry forgot to ask, which plugin are you using to read the json?

5

Infinity: GitHub - yesoreyeram/grafana-infinity-datasource: CSV, JSON, GraphQL, XML and HTML datasource for grafana.

2 Likes
6

here is something that can get you started and you can run with it. infinity itself can do a lot of great things for you out of the box

parse-json
| extend "orderqty"=tonumber("items_ordered"), "orderdate"=unixtime_seconds_todatetime("timestamp")
| summarize "sum by store"=sum("orderqty") by "store", "orderdate"

image
image1771×261 16.6 KB

the following values in your json are problematic as they are string

"total_amount": "24.25",
"items_ordered": "2",
 "timestamp": "1648119600"

which required UQL conversions. Might want to look into why that is.

1 Like
7

Thank you for the tip. I see you built a great example of getting Total Ordered Items by store. But how can I collect Total Orders by store?

8

The sample query I gave you should give you a hint so you can fly :bird: on your own. Try things out on your own and see where you get.

read the documentation

1 Like
9

Thank you! By the way, do you know any kind of tool/plugin which can handle post-data processing? For instance, in order to save performance, I would like to have only 1 api call to my server, and response of that call have everything what is needed. I could have several child panels using the result of the parent panel. UQL is only available for parent panel, and I can do nothing with it in child panels, transform possibilities are limited. Maybe there is a similar query tool? I can’t seem to understand how it works, maybe you could give a hint/link to docu here?

10

Did you read thia fully?

11

Yes, I read it. But still I cannot find out how to use it in child panels.