Extract semi-structured 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.

Contents

Requirements

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

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.
    2023-07-08_14-26-02.png
    Sigma opens the Extract Fields modal. 
  3. In the modal, select the fields you want to pull out into their own columns.
    2023-07-08_14-29-50.png
  4. Click Confirm. 
    Sigma extracts the selected fields and makes new columns.
    extracted-JSON-columns.png
  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:

[ColumnName].fieldName.subFieldName...

Where

Column name
The name of the primary object
fieldName
The name of one of the fields of the primary object
subFieldName
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:

Text([Order JSON].product.productName)

Sigma retrieves the productName field from the product key for each JSON object, converting to the Text data type.

Screenshot_2023-06-01_at_9.48.13_AM.png

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:

[ColumnName].fieldName[i]
[ColumnName].fieldName.subFieldName[i]...

Where

i
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

Text([JSON].cart[0])

The output will appear as:

Screen

Related resources

 

Was this page helpful?
Yes No