Working with JSON v1

Through the Library for Data page, locate the imported datasets that are based on JSON files.

1. You may be able to just search for json.

For each JSON imported dataset:

1. Click the link.

2. In the Dataset Details page, copy the value for the Location. Paste it into a text file.

3. In the Dataset Details page, locate flow or flows where the dataset is in use.

   Astuce

   If you copy the link address of the flow and paste it into a text file, you can paste that later into a browser and jump directly to the flow.

4. Repeat the above steps for each JSON-based imported dataset.

You should now have a list of links to the source data and the flows where your JSON imported datasets are in use.

In the Library page, create a new version of each imported dataset:

1. Click Import Data.

2. Click the appropriate connection.

3. Paste the link to the Location where the source is stored.

4. The data is ingested through the conversion service.

   Astuce

   Click the icon for the dataset in the right panel. All rows in the Preview panel should be properly structured. Nested data may not be broken out into separate columns at this time.

5. Rename the dataset as needed.

   Astuce

   You should give each new version of the imported dataset a consistent prefix or suffix tag, such as -v2. Later, you can locate these new imported datasets easily through search in the Library for Data page.

6. Click Continue.

Repeat the above steps for each imported dataset that you are updating to v2.

For each of these flows:

1. Navigate to it.

2. Locate the v1 imported dataset in it. You might copy the name.

3. Click Add Datasets. Search for the v2 imported dataset. Add it to the flow.

In Flow View:

1. Click the recipe that is in use with the v1 version of the imported dataset. In the context menu in the right panel, select Make a copy > without inputs.

2. Select the copied recipe.

3. In the context menu in the right panel, select Change input. Select the v2 imported dataset.

4. Your v2 imported dataset is now connected to a version of your recipe.

5. Select the recipe object. In the right panel, you should see a preview of the recipe steps.

   Note

   In the recipe, the steps where you modified the imported dataset into tabular format are likely to be be broken. This is ok.

Click Edit recipe.

In the Transformer page:

1. Disable recipe step 1.

2. Review the state of the data grid to see if the data is organized in tabular form.

3. If not, repeat the above steps for the next step in your recipe.

4. Continue until the data is in tabular form.

After some additional tweaking, your recipe should contain no broken steps, and your data should appear in tabular form.

You may wish to run a job or download your sample data to compare it to outputs from your v1 imported dataset and steps. You may need to create an output object first.

You can now integrate these changes in either of the following ways:

1. Apply to existing recipe: Change the input on the existing to the v2 imported dataset. Apply any disabling of steps and other tweaks to the recipe's connected to the v1 imported dataset.

   Note

   Before applying the above changes, you might want to download the v1 recipe through the Recipe panel.

2. Use v2 recipe in the flow: You could simply switch over to using the new recipe. Caveats:

   1. You must recreate any outputs and schedules from the v1 recipe.

   2. Internal identifiers for the new recipe and its outputs are different from the v1 recipe. These new identifiers may impact API-based automation.

   3. Other application objects that reference the v1 recipes, such as flow tasks in your plans, must be fixed to use the new recipe or output objects.

Run a production job to verify that your flow is producing consistent data with the v2 imported dataset.

Repeat as needed for other flows.

Through the Import Data page, navigate and select your JSON file for import.

When the file has been loaded, click Edit settings for the dataset card in the right panel. In the Import Settings dialog, deselect the Detect Structure checkbox.

Complete the rest of the import process.

In Flow View, add the JSON-based imported dataset to your flow and create a recipe for it.

Select the recipe, and click Edit Recipe....

In the Transformer page, you should see your loosely formatted JSON in a single column. Each row contains a separate attribute, and a single record spans multiple rows.

Open the Recipe panel on the right side. The initial parsing steps for the data are displayed.

In Recipe panel, delete all steps except the first one.

The first one is a Break into rows transformation. This transformation can only appear in the first step of a recipe.

Select the step, and click the Pencil icon to edit it.

In the Transform Builder, the Split on value is probably the \n character.

The above signals to the application to break up the data into individual rows on the newline (\n) character. This transformation then breaks up your loose JSON on every single attribute. You must modify the Split on value so that it captures only the first attribute of each JSON record. For the above dataset, the Split on value must be the following, noting the space after the colon:

