Conditional Functions [Back to TOC]

if_null (ifnull)

• Syntax:

   if_null(expression1, expression2, ... expressionN)
  

• Finds first argument which value is not null and returns that value

• Arguments:

  • expressionI : an expression (any type is allowed).

• Return Value:

  • a null if all arguments evaluate to null or no arguments specified
  • a value of the first non-null argument otherwise

• Example:

   {
       "a": if_null(),
       "b": if_null(null),
       "c": if_null(null, "asterixdb"),
       "d": is_missing(if_null(missing))
   };
  

• The expected result is:

   { "a": null, "b": null, "c": "asterixdb", "d": true }
  

The function has an alias ifnull.

if_missing (ifmissing)

• Syntax:

   if_missing(expression1, expression2, ... expressionN)
  

• Finds first argument which value is not missing and returns that value

• Arguments:

  • expressionI : an expression (any type is allowed).

• Return Value:

  • a null if all arguments evaluate to missing or no arguments specified
  • a value of the first non-missing argument otherwise

• Example:

   {
       "a": if_missing(),
       "b": if_missing(missing),
       "c": if_missing(missing, "asterixdb"),
       "d": if_missing(null, "asterixdb")
   };
  

• The expected result is:

   { "a": null, "b": null, "c": "asterixdb", "d": null }
  

The function has an alias ifmissing.

if_missing_or_null (ifmissingornull)

• Syntax:

   if_missing_or_null(expression1, expression2, ... expressionN)
  

• Finds first argument which value is not null or missing and returns that value

• Arguments:

  • expressionI : an expression (any type is allowed).

• Return Value:

  • a null if all arguments evaluate to either null or missing, or no arguments specified
  • a value of the first non-null, non-missing argument otherwise

• Example:

    {
        "a": if_missing_or_null(),
        "b": if_missing_or_null(null, missing),
        "c": if_missing_or_null(null, missing, "asterixdb")
    };
  

• The expected result is:

   { "a": null, "b": null, "c": "asterixdb" }
  

The function has an alias ifmissingornull.