Postgres in depth · Chapter 31 of 65
Lesson settings
By the end of this chapter you can read values out of a JSON document stored in a column, search by what it contains, and build JSON from rows. 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 lesson can have extras: a quiz with some number of questions and a pass score, or downloadable notes. Every course has player options: whether it gives a certificate, whether videos can be downloaded, the playback speeds. Priya did not want a column for every option, most of them empty, and a migration each time Kabir invents a new one. So lessons.settings and courses.settings are JSONB:
settings
──────────────────────────────────────────────────
{"quiz": true, "questions": 3, "pass_score": 60}
Now the app needs answers from inside those documents: which lessons have a quiz, how many questions, which files to show. A JSON document is one value to everything you know so far: = compares it whole, and LIKE on its text cannot tell a key from a value.
What you build: 5 tasks
Each task has tests. You write the code in the lab's editor and press Check.
- Task 1
quizzes.sql: the quizzes and their questions
List every lesson whose settings say it has a quiz (the key quiz is true), with its course and its number of questions.
Return three columns: course (the course title), lesson (the lesson title) and questions (an integer, from the questions key). Order by course, then lesson.
- Task 2
downloadable.sql: downloadable courses with a certificate
Sana needs the published courses whose settings have both downloadable and certificate set to true.
Return two columns: title and price. Order by title.
- Task 3
resources.sql: the first resource of each lesson
Some lessons list downloadable files under the key resources, a JSON array of file names. For every lesson that has that key, return its first file as text.
Return three columns: course, lesson and file. Order by course, then lesson.
- Task 4
quiz-load.sql: quiz questions per course
Arjun wants the quiz load per course: for every course with at least one quiz lesson, how many quizzes it has and how many questions they ask in total.
Return three columns: course, quizzes and questions. Order by questions, most first, then by course.
- Task 5 · interview question
Interview: course-card.sql, a JSON card for the app
The app's course list wants one JSON object per published course. Return two columns: slug and card.
card is a JSON object with four keys: title (a string), price (a number), certificate (true or false, from settings) and speeds (the playback_speeds array from settings). Any order.
Careful: certificate must be a JSON boolean and price a JSON number, not strings.
What you will learn
- json and jsonb: JSON text versus JSON stored parsed, and why LCO uses jsonb.
- ->, ->>, #>, #>>: a value out of a document, as jsonb or as text.
- @>, ?, ?|, ?&: does it contain this, does it have this key.
- jsonb_build_object and to_jsonb: JSON built from columns and rows.
More in The Postgres flavour
Build "Lesson settings" 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.