How to create a column from infinity datasource by adding a substring of a existing column?

Grafana Dashboards
, ,
1
  • What Grafana version and what operating system are you using?
    9.4.7
  • What are you trying to achieve?
    Create a column from infinity datasource by adding a substring (Roll alone) of a existing column (Roll-Name). Example in the pasted json, i want create a column called RollNum and include the substring before first hyphen (-) of the string of column Roll-Name
  • How are you trying to achieve it?
    using infinity datasource and uql queries
  • What happened?
    Not getting the required data filtered
  • What did you expect to happen?
    without using the transformations, want to create a new column with the existing column’s substring.
  • Can you copy/paste the configuration(s) that you are having problems with?
[
{
"Roll-Name": "2316-subhash",
"Sub": "science",
"login": "2023-12-20T10:11:19.92458Z",
"logout": "2023-12-21T10:19:11.90058Z"
},{
"Roll-Name": "9281-Naveen",
"Sub": "social",
"login": "2024-01-20T10:11:19.92458Z",
"logout": "2024-01-26T10:19:11.90058Z"
},
{
"Roll-Name": "8278-Dheeraj",
"Sub": "hindi",
"login": "2024-02-20T10:11:19.92458Z",
"logout": "2024-02-26T10:19:11.90058Z"
},
{
"Roll-Name": "9172-Tiwary",
"Sub": "science",
"login": "2024-01-05T10:11:19.92458Z",
"logout": "2024-01-15T10:19:11.90058Z"
}]

  • Did you receive any errors in the Grafana UI or in related logs? If so, please tell us exactly what they were.

  • Did you follow any online instructions? If so, what is the URL?
    Yes tried with extract, but was showing syntax errors

2

Welcome @itroyalsudheer

I think using a JSONata query like this would allow you to separate the digits before the hyphen.

Example:

$substringBefore("Hello World", " ") => "Hello"
3

Could you help me with the exact content screenshot. Because, i tried extract, substringBefore but these but all were giving nothing. Please help with content screenshot.

4

The JSON you posted above is not formatted with the correct double quotes. When pasted, it does not render. Can you please fix and paste here using the correct formatting? That will allow others to paste in your sample JSON.

WRONG:

image
image1464Ă—266 26.8 KB

RIGHT:
image

1 Like
5

Here is docu on uql and jsonata

https://grafana.github.io/grafana-infinity-datasource/docs/uql

6

Hello, Seems like when pasted the content it missed the closing curly brace after which comma was mentioned. Could you please copy paste the same again.
[
{
“Roll-Name”: “2316-subhash”,
“Sub”: “science”,
“login”: “2023-12-20T10:11:19.92458Z”,
“logout”: “2023-12-21T10:19:11.90058Z”
},
{
“Roll-Name”: “9281-Naveen”,
“Sub”: “social”,
“login”: “2024-01-20T10:11:19.92458Z”,
“logout”: “2024-01-26T10:19:11.90058Z”
},
{
“Roll-Name”: “8278-Dheeraj”,
“Sub”: “hindi”,
“login”: “2024-02-20T10:11:19.92458Z”,
“logout”: “2024-02-26T10:19:11.90058Z”
},
{
“Roll-Name”: “9172-Tiwary”,
“Sub”: “science”,
“login”: “2024-01-05T10:11:19.92458Z”,
“logout”: “2024-01-15T10:19:11.90058Z”
}]

7

Hi @itroyalsudheer

I put your JSON structure into try.jsonata.org just to experiment. A JSONata function like $uppercase works nicely as long as you put in backticks the Roll-Name key. I have not figured out how to map the $substringBefore function, but I think it should be straightforward.

See here: https://try.jsonata.org/9vMOn8BkD

8

Hi @itroyalsudheer

With the incredible help of my fellow Champion @yosiasz here is the JSONata you can use to separate the string:

image
image975Ă—867 64.8 KB

UQL with JSONata (as used in Infinity plugin) is

parse-json
| jsonata "($map($, function($v) {{'RollNameDigits': $substringBefore($v.`Roll-Name`,'-'),'RollNameLetters': $substringAfter($v.`Roll-Name`,'-')}}))"
1 Like
9

Thanks Much @grant2 and @yosiasz this is very helpful.
It worked for me.

1 Like