Postgres with Drizzle · Chapter 11 of 40

The JSON vault

By the end of this chapter you can store a settings object, find rows by a key or by a path deep inside it, change one setting without touching the others, and list the settings a row has. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.

  • Part: JSON & Relations
  • 5 tasks
  • PostgreSQL
  • In the all labs plan

The first 2 chapters of Postgres with Drizzle are free. This one comes with the all labs plan.

Why you need this

Meera's settings page grows every sprint: theme, language, notification choices, and now a preferences group with its own keys inside. A column for every setting would mean a migration for every toggle (chapter 7), so they all live in the users table's metadata column, as jsonb.

Chapter 5 stored that object and found members by one top-level key. That is not enough any more. The theme now sits inside preferences, where one arrow cannot reach. Changing one setting by reading the object, editing it in JavaScript and writing it all back loses whatever another request saved in between, as in chapter 10. And the settings page needs to know which keys a member has at all.

What you build: 5 tasks

Each task has tests. You write the code in the lab's editor and press Check.

  1. Task 1

    insertUserWithMetadata: a member with settings

    Write insertUserWithMetadata(username, email, metadata). username and email are strings; metadata is a plain object. Add one member with these three values.

    Return the new member's whole row as one object: the check reads username, and metadata must equal the object it passed.

  2. Task 2

    queryByJsonField: members by one top-level key

    Write queryByJsonField(field, value). Both are strings, for example role and admin. It finds the members whose metadata has the key field with exactly that value, compared as text.

    Return an array of the matching member rows; raw rows from the pool are fine (the check reads username). Any order.

  3. Task 3

    updateJsonField: change one setting

    Write updateJsonField(userId, key, value). userId is a number, key a string, and value any JSON value (the check passes the string dark). It sets the top-level key of that member's metadata to value, keeping every other key as it was. If the member's metadata is NULL, start from an empty object.

    Return the updated row as one object; raw rows from the pool are fine (the check reads metadata.theme).

  4. Task 4

    queryNestedJson: members by a nested setting

    Write queryNestedJson(path, value). path is an array of keys, like preferences then theme, and value is a string. It finds the members whose metadata, followed down that path, holds exactly that value, compared as text.

    Return an array of the matching member rows; raw rows from the pool are fine. Any order.

  5. Task 5

    getJsonKeys: which settings a member has

    Write getJsonKeys(userId). userId is a number. It lists the top-level keys of that member's metadata.

    Return an array of strings (the key names themselves, not row objects), sorted alphabetically. For the check's member that is alpha, beta, gamma.

What you will learn

  • #> and #>>: follow a path of keys into a jsonb value.
  • jsonb_set: replace the value at one path and keep the rest, and why a NULL document needs COALESCE.
  • jsonb_object_keys: one row per top-level key, and the order jsonb keeps them in.
  • Arrays and JSON as parameters of pool.query.

More in JSON & Relations

Build The JSON vault yourself

MingleSphereQL grows up: its backend talks to Postgres through Drizzle. 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 with Drizzle are free. This one comes with the all labs plan.