JSON Operations

Code

-- Create JSON column
CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    data JSONB
);

-- Insert
INSERT INTO events (data) VALUES
    ('{"name": "click", "user": {"id": 1, "name": "Alice"}, "tags": ["ui", "button"]}');

-- Extract field
SELECT data->'name' AS event_name,
       data->'user'->>'name' AS user_name,
       data#>>'{user,id}' AS user_id
FROM events;

-- Conditional query
SELECT * FROM events WHERE data->>'name' = 'click';
SELECT * FROM events WHERE data @> '{"name": "click"}';

-- Modify JSON
UPDATE events SET data = jsonb_set(data, '{user,name}', '"Bob"');
UPDATE events SET data = data || '{"timestamp": "2024-01-01"}';
UPDATE events SET data = data - 'tags';

-- Expand array
SELECT jsonb_array_elements_text(data->'tags') AS tag
FROM events;

Explanation

JSONB supports GIN indexes and rich operators; ->> returns text, -> returns JSON.

More SQL Snippets