Guides

Adding a credential to Actuals [GUIDE-1]

Working with JSON data in Actuals [GUIDE-2]

Actuals allows two functionalities related to JSON structured data.

Extract data from a JSON string

Create a JSON string

1. Extract data from a JSON string

The Actuals platform allows to extract data from a JSON string inside a column. Imagine a file is provided that contains a column with a string of JSON data, for example a csv file that looks like this:

header name: ID header number: __column1__	header name: JSON_DATA header number: __column2__
Item 1	{“orderId”:”order123”}
Item 2	{“orderId”:”order124”}
Item 3	{“orderId”:”order125”}

If we want extract the orderId from the json string we can use this formula:

sql
JSON_EXTRACT_PATH_TEXT(<column_identifier>, '<path_name>')

In this example the JSON_DATA column is the column we want to look for. The column is located as second column. We need to define this with the column number, in this case this is the second column so you should use __column2__. The path is orderId, to extract the orderId we will create this formula:

sql
JSON_EXTRACT_PATH_TEXT(__column2__, '"orderId"')

It is also possible to extract nested data, for a more comprehensive guide on how to use this formula see: Snowflake JSON_EXTRACT_PATH_TEXT documentation.

2. Construct a JSON String

Actuals also supports the use case to construct a JSON object from source data. Imagine a file that provided that contains a column with a string of JSON data, for example a csv file that looks like this:

ID	payment_method	payment_method_variant
Order 1	Creditcard	VISA
Order 2	Creditcard	VISA
Order 3	Creditcard	Mastercard

If we want construct a json object containing all information about the payment method used for a orders we can use this formula:

OBJECT_CONSTRUCT('<column_key>', '<column_value>', '<column_key2>', '<column_value2>')

In this example we want to construct a object containing columns payment_method and payment_method_variant The columns are located as second and third column in the source data. We need to define this with the column number, in this case this is the second and third column so you should use __column2__ and __column3__.

To construct the object we will create this formula: OBJECT_CONSTRUCT('payment_method', __column2__,'payment_method_variant',__column3__).

For a more comprehensive guide on how to construct objects see: Snowflake OBJECT_CONSTRUCT documentation.

Working with XML data in Actuals [GUIDE-3]

Actuals contains functionality to work with XML structured data. The available functions are:

Extract properties from a XML column

1. Extract properties from a XML column

The Actuals platform allows to extract data from XML data inside a column. Imagine a file is provided that contains a column with a string of XML data, for example a csv file that looks like this:

ID	XML_DATA
Item 1	<order><orderId>order123</orderId></order>
Item 2	<order><orderId>order124</orderId></order>
Item 3	<order><orderId>order125</orderId></order>

If we want to extract the orderId from the XML string we can use this formula:

sql
XML_EXTRACT_XPATH_TEXT(<column_identifier>, <xpath>)

In this example the XML_DATA column is the column we want to look for. The column is located as the second column. We need to define this with the column number, in this case this is the second column so you should use is __column2__. The path is order --> orderId, to extract the orderId we will create this formula:

sql
XML_EXTRACT_XPATH_TEXT(__column3__, '/order/orderId')

To specify the path of the data you are looking for, we are using the XPATH standard. More information on what possibilities you have to extract data can be found in the XPath specification.

ℹ️

The XML_EXTRACT_XPATH_TEXT function will return NULL if the provided XPATH doesn’t exist in the XML string (similar to the JSON_EXTRACT_PATH_TEXT function).

When the XPATH returns multiple results it will return a value error. Make sure you always provide an explicit XPATH function that returns a maximum of 1 result. If you want to test the result of a XPATH formula on a certain XML string you can use an online XML XPATH evaluation tool like Xpather.

Guides

Adding a credential to Actuals [GUIDE-1]

Working with JSON data in Actuals [GUIDE-2]

1. Extract data from a JSON string

2. Construct a JSON String

Working with XML data in Actuals [GUIDE-3]

1. Extract properties from a XML column

Exchanging large datasets via compressed CSV files [GUIDE-4]

Benefits of Using Compressed Formats

Supported Compressed Formats

Steps