How to Create a Dataset with JSONB Values in Bold Reports
The PostgreSQL JSONB data type stores JSON data in a binary format, offering improved storage efficiency and query performance compared to standard JSON columns. JSONB is commonly used to store semi-structured or hierarchical data while retaining the benefits of a relational database. Bold Reports supports PostgreSQL data sources, allowing you to retrieve and display values from JSONB columns within report datasets.
This article explains the procedure for creating a dataset with JSONB data, insert sample JSONB records, create a dataset in Bold Reports, extract values from JSONB columns using PostgreSQL JSON operators and display JSONB data in a report.
1. Create a Table with a JSONB Column
Begin by creating a table in PostgreSQL.
CREATE TABLE JsonBTable (id serial PRIMARY KEY,jsonbNameAge jsonb);
2. Insert JSONB data into the table.
Insert sample records into the table:
INSERT INTO JsonBTable (jsonbNameAge) VALUES ('{"name": "John", "age": 32}');
INSERT INTO JsonBTable VALUES (2,'{"name": "Josh", "age": 29}');
3. Connect the PostgreSQL and create the dataset.
Open the Bold Reports Report Designer, click Data from the right side panel, select New Data Source and choose PostgreSQL as the data source type.
Enter the connection details and click Save.
4. Create a Dataset
Open the query designer and drag the JSONB table you created earlier
5. Retrieve JSONB Fields
To extract the fields from the JSONB table, switch to code mode in the query designer click Finish to save your DataSet.
Use the following syntax to select a specific field from the JSONB table
SELECT jsonb_column::jsonb->>>‘field_name’ AS extracted_field
The following JSON operators are commonly used when working with JSONB columns:
| Operator | Description |
|---|---|
| -> | Returns a JSON object field |
| ->> | Returns a JSON object field as text |
| #> | Returns a nested JSON object |
| #>> | Returns a nested JSON value as text |
These operators allow individual properties within a JSONB document to be selected and displayed in reports.
6. Design your report
Add a table and incorporate the dataset into the table. Preview the report to view the results.
Preview your report to ensure data is retrieved as expected.
Related Articles
How to Resolve “Cannot Pass More Than 100 Arguments to a Function” Error in Bold Reports with PostgreSQL
How to display PostgreSQL database Datetime in specific timezones in Bold Reports
