Navigation

Supported SQL Functions and Operators

MongoDB Connector for BI Version 2.2 is compatible with SQL-99 SELECT statements.

The following tables list the various MySQL functions, constructors, and operators supported in MongoDB Connector for BI 2.2.

Comparison Functions and Operators

Function/Operator Description
BETWEEN ... AND ... Check whether a value is within a range of values
COALESCE() Return the first non-NULL argument
= Equal operator
<=> NULL-safe equal to operator
> Greater than operator
>= Greater than or equal operator
GREATEST() Return the largest argument
IN() Check whether a value is within a set of values
IS Test a value against a boolean
IS NOT Test a value against a boolean
IS NOT NULL NOT NULL value test
IS NULL NULL value test
ISNULL() Test whether the argument is NULL
LEAST() Return the smallest argument
< Less than operator
<= Less than or equal operator
LIKE Simple pattern matching
NOT BETWEEN ... AND ... Check whether a value is not within a range of values
!=, <> Not equal operator
NOT IN() Check whether a value is not within a set of values

Logical Operators

Function/Operator Description
AND, && Logical AND
NOT, ! Negates value
||, OR Logical OR
XOR Logical XOR

JOIN Expressions

Expression Description
JOIN Select records that have matching values in multiple tables.
INNER JOIN Semantically equivalent to JOIN.
LEFT JOIN Return all records from the left table, and the matched records from the right table.
RIGHT JOIN Return all records from the right table, and the matched records from the left table.
NATURAL JOIN Return only records which do not appear in both tables.

Control Flow Functions and Operators

Function/Operator Description
CASE Case operator
IF() If/else construct
IFNULL() Null if/else construct
NULLIF() Return NULL if expr1 = expr2

String Functions

Function/Operator Description
ASCII() Return numeric value of left-most character
CHAR_LENGTH() Return number of characters in argument
CHARACTER_LENGTH() Synonym for CHAR_LENGTH()
CONCAT() Return concatenated string
CONCAT_WS() Return concatenate with separator
ELT() Return string at index number
INSERT() Insert a substring at the specified position up to the specified number of characters
INSTR() Return the index of the first occurrence of substring
LCASE() Synonym for LOWER()
LEFT() Return the leftmost number of characters as specified
LENGTH() Return the length of a string in bytes
LOCATE() Return the position of the first occurrence of substring
LOWER() Return the argument in lowercase
LTRIM() Remove leading spaces
MID() Return a substring starting from the specified position
NOT LIKE Negation of simple pattern matching
NOT REGEXP Negation of REGEXP
REGEXP Pattern matching using regular expressions
REPLACE() Replace occurrences of a specified string
RIGHT() Return the specified rightmost number of characters
RTRIM() Remove trailing spaces
SPACE() Return a string of the specified number of spaces
SUBSTR() Return the substring as specified
SUBSTRING() Return the substring as specified
SUBSTRING_INDEX() Return a substring from a string before the specified number of occurrences of the delimiter
TRIM() Remove leading and trailing spaces
UCASE() Synonym for UPPER()
UPPER() Convert to uppercase

Numeric Functions and Operators

Function/Operator Description
ABS() Return the absolute value
ACOS() Return the arc cosine
ASIN() Return the arc sine
ATAN() Return the arc tangent
ATAN2(), ATAN() Return the arc tangent of the two arguments
CEIL() Return the smallest integer value not less than the argument
CEILING() Return the smallest integer value not less than the argument
COS() Return the cosine
COT() Return the cotangent
DEGREES() Convert radians to degrees
DIV Integer division
/ Division operator
EXP() Raise to the power of
FLOOR() Return the largest integer value not greater than the argument
LN() Return the natural logarithm of the argument
LOG() Return the natural logarithm of the first argument
LOG10() Return the base-10 logarithm of the argument
LOG2() Return the base-2 logarithm of the argument
- Minus operator
MOD() Return the remainder
%, MOD Modulo operator
PI() Return the value of pi
+ Addition operator
POW() Return the argument raised to the specified power
POWER() Return the argument raised to the specified power
RADIANS() Return argument converted to radians
ROUND() Round the argument
SIGN() Return the sign of the argument
SIN() Return the sine of the argument
SQRT() Return the square root of the argument
TAN() Return the tangent of the argument
* Multiplication operator
TRUNCATE() Truncate to specified number of decimal places
- Change the sign of the argument

