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

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"
}

CodePudding user response：

You have to start by connecting to the database and then adding the dataset, refer to this link on how to go about it.

CodePudding user response：

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