Operator Expressions

operators perform a specific operation on the input values or expressions arithmetic operators arithmetic operators are used to perform operations on numeric values operator purpose example a unary operator that denotes a negative value where field name = 1 +, as binary operators, they add or subtract where field name + 1 > 2 multiply where field name 2 > 2 / divide where field name / 2 > 1 % modulo where field name % 2 > 1 abs(x) absolute value where abs(field name) = 1 ceil(x) next whole number where ceil(field name) > 1 floor(x) previous whole number where floor(field name) > 1 string operators performs various operations on string scalar types operator purpose example || string concatenation where field name || 'world' = 'hello world' concat(str, ) string concatenation where concat(field name, 'world') = 'hello world' contains(str, substr) returns true if substr is contained in str , otherwise returns false where contains(field name, 'hello') starts with(str, substr) returns true if str starts with substr , otherwise returns false where starts with(field name, 'hello') ends with(str, substr) returns true if str ends with substr , otherwise returns false where ends with(field name, 'world') byte length(str) returns the length of str in bytes strings in ditto are encoded in utf 8, so a character can take up to 4 bytes where byte length(field name) > 4 char length(str) character length(str) returns the number of characters in str where char length(field name) > 4 regexp like(str, regex, \[flag], ) returns true if str matches the regular expression regex for more information, see the official mozilla developer network docs (mdn) > https //developer mozilla org/en us/docs/web/javascript/guide/regular expressions optional flag properties 'c' case insensitive match 'i' case sensistive match 'm' multiline match 'x' ignore whitespace 's' allow to match newline characters where regexp like(field name, '^foo ') scalar type operators indicates which scalar type to interact with operator purpose example is boolean(x) boolean type test where is boolean(field name) is number(x) float, int, or uint type test where is number(field name) is string(x) string type test where is string(field name) type(x) returns a string name representing the type where type(field name) = 'string' following is the mapping between data types and scalar types; all of which are case sensitive register map attachment null boolean number binary string array object object object conversion operators converts between different value types operator purpose example deserialize json (str) returns the json string deserialized into an object where deserialize json('{"field name" "blue"}') = 'blue' array operators the following table provides an overview of interactions with array arrays can be either scalar values in a register or an input via an argument operator purpose example array contains(array, value) returns true if the array contains the value , otherwise returns false where array contains(field name, 'blue') where array contains(\ your array, field name) array contains null(array) returns true if the array contains a null value, otherwise returns false where array contains null(field name) array length(array) returns the length of the array where array length(field name) > 0 object operators the following table provides an overview of interactions with object objects can be either scalar values in a register , map , attachment , or an input via an argument operator purpose example object length (object) returns the number of key value pairs in the top level of the object where object length(field name) > 0 collection operators the following table provides the collection operators for comparing if a given value is equal to any of the values in a list operator purpose example in (x, y, ) membership test where department in ('hr', 'sales') not in (x, y, ) non membership test where department not in ('hr', 'sales') array and object literals since version 4 8 of the ditto sdk, array and object literals are supported and can be used inline select from your collection name where field1 = \[0, 1] if using a version prior to 4 8, use arguments to pass in your array or object instead select from your collection name where field1 = \ your array comparison operators the comparison operators fall into one of two sub categories m issing value comparisons regular value comparisons dql has two ways of representing missing information in an object t he presence of the field with a null for its value (as in sql) the absence of the field (which json permits) the following table provides operators for comparing if a given value is equal to any of the values in a list null and unknown are synonym keywords and provide the same behavior operator purpose example is null returns true if the value is null, otherwise returns false where field name is null is not null returns true if the value is not null, otherwise returns false where field name is not null is missing returns true if the field is missing in a given document, otherwise returns false where field name is missing is not missing returns true if the field is not missing in a given document, otherwise returns false where field name is not missing is unknown returns true if the value is null, otherwise returns false where field name is unknown is not unknown returns true if the value is not null, otherwise returns false where field name is not unknown = equality test where field name = 100 == equality test where field name == 100 != inequality test where field name != 100 <> inequality test where field name <> 100 < less than where field name < 100 > greater than where field name > 100 <= less than or equal to where field name <= 100 >= greater than or equal to where field name >= 100 comparison operations with null in dql, null represents missing or unknown data it's not a value in the way that 1 or 'text' are values any comparison operation that includes null will result in null equation result null = null null null <> null null 1 = null null 1 > null null conditional operators conditional operators allow you to more easily express conditional logic within your dql queries operator purpose example coalesce(v1, v2, ) returns the first non null value passed to the function where coalesce(field name 1, field name 2, field name 3) = 'foo' decode(input, comp1, res1, \[comp2, res2 ] \[, default value]) compares the input against each comp parameter, and if they match type and value, returns the corresponding res value if no matches are found, the default value is returned (which defaults to null ) where decode(field name, 'foo', 'i found foo', 'bar', 'i found bar', 'i found nothing') = 'foo' nvl(input, default) returns the input if input evaluates to not null, otherwise returns default where nvl(field name, 0) > 10 logical operators logical operators perform logical not , and , and or operations over boolean values ( true and false ), plus null and missing operator purpose example not returns true if the following condition is false, otherwise returns false where not field name = true and returns true if both branches are true, otherwise returns false where field 1 = true and field 2 = false or returns true if one branch is true; otherwise, returns false where field 1 = true or field 2 = false not truth table value result true false false true null null and truth table true null false true true null false null null null false false false false false or truth table true null false true true true true null true null null false true null false