Filtering

The bulk API allows you to create filter statements in export definitions. Filters enable you to select the exact data that you wish to export. Filters use the comparison, logic, and existence operators specified by Eloqua expression language to filter export data based on Eloqua field values, existence in contact filters, contact segments, contact lists, account lists, and status in AppCloud services.

Eloqua uses the SQL server implementation of the LIKE operator.

The correct syntax format for Eloqua field values is:

'{{EML_field_statement}}' <operator> '<value>'

Note that the field, expressed in Eloqua markup language, and the value are both wrapped in single quotes.

The correct syntax format for using the EXISTS existence operator is:

EXISTS('{{EML_entity_statement}}')

The correct syntax format for using the STATUS existence operator is:

STATUS('{{EML_service_statement}}') = '<status>'

For more detail on the syntax for each entity, see Eloqua expression language.

Note: Filters have a 1000 character limit. Contact fields cannot be used in the filter for activity exports.

Simple filters

Simple filters include one criterion and no logical operators, such as one predicate, filtering on a single field, or one existence operator. The following export definitions illustrate simple filters:

Export contacts whose country is Belgium:

{
    "name": "Belgian Contacts Export",
    "fields": {
        "ID": "{{Contact.Id}}",
        "FirstName": "{{Contact.Field(C_FirstName)}}",
        "LastName": "{{Contact.Field(C_LastName)}}",
        "Country": "{{Contact.Field(C_Country)}}"
    },
    "filter": "'{{Contact.Field(C_Country)}}' = 'BE'"
}

Export activities of type "EmailClickthrough":

{
    "name": "Bulk Activity Export - Email Clickthrough",
    "fields": {
        "ActivityId": "{{Activity.Id}}",
        "ActivityType": "{{Activity.Type}}",
        "ActivityDate": "{{Activity.CreatedAt}}",
        "EmailAddress": "{{Activity.Field(EmailAddress)}}",
        "ContactId": "{{Activity.Contact.Id}}",
        "IpAddress": "{{Activity.Field(IpAddress)}}",
        "VisitorId": "{{Activity.Visitor.Id}}",
        "VisitorExternalId": "{{Activity.Visitor.ExternalId}}",
        "EmailRecipientId": "{{Activity.Field(EmailRecipientId)}}",
        "AssetType": "{{Activity.Asset.Type}}",
        "AssetName": "{{Activity.Asset.Name}}",
        "AssetId": "{{Activity.Asset.Id}}",
        "SubjectLine": "{{Activity.Field(SubjectLine)}}",
        "EmailWebLink": "{{Activity.Field(EmailWebLink)}}",
        "EmailClickedThruLink": "{{Activity.Field(EmailClickedThruLink)}}",
        "CampaignId": "{{Activity.Campaign.Id}}",
        "ExternalId": "{{Activity.ExternalId}}",
        "EmailSendType": "{{Activity.Field(EmailSendType)}}"
    },
    "filter": "'{{Activity.Type}}' = 'EmailClickthrough'",
}

Export contacts based on their presence in the contact List with ID 123:

{
    "name": "List 123 Contacts Export",
    "fields": {
        "ID": "{{Contact.Id}}",
        "FirstName": "{{Contact.Field(C_FirstName)}}",
        "LastName": "{{Contact.Field(C_LastName)}}",
        "EmailAddress": "{{Contact.Field(C_EmailAddress)}}"
    },
    "filter": "EXISTS('{{ContactList[123]}}')"
}

Export contacts based on their presence in the contact segment with ID 5:

{
    "name": "Segment 5 Contacts Export",
    "fields": {
        "ID": "{{Contact.Id}}",
        "FirstName": "{{Contact.Field(C_FirstName)}}",
        "LastName": "{{Contact.Field(C_LastName)}}",
        "EmailAddress": "{{Contact.Field(C_EmailAddress)}}"
    },
    "filter": "EXISTS('{{ContactSegment[5]}}')"
}
Note: EXISTS operators can only be used on exports of the same entity:
  • The ContactList, ContactFilter, or ContactSegment EXISTS operators can only be used in filters for contact exports.
  • The AccountList EXISTS operator can only be used in filters for account exports.

Export contacts for which the AppCloud action service instance ID is f82d50cd86a94fcab37e4ec9a98b0339, with an execution ID of 12345 and a status is pending:

{
    "name": "Action Service Contacts Export",
    "fields": {
        "ID": "{{Contact.Id}}",
        "EmailAddress": "{{Contact.Field(C_EmailAddress)}}"
    },
    "filter" : "STATUS('{{ActionInstance(f82d50cd86a94fcab37e4ec9a98b0339).Execution[12345]}}') = 'pending'"
}

With areSystemTimestampsInUTC request parameter set to true, export accounts that have been created after September 9, 2016 13:46:20.975 UTC:

{
    "name": "System Timestamps in UTC Export",
    "fields": {
        "companyName": "{{Account.Field(M_CompanyName)}}",
        "createdDate": "{{Account.Field(M_DateCreated)}}",
        "modifiedDate": "{{Account.Field(M_DateModified)}}"
    },
    "areSystemTimestampsInUTC": true,
    "filter": "'{{Account.Field(M_DateCreated)}}' > '2016-09-01 13:46:20.975'"
}

