Postgres in depth · Chapter 32 of 65
Quiz answers and API responses
By the end of this chapter you can change values inside a JSON document, search it with JSON paths, turn JSON into rows, and turn rows into the nested JSON an app asks for. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.
- Part: The Postgres flavour
- 5 tasks
- PostgreSQL
- In the all labs plan
The first 2 chapters of Postgres in depth are free. This one comes with the all labs plan.
Why you need this
Every quiz attempt stores the student's answers as a document. The first attempt LCO ever recorded:
SELECT answers FROM quiz_attempts ORDER BY attempted_at LIMIT 1;
answers
───────────────────────────────────
{"q1": "d", "q2": "b", "q3": "a"}
Priya wants the long quizzes made harder, which means changing one key inside thousands of documents without touching the rest. Arjun wants to know how often each answer is picked, and who just guesses d every time. Sana's support tool exports tickets as JSON, and the new course page wants one JSON response straight from the database instead of six queries glued together in JavaScript. Chapter 31 read single values out of JSON; now you need to rewrite it, walk all of it, and produce it.
What you build: 5 tasks
Each task has tests. You write the code in the lab's editor and press Check.
- Task 1
pass-score.sql: harder long quizzes
Change the settings of the quiz lessons (quiz is true): a quiz with 6 questions gets pass_score 70 (a JSON number), and every quiz gets a new key max_attempts with the number 3.
Every other key stays as it is, and lessons without a quiz do not change at all.
- Task 2
answer-picks.sql: how often each answer is picked
Arjun wants to see whether students favour some answers. Across all quiz attempts, count how many times each answer was given to each question.
Return three columns: question (the key, such as q1), answer (the letter, as text) and picks. Order by question, then answer.
- Task 3
guesses.sql: the d guessers
List the quiz attempts in which the student answered d to three or more questions.
Return four columns: student (the name), lesson (the title), attempted_at and d_picks (how many answers were d). Order by d_picks, most first, then attempted_at, then student.
- Task 4
tickets.sql: support tickets from JSON
Sana's support tool exported four tickets as a JSON array (in the starter file). Turn them into rows and attach the student's name and the course's title; a ticket from an email that is no student's is left out.
Return three columns: student, course (the title) and issue. Order by student.
- Task 5 · interview question
Interview: course-api.sql, the course page in one query
The course page wants one JSON response for React from Zero (slug react-from-zero). Return one row with one column, api: an object with two keys.
title is the course title; sections is an array of the course's sections in order of position, each an object with position, title and lessons; lessons is an array of the section's lessons in order of position, each an object with position, title and minutes (duration_minutes).
What you will learn
- jsonb_set, ||, - and #-: change, add and remove keys.
- JSON paths: @?, @@ and jsonb_path_query, a small query language for documents.
- jsonb_each and jsonb_to_recordset: JSON in, rows out.
- jsonb_agg and jsonb_object_agg: rows in, JSON out, nested as deep as the app needs.
More in The Postgres flavour
Build "Quiz answers and API responses" yourself
LCO, an edtech company, moves its platform onto a single Postgres server you run. A real editor and terminal in your browser, AI that explains, and a Check that says when it works.
The first 2 chapters of Postgres in depth are free. This one comes with the all labs plan.