APPLY FILTER

Overview

Performs a client-side filter operation on the data set available. Unless otherwise specified, the filter provided will be applied as an SQL where clause. When a Regex, Tx, or XPath filter is specified, the name of the column to use is required.

Syntax

Applies a client-side filter to the pipeline data set. The filter operation can be a SQL filter (default), regular expression, an xpath (JSON or XML), or a deep SQL filter applied after applying a JSON or XML document transformation

APPLY FILTER { < sql | regex | xpath | tx > }
	'...' 
	{ ON '<field>' }
	{ KEEP_EMPTY  }
	{ KEEP_INVALID  }
	{ CASE_INSENSITIVE  }
	{ SINGLE_LINE }
;

TX	The TX filter allows you to transform an XML/JSON column into a data set first in memory, then apply an SQL filter on it
XPath	The XPath filter allows you to apply a XML Xpath or JSON Path filter on a column
Regex	The Regex filter allows you to filter records using a regular expression
SQL	The SQL filter applies a WHERE clause on the data set
ON	The column name on which to apply the filter when using regex, xpath, or tx filter
KEEP_EMPTY	Keep columns that have no data when using regex, xpath, or tx filter
KEEP_INVALID	Keep columns that contain invalid data when using regex, xpath, or tx filter
CASE_INSENSITIVE	Perform a case-insensitive regular expression match
SINGLE_LINE	Use the regular expression as a single line matching operation

Example 1

-- This HTTP request uses the Rss Feed connection and calls the /econi.xml relative URL
-- The APPLY TX operation transforms the HTTP response body using an XPath command that 
-- turns the payload into rows and columns automatically

SELECT * FROM HTTP [Rss Feed] (GET /econi.xml) APPLY TX (//item);

-- Apply a client-side filter using SQL syntax 
APPLY FILTER ([title] like '%january%');

Example 2

-- This HTTP request uses the Rss Feed connection and calls the /econi.xml relative URL
-- The APPLY TX operation transforms the HTTP response body using an XPath command that 
-- turns the payload into rows and columns automatically

SELECT * FROM HTTP [Rss Feed] (GET /econi.xml) APPLY TX (//item);

-- Apply a client-side filter on the 'guid' column using Regex syntax 
APPLY FILTER REGEX '.+2017.+' ON 'guid' 
	CASE_INSENSITIVE 
	SINGLE_LINE;

Example 3

-- This HTTP request uses the Rss Feed connection and calls the /econi.xml relative URL
-- The APPLY TX operation transforms the HTTP response body using an XPath command that 
-- turns the payload into rows and columns automatically; add the raw XML for each
-- row as part of the data set returned

SELECT * FROM HTTP [Rss Feed] (GET /econi.xml) APPLY TX (//item) WITH RAWCOL '_raw';;

-- Apply an XPath client-side filter on the '_raw' column 
APPLY FILTER XPATH '//@*[contains(.,"2019")]' ON '_raw';