CODEWITHSUNDEEP
mysqlnode.jsazureazure-cosmosdbazure-hub
Dr. Piyush Dholariya
Dr. Piyush Dholariya

Reputation: 1326

Why documentdb query returns data which is undefined or empty value?

I am using documentdb & running query of documentdb. my sample query is looks like:

studentQuery = {
        query: 'SELECT * FROM root r WHERE (r.userid=@userid OR r.code=@code) AND r.collectionName="students"',
        parameters: [{
            name: '@userid',
            value: userid
        }, {
            name: '@code',
            value: code
        }]
    };

Now problem is that if I am passing only userid="piyush123" and code= "", then it is returning me all those records which has code="" empty value, if code is undefined though it returns all those documents which doesn't contain code.

shortly I don't want those records which has null or empty string or undefined value, I can resolve it by IS_DEFINED, IS_NULL, NOT IS_NULL kind of keywords, but I don't want to use it in all queries 'cause its making my query structure to complex, so I want to apply it at once place so I should not bother about all kind of check everywhere which can reduce my efforts.

Upvotes: 0

Views: 869

Answers (1)

Aravind Krishna R.
Aravind Krishna R.

Reputation: 8003

You can write a UDF that wraps all cases - empty string, nulls, and undefined and call that within query.

  1. Register a IsMissing UDF like the following using CreateUserDefinedFunction
  2. Use within a query by name. For example, SELECT * FROM c WHERE udf.IsMissing(c.code) AND ...

Here's a simple implementation of IsMissing:

function isMissing(doc, prop) {
  if (typeof doc[prop] === "undefined") {
    return true;
  } 

  if (doc[prop] === "" || doc[prop] === null) {
    return true;
  }

  return false;
}

Upvotes: 1

Related Questions