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.

  1. 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.

  2. 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.

  3. 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.

  4. 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.

  5. 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.