Pattern: Create JSON Array from Data Table

Thomas_937381 Posts: 196
edited July 2020 in Show and Tell

This pattern allows one to create a JSON array from a Catalytic data table by converting it to text, and placing quotes ("), commas (,), curly brackets ({}), and brackets ([]) around the data as necessary to construct the array. It includes a sample imported data table, with three columns (and three rows of dummy data): Order Number, Customer Name, Amount (USD).

There's a workflow-level true/false field that conditionally starts a Field: Create fields from JSON step at the end of the workflow. This step is redundant, but does allow one to test that the array is properly formatted (and will create a data table that mirrors what you started with).

The pattern could be useful where you need to make an API call to another system using the Web API: suite of actions. Using an Excel: Save table to spreadsheet step in front of Tables: Convert data table to text would allow one to create JSON from an Excel workbook.

Keep in mind field size limits if you're creating JSON from a particularly large data table. It may also be wise to split your dataset by a max number of table rows (e.g. 100 rows).

To maintain performance of your workflow's master table, you should also have a data retention policy on the workflow, and/or a Fields: Field formulas step with a formula like the one below to clear any fields whose values contain large amounts of text:

result = ''