"book": 

Click Add to save the step again.

The above dataset should now have four rows, with the first one an empty row. This empty row is caused by the insertion of the \n in front of the first reference to the above string. In the column histogram, select the gray bar, which selects the empty row. In the Suggestions panel, locate the Delete rows suggest, and click Add. The row is removed.

You now have individual rows for each JSON record.

Since JSON uses character indentation to convey structure, you should remove these indentations if they appear in your dataset. For our two-layered example, you can use the following transformation:

column1

/\n\s*"/

\"

true

In standard JSON, a comma is used to demarcate the end of a line or a record, except for the last one in a set.

1. In the above example, the first two records have commas at the end of them. Here is a snippet of their ends:

   ... "description":"An in-depth look at creating applications."},
   ... "description":"Biography of a prince."},
   ... "description":"Classic American novel."}

2. To convert these records to Object type, the commas at the end of the first two rows must be removed:

   Transformation Name	Replace text or patterns
   Parameter: Column	column1
   Parameter: Find	`\n\},\n{end}`
   Parameter: Replace with	}
   Parameter: Match all occurrences	true

   1. The above transformation is similar to the previous one. However, in this one, the Find pattern uses a Wrangle to indicate that the pattern should only be matched at the end of a record:

      {end}

   2. This token in the pattern prevents it from matching if there are other instances of the pattern nested within the record.

Individual records should look similar to the following:

{"id": "bk101","author": "Guy, Joe","title": "Json Guide","genre": "Computer", \
"price": "44.95","publish_date": "2002-04-26",{"cover_color": "black", \
"paper_stock": "20","paper_source": "new"}, \
"description": "An in-depth look at creating applications."}

These records are suitable for conversion to Object data type.

To change the data type for the column, click the icon to the left of the column header. Select Object.

The column data type is changed to Object. The step to change data type is added to your recipe, too.

If the column histogram now displays some mismatched records.

1. Review those records to determine what is malformed.

2. Delete the recipe step that changes the data type to Object.

3. Make fixes as necessary.

4. Switch back to Object data type. Iterate as needed until all records are valid when the column is converted to Object type.

In the Recipe panel, click New Step.

In the Search panel, enter unnest object elements.

Specify the following transformation. Substitute the Paths to elements values below with the top-level keys in your JSON records:

column1

id

author

title

genre

price

publish_date

description

true

Repeat the above process for the next level in the hierarchy. In the example, this step means unnesting the characteristics node:

column1

characteristics.cover_color

characteristics.paper_stock

characteristics.paper_source

true

You can now delete column1. From the column menu to the right of column1, select Delete.

You have now converted your JSON to tabular format.

Generate the JSON file with a flat hierarchy. Output looks like the above. Use an external tool to unnest the second and lower hierarchies appropriately.

Re-nest the lower hierarchies until have you have a single flat record, containing some Object type columns that hold the underlying hierarchies. When the re-nested JSON records are exported, secondary hierarchies appear as escaped string values. More details later.

When you re-nest, you want to nest from the lowest to top tier of the hierarchy.

In the example, the following columns should be nested together: characteristics.cover_color, characteristics.paper_stock, and characteristics.paper_source:

characteristics.cover_color

characteristics.paper_stock

characteristics.paper_source

Object

characteristics

In the generated characteristics column, you can remove the characteristics. from the key value:

characteristics

`characteristics.`

(empty)

Now, delete the three source columns:

characteristics.cover_color

characteristics.paper_stock

characteristics.paper_source

Repeat the above steps for the next level of the hierarchy in your dataset.

Since the JSON output is newline delimited, your downstream system may need you to add commas at the end of each record but the last one.

If you have re-nested JSON hierarchies into your flat records, the exported JSON for secondary hierarchies appears as quoted strings, like the following:

"characteristics":"{\"cover_color\":\"black\",\"paper_stock\":\"20\",\"paper_source\":\"new\"}",
"characteristics":"{\"cover_color\":\"white\",\"paper_stock\":\"15\",\"paper_source\":\"recycled\"}",
"characteristics":"{\"cover_color\":\"blue\",\"paper_stock\":\"20\",\"paper_source\":\"new\"}",

The quoted strings can be fixed by simple search and replace.