Importing and visualising JSONB data in QuickSight

Handling JSONB Data in Amazon QuickSight

QuickSight does not natively support JSONB data types from relational databases like Aurora PostgreSQL, which is why your JSONB fields were skipped when you imported the table. However, there are ways to work around this limitation and make the data usable for visualization in QuickSight.

1. Flattening JSONB Data Using SQL Queries:

One effective way to handle JSONB data is by flattening the structure using SQL queries before sending the data to QuickSight.

• You can use PostgreSQL’s built-in JSON functions like jsonb_extract_path, jsonb_to_record, or jsonb_each to extract values and create flat columns.

Example:

SELECT 
    id, 
    jsonb_column->>'field1' AS field1, 
    jsonb_column->>'field2' AS field2
FROM 
    your_table;

This method converts JSON fields into separate columns, making it easier to analyze in QuickSight.

2. Using the parseJson Function in QuickSight:

Once the JSONB data is cast as text and imported into QuickSight, you can use the parseJson function to extract specific values from the JSON structure. This function is particularly useful if you want to keep the JSON structure intact but still access its contents.

Important Consideration:

You can use parseJson in SPICE datasets during the data preparation stage, but it’s not available for creating calculated fields during analysis. For direct query connections, you can use parseJson both during data preparation and in the analysis phase. This function helps to access nested JSON elements without fully flattening the data.

3. Additional Functions:

If you extract numeric data from the JSON structure using parseJson, you may need to use additional functions like parseInt to convert it into a usable number for calculations within QuickSight.

4. Using Views to Flatten Frequently Used JSON Fields:

If you frequently need to access specific fields from your JSONB columns, another option is to create views in PostgreSQL that pre-flatten the commonly used elements. This approach simplifies your queries in QuickSight and can also improve performance by reducing the need for repeated JSON parsing.

5. Manual Flattening vs. QuickSight’s Built-in JSON Handling:

While AWS QuickSight does automatically flatten JSON structures when working with JSON files (such as from S3), this functionality doesn’t apply to JSONB columns in Aurora PostgreSQL. You’ll need to manually flatten the data via SQL queries or use tools like AWS Glue if you want a more automated approach for larger datasets.

Recommendation:

• Best Approach: Continue using SQL to flatten your JSONB data before bringing it into QuickSight. Extract the parts of the JSON structure that are relevant to your analysis and present them as separate columns.

• Use parseJson: If you prefer keeping the JSON structure in text form, you can use parseJson in QuickSight to extract specific values, but be aware of the limitations based on whether you're using SPICE or direct queries.

• Creating Views: If you find yourself frequently needing access to specific JSON fields, consider creating views in PostgreSQL to make those fields directly accessible.

Documentation Links:

https://docs.aws.amazon.com/glue/latest/dg/what-is-glue.html

https://docs.aws.amazon.com/quicksight/latest/user/functions.html

https://www.postgresql.org/docs/current/functions-json.html

https://docs.aws.amazon.com/quicksight/latest/user/spice.html

https://docs.aws.amazon.com/quicksight/latest/user/supported-data-types-and-values.html