Extract columns from JSON or variant data

The JSON data type stores inherently hierarchical data. Sigma can extract fully structured and semi-structured data from a workbook's table column that contains JSON. When Sigma detects a JSON or Variant column type, it provides an Extract Columns option in the column menu. If your data is semi-structured and Sigma does not provide the option, you can use the type functions Json or Variant to change how Sigma interprets the column data.


The ability to extract columns from JSON or variant data requires the following:

  • You must be assigned an account type with the Basic explore permission enabled.
  • You must be the workbook owner or be granted Can explore or Can edit workbook permission

Extract JSON data from a column

To extract JSON data from a column and put it in separate columns:

  1. Go to the column in the workbook. This example uses the Cust Json column of the PLUGS sample data.
  2. Use the column's menu to select Extract columns.
    Sigma opens the Extract Fields modal.
  3. In the modal, select the fields you want to pull out into their own columns.
  4. Click Confirm.
    Sigma extracts the selected fields and makes new columns.
  5. Use the new columns in your explorations and analysis.

Use column extraction notation

You can add JSON column extraction directly into Sigma formulas, by using field name and list item notation.

Extract fields

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:



  • Column name is the name of the primary object.
  • fieldName is the name of one of the fields of the primary object.
  • subFieldName is the name of one of the fields of a secondary field.
  • and so on...

In the following example, a table contains an Order JSON column of type JSON, and the column contains product details for each order. To retrieve the product name, use the following formula:

Text([Order JSON].product.productName)

Sigma retrieves the productName field from the product key for each JSON object in the Order JSON column, converting the output to the Text data type:

Excerpt of the table showing an Order JSON column with contents {"order":{"orderId":1}, "product":{"productId":117,"productName":"Ground... and a productName field that extracts the product name from the JSON, listing "Ground Turkey Chub".

Extract entries from lists

To extract an entire JSON object from a list, pass the valid index of the object to the function, using the . (dot) notation:


Where i is the index of the array, starting with 0.

In the following example, the JSON object contains entries in an array (a list of values). The formula returns the item that corresponds to index 0 of the cart array:


The output appears:

A column named JSON with data {"cart":["apples","yogurt","steak"],"orderId":1}, another column called cart with the array data ["apples","yogurt","steak"] and a third column cart[0] with "apples".