JSON (JavaScript Object Notation) is a popular data format for exchanging information between servers and web applications. Its lightweight and readable structure makes it a preferred choice for many developers and data analysts.
JSON files store data in nested objects and arrays made up of keys and values:Â
Keys: Always strings enclosed in quotation marksÂ
Values: Can be strings, numbers, boolean expressions, arrays, or objectsÂ
Key/value pairs: Follow a specific syntax, with the key followed by a colon followed by the value
JSON can be leveraged in Sigma Computing to enhance data processing, integration, and visualization capabilities. This blog will guide you through the steps to effectively use JSON in Sigma Computing.
Why JSON is Important in Sigma Computing
JSON plays an important role in Sigma Computing, and here are some specific reasons why JSON is significant in Sigma:
Sigma Computing analyzes structured and semi-structured data. Since JSON is inherently semi-structured, Sigma can parse and transform JSON data into a tabular format for easier analysis.
Users can leverage Sigma to flatten or extract meaningful data from nested JSON objects.
How to Use JSON in Sigma Computing
Prerequisites
To extract columns from JSON or variant data, the following conditions must be met:
Your account must have the Basic Explorer permission enabled.
You need to own the workbook or have the Can Explore or Can Edit Workbook permissions granted.
Login to Sigma and create a new Workbook and Page. We will use a table from the Sigma Sample Database. Click to add a new table:
Select Tables and Dataset; under it, click on the Sigma Sample Database. Expand the RETAIL schema and then select INSTACARTÂ >ORDER_PRODUCT_JSON.
We have a table with four columns, but we must select only the Detail field with JSON data. Click on the ORDER_PRODUCT_JSON table view and uncheck the button for the other three columns:
So, we now have a table with a single column of JSON data. Click the Details JSON Field column’s drop arrow and select Extract columns.
Afterward, a window opens where you can select the fields you want to extract or parse.
Click to select the fields userId, firstName, and qty, and click the Confirm button.
The other way to extract the fields from JSON is by dot notation. To retrieve values from a JSON column, create a formula using dot notation to specify constant values for referencing the JSON keys. The syntax follows this structure:
[ColumnName].fieldName.subFieldName
Where
The 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 secondary fields.
And so on…
Let us do this by using the same workbook. Click on the + icon to create a new formula.
As we want to extract the unit_price field, the formula input in the formula bar is
Text([Details].order.unit_price)
We will get an additional column called Calc, which you can rename to unit_price.
Best Practices
Here are some key best practices to consider:
Limit Extracted Columns: Extract only the necessary columns from your JSON data. Focusing on required fields reduces data complexity and enhances query performance.
Convert the JSON or semi-structured column data type: To extract JSON or semi-structured data from a column, ensure the Extract Columns option is available in the column menu. Change the column’s data type to JSON or Variant if it isn’t visible.
Closing
In summary, leveraging JSON in Sigma Computing provides a powerful way to work with hierarchical and semi-structured data. Users can seamlessly analyze and manipulate complex datasets by utilizing its advanced capabilities, such as dot notation and data extraction tools, driving more insightful and efficient decision-making processes.
If you’re ready to optimize your data workflows in Sigma, contact phData’s experts to get started today!
FAQs
How can we connect JSON files directly in Sigma Computing?
Sigma Computing does not directly upload or manage JSON files. Instead, JSON files must be stored in your data warehouse (e.g., Snowflake). Once the JSON file is loaded into your data warehouse, Sigma can connect to it, and you can query or extract values from the JSON data using Sigma’s tools, such as column menus or formulas with dot notation.
Can I perform aggregations or calculations on data extracted from a JSON column in Sigma Computing?
You can perform aggregations or calculations on data extracted from a JSON column. First, use dot notation or the Extract Columns feature to isolate the desired JSON keys into individual columns. Once extracted, these values can be treated like any other column in Sigma, allowing you to apply formulas, aggregations, filters, and other operations directly.
