Question 114775 in PostgreSQL Question posted in
The official documentation can be found here.

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

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

2

Answers


  1. 0

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

    https://superset.apache.org/docs/databases/db-connection-ui

    After adding the dataset navigate to SQL Lab, select the datataset and perform SQL operations on the dataset.

    enter image description here

    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search