Planned lessonMID-019-L04

jsonb: when and how

On This Lesson 4 topics

This lesson is planned. The outline below lists the topics it will cover. The full lesson has not been published yet.

In this lesson

use jsonb for flexible attributes without abandoning the schema.

Good for sparse, varying attributes (product specs per category) and stored external payloads.
Not a way to avoid designing the schema: keep ids, foreign keys, and frequently filtered fields as real columns.
Query with ->, ->>, @>; index with GIN (jsonb_path_ops for containment) or a B-tree expression index on one key.
Updating one key rewrites the whole value; large frequently updated documents are costly.

Draft outline details

sql

CREATE INDEX idx_product_attrs ON product USING gin (attrs jsonb_path_ops);
SELECT id FROM product WHERE attrs @> '{"color":"red"}';

Which fields should not live only in jsonb?

Show answer

Keys, foreign keys, and fields used in joins and frequent filters.

Apply this lesson with the module's lab, exercises and quiz.

Marking a lesson complete is optional and you can undo it any time. Your progress stays in this browser.