By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
create table survey_responses (UUID text, Survey_id int, Employee_id int, Employee_Response jsonb, Status text);
CREATE TABLE
insert into survey_responses
values
('f212', 2, 17, '[{"q_id": "5", "answer": {"value": "Agree"}, "q_type": "radio-buttons"}, {"q_id": "6", "answer": {"value": "4"}, "q_type": "star-ratings"}, {"q_id": "7", "answer": {"value": "9"}, "q_type": "slider-type"}]', 'active'),
('a3f5', 2, 46, '[{"q_id": "5", "answer": {"value": "Agree"}, "q_type": "radio-buttons"}, {"q_id": "6", "answer": {"value": "4"}, "q_type": "star-ratings"}, {"q_id": "7", "answer": {"value": "8"}, "q_type": "slider-type"}]', 'active'),
('2db8', 2, 32, '[{"q_id": "5", "answer": {"value": "Agree"}, "q_type": "radio-buttons"}, {"q_id": "6", "answer": {"value": "3"}, "q_type": "star-ratings"}, {"q_id": "7", "answer": {"value": "9"}, "q_type": "slider-type"}]', 'active'),
('d2bd', 2, 40, '[{"q_id": "5", "answer": {"value": "Disagree"}, "q_type": "radio-buttons"}, {"q_id": "6", "answer": {"value": "2"}, "q_type": "star-ratings"}, {"q_id": "7", "answer": {"value": "3"}, "q_type": "slider-type"}]', 'active'),
('g632', 2, 31, '[{"q_id": "5", "answer": {"value": "Strongly Agree"}, "q_type": "radio-buttons"}, {"q_id": "6", "answer": {"value": "3"}, "q_type": "star-ratings"}, {"q_id": "7", "answer": {"value": "6"}, "q_type": "slider-type"}]', 'active');
INSERT 0 5
select r."uuid",
r.survey_id,
r.employee_id,
jsonb_path_query_first(r.employee_response, '$[*] ? (@.q_id == "5").answer.value') #>> '{}' as q_5,
jsonb_path_query_first(r.employee_response, '$[*] ? (@.q_id == "6").answer.value') #>> '{}' as q_6,
jsonb_path_query_first(r.employee_response, '$[*] ? (@.q_id == "7").answer.value') #>> '{}' as q_7
from survey_responses r;
| uuid | survey_id | employee_id | q_5 | q_6 | q_7 |
|---|---|---|---|---|---|
| f212 | 2 | 17 | Agree | 4 | 9 |
| a3f5 | 2 | 46 | Agree | 4 | 8 |
| 2db8 | 2 | 32 | Agree | 3 | 9 |
| d2bd | 2 | 40 | Disagree | 2 | 3 |
| g632 | 2 | 31 | Strongly Agree | 3 | 6 |
SELECT 5