Here is an example filtering on the same UTC date value using markers:

{  
    "name":"System Timestamps in UTC Export",
    "fields":{  
        "companyName":"{{Account.Field(M_CompanyName)}}",
        "createdDate":"{{Account.Field(M_DateCreated)}}",
        "modifiedDate":"{{Account.Field(M_DateModified)}}"
    },
    "areSystemTimestampsInUTC":true,
    "filter":"'{{Account.Field(M_DateCreated)}}' > '2016-09-01T13:46:20.975Z'"
}

Export all custom object records that are mapped to a contact in custom object with ID 9:

{
    "name": "Custom Object Records Export - Mapped to Contacts",
    "fields": {
        "Id": "{{CustomObject[9].ExternalId}}",
        "ContactId": "{{CustomObject[9].Contact.Id}}",
        "ContactFirstName": "{{CustomObject[9].Contact.Field(C_FirstName)}}"
   },
   "filter": "'{{CustomObject[9].Contact.Id}}' > '0'"
}

Export all custom object records that are not mapped to a contact in custom object with ID 9:

{
    "name": "Custom Object Records Export - Not Mapped to Contacts",
    "fields": {
        "Id": "{{CustomObject[9].ExternalId}}",
        "ContactId": "{{CustomObject[9].Contact.Id}}",
        "ContactFirstName": "{{CustomObject[9].Contact.Field(C_FirstName)}}"
   },
   "filter": "'{{CustomObject[9].Contact.Id}}' = ''"
}

Complex filters

The bulk API also supports complex filters that filter based on multiple criteria using the AND, OR, and NOT operators.

Note: You can only use one EXISTS and STATUS operator in a filter.

The bulk API processes complex filters much like a WHERE clause in a SQL environment. You can use parentheses to group predicates together.

Important: Supported filter formats with logical operators:

  • (A OR B) AND (C OR D)
  • A AND NOT B AND (C OR D)
  • A AND B AND (C OR D)
  • A AND (B OR C)

Note: Activity exports only support the A AND B AND C filter format.

The following export definitions illustrate some complex filters:

Export contacts whose country is Belgium or France:

{
    "name": "Belgian and French Contacts Export",
    "fields": {
        "ID": "{{Contact.Id}}",
        "FirstName": "{{Contact.Field(C_FirstName)}}",
        "LastName": "{{Contact.Field(C_LastName)}}",
        "Country": "{{Contact.Field(C_Country)}}"
    },
    "filter": "'{{Contact.Field(C_Country)}}' = 'BE' OR '{{Contact.Field(C_Country)}}' = 'FR'"
}

Export activities of type "EmailClickedThrough" that occurred in August 2014:

{
    "name":"EmailClickthrough Activity Export - Aug 2014",
    "fields": {
        "ActivityId": "{{Activity.Id}}",
        "ActivityType": "{{Activity.Type}}",
        "ActivityDate": "{{Activity.CreatedAt}}",
        "EmailAddress": "{{Activity.Field(EmailAddress)}}",
        "ContactId": "{{Activity.Contact.Id}}",
        "IpAddress": "{{Activity.Field(IpAddress)}}",
        "VisitorId": "{{Activity.Visitor.Id}}",
        "VisitorExternalId": "{{Activity.Visitor.ExternalId}}",
        "EmailRecipientId": "{{Activity.Field(EmailRecipientId)}}",
        "AssetType": "{{Activity.Asset.Type}}",
        "AssetName": "{{Activity.Asset.Name}}",
        "AssetId": "{{Activity.Asset.Id}}",
        "SubjectLine": "{{Activity.Field(SubjectLine)}}",
        "EmailWebLink": "{{Activity.Field(EmailWebLink)}}",
        "EmailClickedThruLink": "{{Activity.Field(EmailClickedThruLink)}}",
        "CampaignId": "{{Activity.Campaign.Id}}",
        "ExternalId": "{{Activity.ExternalId}}",
        "EmailSendType": "{{Activity.Field(EmailSendType)}}"
   },
   "filter":"'{{Activity.Type}}' = 'EmailClickthrough' AND '{{Activity.CreatedAt}}' >= '2014-08-01' AND '{{Activity.CreatedAt}}' < '2014-09-01'"
}

Export contacts whose CreatedAt field is greater than 2013-12-31 and whose account's company name is not Oracle and whose C_Country field is Canada or United States:

{
    "name": "Contacts Created after 2013 - Not Oracle - Canada or US",
    "fields": {
        "firstName": "{{Contact.Field(C_FirstName)}}",
        "lastName": "{{Contact.Field(C_LastName)}}",
        "email": "{{Contact.Field(C_EmailAddress)}}",
        "country": "{{Contact.Field(C_Country)}}"
    },
    "filter": "'{{Contact.CreatedAt}}' > '2013-12-31' AND '{{Contact.Account.Field(M_CompanyName)}}' != 'Oracle' AND ('{{Contact.Field(C_Country)}}' = 'CA' OR '{{Contact.Field(C_Country)}}' = 'US')"
}

Related