IIF (NoSQL query)

  • Article

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

Description
bool_expr A boolean expression, which is evaluated and used to determine which of the two supplemental expressions to use.
true_expr The expression to return if the boolean expression evaluated to true.
not_true_expr The expression to return if the boolean expression evaluated to NOT true.

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.

[
  {
    "id": "68719519221",
    "name": "Estrel Set Cutlery",
    "onSale": true,
    "pricing": {
      "msrp": 55.95,
      "sale": 30.85
    }
  },
  {
    "id": "68719520367",
    "name": "Willagno Spork",
    "onSale": false,
    "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.
  • This function doesn't utilize the index.

See also