Need help with JSONata functions

Grafana
1

I need a help on preparing the JSONata query

I need to group by completeDate and get the average like (“2023-03-12” - “2024-03-08” + “2024-03-11” - “2024-03-05” + “2024-03-11” - “2024-03-02”) / 3, similarly it should perform against all the other entries in the array and the result should be like the “completedDate” and its value.

below is the same data

[
  {
    "completeDate": "2024-03-11 11:00:00",
    "returnDate": "2024-03-08 05:00:00",
    "item": "123"
  },
  {
    "completeDate": "2024-03-11 07:02:00",
    "returnDate": "2024-03-05 12:00:00",
    "item": "120"
  },
  {
    "completeDate": "2024-03-11 07:00:04",
    "returnDate": "2024-03-02 23:00:00",
    "item": "220"
  },
  {
    "completeDate": "2024-03-20 08:00:04",
    "returnDate": "2024-03-04 07:00:00",
    "item": "221"
  },
  {
    "completeDate": "2024-03-20 07:00:10",
    "returnDate": "2024-03-11 09:00:00",
    "item": "222"
  },
  {
    "completeDate": "2024-03-21 10:00:10",
    "returnDate": "2024-03-10 09:00:00",
    "item": "222"
  }
]

Output: {
  "2023-03-11": 6.3, //assuming
 "2024-03-20": 2, , //assuming
 "2024-03-21": 1 , //assuming
}
2

what have you tried so far? and where are you stuck?

the functions here should be of help

3

Sorry I forgot to paste the query, below is the query I was trying but it is not working

$reduce(data, function($acc, $v) {
  $merge([$acc, {
    ($substring($v.completeDate, 0, 10)): {
      "averageDifference": $avg($map($v, function($item) {
        $abs($number($toMillis($replace($item.completeDate, ' ', 'T'))) - $number($toMillis($replace($item.returnDate, ' ', 'T'))))
      }))
    }
  }])
}, {})
1 Like
4

play with this jsonata

parse-json
| jsonata "($data2 := $ ~> |$|{'clean_date': $substringBefore(completeDate,' ') }|;$data2{clean_date: $count(clean_date)})"

https://try.jsonata.org/u66iRTVgN

5

Actually the requirement is like I need to groupBy by “completeDate” (excluding time).
let say there are 3 records for 11-03-2024 and 2 records for 20-03-2024 and 1 for 21-03-2024
it should perform a average like for 11-03-24 as (“completeDate” - “returnDate”)

For example:

(“2023-03-11” - “2024-03-08” + “2024-03-11” - “2024-03-05” + “2024-03-11” - “2024-03-02”) / 3 (no of complete dates for that date)
similarly for other complete dates.

and the result would be like

[
  { date: "2023-03-11", value: somevalue1 },
  { date: "2023-03-20", value: somevalue2 },
  { date: "2023-03-21", value: somevalue3 }
]

I have prepared a new query something like this not able to understand how to proceed further:

https://try.jsonata.org/TInYqSQCP

1 Like
6

try this

(
${$substringBefore(completeDate,' '): item[]} ~> $each(function($v, $k) {
    {
      "date": $k,
      "value": $count($v.{"code": $})
    }
  })
)
7

Thanks for the updates!
But your output contains the count of number of same completeDate,
But I need to perform a operation as I have mentioned before

For 2023-03-11 it is like:

(“2023-03-11” - “2024-03-08” + “2024-03-11” - “2024-03-05” + “2024-03-11” - “2024-03-02”) / 3 (no of complete dates for that date)

For “2024-03-20”

(“2023-03-20” - “2024-03-04” + “2024-03-20” - “2024-03-11”) / 2 (no of complete dates for that date)

For “2024-03-21”

(“2023-03-21” - “2024-03-10”) / 1 (no of complete dates for that date)

I hope it is clear now :slight_smile: