coalesce.ioTake documentation survey

Parsers

Coalesce's parsers analyze the structure of entries within a variant column and automatically generate columns and a JOIN that captures the different attributes within them.

Suggest Edits

JSON Parsing

Open up any non-source node, right-click on the column, and select Derive JSON mappings.

Derive JSON mappings will recursively:

  • Create a column in the mapping grid for every primitive type (string, number, boolean, and null) within the object with the appropriate transform to parse that value.
  • Flatten every JSON array using a table function in the Join Tab.
Derive JSON mappings

Derive JSON mappings

Setting JSON Sample Size

By default, the JSON parser captures only one record of the variant column. To scan for variation in the JSON shape across entries, the parser sample size (applies to both JSON and XML) can be increased via User Menu → Org Settings > Preferences. This preference is stored organization wide.

This number affects the number of rows we will attempt to scan for variances in JSON shape. Coalesce uses Snowflake's table sample to construct a sample of data. This is a sample of what is queried: 

SELECT /* variant_column_name */ FROM /* fully qualified table */ SAMPLE ( /* sample size */ ROWS)

🚧

Sample Size Limit

If a user enters a sample size larger than the number of rows in the data, all the rows in the data will be scanned for structure.

JSON Parser sample size

Parser sample size

Data Type Mapping JSON

When the JSON parser scans for variances in JSON shape, a mapping occurs to convert JSON data types to the appropriate corresponding Snowflake data type.

JSON Data TypeSnowflake Data Type
NUMBER *
double- precision floating-point		DOUBLE
String
double-quoted Unicode with backslash escaping		STRING
Boolean
true or false		BOOLEAN
Null
empty		STRING
Array
an ordered sequence of values		Flattens compound values into multiple rows
Object
an unordered collection of key:value pairs		Explodes compound values into multiple columns

📘

Coalesce Versions < 5.4

Prior to Coalesce version 5.4, JSON Numbers were mapped to Snowflake NUMBER(38,0), resulting in truncation of decimal places and converting them to integers.

XML Parsing (Beta Feature)

Open up any non-source node, right-click on the variant column, and select Derive Mappings > From XML.

Derive XML mappings will recursively:

  • Create a column in the mapping grid for every element and every attribute within the XML with the appropriate transform to parse that value.
  • Flatten any XML sibling elements using a function in the Join Tab
Derive XML Mappings

Derive XML Mappings

Setting XML Sample Size

By default, the XML parser captures only one record of the variant column. To scan for variation in the XML shape across entries, the parser sample size can be increased via User Menu → Org Settings → Preferences. The parser sample size selected here applies to both JSON and XML and is used org-wide.

This number affects the number of rows we will attempt to scan for variances in XML shape. Coalesce uses Snowflake's table sample to construct a sample of data. This is a sample of what is queried: 

SELECT /* variant_column_name */ FROM /* fully qualified table */ SAMPLE ( /* sample size */ ROWS)

🚧

Sample Size Limit

If a user enters a sample size larger than the number of rows in the data, all the rows in the data will be scanned for structure.

Parser Sample Size

Parser Sample Size

📘

Snowflake Data Types

When the XML parser scans for variances in XML shape, all values in the XML are converted to Snowflake strings.

Updated about 1 month ago