Search a date range

Problem

You want to find documents which contain a date within a specified range.

Solution

The solution has several steps:

Create some documents to search

Map(
  [
    ["Walk the dog", true, "2022-01-10"],
    ["Feed the cat", true, "2022-01-12"],
    ["Wash the car", false, "2022-01-26"],
  ],
  Lambda(
    ["title", "completed", "date"],
    Create(
      Collection("Todo"),
      {
        data: {
          title: Var("title"),
          completed: Var("completed"),
          completedDate: Date(Var("date")),
        }
      }
    )
  )
)

[
  {
    ref: Ref(Collection("Todo"), "325694738894160384"),
    ts: 1646865557490000,
    data: {
      title: 'Walk the dog',
      completed: true,
      completedDate: Date("2022-01-10")
    }
  },
  {
    ref: Ref(Collection("Todo"), "325694738896257536"),
    ts: 1646865557490000,
    data: {
      title: 'Feed the cat',
      completed: true,
      completedDate: Date("2022-01-12")
    }
  },
  {
    ref: Ref(Collection("Todo"), "325694738898354688"),
    ts: 1646865557490000,
    data: {
      title: 'Wash the car',
      completed: false,
      completedDate: Date("2022-01-26")
    }
  }
]

Query metrics:

bytesIn: 346
bytesOut: 717
computeOps: 1
readOps: 0
writeOps: 3
readBytes: 0
writeBytes: 723
queryTime: 45ms
retries: 0

Create an index to search by date range

CreateIndex({
  name: "todos_by_completed_date",
  source: Collection("Todo"),
  values: [
    { field: ["data", "completedDate"] },
    { field: ["ref"] }
  ]
})

{
  ref: Index("todos_by_completed_date"),
  ts: 1643836096660000,
  active: true,
  serialized: true,
  name: 'todos_by_completed_date',
  source: Collection("Todo"),
  values: [ { field: [ 'data', 'completedDate' ] }, { field: [ 'ref' ] } ],
  partitions: 8
}

Query metrics:

bytesIn: 179
bytesOut: 342
computeOps: 1
readOps: 0
writeOps: 4
readBytes: 1,691
writeBytes: 922
queryTime: 37ms
retries: 0

Create a UDF

CreateFunction({
  name: "todosByDateRange",
  body: Query(
    Lambda(
      ["fromDate", "toDate"],
      Map(
        Select(
          ["data"],
          Paginate(
            Range(
              Match(Index("todos_by_completed_date")),
              Date(Var("fromDate")),
              Date(Var("toDate"))
            )
          )
        ),
        Lambda(["date", "ref"], Get(Var("ref")))
      )
    )
  )
})

{
  ref: Function("todosByDateRange"),
  ts: 1646866626110000,
  name: 'todosByDateRange',
  body: Query(Lambda(["fromDate", "toDate"], Map(Select(["data"], Paginate(Range(Match(Index("todos_by_completed_date")), Date(Var("fromDate")), Date(Var("toDate"))))), Lambda(["date", "ref"], Get(Var("ref"))))))
}

Query metrics:

bytesIn: 364
bytesOut: 469
computeOps: 1
readOps: 0
writeOps: 1
readBytes: 29
writeBytes: 567
queryTime: 40ms
retries: 0

The UDF makes it easy to re-use the query.

Call the UDF

Call("todosByDateRange", "2022-01-01", "2022-01-15")

[
  {
    ref: Ref(Collection("Todo"), "325696243734938112"),
    ts: 1646866992610000,
    data: {
      title: 'Walk the dog',
      completed: true,
      completedDate: Date("2022-01-10")
    }
  },
  {
    ref: Ref(Collection("Todo"), "325696243737035264"),
    ts: 1646866992610000,
    data: {
      title: 'Feed the cat',
      completed: true,
      completedDate: Date("2022-01-12")
    }
  }
]

Query metrics:

bytesIn: 67
bytesOut: 482
computeOps: 1
readOps: 10
writeOps: 0
readBytes: 462
writeBytes: 0
queryTime: 35ms
retries: 0

Discussion

The UDF todosByDateRange uses the Range function to find documents which have a completedDate field which is within a given range. Range takes three parameters: a set, a starting value, and an ending value. Range is inclusive, so it matches up to and including the ending value.