Working with JSON v1

Version 1: This section describes how you can import JSON files into Designer Cloud, convert them to tabular format, wrangle them, and then export them back in the same JSON format.

The basic method is described by way of example. In the example, the JSON file must be imported into Designer Cloud, a new column must be inserted into the JSON, and the resulting JSON must be exported in the same structure.

Enable

Warning

This legacy method of working with JSON is likely to be deprecated in a future release. Features developed in the future, such as schema management, are unlikely to support this method of JSON import.

If you have existing flows that were created using this legacy method, they should continue to work as expected. However, you should migrate your flows to use the newer version as soon as possible. See Migrate below.

Note

The legacy version of JSON import is required if you are working with compressed JSON files or only Newline JSON files.

This method can be enabled through workspace settings. For more information, see Workspace Settings Page.

Migrate to v2

Any flow that you have created using the v1 version of the JSON importer should work without modification.

In the future, the v1 version will be deprecated. You can use the following method to migrate your flows to use the new version of the JSON importer.

Note

The v1 version of JSON import is supported for imported datasets. If you use these datasets in other flows, they are likely to require modifications that you have done in recipes.

Basic process:

This migration process creates new versions of these imported datasets and fixes recipes accordingly.

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.
  Tip
  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.
  Tip
  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.
  Tip
  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.

JSON Input and Output

Input:

It is easier to work with JSON in which each row of the file is a record. When a record spans multiple rows, additional steps are required in the application to render it into tabular format. The example uses multi-row JSON records.

Output:

Note

JSON-formatted files that are generated by Designer Cloud are rendered in JSON Lines format, which is a single line per-record variant of JSON. For more information, see http://jsonlines.org.

Designer Cloud can generate a JSON file as an output for your job. Characteristics of generated JSON files:
- Newline-delimited: The end of each record is the \n character. If your downstream system is expecting comma-delineated records except for the last one, additional work is required outside of the application.
- Non-nested: Each record in the generated file is flat.
  - For multi-level JSON hierarchies, you can nest columns together and leave the top level as a set of columns in the data grid. However, on output, the second and lower hierarchies appear as quoted string values in the output. Additional cleanup is required outside of the application.

Example

This example dataset contains information on books. In this case:

The data is submitted as one attribute per row. A single JSON record spans many rows.
The total number of books is three.
The JSON data has two hierarchies.

"book": {
  "id": "bk101",
  "author": "Guy, Joe",
  "title": "Json Guide",
  "genre": "Computer",
  "price": "44.95",
  "publish_date": "2002-04-26",
  "characteristics": {
    "cover_color": "black",
    "paper_stock": "20",
    "paper_source": "new"
  },
  "description": "An in-depth look at creating applications."
},
"book": {
  "id": "bk102",
  "author": "Nelson, Rogers",
  "title": "When Doves Cry",
  "genre": "Biography",
  "price": "24.95",
  "publish_date": "2016-04-21",
  "characteristics": {
    "cover_color": "white",
    "paper_stock": "15",
    "paper_source": "recycled"
  },
  "description": "Biography of a prince."
},
"book": {
  "id": "bk103",
  "author": "Fitzgerald, F. Scott",
  "title": "The Great Gatsby",
  "genre": "Fiction",
  "price": "9.95",
  "publish_date": "1925-04-10",
  "characteristics": {
    "cover_color": "blue",
    "paper_stock": "20",
    "paper_source": "new"
  },
  "description": "Classic American novel."
}

JSON Ingestion Process

Import the JSON file.
Note
During import, you should deselect the Detect Structure option. You are likely to need to rebuild the initial parsing steps to consume the file properly. Details are provided later.
If needed, convert loose JSON to a single JSON record per row.
Unnest the data into columns.
1. Each level in the JSON hierarchy must be un-nested in a separate step.
When all of the JSON data is in tabular form, perform any Wrangle transformations.
If you need to retain the hierarchy, you must nest the lower levels of the JSON hierarchy back into their original form. Leave the top level un-nested.
1. If it is ok to write out flat JSON records, you can export without nesting the data again.
Run the job, generating a JSON output.

Step - Import the file

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....

Step - Convert to one JSON record per row

Note

This step is required only if a single JSON record in your imported dataset spans multiple rows. If you have single-row JSON records in the Transformer page, please skip to the next section.

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.

Step - Convert JSON to Object type

The next step involves converting your JSON records to a column of Object type values. The Object data type is a means of rendering records into key-value pairs. However, its structure is a little different from JSON. For more information, see Object Data Type.

Steps:

The following steps convert your JSON to an Object data type.

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:
Transformation Name
Replace text or patterns
Parameter: Column
column1
Parameter: Find
/\n\s*"/
Parameter: Replace with
\"
Parameter: Match all occurrences
true
1. In the above, the key term is the Find pattern, which is a regular expression:
```
/\n\s*"/
```
2. The two forward slashes at the ends define the pattern as a regular expression.
3. The content in the middle matches on the pattern of a newline character, an arbitrary number of spaces, and a double quote.
4. This pattern is replaced with just the double-quote, removing the preceding part of the pattern from the dataset.
5. For more information on matching patterns, see Text Matching.
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.

