Writing queries in MQL

MQL is the language provided by magnews to query platform entities such as contacts or communications, as well as custom data tables.

You can run these queries through:

• REST APIs, such as Query contacts, Query audiences, Bulk download, MQL select, etc.

• SOAP APIs, such as queryContacts, queryContents, and executeSimpleQuery.

Or directly within Giotto communication code.

MQL is a sub-dialect of standard SQL, with some extensions and some limitations.

Basic syntax

Basic syntax is:

SELECT * FROM TABLENAME WHERE CONDITIONS ORDER BY ORDERBYEXPRESSIONS

In most queries, you need to use the asterisk (*) instead of specifying individual table fields.

 SELECT * FROM CONTACTS WHERE status='subscribed'

 SELECT * FROM CONTACTS WHERE name = ?

 SELECT * FROM CONTACTS WHERE relationfield in (SELECT myvalue FROM MYCUSTOMTABLE WHERE customfield=123)

 SELECT name,myfield FROM MYCUSTOMTABLE ORDER BY myorderfield

 SELECT name,myfield FROM MYCUSTOMTABLE INNER JOIN OTHERTABLE ON MYCUSTOMTABLE.a=OTHERTABLE.b ORDER BY myorderfield

 SELECT * FROM MYCUSTOMTABLE WHERE myfield in (SELECT idcontact from CONTACTS)

 SELECT * FROM MYCUSTOMTABLE WHERE myfield = strtonumber('100,66')

 SELECT max(myfield) FROM MYCUSTOMTABLE

 SELECT * FROM CONTACTS WHERE to_datetime(data_ultima_modifica) >= now(-3600)

 SELECT * FROM CONTACTS WHERE to_date(data_iscrizione) >= today(-2)

 SELECT * FROM CONTACTS WHERE to_date(data_iscrizione) >= strtodate('10/01/2020')

 SELECT * FROM CONTACTS WHERE to_datetime(data_iscrizione) >= strtodatetime('10/01/2020 00:05:00')

 SELECT email, data_eliminazione FROM contacts WHERE status='unsubscribed' AND to_date(data_eliminazione) = strtodate('05/08/2020 19:22:00')

 SELECT * FROM WEBPAGES WHERE name  LIKE 'mypagenamestartswith%'

 SELECT * FROM CONTACTS WHERE to_date(data_ultima_modifica) >= to_date(dateadd(data_ultima_modifica, -1, 'day'))

 SELECT * FROM CONTACTS WHERE STATS.TOTALCLICKS > 10

 SELECT * FROM CONTACTS WHERE STATS.IDCHANNEL = 0 AND STATS.TOTALCLICKS > 10

Supported SQL functions

In queries executed via API, you can use certain SQL functions to process the returned data. Below is the list of currently supported functions.

• count(*): same as SQL function
• max(columnname): same as SQL function
• min(columnname): same as SQL function
• avg(columnname): same as SQL function
• sum(columnname): same as SQL function
• lcase(columnname): same as SQL function (convert string to lowercase)
• ucase(columnname): same as SQL function (convert string to uppercase)
• to_date(columnname): converts a column/scalar value to a date without time information
• to_datetime(columnname): converts a column/scalar value to a date with time information
• to_int (columnname): converts a value (string, decimal...) in an integer
• to_string (columnname): converts a value (date, decimal...) into a string value
• strtodate(date_literal): converts a string written as the date format of the user which is issuing the query (using the user timezone)
• strtodatetime(datetime_literal): converts a string written as the datetime format of the user which is issuing the query (using the user timezone)
• strtonumber (number_literal): converts a string containing numeric characters in a number
• now(offset_in_millis): obtains the current date time, scaled by an offset in milliseconds
• today(offset_in_day): obtains the current date without time (at midnight), scaled by an offset in days
• year(columnname): same as SQL function
• dayofmonth(columnname): same as SQL function
• weekday(columnname): same as SQL function
• month(columnname): same as SQL function
• dateadd(columnname,number_of_days,'day'): adds a number of days to the specified date
• fielddescription(fieldname): performs the lookup of a value and returns the value description of a list of value field

Dealing with dates and datetimes

When you write datetime and date literals directly in your MQL query, pay attention to always use the strtodate/strtodatetime functions which guarantee you that the system is going to parse them using your datetime format and especially your timezone.

Joins and Subqueries

You can use INNER JOIN, RIGHT JOIN and LEFT JOINs while writing queries which are on a custom table but not on system tables.
You can always use the 'IN' operator with a subselect in order to obtain the same result.
In subselects you cannot use data from the outer query.