Menu
Open source Enterprise

Visualizing JSON data

Sample JSON Query in Infinity datasource

Select Type of the query to JSON. You can either specify the URL of the JSON API, JSON file or can provide inline CSV.

Using public JSON API endpoints

Below example shows about fetching data from a publicly accessible JSON URL/API endpoint.

URL : https://jsonplaceholder.typicode.com/users

image

In the above example, the data in the URL is array. So no need to configure any additional fields except url in the panel.

Accessing nested properties of JSON data

URL : https://thingspeak.com/channels/38629/feed.json

image

In the above example, data is in the feeds property. So the same is specified as root / rows field. But still our plugin doesn’t know anything about the fields or it’s types. So we are going to add the columns to make it more meaningful.

We are adding columns and defining their types as shown below.

image

JSON Data without time field

URL : https://gist.githubusercontent.com/yesoreyeram/2433ce69862f452b9d0460c947ee191f/raw/f8200a62b68a096792578efd5e3c72fdc5d99d98/population.json

image

In the above example, we are visualizing a json data without time field. Our JSON has only two fields aka country and population. So we asked the plugin to add a dummy time field to the data so that we can visualize them in any of the grafana’s stock panel. If you closely look at the image above, you can see we specified ‘format’ as timeseries.

For reference, JSON data from the URL is given below

json
[
  { "country": "india", "population": 300 },
  { "country": "usa", "population": 200 },
  { "country": "uk", "population": 150 },
  { "country": "china", "population": 400 }
]

JSON Inline

Instead of specifying URL, you can hardcoded JSON object. For example, you can specify the json as shown in the below example

json
[
  { "country": "india", "population": 420 },
  { "country": "india", "population": 440 },
  { "country": "usa", "population": 200 },
  { "country": "uk", "population": 150 },
  { "country": "china", "population": 400 }
]

You need to also specify the column names manually for display purposes.

JSONPath in root selector

In the root selector, you can use the selector in JSONPath format.

Note: Any root selector that starts with $ will be considered as JSONPath selector

image

Example:

json
{
  "customers": [
    { "name": "mary", "age": 22, "gender": "female" },
    { "name": "joseph", "age": 41, "gender": "male" }
  ],
  "premium_customers": [{ "name": "john doe", "age": 21, "gender": "male" }]
}

In the above json, if $.premium_customers is the root selector then only “john doe” will return. If $.* is the root selector all the three rows will be returned.

UQL Parser

If you are looking for more JSON options like group by, order by, JSONata, field manipulation etc, then UQL query is the one you need. Following is the simple UQL command to parse

sql
parse-json
| scope "feeds"
| project "ts"=todatetime("created_at"), "Density of Westbound Cars"=tonumber("field1"), "Density of Eastbound Cars"=tonumber("field2")

UQL Parser

Backend Parser

If you need advanced options such as alerting/recorded queries, then use backend as the parser.

backend parser

when using the backend as parsing option, your timestamp fields needs to be ISO datetime format. Example: 2006-01-02T15:04:05Z07:00. If they are not in the ISO timestamp format, you can specify the format using layout option. The layout needs to be in golang time layout spec.

When using backend parser, you also have an option to summarize the numeric fields into a single aggregated number using Summarize field. Example usage: last(density_of_eastbound_cars) - last(density_of_westbound_cars). You can also use numeric options such as sum,min,max,mean,first and last.

summarize option in backend parser