Conversion from SQL query to Infinity JSON parser

Grafana
1

Hi Everyone

I pretty new to Grafana, and I’m working on a refactory to create a new dashboard based on the API from a previous one based on a CSV file, the previous one have a SQL query to parse the data and show in a bar chart panel, group by date, and in each day show the count of each group found, here below for clarity

SELECT SUBSTRING("time",1 ,10) AS "date"

,

COUNT(CASE WHEN

"element name" NOT LIKE 'OS%' AND

"Parameter description" NOT LIKE '%ATVA%' AND

"Parameter description" NOT LIKE '%TSPT%'

THEN 1 END) AS "Other",

COUNT(CASE WHEN "element name" LIKE 'OS%' THEN 1 END) AS "nCC",

COUNT(CASE WHEN "Parameter description" LIKE '%ATVA%' THEN 1 END) AS "ATV",

COUNT(CASE WHEN "Parameter description" LIKE '%TSPT%' THEN 1 END) AS "Sentry"

  FROM "prod_shasrv_euw2_insight_grafana_general_db"."tb_platform_control_csv_data"

WHERE 

  $__timeFilter(date_parse(SUBSTRING("time", 1, 10), '%Y/%m/%d'))

  AND "element name" NOT LIKE '%MCM9000-SN%'




GROUP BY

  1

ORDER BY 

  "date"

Now I spend the last 2 days to try to replicate the same query on the the Jsonata parser but without any luck, can I ask for an help to do this conversion? this is the kind of data I’m recive via API

{

    "d": [

        {

            "DataMinerID": 11111,

            "HostingAgentID": 22222,

            "ID": 357587,

            "RootAlarmID": 338575,

            "ElementID": 85,

            "ElementName": "Zixi Zen Master Test",

            "IsElementMasked": false,

            "ParameterID": 7002,

            "ParameterName": "Type (Events) xwxkq6o5mn",

            "TableIndex": "xwxkq6o5mn",

            "DisplayValue": "error",

            "AlarmState": "Critical",

            "Type": "Escalated From Warning",

            "IsAggregation": false,

            "IsMasked": false,

            "Services": [],

            "TimeOfArrival": "2026-03-19 08:40:56",

            "TimeOfArrivalUTC": 1773909656000,

            "RootTime": "2026-03-17 19:02:01",

            "RootTimeUTC": 1773774121000,

            "IsTrending": false,

            "IsOwner": false,

            "OwnerName": "System",

            "LastChangeUTC": 1773909679578,

            "IsCleared": false

        }

    ]

}
2

you can start playing with this


$.d.{
  'nCC': $contains(ElementName,'OS') ? 1 : 0,
  'Other': (
    $not($substring(ElementName,0,2)="OS") and
    $not($contains(ParameterName, "ATVA")) and
    $not($contains(ParameterName, "TSPT"))
  ) ? 1 : 0
}

use documentation here to guide you

and here is a playground for jsonata

https://try.jsonata.org/yI_VrdefL

1 Like