Access Custom JSON Data from PostgreSQL in Bold BI

Accessing Custom JSON Data in Bold BI Using PostgreSQL

Bold BI by allows users to integrate custom JSON data from a PostgreSQL data source into their dashboards. This integration can be achieved by following a series of steps that involve connecting to the PostgreSQL data source, creating expressions, and assigning data to widgets for visualization.

Steps to Access Custom JSON Data in Bold BI

1. Connect to PostgreSQL Data Source: Begin by establishing a connection between Bold BI and your PostgreSQL data source. Detailed instructions can be found in the Connecting Bold BI to PostgreSQL data source documentation.

2. Drag and Drop JSONB Table: In Bold BI, locate the table containing the JSONB data type and drag it into the workspace. Although the JSONB data type may not be visible in the data preview, it is accessible through expressions.

3. Save Data Source: After dragging the table, click the ‘Save’ button to store the data source with an appropriate name, which will allow you to proceed with dashboard design.

4. Configure Expression Columns: On the data source page, you can create expressions by following these steps:

   a. Drag and drop the Grid widget onto the dashboard.

   b. Click on the ‘Assign data’ section to begin creating expressions.

   c. Select the ‘Add Expression’ button to open the Expression editor.
   

   

   d. Enter the JSON path value in the Expression editor and click ‘Save’.
   

   

   Here are some operators you can use within expressions:

   • ->: Returns the JSON object field by key.
   • ->>: Returns the JSON object field by key as text.
   • Index: Used to filter data from an array without using a key.

5. Assign Values to Widget: Assign the expression values to the widget for visualization.

6. View JSONB Data: The JSONB data retrieved from the PostgreSQL server can now be visualized in the Bold BI dashboard.

7. Access JSONB Data Using Queries: To access JSONB data using queries:

   a. Enable the code view toggle in the tools pane of the data design view.

b. In the Query Editor Window, enter your query to access JSONB data.

c. Click the ‘Run’ icon to execute the query and click ‘Update’ to preview the data.

By following these steps, users can effectively access and visualize custom JSON data from a PostgreSQL data source within Bold BI dashboards.

Additional References

• PostgreSQL JSON Functions and Operators
• How to flatten the JSON Data in Bold BI?