Json_types

Chapter 49: JSON & JSONB Data Types

Working with Semi-Structured Data

---

49.1 JSON vs JSONB

PostgreSQL supports two JSON data types with different characteristics.

                    JSON Types Comparison
    ========================================================================

    ┌─────────────────────────────────────────────────────────────────────┐
    │                        JSON vs JSONB                                 │
    │                                                                      │
    │   JSON:                                                              │
    │   ─────                                                             │
    │   • Stores exact text                                               │
    │   • Preserves whitespace                                            │
    │   • Faster write, slower read                                       │
    │   • Duplicate keys allowed                                          │
    │                                                                      │
    │   JSONB:                                                            │
    │   ──────                                                            │
    │   • Stores parsed binary                                            │
    │   • No whitespace preserved                                         │
    │   • Slower write, faster read                                       │
    │   • No duplicate keys                                               │
    │   • Supports indexing                                                │
    │                                                                      │
    │   Recommendation: Use JSONB for most use cases                       │
    │                                                                      │
    └─────────────────────────────────────────────────────────────────────┘

---

49.2 Creating JSON Tables

-- Create table with JSONB column
CREATE TABLE events (
    event_id SERIAL PRIMARY KEY,
    event_name VARCHAR(200),
    payload JSONB,
    created_at TIMESTAMP DEFAULT NOW()
);

-- Insert JSON data
INSERT INTO events (event_name, payload) VALUES
    ('user_signup', '{"user_id": 1, "email": "john@example.com", "source": "web"}'),
    ('order_placed', '{"order_id": 100, "items": ["a", "b", "c"], "total": 99.99}'),
    ('page_view', '{"url": "/home", "user_id": 1, "duration": 30}');

-- Query JSON data
SELECT event_name, payload->>'user_id' as user_id
FROM events;

---

49.3 JSON Operators

-- Extract value (-> returns JSON, ->> returns text)
SELECT payload->>'email' FROM events WHERE event_name = 'user_signup';
SELECT payload->'items' FROM events WHERE event_name = 'order_placed';

-- Nested extraction
SELECT payload->'user'->>'name' FROM events;

-- Path extraction (#>)
SELECT payload#>>'{user,name}' FROM events;

-- Containment (@>)
SELECT * FROM events WHERE payload @> '{"user_id": 1}';

-- Has key (?)
SELECT * FROM events WHERE payload ? 'user_id';

-- Contains any keys (?|)
SELECT * FROM events WHERE payload ?| array['user_id', 'order_id'];

---

49.4 JSONB Indexing

-- Create GIN index for JSONB
CREATE INDEX idx_events_payload ON events USING GIN(payload);

-- Create index for specific path
CREATE INDEX idx_events_user_id ON events ((payload->>'user_id'));

-- Use index for containment queries
EXPLAIN SELECT * FROM events WHERE payload @> '{"user_id": 1}';

---

49.5 JSON Functions

-- Convert row to JSON
SELECT row_to_json(orders) FROM orders LIMIT 1;

-- Convert array to JSON
SELECT jsonb_agg(name) FROM products;

-- Build JSON object
SELECT jsonb_build_object('name', 'John', 'age', 30);

-- Build JSON array
SELECT jsonb_build_array(1, 2, 3);

-- Convert JSON array to rows
SELECT * FROM jsonb_array_elements_text('["a", "b", "c"]');

-- Pretty print
SELECT jsonb_pretty(payload) FROM events;

---

Summary

Operator	Returns	Use
->	JSON	Get by key
->>	Text	Get as text
@>	Bool	Contains
?	Bool	Has key

---

Next: Chapter 50: Full-Text Search