SQL-How would I acess this data using SQL in superset

Ugo Nnadi 0
Viewed 41

I have a field in my table as shown below. I want to create a bar chart for all choices by counting the ratings of each activity. I.e a very useful count for a newsletter, weekly tips, and other activities and a useful, and also not useful count. This would come in the form of a multiple side-by-side barchat.

{
"Newsletter": "5 Very helpful", 
"Weekly tips": "5 Very helpful", 
"Office Hours": "5 Very helpful", 
"Speaker Series": "5 Very helpful", 
"Weekly challenges": "5 Very helpful", 
"C.H.A.I.R. value exercises": "5 Very helpful"
}
apache-superset sql postgresql
2 Answers
user1R5W0

You can do it using jsonb_each_text or json_each_text depending on datatype in the column. Even if it's stored as text you can cast it to json. There's both "point" sum and count

select 
  j.key,
  trim(regexp_replace(j.value, '[^[:alpha:]\s]', '', 'g')) as grade_desc, 
  count(*) as grade_count,
  sum(split_part(j.value,' ', 1)::integer) as points_sum 
from test t
Cross join lateral jsonb_each_text(t.jsonset) j
Group by j.key,grade_desc;

Here's fiddle

Revuimar0
Related