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

Question

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.

Aravind Krishna R. · Accepted Answer · 2017-03-07 13:46:54Z

1

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

Register a IsMissing UDF like the following using CreateUserDefinedFunction
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;
}

edited Mar 7, 2017 at 13:46

answered Mar 6, 2017 at 19:34

Aravind Krishna R.

8,00332 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Dr. Piyush Dholariya Over a year ago

How to write or inject UDF function in query & how to use it? If you can suggest it would be appreciable.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related