Transformation Name	`Replace text or patterns`
Parameter: Column	column1
Parameter: Find	/\n\s*"/
Parameter: Replace with	\"
Parameter: Match all occurrences	true

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

Individual records should look similar to the following:

Note

Below, some values are too long for a single line. Single lines that overflow to additional lines are marked with a \. The backslash should not be included if the line is used as input.

{"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.

Step - Unnest JSON records

The next step is to convert your JSON records to tabular format.

Note

For JSON records that have multiple levels in the hierarchy, you should unnest the top level of the hierarchy first, followed by each successive level.

Tip

The easiest way to unnest is to select the column header for the column containing your Object data. Unnest should be one of the suggested options. If not, you can use the following process.

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:

Transformation Name	`Unnest object elements`
Parameter: Column	column1
Parameter: Path to elements1	id
Parameter: Path to elements2	author
Parameter: Path to elements3	title
Parameter: Path to elements4	genre
Parameter: Path to elements5	price
Parameter: Path to elements6	publish_date
Parameter: Path to elements7	description
Parameter: Remove elements from original	true

In the above, each Paths to elements entry specifies a key in the JSON record. The key's associated value becomes the value in the new column, which is given the same name as the key.
So, this step breaks out the key-value pairs for the specified keys into separate columns in the dataset.
Tip
You can choose to remove the original from the source or not. In deeper or wider JSON files, removing can help to identify what remains to be unnested.

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

Transformation Name	`Unnest object elements`
Parameter: Column	column1
Parameter: Path to elements1	characteristics.cover_color
Parameter: Path to elements2	characteristics.paper_stock
Parameter: Path to elements3	characteristics.paper_source
Parameter: Remove elements from original	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.
Tip
If the above set of steps needs to be applied to multiple files, you might consider stopping your work and returning to Flow View. Select this recipe and click Add New Recipe. If you add successive steps in another recipe, the first one can be used for doing initial processing of your JSON files, separate from any wrangling that you may do for individual files.
Tip
The unnesting process may have moved some columns into positions that are different from their order in the original JSON. Use the Move command from the column menu to reposition your columns.

Step - Wrangle your dataset

Your JSON data is ready for wrangling.

In the following example, the discount column is created. If the publication date is before 01/01/2000, then the discount is 0.1 (10%):

Transformation Name	`New formula`
Parameter: Formula type	Single row formula
Parameter: Formula	IF(publish_date < DATE(2000, 1, 1), 0.1, 0)
Parameter: New column name	discount

Continue adding steps until you have transformed your data as needed and are ready to run a job on it.

Step - Nest the JSON records

Note

If your desired JSON output does not include multiple hierarchies, you can skip this section. The generated JSON files are a single JSON record per row.

If a job is run using the recipe created so far on the example data, a newline-delimited JSON file that has no hierarchies in it can be generated by the application. However, the dataset is a two-level hierarchy, so the elements in the characteristics hierarchy are written out in the following manner:

"characteristics.cover_color":"black","characteristics.paper_stock":20,"characteristics.paper_source":"new",
"characteristics.cover_color":"white","characteristics.paper_stock":15,"characteristics.paper_source":"recycled",
"characteristics.cover_color":"blue","characteristics.paper_stock":20,"characteristics.paper_source":"new",

You can take one of two approaches:

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.

If you are re-nesting the lower hierarchies, you can use the following approach.

Tip

The following steps reshape your data. You may wish to create a new recipe as an output of the previous recipe where you can add the following steps.

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:

Transformation Name	`Nest columns into Objects`
Parameter: column1	characteristics.cover_color
Parameter: column2	characteristics.paper_stock
Parameter: column3	characteristics.paper_source
Parameter: Nest columns to	Object
Parameter: New column name	characteristics

In the generated characteristics column, you can remove the characteristics. from the key value:
Transformation Name
Replace text or patterns
Parameter: Column
characteristics
Parameter: Find
`characteristics.`
Parameter: Replace with
(empty)
Now, delete the three source columns:
Transformation Name
Delete columns
Parameter: column1
characteristics.cover_color
Parameter: column2
characteristics.paper_stock
Parameter: column3
characteristics.paper_source
Repeat the above steps for the next level of the hierarchy in your dataset.
Note
Do not nest the columns at the top level of the hierarchy.

Transformation Name	`Replace text or patterns`
Parameter: Column	characteristics
Parameter: Find	`characteristics.`
Parameter: Replace with	(empty)

Transformation Name	`Delete columns`
Parameter: column1	characteristics.cover_color
Parameter: column2	characteristics.paper_stock
Parameter: column3	characteristics.paper_source

Step - Generate JSON output

When you are ready, you can run the job. Create or modify a publishing action to generate a JSON file for output. See Run Job Page.

When the job completes, you can click the JSON link in the Output Destinations tab of the Job Details page to download your JSON file. See Job Details Page.

Step - Final Cleanup

Outside the application, you may need to do the following:

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.

In this section:

Working with JSON v1

Enable

Migrate to v2

JSON Input and Output

Example

JSON Ingestion Process

Step - Import the file

Step - Convert to one JSON record per row

Step - Convert JSON to Object type

Step - Unnest JSON records

Step - Wrangle your dataset

Step - Nest the JSON records

Step - Generate JSON output

Step - Final Cleanup

Search results