- Your account type must be a Creator or Admin.
- You must own the workbook or be granted Can Explore or Can Edit permissions on the workbook. See Share a workbook.
- A workbook table containing a column of semi-structured data.
To extract JSON data from a column and put it in separate columns:
- Go to the column in the workbook. This example uses the Cust Json column of the PLUGS sample data.
- Use the column's menu to select Extract columns.
Sigma opens the Extract Fields modal.
- In the modal, select the fields you want to pull out into their own columns.
- Click Confirm.
Sigma extracts the selected fields and makes new columns.
- Use the new columns in your explorations and analysis.
You can add JSON column extraction directly into Sigma formulas, by using field name and list item notation.
To use the notation, supply constant values for JSON key references. For example,
[JSON column].[Key Name] is invalid even if
[Key Name] is a column with the same data type as the key entry in the JSON object.
You can use the following . (dot) notation in the formula bar to extract the relevant value of the field (or subfield) from column that contains JSON data type values:
The name of the primary object
The name of one of the fields of the primary object
The name of one of the fields of a secondary field, and so on
In the following example, a table contains the Order column of type JSON, and has holds product details for each order. To retrieve the product name, issue the following request in the formula bar:
Sigma retrieves the productName field from the product key for each JSON object, converting to the Text data type.
To extract an entire JSON object from a list, pass the valid index of the object to the function, using the . (dot) notation:
The index of the array/list, starting with 0.
In the following example, the JSON object contains array entries (a list of values), the returns the item that corresponds to index 0 of the cart array
The output will appear as:
Updated 3 months ago