-- JSONB: document storage with full indexing
CREATE TABLE events (
  id SERIAL PRIMARY KEY,
  metadata JSONB NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Index on field inside JSON
CREATE INDEX idx_events_user ON events ((metadata->>'user_id'));

-- Query on nested JSON
SELECT * FROM events
WHERE metadata->>'event_type' = 'purchase'
AND (metadata->>'amount')::numeric > 100;

-- Full-text search built-in
SELECT title, ts_rank(search_vector, query) AS rank
FROM articles,
     to_tsquery('english', 'database & performance') query
WHERE search_vector @@ query
ORDER BY rank DESC;

-- Recursive CTE for hierarchy
WITH RECURSIVE category_tree AS (
  SELECT id, name, parent_id, 0 AS depth
  FROM categories WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.name, c.parent_id, ct.depth + 1
  FROM categories c
  JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree ORDER BY depth, name;

מבוא למסדי נתונים — סוגים ומתי מה

מבוא למסדי נתונים — סוגים ומתי מה

מבוא למסדי נתונים — סוגים ומתי מה