Advanced PostgreSQL: Functions, JSON, and SecurityLesson 6.3

Working with JSONB in PostgreSQL: operators and indexing

JSON vs JSONB, -> ->> #> operators, @> containment, ? key exists, jsonb_set, jsonb_agg, GIN index on JSONB, when to use JSONB vs relational columns

JSONB in PostgreSQL

JSONB stores JSON in a decomposed binary format — faster to query than JSON, and fully indexable. Use it for flexible or evolving attributes alongside relational columns.

Operators

-- Stored as JSONB: {"name":"Alice","tags":["admin","beta"],"address":{"city":"NY"}}

SELECT metadata -> 'address'          -- returns JSONB object
SELECT metadata ->> 'name'           -- returns TEXT: 'Alice'
SELECT metadata -> 'address' ->> 'city' -- 'NY'
SELECT metadata #>> '{address,city}' -- path operator: 'NY'

Containment and key existence

WHERE metadata @> '{"tags": ["admin"]}'  -- has admin tag
WHERE metadata ? 'address'              -- key exists
WHERE metadata ?| ARRAY['admin','beta'] -- any key exists

Update JSONB

UPDATE users
SET    metadata = jsonb_set(metadata, '{address,city}', '"LA"')
WHERE  id = 1;

GIN index

CREATE INDEX idx_gin_metadata ON users USING GIN (metadata);
-- Enables fast @> and ? queries

Do not store everything in JSONB. If a field is always present, always queried, or used in JOINs — make it a proper column. JSONB excels for optional, user-defined, or schema-less attributes.