Week 9

Types of Databases

Databases: Comparison, Choosing & Polyglot

jsonb: Concepts & Setup

jsonb: querying updating

jsonb: Indexes, Tradeoffs & Exercise

Object Storage

Practice

Assignment

• Teachers

Backend Track

What you will learn on this page

• What jsonb is and why PostgreSQL stores it in binary format
• When to use jsonb instead of normal columns
• How to create a table with a jsonb column from Spring Boot
• How to insert JSON data using JdbcTemplate

What we are building

You are building a books API for a library. The library has three types of books — novels, textbooks, and comics. Each type has completely different attributes.

This is a perfect use case for jsonb — instead of creating a messy table full of NULL columns, you store the flexible attributes in one jsonb column called details.

By the end of this page your API will:

• Have a books table with a jsonb column created automatically on startup
• Load three sample books with different JSON shapes on startup
• Expose a GET /api/books endpoint that returns all books

1. What is jsonb?

jsonb stands for JSON Binary. It is a PostgreSQL column type that stores a JSON value an object, array, string, or number in a binary format inside a regular relational table.

PostgreSQL offers two JSON column types:

Always use jsonb it is faster to query, supports indexing, and validates JSON on insert. If you insert invalid JSON, PostgreSQL rejects it immediately with an error. That is actually useful.

2. When to use jsonb vs normalized tables

This is the most important decision. jsonb solves a specific problem, use it only when that problem exists.

The problem, the NULL column problem

A library has three types of books novels, textbooks, and comics. Each type has completely different attributes. In a normal table you are forced to add a column for every possible attribute:

books table (bad approach - full of NULLs)

Most columns are NULL for most rows. Adding a new book type means ALTER TABLE to add more columns.

The jsonb solution

books table(good approach)

No NULL columns. No schema change when you add a new book type.

When to use what

Use jsonb when:

• Fields vary between rows novels, textbooks, comics all have different attributes
• Schema is still evolving you keep adding new fields
• Data comes from an external API with unpredictable structure

Use normal columns when:

• All rows share the same fields
• You need NOT NULL, UNIQUE, or foreign key constraints
• You filter, join, or aggregate on that field frequently

<aside> 💡

The practical rule: put stable fields you always query (title) as proper columns. Put flexible varying fields (details) in jsonb.

</aside>

3. Creating a Table with a jsonb Column

There are two ways to create the table in Spring Boot. The recommended way is using a SQL file but both are shown here.

Project structure

Step 1 - Start PostgreSQL

Open the IntelliJ terminal and check it is running:

docker ps
docker start my-postgres

Step 2 - Create the database

docker exec -it my-postgres psql -U hyfuser -d postgres

CREATE DATABASE jsonb_demo;
\q

Step 3 - Create the Spring Boot project

Go to start.spring.io:

Click Generate, unzip, open in IntelliJ.

Add Jackson to pom.xml inside <dependencies>:

<dependency>
  <groupId>com.fasterxml.jackson.core</groupId>
  <artifactId>jackson-databind</artifactId>
</dependency>

Step 4 Configure application.yaml

spring:
  application:
    name: WeekFourRestAPI
  datasource:
    url: jdbc:postgresql://localhost:5432/jsonb_demo
    username: hyfuser
    password: hyfpassword
  sql:
    init:
      # runs schema.sql automatically every time the app starts
      mode: always

Step 5 Create the table using a SQL file(Recommended)

In IntelliJ, right-click on src/main/resources → New → File → name it schema.sql.

Paste this inside:

CREATE TABLE IF NOT EXISTS books (
    id      SERIAL PRIMARY KEY,
    title   VARCHAR(200) NOT NULL,
    details JSONB
);

CREATE TABLE IF NOT EXISTS means it only creates the table if it does not already exist — safe to run every time the app starts. Notice the details column type is JSONB that is the only special part.

When you start the app, Spring Boot finds schema.sql automatically and runs it.

Alternative - Create the table from Java code

Use one approach only - either schema.sql or DatabaseInitializer, not both.

java

package net.hackyourfuture.WeekFourRestAPI;

import org.springframework.boot.CommandLineRunner;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

@Component
public class DatabaseInitializer implements CommandLineRunner {

    private final JdbcTemplate jdbcTemplate;

    public DatabaseInitializer(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    @Override
    public void run(String... args) {
        jdbcTemplate.execute("""
            CREATE TABLE IF NOT EXISTS books (
                id      SERIAL PRIMARY KEY,
                title   VARCHAR(200) NOT NULL,
                details JSONB
            )
        """);
        System.out.println("Books table ready!");
    }
}

Verify the table was created

bash

docker exec -it my-postgres psql -U hyfuser -d jsonb_demo