Check if parameter is null in CosmosDB query

Question

How can I check if a parameter is null in @azure/cosmos sdk when querying the database?

I've tried the IS_NULL or IS_EMPTY, either breaks the query:

await container.items
  .query({
    query: `
        SELECT * from company
        WHERE company.location = @location

        AND (@dateFrom IS NULL OR company.createdAt >= @dateFrom)
        AND (@dateTo IS NULL OR company.createdAt < @dateTo)

        ORDER BY company.createdAt DESC
    `,
    parameters: [
        { name: "@location", value: "Germany" },
        { name: "@dateFrom", value: new Date().setFullYear(new Date().getFullYear() - 5)},
        { name: "@dateTo", value: new Date() },
    ]
  })
  .fetchAll();

I'm getting this error:

Message: {\"errors\":[{\"severity\":\"Error\",\"location\":{\"start\":243,\"end\":245},\"code\":\"SC1001\",\"message\":\"Syntax error, incorrect syntax near 'IS'.\"}]}

Answer 1

I figured it out with the help of the IS_DEFINED expressor:

AND (IS_DEFINED(@dateFrom) = false OR company.createdAt >= @dateFrom)
AND (IS_DEFINED(@dateTo) = false OR company.createdAt < @dateTo)