Extracting and Aggregating Specific CSV Values in PostgreSQL

When working with data stored as comma-separated values (CSV) in a PostgreSQL database, you may encounter a scenario where you need to extract specific values from a column that contains CSV data. This article will guide you through the process of extracting the second value from each row in a PostgreSQL data source table and then aggregating these values into a single string with commas as delimiters.

Extracting the Second Value from CSV Data

To extract the second value from each row in a PostgreSQL data source table where the data is stored as CSV in a single column, you can use the split_part function in the Expression Designer of Bold BI. This function splits a string on a specified delimiter and returns the nth part of the string.

Here is the SQL function to extract the second value:

split_part(column_name, ',', 2)

Replace column_name with the name of your column that contains the CSV data.

Aggregating the Extracted Values

Once you have extracted the second value from each row, you can aggregate these values into a single string using the string_agg function. This function concatenates values and separates them with a specified delimiter.

Here is the SQL function to aggregate the extracted values:

string_agg(split_part(column_name, ',', 2), ',')

This will give you a single string with all the second values separated by commas.

Example Usage

Assuming you have a table named stakeholders and a column named adminusers that contains CSV data.

The following SQL query will extract and aggregate the second values:

string_agg(split_part([adminusers], ',', 2), ',')

This expression will return a single string containing all the second values from the adminusers column, separated by commas.

Conclusion

By using the split_part and string_agg functions of PostgreSQL in Bold BI Expressions, you can efficiently extract and concatenate specific values from rows of CSV data stored in a single column. This method is particularly useful when dealing with data that is not normalized and requires manipulation to retrieve meaningful information.

Additional References

• PostgreSQL Documentation on split_part
• PostgreSQL Documentation on string_agg