Handling JSON column in Redshift for Raw Data Table and charts

#1

Hi everyone,

I’m using Redshift with a table that feeds into a Raw Data Table , bar charts, a trend chart, and a few KPIs in ChartFactor.

One of the columns in this table is of type JSON , and I’ve noticed that ChartFactor doesn’t handle it very well in certain cases — for example, in the Raw Data Table statistics .

The JSON in this column is always very simple: it contains only one property called region .

Has anyone run into this issue? Is there a way to extract or convert that property so it works smoothly in ChartFactor visualizations?

Thanks!

#2

I see, well, there is a solution for this using custom queries, here is an example of a query you can use:
The query:

SELECT 
    t.* EXCEPT (your_json_field),
    your_json_field->>'region' AS region
FROM my_table t;

Then use that sql in your metadata, like this:

const myQuery = `
SELECT 
    t.* EXCEPT (your_json_field),
    your_json_field->>'region' AS region
FROM my_table t;
`;

var providers = [ {
        name:'MyProvider',
        provider:'redshift',
        url:'http://localhost:3000',
        metadata: {
            'my_custom_query': {
                'query': myQuery
            }
        }
    }
]

cf.setProviders(providers);

And finally, just use this custom query as your source:

cf.provider('MyProvider')
  .source('my_custom_query')
  .groupby(cf.Attribute('region').limit(10).sort('asc', 'region'))
  .metric(cf.Metric('count'))
  .graph('Bars')
  .element('your_element_id')
  .execute()
#3

Got it, but what happen when I have new data, do i need to refresh this somehow?

#4

Don’t worry we execute the query inside the custom query every time you run a visualization

#5

Thank you :pray:t3:, i’ll give this a try