'Check if parameter is null in CosmosDB query

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'.\"}]}
node.jsazureazure-cosmosdb


Solution 1:[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)

Solution 2:[2]

I found that the IS_DEFINED function was not quite what was needed as the property is usually defined, but holds a null value.

Instead, I found the IS_NULL function to work:

AND (IS_NULL(@dateFrom) = true OR company.createdAt >= @dateFrom)
AND (IS_NULL(@dateTo) = true OR company.createdAt < @dateTo)

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 ekclone
Solution 2 Jameel Khan

Related Questions