Data Filter
This component allows you to filter the data. Different options are available:
- SQL Filter: apply a simple SQL Server where clause (ex: [value] LIKE '%cash%') - certain restrictions apply (for full control, use the Run SQL component)
- Column 2 Dataset SQL Filter: transform a JSON/XML column into rows and columns, then apply a simple SQL Server where clause (ex: [value] LIKE '%cash%') - certain restrictions apply (for full control, use the Run SQL component)
- Column XML/JSON Path: apply a JSON Path or XPath operation on a column containing either an XML or JSON document (ex: //entry[value=1] )
- Regex Filter: apply a Regex filter on a specific column -
SQL Filter Considerations
When using a SQL filter, only a limited subset of operations are supported. For example, you can use simple operators that compare field values:
price >= 50, use dates birthdate < #1/31/2000#, use most operators (LIKE, IN, =, >, <... ), arithmetic operations,
wildcard characters (% and *) used in brackets, and a few functions: CONVERT, LEN, ISNULL, IIF, TRIM and
SUBSTRING. For more details, please see Microsoft's documentation on the
DataColumn Expression property.
To have full control over your data transformation and filtering capabilities, consider using the Run SQL component.
Column 2 DataSet XML/JSON Filter
Use this option to perform an SQL filter on the content of a field that contains JSON or XML. In addition to the considerations provided earlier as it relates to the use of the filter operation, the automatic conversion of the field is limited to the first two levels of the document. It performs an operation similar to the Document Path transformation feature, but the path is essentially the root node. For more control, consider using the Column XML/JSON Path option, which allows you to enter a complex XPath / JSON Path operation.
Example
In this example, the RegEx filter is used to keep all rows that contain the word 'accountkey', regardless of case.
