Postgres jsonb data into Amazon Quicksight

Question

I am looking into Amazon Quicksight as a reporting tool and I am using data from a postgres database, which in includes some columns in a few tables in jsonb format. Unfortunately these columns are skipped by Quicksight, because it only supports primitive types as mentioned here: https://docs.aws.amazon.com/quicksight/latest/user/data-source-limits.html

I am looking for a solution where I can include these data, together with the rest of the relational data that are in the same tables.

So far I cannot find anything better than actually making a view in my own application with this data in a relational format, that can be used by Quicksight. Is there anything else that does not pollute my original database with reporting stuff? I also thought of having these views only in the read-only replica of my db, but this is not possible with postgres on RDS. Athena is also not an option, and nor is the option to choose json as the data set, and this is because I want to have both the relational data and the json for my analysis.

Any better ideas?

Answer 1

1. Created a test Postgres table with the following columns:

id     integer
info    jsonb

2. Added data to the table, with a sample value:

{ "customer": "John Doe", "items": {"product": "Beer","qty": 6}}

3. In QuickSight, created a data set using custom SQL, with a SQL statement (based on [1]) similar to:

select id, (info#>>'{}') as jsonb_value from "orders"

With the above data set I was able to import both the columns to QuickSight SPICE as well as directly query the data. The JSONB column gets imported as 'String' type field in QuickSight.