JSON, массивы, расширенные типы

-- Извлечение (наша таблица products имеет поле attributes JSONB)

-- Получить значение по ключу → JSONB
SELECT attributes -> 'brand' FROM products;
-- → "Apple" (с кавычками, тип jsonb)

-- Получить значение по ключу → TEXT
SELECT attributes ->> 'brand' FROM products;
-- → Apple (без кавычек, тип text)

-- Вложенный путь → JSONB
SELECT attributes -> 'specs' -> 'cpu' FROM products;

-- Вложенный путь → TEXT
SELECT attributes #>> '{specs,cpu}' FROM products;

-- Содержит ли JSONB другой JSONB? (containment)
SELECT * FROM products
WHERE attributes @> '{"brand": "Apple"}';

-- Существует ли ключ?
SELECT * FROM products WHERE attributes ? 'color';

-- Существует ли хотя бы один ключ?
SELECT * FROM products WHERE attributes ?| array['color', 'size'];

-- Существуют ли все ключи?
SELECT * FROM products WHERE attributes ?& array['brand', 'color'];

-- Добавить/обновить ключ
UPDATE products
SET attributes = attributes || '{"warranty_months": 12}'
WHERE product_id = 1;

-- Удалить ключ
UPDATE products
SET attributes = attributes - 'warranty_months'
WHERE product_id = 1;

-- Обновить вложенное значение
UPDATE products
SET attributes = jsonb_set(attributes, '{storage_gb}', '256')
WHERE product_id = 1;

-- GIN на весь JSONB (поддерживает @>, ?, ?|, ?&)
CREATE INDEX idx_products_attrs ON products USING gin (attributes);

-- GIN с jsonb_path_ops (только @>, но на 30% компактнее)
CREATE INDEX idx_products_attrs_path ON products USING gin (attributes jsonb_path_ops);

-- B-tree на конкретное поле (для =, <, >, сортировка)
CREATE INDEX idx_products_brand ON products ((attributes ->> 'brand'));

-- Агрегация: сколько товаров каждого бренда?
SELECT attributes ->> 'brand' AS brand, COUNT(*)
FROM products
WHERE attributes ? 'brand'
GROUP BY 1
ORDER BY 2 DESC;

-- JSON-агрегация: собрать массив объектов
SELECT customer_id,
       jsonb_agg(jsonb_build_object(
           'product', name,
           'rating', rating
       )) AS reviews
FROM reviews r
JOIN products p USING (product_id)
GROUP BY customer_id;

CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title TEXT,
    tags TEXT[]                    -- массив строк
);

INSERT INTO articles (title, tags)
VALUES ('ETL-паттерны', ARRAY['etl', 'data-engineering', 'best-practices']);

-- Альтернативный синтаксис
INSERT INTO articles (title, tags)
VALUES ('SQL для DE', '{sql, postgresql, analytics}');

-- Содержит ли массив элемент?
SELECT * FROM articles WHERE 'etl' = ANY(tags);

-- Содержит ли массив все элементы?
SELECT * FROM articles WHERE tags @> ARRAY['etl', 'sql'];

-- Пересечение (overlap)
SELECT * FROM articles WHERE tags && ARRAY['python', 'sql'];

-- Длина массива
SELECT title, array_length(tags, 1) AS tag_count FROM articles;

-- Развернуть массив в строки
SELECT title, unnest(tags) AS tag FROM articles;

-- GIN для @>, &&, =
CREATE INDEX idx_articles_tags ON articles USING gin (tags);

CREATE TABLE room_bookings (
    id SERIAL PRIMARY KEY,
    room TEXT,
    period TSRANGE NOT NULL,                 -- [start, end)
    EXCLUDE USING gist (room WITH =, period WITH &&)  -- запрет пересечений!
);

INSERT INTO room_bookings (room, period) VALUES
('A101', '[2025-03-15 09:00, 2025-03-15 12:00)'),
('A101', '[2025-03-15 13:00, 2025-03-15 17:00)');

-- Это упадёт — пересечение!
INSERT INTO room_bookings (room, period) VALUES
('A101', '[2025-03-15 11:00, 2025-03-15 14:00)');
-- ERROR: conflicting key value violates exclusion constraint

-- Содержит ли диапазон значение?
SELECT * FROM room_bookings WHERE period @> '2025-03-15 10:00'::timestamp;

-- Пересечение
SELECT * FROM room_bookings WHERE period && '[2025-03-15 11:00, 2025-03-15 14:00)';

-- Границы
SELECT lower(period), upper(period) FROM room_bookings;

CREATE TABLE order_items_v2 (
    item_id SERIAL PRIMARY KEY,
    quantity INT NOT NULL,
    unit_price NUMERIC(10,2) NOT NULL,
    discount NUMERIC(5,2) DEFAULT 0,
    -- Автоматически рассчитывается
    total NUMERIC(12,2) GENERATED ALWAYS AS (quantity * unit_price * (1 - discount / 100)) STORED
);

INSERT INTO order_items_v2 (quantity, unit_price, discount) VALUES (3, 1000, 10);
SELECT total FROM order_items_v2;
-- → 2700.00  (3 * 1000 * 0.9)

-- Числа от 1 до 10
SELECT generate_series(1, 10);

-- Даты за последний месяц
SELECT generate_series(
    CURRENT_DATE - INTERVAL '30 days',
    CURRENT_DATE,
    '1 day'
)::date AS day;

-- Заполнить пропуски в данных (LEFT JOIN с рядом дат)
SELECT d.day, COALESCE(COUNT(o.order_id), 0) AS orders
FROM generate_series('2024-06-01'::date, '2024-06-30'::date, '1 day') AS d(day)
LEFT JOIN orders o ON o.order_date = d.day
GROUP BY d.day
ORDER BY d.day;

-- CAST
SELECT CAST('42' AS INTEGER);
SELECT '42'::INTEGER;

-- to_char / to_date / to_timestamp
SELECT to_char(NOW(), 'YYYY-MM-DD HH24:MI');
SELECT to_date('15-03-2025', 'DD-MM-YYYY');