Preparing your workspace
JSON and JSONB allow PostgreSQL to store structured JSON data while still supporting SQL queries and indexing strategies.
PostgreSQL supports both JSON and JSONB. JSON stores the textual representation while JSONB stores a processed binary representation that is usually better for querying and indexing. JSONB is useful when an application has flexible metadata or configuration that does not fit neatly into fixed columns. It should not automatically replace relational modeling. Core business data that needs strong relationships and predictable queries is often clearer in normal columns and tables.
A course platform can store flexible lesson metadata such as UI preferences or imported content settings in a JSONB column while keeping course IDs and relationships as normal columns.
CREATE TABLE courses (
id UUID PRIMARY KEY,
title TEXT NOT NULL,
metadata JSONB NOT NULL DEFAULT '{}'::jsonb
);
SELECT id, title
FROM courses
WHERE metadata->>'level' = 'beginner';The query retrieves courses whose JSON metadata contains a level value of beginner.