Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table test (jsonset jsonb);
insert into test(jsonset) values ('{"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"}'),(
'{"Newsletter": "1 Least helpful", "Weekly tips": "3 Somewhat Helpful", "Office Hours": "1 Tottaly iseles", "Speaker Series": "5 Very helpful", "Weekly challenges": "5 Very helpful", "C.H.A.I.R. value exercises": "1 mediocre"}'),
('{"Newsletter": "2 heheheh", "Weekly tips": "4 Very helpful", "Office Hours": "2 Bullsh*t", "Speaker Series": "2 Oof boring!", "Weekly challenges": "3 Not Very Challenging", "C.H.A.I.R. value exercises": "1 C.H.A.I.R. broken"}');
3 rows affected

select * from test;


jsonset


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

{"Newsletter": "1 Least helpful", "Weekly tips": "3 Somewhat Helpful", "Office Hours": "1 Tottaly iseles", "Speaker Series": "5 Very helpful", "Weekly challenges": "5 Very helpful", "C.H.A.I.R. value exercises": "1 mediocre"}

{"Newsletter": "2 heheheh", "Weekly tips": "4 Very helpful", "Office Hours": "2 Bullsh*t", "Speaker Series": "2 Oof boring!", "Weekly challenges": "3 Not Very Challenging", "C.H.A.I.R. value exercises": "1 C.H.A.I.R. broken"}


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;


key
grade_desc
grade_count
points_sum


Newsletter
heheheh
1
2

Office Hours
Tottaly iseles
1
1

C.H.A.I.R. value exercises
mediocre
1
1

Weekly tips
Somewhat Helpful
1
3

Newsletter
Least helpful
1
1

Speaker Series
Very helpful
2
10

Office Hours
Bullsht
1
2

Newsletter
Very helpful
1
5

C.H.A.I.R. value exercises
CHAIR broken
1
1

Weekly challenges
Very helpful
2
10

Weekly challenges
Not Very Challenging
1
3

Weekly tips
Very helpful
2
9

Speaker Series
Oof boring
1
2

C.H.A.I.R. value exercises
Very helpful
1
5

Office Hours
Very helpful
1
5

key	grade_desc	grade_count	points_sum
Newsletter	heheheh	1	2
Office Hours	Tottaly iseles	1	1
C.H.A.I.R. value exercises	mediocre	1	1
Weekly tips	Somewhat Helpful	1	3
Newsletter	Least helpful	1	1
Speaker Series	Very helpful	2	10
Office Hours	Bullsht	1	2
Newsletter	Very helpful	1	5
C.H.A.I.R. value exercises	CHAIR broken	1	1
Weekly challenges	Very helpful	2	10
Weekly challenges	Not Very Challenging	1	3
Weekly tips	Very helpful	2	9
Speaker Series	Oof boring	1	2
C.H.A.I.R. value exercises	Very helpful	1	5
Office Hours	Very helpful	1	5