Flatten

If your data is from API, you may see some values are presented within curl brackets {} or square brackets []. Generally, the one with curl brackets is called a JSON object, whereas the other one with square brackets is called an array. These data types contain lots of information in a single cell and are not easy to analyze. Thus, Flatten action is to solve this issue.

You can go to the table that includes JSON objects or arrays and navigate to Data Cleaning > Flatten. Then, select the column that you want to flatten and select the corresponding method.

Flatten JSON

1. What is a JSON object?

JSON represents JavaScript Object Notation and is one kind of data type.

{"Product":"Milk", "Sales":1200}

Generally, JSON has the following characteristics:

• Values are presented within a pair of curl brackets {}.

• All values within the brackets are "key-value" pairs. That is, each key has a corresponding value and keys cannot be duplicated.

• Values can be any data type, such as String, Integer, Date, or even another JSON object.

• The strings with quotes before the colon, : are the names of the keys. For example, the object above has two keys, Product and Sales. The value of Product is "Milk", and the value of Sales is 1200.

2. How to extract values from JSON?

Step 1: Go to the table that includes JSON objects and navigate to Data Cleaning > Flatten.

Step 2: Choose a column and select Flatten JSON. (All JSON columns are presented in a String data type)

Step 3: Type the keys that you want to extract values from. Here you can type several keys, and you can enter space to separate each key. (Remember to click Enter when you finish a keyword.)

Step 4: The system will extract a specific key's value from each row and put these values in another column. The new column will have a prefix of the original JSON column. (You can use Rename action to change the column name)

Note: The Flatten action will convert all values extracted from JSON into String, no matter which data types they originally are. You can use Change Type action to correct their types.

3. How to identify available keys when the JSON object has multiple keys?

If your JSON object has dozens of keys, or it is a complicated nested JSON object with several levels, you can use JSON Formatter to help yourself to identify available keys. The tool can convert the JSON object into a readable and structured format.

The following example is the result after using JSON Formatter, so you can see available keys in the first level are price, rate, title, price_set, and channel_liable.

{
   "price":"8.07",
   "rate":0.0625,
   "title":"Massachusetts State Tax",
   "price_set":{
      "shop_money":{
         "amount":"8.07",
         "currency_code":"USD"
      },
      "presentment_money":{
         "amount":"8.07",
         "currency_code":"USD"
      }
   },
   "channel_liable":false
}

Flatten Array

1. What is an array?

Like JSON, arrays are another kind of data type and usually present values within a pair of square brackets []. All values in an array are separated by commas and should have the same data type. For example, all values in the following array are integers, so you will not see any string or boolean. However, it is allowed to have Nulls in an array. The number of elements in an array can be from 0 to infinite.

[2, 3, 0, 1, 5, Null]

2. How to flatten values from an array?

Step 1: Go to the table that includes JSON objects and navigate to Data Cleaning > Flatten.

Step 2: Choose a column and select Flatten Array. Note that you can only choose columns in an Array data type.

Step 3: The system will separate each element into different rows and put these values in another column.

Step 4: Each element in an array will be separated into different rows as shown below. The new column will have a prefix of the original Array column. (You can use Rename action to change the column name)