Date range with jsonata

I know I can use $now() in jsonata to compare against dates in my JSON, e.g. jsonDate > $now() but I want to be able to find dates in the JSON that fall in the current week that we're in, Monday to Sunday, e.g. jsonDate >= $monday and jsonDate <= $sunday.

How would I do that?

A more complete example of what I have so far:

matches[venueId = 2043][startTime >= $now()].{
  "field": venue.name,
  "id": id,
  "startTime": startTime,
  "team1": team1.name,
  "team2": team2.name
}

Solution

In JSONata, you can use the $fromMillis() function to convert milliseconds since the Unix epoch to a date-time string, and $toMillis() to convert a date-time string back to milliseconds. This, combined with some math, will help you find the start of the week (Monday) and the end of the week (Sunday).

Here's how you can create a JSONata expression to find dates in your JSON that fall within the current week:

(
  $nowMs := $toMillis($now());
  $daysSinceEpoch := $floor($nowMs / (1000 * 60 * 60 * 24));
  $daysSinceMonday := ($daysSinceEpoch - 4) % 7;
  $mondayMs := $nowMs - $daysSinceMonday * (1000 * 60 * 60 * 24);
  $sundayMs := $mondayMs + 6 * (1000 * 60 * 60 * 24);
  $monday := $fromMillis($mondayMs, "[Y0001]-[M01]-[D01]");
  $sunday := $fromMillis($sundayMs, "[Y0001]-[M01]-[D01]");
  $monday & " to " & $sunday
)

$daysSinceMonday := ($daysSinceEpoch - 4) % 7; - bear in mind that January 1, 1970, was a Thursday.

You can check out this expression in Stedi's JSONata Playground: https://stedi.link/E62tWKi