IIF (NoSQL query)

APPLIES TO: NoSQL

Evaluates a boolean expression and returns the result of one of two expressions depending on the result of the boolean expression. If the boolean expression evaluates to true, return the first expression option. Otherwise, return the second expression option.

Syntax

IIF(<bool_expr>, <true_expr>, <not_true_expr>)

Arguments

Return types

Returns an expression, which could be of any type.

Examples

This first example evaluates a static boolean expression and returns one of two potential expressions.

SELECT VALUE {
    evalTrue: IIF(true, 123, 456),
    evalFalse: IIF(false, 123, 456),
    evalNumberNotTrue: IIF(123, 123, 456),
    evalStringNotTrue: IIF("ABC", 123, 456),
    evalArrayNotTrue: IIF([1,2,3], 123, 456),
    evalObjectNotTrue: IIF({"name": "Alice", "age": 20}, 123, 456)
}

[
  {
    "evalTrue": 123,
    "evalFalse": 456,
    "evalNumberNotTrue": 456,
    "evalStringNotTrue": 456,
    "evalArrayNotTrue": 456,
    "evalObjectNotTrue": 456
  }
]

This example evaluates one of two potential expressions on multiple items in a container based on an expression that evaluates a boolean property.

[
  {
    "name": "Estrel Set Cutlery",
    "onSale": true,
    "category": "camp-utensils",
    "pricing": {
      "msrp": 55.95,
      "sale": 30.85
    }
  },
  {
    "name": "Willagno Spork",
    "onSale": false,
    "category": "camp-utensils",
    "pricing": {
      "msrp": 20.15,
      "sale": 12.55
    }
  }
]

The query uses fields in the original items.

SELECT
    p.name,
    IIF(p.onSale, p.pricing.sale, p.pricing.msrp) AS price
FROM
    products p
WHERE
    p.category = "camp-utensils"

[
  {
    "name": "Estrel Set Cutlery",
    "price": 30.85
  },
  {
    "name": "Willagno Spork",
    "price": 20.15
  }
]

Remarks

• This function is similar to the ternary conditional operator in various programming languages. For more information, see ternary conditional operator.
• This function doesn't utilize the index.

See also

• System functions
• ToString