Working with JSON and JSONB in PostgreSQL

-- JSON aggregation
SELECT
  profile ->> 'city' AS city,
  jsonb_agg(
    jsonb_build_object(
      'username', username,
      'age', profile ->> 'age'
    )
  ) AS users
FROM users
GROUP BY profile ->> 'city';

-- JSON object aggregation
SELECT
  jsonb_object_agg(
    username,
    profile -> 'age'
  ) AS user_ages
FROM users;

-- Pretty print JSON
SELECT jsonb_pretty(profile) FROM users LIMIT 1;

-- Get all keys
SELECT DISTINCT jsonb_object_keys(profile) AS key
FROM users;

-- Get all values
SELECT
  username,
  key,
  value
FROM users,
     jsonb_each(profile)
WHERE key IN ('age', 'city');

-- Expand JSON to rows
SELECT
  u.username,
  e.key,
  e.value
FROM users u
CROSS JOIN LATERAL jsonb_each_text(u.profile) e;

-- Array to rows
SELECT
  u.username,
  skill
FROM users u
CROSS JOIN LATERAL jsonb_array_elements_text(u.profile -> 'skills') skill;

-- Merge JSONB objects
SELECT
  profile || preferences AS merged
FROM users
LIMIT 1;

-- Deep merge
SELECT
  jsonb_set(
    profile,
    '{contact}',
    COALESCE(profile -> 'contact', '{}'::jsonb) ||
      '{"twitter": "@user"}'::jsonb
  ) AS updated_profile
FROM users;

-- Conditional update
UPDATE users
SET preferences = CASE
  WHEN preferences ? 'theme'
  THEN jsonb_set(preferences, '{theme}', '"light"')
  ELSE preferences || '{"theme": "light"}'
END;

-- Type checking
SELECT
  username,
  jsonb_typeof(profile -> 'age') AS age_type,
  jsonb_typeof(profile -> 'skills') AS skills_type
FROM users;

-- Validate JSON structure
CREATE OR REPLACE FUNCTION validate_user_profile(data JSONB)
RETURNS BOOLEAN AS $$
BEGIN
  RETURN (
    data ? 'age' AND
    jsonb_typeof(data -> 'age') = 'number' AND
    (data ->> 'age')::INT BETWEEN 0 AND 150 AND
    data ? 'city' AND
    jsonb_typeof(data -> 'city') = 'string'
  );
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- Add constraint
ALTER TABLE users
  ADD CONSTRAINT valid_profile
  CHECK (validate_user_profile(profile));

-- Full-text search on JSON
CREATE INDEX idx_users_profile_fts
  ON users
  USING GIN (to_tsvector('english', profile::text));

SELECT username
FROM users
WHERE to_tsvector('english', profile::text) @@
      to_tsquery('english', 'Python & SQL');

-- Event store pattern
CREATE TABLE events (
  id SERIAL PRIMARY KEY,
  event_type VARCHAR(50) NOT NULL,
  payload JSONB NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_events_type ON events (event_type);
CREATE INDEX idx_events_payload_gin ON events USING GIN (payload);

INSERT INTO events (event_type, payload) VALUES
(
  'user_registered',
  '{"user_id": 123, "email": "user@example.com", "source": "web"}'
),
(
  'order_placed',
  '{"order_id": 456, "user_id": 123, "total": 99.99, "items": [{"id": 1, "qty": 2}]}'
);

-- Query events
SELECT *
FROM events
WHERE event_type = 'order_placed'
  AND (payload ->> 'total')::DECIMAL > 50;

-- Schemaless tables with JSONB
CREATE TABLE documents (
  id SERIAL PRIMARY KEY,
  type VARCHAR(50) NOT NULL,
  data JSONB NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_documents_type ON documents (type);
CREATE INDEX idx_documents_data_gin ON documents USING GIN (data);

-- Different document types in same table
INSERT INTO documents (type, data) VALUES
('product', '{"name": "Laptop", "price": 999, "specs": {"cpu": "i7", "ram": "16GB"}}'),
('article', '{"title": "DB Guide", "author": "Maria", "content": "...", "tags": ["db", "sql"]}');

-- Type-specific queries
SELECT data ->> 'name' AS product_name
FROM documents
WHERE type = 'product'
  AND (data ->> 'price')::DECIMAL < 1000;

SELECT data ->> 'title' AS article_title
FROM documents
WHERE type = 'article'
  AND data -> 'tags' @> '["sql"]';