Hồi team mình mới chuyển sang lưu config và event log dạng JSON vào MySQL, mọi thứ ban đầu trông gọn gẽ lắm. Nhưng đến lúc cần báo cáo — lấy từng field ra, aggregate, JOIN với bảng khác — mới thấy đau. Mấy câu JSON_EXTRACT() lồng nhau nhìn xong không muốn maintain nữa. Đó là lúc mình mò ra JSON_TABLE(), một hàm có từ MySQL 8.0 nhưng ít được nhắc đến trong các tutorial thông thường.
Ba cách tiếp cận khi truy vấn JSON lồng nhau
Giả sử bạn có bảng orders với cột items lưu dạng JSON array:
[
{"product_id": 101, "name": "Laptop", "qty": 1, "price": 25000000},
{"product_id": 205, "name": "Mouse", "qty": 2, "price": 350000}
]Khi cần báo cáo “mỗi sản phẩm bán được bao nhiêu cái”, bạn có ba lựa chọn thực tế:
Cách 1: JSON_EXTRACT() / JSON_VALUE()
SELECT
id,
JSON_EXTRACT(items, '$[0].name') AS first_item,
JSON_EXTRACT(items, '$[0].qty') AS first_qty
FROM orders;Đơn giản, nhưng vấn đề là bạn phải hard-code index ($[0], $[1]…). Mỗi đơn hàng có số lượng item khác nhau? Query phải viết lại tương ứng. Và GROUP BY toàn bộ items thì không làm được.
Cách 2: Xử lý ở tầng application
Pull toàn bộ JSON về Python hoặc PHP rồi loop qua từng phần tử, aggregate bằng code. Cách này linh hoạt nhưng tốn memory và CPU ở app server. Mình từng thấy một service PHP bị OOM crash vì kéo 500k rows JSON về để xử lý — không vui chút nào, đặc biệt lúc đó là giữa đêm.
Cách 3: JSON_TABLE() — chuyển thẳng thành bảng quan hệ
SELECT
o.id AS order_id,
t.product_id,
t.name,
t.qty,
t.price
FROM orders o,
JSON_TABLE(
o.items, '$[*]'
COLUMNS (
product_id INT PATH '$.product_id',
name VARCHAR(100) PATH '$.name',
qty INT PATH '$.qty',
price DECIMAL(15,2) PATH '$.price'
)
) AS t;JSON_TABLE() “flatten” JSON array thành rows, và từ đó bạn dùng SQL bình thường: GROUP BY, JOIN, WHERE, aggregate functions — tất cả hoạt động y như với bảng thật.
Phân tích ưu nhược điểm từng cách
JSON_EXTRACT() / JSON_VALUE() — Phù hợp khi chỉ cần lấy 1-2 field đơn lẻ từ JSON object (không phải array có nhiều phần tử). Syntax ngắn, query plan đơn giản. Dùng sai chỗ sẽ thành mớ code rối.
Xử lý ở application — Linh hoạt. Nhưng kém về network bandwidth và memory — kéo 1 triệu rows JSON về app để aggregate thì request timeout trước khi kịp trả kết quả. Chỉ dùng khi logic quá phức tạp để viết bằng SQL.
JSON_TABLE() — Mạnh nhất cho bài toán flatten array và query phức tạp. Xử lý nằm hoàn toàn ở database — MySQL tự lo execution plan. Nhược điểm: syntax dài hơn, cần MySQL 8.0+, và index không apply trực tiếp lên virtual table.
Khi nào nên chọn JSON_TABLE()
JSON_TABLE() không phải công cụ cho mọi bài toán JSON — nhưng với 4 tình huống dưới đây, không có nó thì khá đau:
- JSON column chứa array và cần unpack từng phần tử để GROUP BY hoặc tính tổng
- Cần JOIN kết quả từ JSON với bảng quan hệ khác (ví dụ: JOIN
productstheoproduct_idtrong JSON) - Báo cáo định kỳ trên dữ liệu JSON — viết query một lần, chạy nhiều lần
- Migration data: đọc JSON cũ, transform, insert vào schema chuẩn
Ngược lại, nếu chỉ cần filter theo một field trong JSON object phẳng, JSON_VALUE() đơn giản hơn và đủ dùng.
Hướng dẫn triển khai JSON_TABLE() thực tế
Setup dữ liệu test
CREATE TABLE orders (
id INT PRIMARY KEY AUTO_INCREMENT,
customer_id INT,
order_date DATE,
items JSON
);
INSERT INTO orders (customer_id, order_date, items) VALUES
(1, '2024-01-15', '[{"product_id":101,"name":"Laptop","qty":1,"price":25000000},{"product_id":205,"name":"Mouse","qty":2,"price":350000}]'),
(2, '2024-01-16', '[{"product_id":101,"name":"Laptop","qty":2,"price":25000000},{"product_id":301,"name":"Keyboard","qty":1,"price":800000}]'),
(1, '2024-01-17', '[{"product_id":205,"name":"Mouse","qty":1,"price":350000}]');Báo cáo tổng doanh thu theo sản phẩm
SELECT
t.product_id,
t.name,
SUM(t.qty) AS total_qty,
SUM(t.qty * t.price) AS total_revenue
FROM orders o,
JSON_TABLE(
o.items, '$[*]'
COLUMNS (
product_id INT PATH '$.product_id',
name VARCHAR(100) PATH '$.name',
qty INT PATH '$.qty',
price DECIMAL(15,2) PATH '$.price'
)
) AS t
GROUP BY t.product_id, t.name
ORDER BY total_revenue DESC;Kết quả ra đúng như query trên bảng quan hệ thật — không cần xử lý gì thêm ở application.
Xử lý JSON lồng nhiều cấp với NESTED PATH
Khi JSON có nhiều cấp lồng nhau — ví dụ object cha chứa array con — dùng NESTED PATH:
-- JSON structure: {"order_id": 1, "customer": {"name": "Nguyen Van A"}, "items": [...]}
SELECT
t.order_id,
t.customer_name,
t.product_name,
t.qty
FROM raw_import_data,
JSON_TABLE(
payload, '$'
COLUMNS (
order_id INT PATH '$.order_id',
customer_name VARCHAR(100) PATH '$.customer.name',
NESTED PATH '$.items[*]' COLUMNS (
product_name VARCHAR(100) PATH '$.name',
qty INT PATH '$.qty'
)
)
) AS t;NESTED PATH giữ context của row cha trong khi unpack từng item của array con — cái này dùng JSON_EXTRACT() đơn thuần không làm được.
Dùng FOR ORDINALITY để track vị trí trong array
SELECT
o.id,
t.row_num, -- Vị trí trong array gốc (1-based)
t.name,
t.qty
FROM orders o,
JSON_TABLE(
o.items, '$[*]'
COLUMNS (
row_num FOR ORDINALITY,
name VARCHAR(100) PATH '$.name',
qty INT PATH '$.qty'
)
) AS t;Handle dữ liệu không nhất quán với ON ERROR / ON EMPTY
Dữ liệu JSON từ production đôi khi có format lộn xộn — field bị thiếu hoặc sai kiểu dữ liệu. Thêm ON EMPTY và ON ERROR để tránh query crash:
JSON_TABLE(
o.items, '$[*]'
COLUMNS (
product_id INT PATH '$.product_id' DEFAULT '0' ON EMPTY DEFAULT '-1' ON ERROR,
name VARCHAR(100) PATH '$.name' DEFAULT 'Unknown' ON EMPTY,
price DECIMAL(15,2) PATH '$.price' DEFAULT '0.00' ON EMPTY
)
)Thay vì query fail toàn bộ vì một row JSON lỗi, bạn nhận được default value — production-safe hơn nhiều.
Những lưu ý khi dùng trong production
Index không apply trực tiếp — JSON_TABLE() generate virtual rows nên MySQL không thể dùng index trên JSON column để speed up. Nếu query chậm, cân nhắc tạo generated column kết hợp index cho các field hay dùng trong WHERE.
Luôn EXPLAIN trước khi deploy — Chạy EXPLAIN FORMAT=JSON SELECT ... để xem execution plan. Bảng 100k rows mà query JSON_TABLE không có filter tốt — expect full table scan, query mất vài giây thay vì milliseconds.
Validate JSON trước khi query — Invalid JSON trong column sẽ khiến JSON_TABLE() throw error. Lọc trước bằng:
SELECT ...
FROM orders o, JSON_TABLE(...) AS t
WHERE JSON_VALID(o.items) = 1;Backup trước migration — Mình từng gặp database corruption lúc 3 giờ sáng, phải restore từ backup mất hơn 2 tiếng. Từ đó luôn mysqldump trước bất kỳ migration script nào. Nếu bạn dùng JSON_TABLE() để migrate từ JSON column sang schema chuẩn, dump bảng đó trước đã.
Trước khi viết path expression cho JSON_TABLE(), nên kiểm tra cấu trúc JSON với JSON Formatter trên ToolCraft — format và validate nhanh ngay trên trình duyệt. Tool chạy client-side hoàn toàn nên không lo dữ liệu production bị gửi đi đâu.
Tổng kết
JSON_TABLE() giải một bài toán cụ thể: flatten JSON array để query phức tạp. JSON_EXTRACT() không làm được việc này; xử lý ở application thì ngốn bandwidth và memory. Cú pháp nhìn qua dài, nhưng viết vài câu đầu là quen ngay. Đang loop JSON ở app chỉ để GROUP BY hay JOIN? Thử chuyển xuống database — query gọn hơn và thường nhanh hơn đáng kể.