Date and Time Functions

Function/Operator Description
ADDDATE() Add time values (intervals) to a date value
CURDATE() Return the current date
CURRENT_DATE(), CURRENT_DATE Synonyms for CURDATE()
CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP Synonyms for NOW()
DATE() Extract the date part of a date or datetime expression
DATEDIFF() Returns the difference, in days, between two specified dates.
DATE_ADD() Add time values (intervals) to a date value
DATE_FORMAT() Format date as specified
DATE_SUB() Subtract a time value (interval) from a date
DAY() Synonym for DAYOFMONTH()
DAYNAME() Return the name of the weekday
DAYOFMONTH() Return the day of the month (0-31)
DAYOFWEEK() Return the weekday index of the argument
DAYOFYEAR() Return the day of the year (1-366)
EXTRACT() Extract part of a date
FROM_DAYS() Convert a day number to a date
HOUR() Extract the hour
LAST_DAY Return the last day of the month for the argument
MAKEDATE() Create a date from the year and day of year
MINUTE() Return the minute from the argument
MONTH() Return the month from the date passed
MONTHNAME() Return the name of the month
NOW() Return the current date and time
QUARTER() Return the quarter from a date argument
SECOND() Return the second (0-59)
STR_TO_DATE() Convert a string to a date
SUBDATE() Synonym for DATE_SUB() when invoked with three arguments
TIME_TO_SEC() Return the argument converted to seconds
TIMEDIFF() Subtract time
TIMESTAMP() With a single argument, this function returns the date or datetime
TIMESTAMPADD() Add an interval to a datetime expression
TIMESTAMPDIFF() Subtract an interval from a datetime expression
TO_DAYS() Return the date argument converted to days
UTC_TIMESTAMP() Return the current UTC date and time
WEEK() Return the week number
WEEKDAY() Return the weekday index
WEEKOFYEAR() Return the calendar week of the date (1-53)
YEAR() Return the year
YEARWEEK() Return the year and week

Cast Functions and Operators

Function Description
CAST() Cast a value as a certain type
CONVERT() Convert a value as a certain type

Subquery Constructors

Function Description
ROW() Returns one row of values rather than one column of values

Information Functions

Function Description
CONNECTION_ID() Return the connection ID (thread ID) for the connection
CURRENT_USER(), CURRENT_USER The authenticated user name and host name
DATABASE() Return the default (current) database name
SCHEMA() Synonym for DATABASE()
SESSION_USER() Synonym for USER()
SYSTEM_USER() Synonym for USER()
USER() The user name and host name provided by the client
VERSION() Return a string that indicates the MySQL server version

GROUP BY (Aggregate) Functions

Function Description
AVG() Return the average value of the argument
COUNT() Return a count of the number of rows returned
COUNT(DISTINCT) Return the count of a number of different values
MAX() Return the maximum value
MIN() Return the minimum value
STD() Return the population standard deviation
STDDEV() Return the population standard deviation
STDDEV_POP() Return the population standard deviation
STDDEV_SAMP() Return the sample standard deviation
SUM() Return the sum

Utility Statements

Statement Description
USE <databaseName> Choose the database to use as the current database for subsequent statements

Database Administration Statements

Statement Description
SHOW CREATE TABLE <tableName> Shows the structure of the <tableName> table. The structure includes comments that describe the mapping from the SQL table and columns to MongoDB collection and fields.
SHOW [GLOBAL | SESSION] STATUS [LIKE 'pattern' | WHERE expr]

Shows mongosqld server metrics. The structure contains non-empty values for at least the following fields:

  • Bytes_received
  • Bytes_sent
  • Connections
  • Queries
  • Threads_connected
  • Threads_created
  • Uptime

By default the SHOW STATUS statement will only show statistics for the current connection, corresponding to the SESSION qualifier. If you specify the GLOBAL qualifier, SHOW STATUS will instead show aggregate statistics for the entire server.