APPLY TX

Overview

The APPLY TX operation transforms a specific column containing a XML or JSON document and replaces the current pipeline data with the transformed data set. It is equivalent to the APPLY TX option available through the SELECT HTTP command, but can be applied anywhere in an ETL pipeline regardless of the original source of the data.

Syntax

Transforms an XML or JSON document from an existing field into rows and columns based on the path provided for each row found in the data pipeline. The resulting transformation becomes the new pipeline data set.

APPLY TX 
	'...'	-- the JSON/XML path
ON  '...'	
{ WITH 
	RAWCOL '...'
}
;

PATH	The Json or XML Path that represents the node(s) to transform; this path supports the Document Path notation specified in the DataZen documentation
ON	The column name or column index to inspect containing the JSON or XML document
ROWCOL	When specified, represents the column name to add with the raw content for each row

Example 1


SELECT * FROM HTTP [Rss Feed] (GET /econi.xml);

-- Apply a JSON transformation on the payload column and add the original 
-- document used to create each row
APPLY TX (//item) ON 'payload' WITH RAWCOL '_raw';

Example 2


-- Call an ADP Endpoint returning demographics data
SELECT * FROM HTTP [ADP] (GET /worker-demographics?limit=100&select=workers/person/legalName);

-- Apply a document transformation on the payload column and add the original 
-- document used to create each row; 
-- The transformation joins multiple nodes within the document 
APPLY TX (workers || person || legalAddress) ON 'payload' WITH RAWCOL '_raw';