filter by $__timeFilter using jsonata

[yosiasz]

Greetings given the following sample data

[
	{
		"Name": "Zoro",
		"Place": "New York",
		"date": 1355529600,
		"Longitude": 40.6943,
		"Latitude": -73.9249
	},
	{
		"Name": "Vader",
		"Place": "Beijing",
		"date": 1355184000,
		"Longitude": 39.904,
		"Latitude": 116.4075
	}
]

How can one filter by the date range selected?

parse-json
| project "Name", "Place","dt"=unixtime_seconds_todatetime("date"),"Longitude","Latitude"
| jsonata { 'dt': $__timeFilter} 
| order by "dt" asc

Thanks

[yesoreyeram]

@yosiasz - $__timeFilter is not a macro in infinity. Typically, SQL datasources in grafana uses that macro and they are aware how to expand that. But infinity is a generic plugin and doesn't aware how to expand that macro.

In your case, I would prefer something like this

parse-json
| jsonata "*[date>=${__from:date:seconds} and date<=${__to:date:seconds}]"
| extend "date"=unixtime_seconds_todatetime("date")
| order by "date" asc

Let me know if this solves your issue or if you find any bug with the implementation.

[gdiana]

The problem might lie in your date format. The date format you are using is in seconds and not milliseconds. UQL and jsonata uses ISO 8601 and will otherwise throw an error or just omit if using jsonata.

When you use date in jsonata you must use 2012-12-14T00:00:00 and not 2012-12-14 00:00:00

[yosiasz]

wonder what would happen if the result set is just one. testing

[gdiana]

This works multiple or single

parse-json
| jsonata "*.{ 'time' : $fromMillis(date.$number()*1000),'Name' : Name,'Place' : Place,'Lat': Latitude,'Long' : Longitude}"

It just reformulates the json and converts date to IOS format

[yesoreyeram]

Glad it works for you @gdiana . The original question was about using grafana dashboard time range filter here

[yosiasz]

Got it confused with the other issue 😆 thanks!