JSONB Working with JSON Data

Store, query, and index semi-structured JSON data using PostgreSQL's powerful JSONB type.

Overview

JSONB (Binary JSON) is PostgreSQL's primary type for storing JSON documents. Unlike the JSON type (which stores JSON as-is), JSONB parses and stores JSON in a binary format: key order is not preserved, duplicate keys are removed, and it supports indexing. JSONB is almost always the right choice over JSON for stored data. PostgreSQL's JSONB operators allow you to extract fields, check for key existence, filter by value, and update nested data without full document replacement. Combined with GIN indexes, JSONB can make document-centric queries very fast.

Key Points

•JSONB is binary JSON indexed, faster to query than JSON type
•-> returns JSON; ->> returns TEXT (for top-level keys)
•#> extracts nested: doc #> '{key1,key2}'
•@> containment: doc @> '{"key":"value"}' fast with GIN index
•jsonb_set() for updating nested values
•jsonb_each() to expand keys as rows

Examples

JSONB extraction operators

-- Sample data
CREATE TABLE users (id SERIAL PRIMARY KEY, profile JSONB);
INSERT INTO users (profile) VALUES
  ('{"name": "Alice", "age": 30, "address": {"city": "NYC"}}');

-- Extract value as JSON
SELECT profile -> 'name' FROM users;          -- "Alice"

-- Extract value as TEXT (remove quotes)
SELECT profile ->> 'name' FROM users;         -- Alice

-- Nested extraction
SELECT profile #>> '{address,city}' FROM users;  -- NYC

-- Containment check
SELECT * FROM users WHERE profile @> '{"age": 30}';

Updating JSONB and GIN indexing

-- Update a nested value
UPDATE users SET profile = jsonb_set(profile, '{address,city}', '"LA"')
WHERE id = 1;

-- Add a new key
UPDATE users SET profile = profile || '{"verified": true}';

-- GIN index for fast containment queries
CREATE INDEX idx_users_profile ON users USING GIN (profile);

JSONB Working with JSON Data

Overview

Key Points

Examples

Exercises

Knowledge Check

What is the difference between -> and ->> in JSONB?