Query operation field selection, filtering, and sorting

When implementing the Query operation, you can filter, sort, and select fields based on simple type root elements in the schema that you select during browsing. Their child elements inherit those characteristics by default.

Schema example 1: Filtering, sorting, and selecting by firstName, lastName, and phone fields

In this schema example, the XML structure has firstName, lastName, and phone fields all of which you can filter, sort, and/or select:

<User>
  <firstName>Joe</firstName>
  <lastName>Smith</lastName>
  <phone>555-1234</phone>
</User>

You can use annotations in your schema to add and/or remove fields from the available field list. For example, the following annotation to the phone element definition in the User schema ignores the phone element as a filter field and a sort field, but makes it a selectable field:

<xs:annotation>
  <xs:appinfo>
    <fieldSpec ignoreForFilters="true" ignoreForSorts="true" ignoreForSelection="false" xmlns="http://www.boomi.com/connector/annotation" />
  </xs:appinfo>
</xs:annotation>

Schema example 2: Adding annotations for additional filtering, sorting, and selecting

The following example adds annotation to the top-level User element so you can perform additional filtering, sorting, and selecting. In this snippet, you:

• Add filter field options to email, fullName, and zipCode.
• A sort option to city.
• A selectable and selected option to city and state.
• type and typeNameSpace options to the zipcode field.

Fields that are added automatically from the schema take the type and typeNamespace from the schema.

On the zipCode field, the type and typeNamespace attributes indicate the data type and namespace of the data type. You can limit the filter and/or sort operators that display when the field is used by combining these attributes with the type and typeNamespace attributes on the filter operator and sort operator elements in the descriptor.

There are two selection attributes — one for global settings and one for individual fields. Global settings override individual settings, so:

• When @allowFieldSelection is false, field selection is disabled. As a result, the Query operation does not display the Fields tab and no field selection is available. If the connector developer prefers to show the Fields tab and allow selection by the user, they can set this attribute to true.
• When @fieldSelectionNone is false, no fields are selected by default on the Fields tab. If the connector developer prefers that no fields are selected by default, but still allow individual selection by the user, they can set this attribute to true.

The default value for selected is false. In the following example, with fieldSelectionNone set to true, only state is selected by default on import.

<xs:annotation>
  <xs:appinfo>
    <fieldSpec xmlns="http://www.boomi.com/connector/annotation">
      <field name="email" filterable="true"/>
      <field name="fullName" filterable="true"/>
      <field name="state" filterable="true" sortable="true" selectable="true" selected="true"/>
      <field name="city" sortable="true" selected="true"/>
      <field name="address" selectable="true"/>
      <field name="zipCode" filterable="true" type="number" typeNamespace="myNamespace"/>
    </fieldSpec>
  </xs:appinfo>
</xs:annotation>

Here is the corresponding JSON code snippet:

"annotations": {
    "boomi_fieldSpec": {
       "ignoreForSort": true,
       "ignoreForSelection": false,
       "fields": {
          "email": {
             "filterable": true,
      },
          "fullname": {
             "filterable": true,
     },
          "state": {
            "filterable": true,
            "sortable": true,
            "selectable": true,
            "selected": true
       }
        "city": {
            "filterable": true,
            "sortable": true,
            "selectable": true,
            "selected": true
       }
        "address": {
            "selectable": true,
       }
        "zipcode": {
            "filterable": true,
            "type": number,
            "typeNamespace": myNamespace
       }
    }
   }
 }

The filter, sort, and selection fields display the number of levels below the root element of the schema that is specified by the fieldSelectionLevels attribute for the Query operation in the descriptor. The default is two levels, as shown in this example:

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">

	<xs:complexType name="User">
		<xs:sequence>
			<xs:element minOccurs="0" name="ID" type="xs:string" />
			<xs:element minOccurs="0" name="firstName" type="xs:string" />
			<xs:element minOccurs="0" name="lastName" type="xs:string" />
			<xs:element minOccurs="0" name="address" type="Address" />
		</xs:sequence>
	</xs:complexType>
	<xs:complexType name="Address">
		<xs:sequence>
			<xs:element minOccurs="1" name="street" type="Street" />
			<xs:element minOccurs="1" name="city" type="xs:string" />
			<xs:element minOccurs="1" name="state" type="xs:string" />
			<xs:element minOccurs="1" name="zipCode" type="xs:string" />
		</xs:sequence>
	</xs:complexType>
	<xs:complexType name="Street">
		<xs:sequence>
			<xs:element minOccurs="1" name="street1" type="xs:string" />
			<xs:element minOccurs="0" name="street2" type="xs:string" />
		</xs:sequence>
	</xs:complexType>
