Ví dụ truy vấn phức tạp với JSONB

Ở các bài trước, tụi mình đã học căn bản về JSONB: tạo, sửa và lấy dữ liệu. Giờ là lúc thử thách thực sự — những truy vấn phức tạp để thấy hết sức mạnh của kiểu dữ liệu này.

Hãy tưởng tượng một shop online với catalog sản phẩm. Mỗi sản phẩm có info cơ bản (tên, ID), nhưng thuộc tính thì khác nhau hoàn toàn: laptop thì có RAM và CPU, quần áo thì có size và chất liệu, sách thì có tác giả và thể loại. Lưu hết vào các bảng riêng? Phiền phết. Dùng JSONB? Quá hợp lý! Nhưng làm sao tìm tất cả sản phẩm của một brand nhất định, sắp xếp theo giá hoặc tính thống kê theo category? Làm sao xử lý dữ liệu không nằm ở cột bình thường mà nằm sâu trong cấu trúc JSON?

Hôm nay mình sẽ đi qua các kịch bản thực tế: từ lọc đơn giản đến truy vấn phức tạp với group by và aggregate. Bạn sẽ thấy JSONB biến PostgreSQL thành công cụ cực kỳ linh hoạt cho mọi loại dữ liệu.

Lọc dữ liệu trong JSONB

Lọc dữ liệu giống như cái rây lọc trà: giữ lại cái cần, bỏ cái thừa. Với JSONB còn vui hơn nữa, vì mình có thể lọc không chỉ theo cột thường mà còn theo dữ liệu nằm sâu trong JSON.

Các operator để lọc JSONB:

• @> — "JSONB-chứa". Kiểm tra xem object JSONB có chứa subset chỉ định không.
• ? — "Key tồn tại". Kiểm tra xem key chỉ định có trong object JSONB không.
• ?| — "Bất kỳ key nào tồn tại". Kiểm tra xem có ít nhất một key trong số chỉ định tồn tại không.
• ?& — "Tất cả key tồn tại". Kiểm tra xem tất cả các key chỉ định đều tồn tại không.

Ví dụ: lọc theo key và giá trị của nó. Giả sử mình có bảng products với cột details lưu info sản phẩm dạng JSONB:

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT,
    details JSONB
);

Dữ liệu ví dụ:

INSERT INTO products (name, details) VALUES
('Laptop', '{"brand": "Dell", "price": 1200, "specs": {"ram": "16GB", "cpu": "i7"}}'),
('Smartphone', '{"brand": "Apple", "price": 800, "specs": {"ram": "4GB", "cpu": "A15"}}'),
('Tablet', '{"brand": "Samsung", "price": 500, "specs": {"ram": "8GB", "cpu": "Exynos"}}');

Kết quả:

Để tìm tất cả sản phẩm có brand là "Apple":

SELECT *
FROM products 
WHERE details @> '{"brand": "Apple"}';

Kết quả:

Nếu bạn muốn tìm tất cả sản phẩm có key specs, dùng operator ?:

SELECT *
FROM products 
WHERE details ? 'specs';

Kết quả:

Tất cả các dòng đều có trường details và key specs.

Sắp xếp dữ liệu trong JSONB

Đôi khi bạn cần sắp xếp dữ liệu không phải theo cột thường mà theo giá trị nằm trong JSONB. Để làm vậy, bạn dùng operator ->> (lấy giá trị dạng text) và CAST để chuyển sang kiểu cần thiết.

Ví dụ: sắp xếp sản phẩm theo giá:

SELECT *
FROM products 
ORDER BY (details->>'price')::INTEGER;

Kết quả:

Group by dữ liệu trong JSONB

Group by giúp tổng hợp dữ liệu và xuất thống kê. Ví dụ, bạn có thể biết mỗi brand có bao nhiêu sản phẩm.

Ví dụ: đếm số sản phẩm cho từng brand:

SELECT
    details->>'brand' AS brand,
    COUNT(*) AS product_count
FROM products
GROUP BY details->>'brand';

Kết quả:

Ví dụ thực tế

Lọc và group by. Đếm số sản phẩm giá trên 600 cho từng brand:

SELECT
    details->>'brand' AS brand,
    COUNT(*) AS product_count
FROM products
WHERE (details->>'price')::INTEGER > 600
GROUP BY details->>'brand';

Kết quả:

Sắp xếp sau khi group by. Giờ sắp xếp brand theo số lượng sản phẩm:

SELECT
    details->>'brand' AS brand,
    COUNT(*) AS product_count
FROM products
GROUP BY details->>'brand'
ORDER BY product_count DESC;

Truy vấn phức tạp: lọc, sắp xếp, group by

Giả sử bạn muốn tìm các brand có sản phẩm giá trên 600 và chọn ra một sản phẩm rẻ nhất cho mỗi brand. Làm như này nhé:

WITH filtered_products AS (
    SELECT *
    FROM products
    WHERE (details->>'price')::INTEGER > 600
)
SELECT
    details->>'brand' AS brand,
    MIN((details->>'price')::INTEGER) AS min_price
FROM filtered_products
GROUP BY details->>'brand'
ORDER BY min_price;

Kết quả:

Lỗi thường gặp và tips

Lỗi: Dùng operator sai. Đừng nhầm -> và ->>: cái đầu trả về object, cái sau trả về giá trị dạng text.

Lỗi: Vấn đề hiệu năng. Nếu bạn hay chạy truy vấn phức tạp, hãy tạo index GIN cho cột JSONB.

Lỗi: Vấn đề kiểu dữ liệu. Giá trị lấy từ JSONB là string, nhớ dùng CAST nhé.

Ví dụ tạo index:

CREATE INDEX idx_products_details ON products USING GIN (details);

Giờ thì lọc kiểu details @> '{"brand": "Apple"}' sẽ chạy nhanh hơn nhiều.