JSON & JSONB

jsonb columns store JSON documents in an efficient binary format. They're useful for data whose shape varies — preferences, extra profile fields, data from external APIs — alongside normal columns for the core data.

Read values with -> (returns JSON) and ->> (returns text). @> tests containment ("does this document include these keys/values?"), and a GIN index makes containment queries fast.

PostgreSQL can also build JSON: jsonb_build_object and jsonb_agg turn query results into nested JSON ready to send from an API — often in a single query.

jsonb.sqlSQL

ALTER TABLE students ADD COLUMN profile jsonb NOT NULL DEFAULT '{}';

UPDATE students SET profile = '{"guardian": {"name": "Mama Amina", "phone": "0754000111"}, "clubs": ["debate", "science"], "boarding": true}'
WHERE id = 1;
UPDATE students SET profile = '{"guardian": {"name": "Baba Neema", "phone": "0688000222"}, "clubs": ["science"], "boarding": false}'
WHERE id = 3;

SELECT full_name,
       profile -> 'guardian' ->> 'phone' AS guardian_phone,
       (profile ->> 'boarding')::boolean AS boarding
FROM students
WHERE profile ? 'guardian';

-- Containment: members of the science club
SELECT full_name FROM students WHERE profile @> '{"clubs": ["science"]}';

CREATE INDEX idx_students_profile ON students USING gin (profile);

-- Update one key without replacing the document
UPDATE students SET profile = jsonb_set(profile, '{boarding}', 'false') WHERE id = 1;

-- Build an API-ready JSON document with nested results
SELECT jsonb_build_object(
    'student', s.full_name,
    'form', s.form,
    'results', jsonb_agg(
        jsonb_build_object('subject', sub.code, 'score', r.score)
        ORDER BY sub.code
    )
) AS report
FROM students s
JOIN results r    ON r.student_id = s.id AND r.term = 2
JOIN subjects sub ON sub.id = r.subject_id
WHERE s.id = 1
GROUP BY s.id;

Runs in your browser · PostgreSQL

Key points

Use jsonb for flexible attributes; keep core, frequently-queried data in columns.
-> returns JSON, ->> returns text; @> checks containment.
A GIN index speeds up @> and ? queries on jsonb.

Exercise

Add a settings jsonb column to teachers (e.g. preferred language, notification options). Write queries that find teachers who want SMS notifications, and produce one JSON array of all subjects with their teacher's name and settings.

Show solution

Try the exercise yourself first — then compare your approach with this one.

The settings column holds each teacher's preferences. The @> containment check finds teachers whose notifications include SMS (fast with a GIN index), and jsonb_agg + jsonb_build_object produce the whole JSON array in one query.

teacher-settings.sqlSQL

ALTER TABLE teachers ADD COLUMN settings jsonb NOT NULL DEFAULT '{}';

UPDATE teachers SET settings = '{"language": "sw", "notify": ["sms", "email"]}' WHERE full_name = 'Mr. Mwakyusa';
UPDATE teachers SET settings = '{"language": "en", "notify": ["email"]}'        WHERE full_name = 'Ms. Lyimo';
UPDATE teachers SET settings = '{"language": "sw", "notify": ["sms"]}'          WHERE full_name = 'Mrs. Mrema';

CREATE INDEX idx_teachers_settings ON teachers USING gin (settings);

-- Teachers who want SMS notifications
SELECT full_name, settings ->> 'language' AS language
FROM teachers
WHERE settings @> '{"notify": ["sms"]}'
ORDER BY full_name;

-- One JSON array of all subjects with their teacher
SELECT jsonb_agg(
    jsonb_build_object(
        'subject', sub.name,
        'teacher', t.full_name,
        'settings', t.settings
    ) ORDER BY sub.name
) AS subjects
FROM subjects sub
LEFT JOIN teachers t ON t.id = sub.teacher_id;

Runs in your browser · PostgreSQL

Key points

Exercise

Check your understanding