PostgreSQL JSONB: stores semi-structured data

When JSONB makes sense

Some data, such as user preferences or metadata from third-party services, often change structure over time. Adding a new column each time the structure changes can be cumbersome. JSONB stores this semi-structured data in a single queryable column.

Creating JSONB column

CREATE TABLE preferensi_pengguna (
    id BIGSERIAL PRIMARY KEY,
    pengguna_id BIGINT NOT NULL,
    data JSONB NOT NULL DEFAULT '{}'
);

INSERT INTO preferensi_pengguna (pengguna_id, data)
VALUES (1, '{"tema": "gelap", "notifikasi": {"email": true, "push": false}}');

Fetching value from JSONB

SELECT
    pengguna_id,
    data->>'tema' AS tema,
    data->'notifikasi'->>'email' AS notif_email
FROM preferensi_pengguna
WHERE data->>'tema' = 'gelap';

Operator ->> returns the value as text, while -> returns the value as JSON so it can be traced deeper.

Creating an index for JSONB search

CREATE INDEX idx_preferensi_data ON preferensi_pengguna USING GIN (data);

The GIN index speeds up searches with operators @> which checks whether a JSONB contains a particular structure.

SELECT * FROM preferensi_pengguna
WHERE data @> '{"tema": "gelap"}';

JSONB is not a replacement for regular columns

Columns that are often used for filters, joins, or need strict constraints should still be ordinary columns. Use JSONB for additional data that is rarely used as the main search requirement.

practice;

Add an index to only certain paths, for example (data->>'tema'), then compare the query plan with the GIN index in the entire column.