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>' }
	{ LIMIT <n> }
	{ ORDER BY '<field> { ASC | DESC }, ...' }
	{ 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
LIMIT	Limits the returned data set to the number of rows requested
ORDER BY	Orders the data set using the list of fields provided (applied before the LIMIT operation); supports the ASC (default) or DESC specfier after the list of columns
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 
-- Limit to 2 rows in ascending order on the [guid] field and descending on the [title] field
APPLY FILTER XPATH '//@*[contains(.,"2019")]' ON '_raw' ORDER BY 'guid,title desc' LIMIT 2;