JSONB: The NoSQL Killer

PostgreSQL isn’t just a relational database; it’s a multi-model database. With JSONB, you can store unstructured data (like MongoDB documents) alongside your structured relational data, often with better performance.

[!NOTE] JSON vs JSONB:

JSON: Stores exact text (preserves whitespace/keys order). Slow to query.

JSONB: Stores binary format. Reorders keys, removes whitespace, removes duplicate keys. Supports Indexing. Always use JSONB.

1. JSONB: The NoSQL Killer

2. The Big 4 Operators

You only need to memorize these four to survive.

Operator	Meaning	Example	Result Type
`->`	Get JSON object/array by key/index	`data->'user'`	JSONB
`->>`	Get value as Text	`data->>'email'`	Text
`@>`	Contains (Is left a superset of right?)	`data @> '{"role": "admin"}'`	Boolean
`?`	Exists (Does key/element exist?)	`data ? 'tags'`	Boolean

-- Select email as text
SELECT info->>'email' FROM users;

-- Filter where role is admin (using Index!)
SELECT * FROM users WHERE info @> '{"role": "admin"}';

-- Check if 'urgent' tag exists in array
SELECT * FROM tasks WHERE tags ? 'urgent';

3. JSONB: The NoSQL Killer

4. GIN Indexing

Querying JSONB is slow if you scan every row. The GIN (Generalized Inverted Index) changes the game.

-- Create index on the whole JSONB column
CREATE INDEX idx_users_info ON users USING GIN (info);

With this index, queries using @>, ?, ?&, and ?| are instantaneous.

[!WARNING] The standard GIN index does not support ->> comparisons (e.g., WHERE info->>'age' > '25'). For that, you need a B-Tree index on the expression.

Concept: How GIN Works (Inverted Index)

1. The Data (Rows)

Row 1: {"tags": ["sql", "db"]}

Row 2: {"tags": ["sql", "web"]}

Row 3: {"tags": ["db"]}

→

2. The GIN Index (Keys Map to Rows)

"sql" → [Row 1, Row 2]

"db" → [Row 1, Row 3]

"web" → [Row 2]

When you query WHERE tags ? 'sql', Postgres jumps straight to the "sql" entry in the index and grabs Row 1 and Row 2 directly. No scanning!

5. JSONB: The NoSQL Killer

6. Modifying JSONB

You can modify JSON data in place.

-- Update a specific field
UPDATE users
SET info = jsonb_set(info, '{preferences, theme}', '"dark"')
WHERE id = 1;

-- Remove a key
UPDATE users
SET info = info - 'deprecated_key';

7. JSONB: The NoSQL Killer

8. Interactive: JSONB Operator Playground

Test the operators against a live JSON document.

Source Document (jsonb)

{
  "id": 101,
  "user": {
  "name": "Alice",
  "active": true
  },
  "tags": ["sql", "postgres", "db"],
  "stats": {
  "login_count": 42
  }
}

Query

Result

Running...

Type: -

9. JSONB: The NoSQL Killer

[!NOTE] This module explores the core principles of JSONB Queries, deriving solutions from first principles and hardware constraints to build world-class, production-ready expertise.

10. Summary

Always use JSONB, never JSON.
Use ->> to extract text for display.
Use @> to filter rows efficiently with a GIN index.
Use GIN Indexes (USING GIN (col)) for performance.