</xs:schema>

Schema example 3: Filtering, sorting, and selecting fields three levels deep

In the following code snippet, the Street complex type is three levels deep. To make the street1 and street2 elements available as filters, sorts, or selection fields more than two levels from the root element, either set the fieldSelectionLevels attribute value to 3 or add an annotation to the User or to the Address complex types:

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">

	<xs:complexType name="User">
		<xs:sequence>
			<xs:element minOccurs="0" name="ID" type="xs:string" />
			<xs:element minOccurs="0" name="firstName" type="xs:string" />
			<xs:element minOccurs="0" name="lastName" type="xs:string" />
			<xs:element minOccurs="0" name="address" type="Address" />
		</xs:sequence>
	</xs:complexType>
	<xs:complexType name="Address">
		<xs:annotation>
			<xs:appinfo>
				<fieldSpec xmlns="http://www.boomi.com/connector/annotation">
					<field filterable="true" name="street1" selectable="true"
						sortable="true" />
					<field filterable="true" name="street2" selectable="true" 
						sortable="true" />
				</fieldSpec>
			</xs:appinfo>
		</xs:annotation>
		<xs:sequence>
			<xs:element minOccurs="1" name="street" type="Street" />
			<xs:element minOccurs="1" name="city" type="xs:string" />
			<xs:element minOccurs="1" name="state" type="xs:string" />
			<xs:element minOccurs="1" name="zipCode" type="xs:string" />
		</xs:sequence>
	</xs:complexType>
	<xs:complexType name="Street">
		<xs:sequence>
			<xs:element minOccurs="1" name="street1" type="xs:string" />
			<xs:element minOccurs="0" name="street2" type="xs:string" />
		</xs:sequence>
	</xs:complexType>
</xs:schema>

If you use ignoreForFilters="true", ignoreForSorts="true", or ignoreForSelection="true" at the top-level element, all child elements with filter options, sort options, or selection options are ignored.

note

The representation of JSON schema annotations for Query browsing generally corresponds to the XML representation:

• The JSON boomi_fieldSpec annotation property corresponds to the XML fieldSpec element.

• boomi_fieldSpec properties and values for specifying field filtering, sorting, and selection options are identical to the XML fieldSpec element’s attributes and values.

The following JSON represents the phone field definition with annotation from the first schema example in this topic:

"phone" : {
  "type": "string", 
  "annotations": {
    "boomi_fieldSpec": { "ignoreForFilters": false, "ignoreForSort": true, "ignoreForSelection": true }
  }
}

note

The JSON implementation in Integration supports only the JSON schema primitive data types (integer, number, string, boolean, null, object, and array) as field properties. Date and/or time fields are typically defined as strings in supported JSON field definitions.

Declaring fields in an object definition

You can use Java to declare filter, sort, and selection fields directly in the object definitions rather than declare them in a schema. If you use Java browsing and either XML or JSON schema browsing, Java browsing takes precedence.

Filtering Query operations with binary results

note

Binary results do not have schemas. If your connector returns binary results (for example, the Mail connector) consider using Java browsing.

To filter or sort binary results, add these Java object definitions in the getObjectDefinitions method:

ObjectDefinition definition = new ObjectDefinition(); 
                    
FieldSpecField filterable = new FieldSpecField().withName("email").withFilterable(true);
definition.getFieldSpecFields().add(filterable);
                    
FieldSpecField sortable = new FieldSpecField().withName("fullName").withFilterable(true);
definition.getFieldSpecFields().add(sortable);
                    
FieldSpecField selectable = new FieldSpecField().withName("state").withFilterable(true).withSortable(true).withSelectable(true).withSelected(true);
definition.getFieldSpecFields().add(selectable);
                    
FieldSpecField selected = new FieldSpecField().withName("city").withSortable(true).withSelected(true);
definition.getFieldSpecFields().add(selected);
                    
FieldSpecField stringType = new FieldSpecField().withName("address").withSelectable(true);
definition.getFieldSpecFields().add(stringType);
                    
FieldSpecField numberType = new FieldSpecField().withName("zipCode").withFilterable(true).withType("number").withTypeNamespace("myNamespace");
definition.getFieldSpecFields().add(numberType);