Bài 13: JSON & Generated Columns

Trong thế giới thiết kế cơ sở dữ liệu hiện đại, ranh giới giữa mô hình quan hệ chặt chẽ (Relational) và mô hình tài liệu phi cấu trúc (NoSQL Document) đang ngày càng mờ nhạt. Thay vì tách nhỏ mọi thuộc tính thành hàng chục bảng chuẩn hóa hoặc chuyển hẳn sang các DB không quan hệ, SQL hỗ trợ các cột lưu trữ dữ liệu dạng JSON kết hợp với cơ chế Generated Columns (Cột tự động sinh). Bài viết này sẽ đi sâu vào cách SQLite xử lý dữ liệu bán cấu trúc, cách trích xuất, làm phẳng các mảng phức tạp, và phương pháp tối ưu hóa hiệu năng bằng chỉ mục trên biểu thức (Index on Expression).

1. Lưu Trữ JSON Trong SQLite: Text thô vs Định Dạng Nhị Phân (JSONB)

Khác với các hệ quản trị lớn như PostgreSQL có kiểu dữ liệu jsonb lưu nhị phân chuyên dụng từ sớm, triết lý của SQLite là tối giản và tương thích ngược. Mặc định, SQLite lưu trữ dữ liệu JSON dưới dạng một chuỗi văn bản thô (kiểu TEXT).

Điều này có nghĩa là mỗi khi bạn chạy câu lệnh trích xuất thuộc tính bên trong JSON, SQLite buộc phải quét qua chuỗi văn bản và phân tích cú pháp (parse) lại từ đầu. Nếu bảng có $N$ dòng, CPU sẽ phải thực hiện $N$ lần thao tác parse chuỗi. Độ phức tạp tính toán trung bình cho việc phân tích cú pháp một chuỗi JSON độ dài $L$ là \(O(L)\), khiến các truy vấn quét toàn bảng trên dữ liệu JSON thô tốn rất nhiều CPU.

Để giải quyết bài toán hiệu năng này, kể từ phiên bản SQLite 3.45.0 (đầu năm 2024), định dạng JSONB đã được giới thiệu. JSONB chuyển đổi văn bản JSON thành một chuỗi nhị phân (kiểu BLOB) đã được phân tích sẵn cấu trúc cây.

Trong thực tế, bạn không khai báo kiểu cột là JSONB vì SQLite dùng hệ kiểu động (Type Affinity). Bạn chỉ cần lưu kết quả của hàm jsonb() vào một cột kiểu BLOB:

CREATE TABLE product_specs (
  product_id INTEGER PRIMARY KEY,
  specs_raw TEXT,         -- Lưu JSON thô dạng văn bản
  specs_bin BLOB          -- Lưu JSONB nhị phân bằng hàm jsonb()
);

INSERT INTO product_specs (product_id, specs_raw, specs_bin)
VALUES (
  1, 
  '{"RAM": "16GB", "CPU": "M2", "weight_kg": 1.4}', 
  jsonb('{"RAM": "16GB", "CPU": "M2", "weight_kg": 1.4}')
);

2. Truy Vấn Dữ Liệu Bán Cấu Trúc: Hàm Trích Xuất & Toán Tử -> / ->>

Hãy xem xét tình huống kinh doanh thực tế tại TechMart: Cửa hàng bán các dòng sản phẩm có thông số kỹ thuật (Specs) hoàn toàn khác nhau. Thay vì tạo hàng chục cột thưa chứa nhiều giá trị NULL (ví dụ: cột battery_life chỉ có cho Laptop, dpi chỉ có cho Mouse, frequency_response chỉ có cho Headphones), chúng ta gói tất cả các thuộc tính động này vào cột JSON specs.

Để trích xuất các thuộc tính bên trong JSON, SQLite cung cấp hàm json_extract(json, path). Đường dẫn path bắt đầu bằng ký tự $ đại diện cho gốc của đối tượng.

-- Truy vấn truyền thống bằng json_extract
SELECT product_name, 
       json_extract(specs, '$.RAM') AS ram,
       json_extract(specs, '$.weight_kg') AS weight
FROM techmart_products
WHERE json_extract(specs, '$.category_type') = 'Laptop';

Để cú pháp ngắn gọn và trực quan hơn (tương tự như PostgreSQL), kể từ phiên bản 3.38.0, SQLite hỗ trợ hai toán tử mũi tên:

• Toán tử ->: Trích xuất một đối tượng con hoặc giá trị dưới dạng chuỗi JSON (giữ nguyên dấu nháy " bọc quanh chuỗi và định dạng JSON).
• Toán tử ->>: Trích xuất một đối tượng dưới dạng giá trị thô của SQL (trả về kiểu text, integer, real thực tế, tự động gỡ bỏ dấu nháy kép bọc ngoài chuỗi).

-- Sử dụng các toán tử mũi tên hiện đại
SELECT product_name,
       specs -> '$.RAM' AS ram_json,     -- Trả về '"16GB"' (có dấu nháy)
       specs ->> '$.RAM' AS ram_raw,     -- Trả về '16GB' (chuỗi sạch)
       specs ->> '$.weight_kg' AS weight -- Trả về 1.4 dưới dạng số thực
FROM techmart_products
WHERE specs ->> '$.category_type' = 'Laptop';

WHERE specs -> '$.RAM' = '16GB'

SELECT product_name FROM techmart_products
WHERE CAST(specs ->> '$.weight_kg' AS REAL) > 1.5;

3. Làm Phẳng Dữ Liệu JSON Phức Tạp: Sử Dụng json_each và json_tree

Trong nhiều trường hợp, một trường JSON không chỉ chứa các cặp key-value đơn giản mà chứa các mảng (Arrays) hoặc các cây phân cấp lồng nhau. Tình huống kinh doanh: Bảng api_logs ghi nhận các sự kiện từ client. Cột payload chứa mảng danh sách lỗi phát sinh trong một request:

{
  "request_id": 9901,
  "status": "failed",
  "errors": [
    {"code": "ERR_STOCK", "msg": "Wireless Mouse hết hàng"},
    {"code": "ERR_PRICE", "msg": "Sai lệch giá bán tối thiểu"}
  ]
}

Làm sao để lọc ra toàn bộ các bản ghi lỗi chi tiết của từng request thành từng hàng SQL độc lập? Chúng ta sử dụng hàm json_each(json, path) như một bảng ảo và kết hợp với phép nối JOIN (thực chất là một lateral join ngầm định).

SELECT log_id,
       payload ->> '$.request_id' AS request_id,
       value ->> '$.code' AS error_code,
       value ->> '$.msg' AS error_msg
FROM api_logs, json_each(api_logs.payload, '$.errors');

Trong câu lệnh trên, hàm json_each duyệt qua mảng nằm ở đường dẫn $.errors. Với mỗi phần tử trong mảng, nó trả về một hàng ảo có cột value chứa chuỗi JSON của phần tử đó. Nhờ đó, chúng ta có thể tiếp tục dùng toán tử ->> trên cột value để bóc tách thông tin lỗi chi tiết.

Sự khác biệt giữa json_each và json_tree:

• json_each: Chỉ duyệt qua các phần tử con trực tiếp (mức độ 1) của đường dẫn được chỉ định.
• json_tree: Duyệt đệ quy theo chiều sâu qua toàn bộ các nhánh con, cháu, chắt của cây JSON. Nó trả về đầy đủ đường dẫn cụ thể (cột path) của từng phần tử xuất hiện trong cấu trúc.

-- Tìm kiếm tất cả các giá trị văn bản bất kể nó nằm ở cấp độ nào trong JSON payload
SELECT log_id, key, value, path
FROM api_logs, json_tree(api_logs.payload)
WHERE type = 'text' AND value LIKE '%hết hàng%';

4. Cập Nhật Dữ Liệu JSON: So Sánh json_set, json_insert, json_replace và json_patch

Không chỉ đọc, SQL còn cho phép chỉnh sửa nội dung bên trong tài liệu JSON mà không cần phải ghi đè toàn bộ cột bằng code ứng dụng. Tình huống kinh doanh: Lưu trữ cấu hình giao diện và tùy chọn thông báo của người dùng trong bảng user_profiles. SQLite cung cấp các hàm chuyên biệt sau:

Ví dụ minh họa sự khác biệt khi chạy cùng một cấu hình cập nhật:

-- Giả sử settings ban đầu là: '{"theme": "dark"}'

-- 1. json_set: Cập nhật theme cũ đồng thời thêm mới language
SELECT json_set('{"theme": "dark"}', '$.theme', 'light', '$.lang', 'vi');
-- Kết quả: '{"theme": "light", "lang": "vi"}'

-- 2. json_insert: theme giữ nguyên vì đã có, lang được thêm mới
SELECT json_insert('{"theme": "dark"}', '$.theme', 'light', '$.lang', 'vi');
-- Kết quả: '{"theme": "dark", "lang": "vi"}'

-- 3. json_replace: theme được cập nhật, lang bị bỏ qua vì chưa có
SELECT json_replace('{"theme": "dark"}', '$.theme', 'light', '$.lang', 'vi');
-- Kết quả: '{"theme": "light"}'

Ngoài việc cập nhật từng đường dẫn đơn lẻ, bạn có thể thực hiện trộn (merge) hai đối tượng JSON bằng hàm json_patch(json1, json2) (tuân thủ tiêu chuẩn RFC-7396). Nó sẽ ghi đè các trường của json1 bằng dữ liệu từ json2, và nếu một trường ở json2 có giá trị là null, trường đó sẽ bị xóa khỏi đối tượng kết quả.

-- Gộp cấu hình mặc định với cấu hình tùy biến của user
SELECT json_patch(
  '{"theme": "dark", "font_size": 14, "show_sidebar": true}',
  '{"theme": "light", "show_sidebar": null}' -- null nghĩa là xóa trường này
);
-- Kết quả: '{"theme": "light", "font_size": 14}'

5. Generated Columns: Cột Tự Động Tạo (Virtual vs Stored)

Một trong những phàn nàn lớn nhất khi làm việc với JSON trong cơ sở dữ liệu quan hệ là cú pháp trích xuất dữ liệu rườm rà. Mỗi khi cần lấy cân nặng sản phẩm để tính phí ship, chúng ta lại phải gõ specs ->> '$.weight_kg'.

Cơ chế Generated Columns ra đời để tạo ra các cột có giá trị được tính toán tự động dựa trên biểu thức từ các cột khác. Chúng ta có hai chế độ lưu trữ: VIRTUAL (Mặc định) và STORED.

Khai báo cột tự động trích xuất cấu hình từ JSON specs:

CREATE TABLE techmart_products (
  product_id INTEGER PRIMARY KEY,
  product_name TEXT NOT NULL,
  specs TEXT,
  -- Cột ảo: Không tốn dung lượng đĩa
  device_ram TEXT GENERATED ALWAYS AS (specs ->> '$.RAM') VIRTUAL,
  -- Cột lưu trữ: Tính sẵn khi ghi, đọc cực nhanh
  shipping_weight REAL GENERATED ALWAYS AS (CAST(specs ->> '$.weight_kg' AS REAL)) STORED
);

Với khai báo trên, lập trình viên có thể viết các câu truy vấn sạch đẹp như sau mà không cần quan tâm đến cú pháp JSON phức tạp phía sau:

SELECT product_name, device_ram, shipping_weight 
FROM techmart_products 
WHERE shipping_weight > 1.5;

6. Tối Ưu Hóa Hiệu Năng: Tạo Chỉ Mục Trên Biểu Thức (Index on Expression)

Mặc dù cột ảo (VIRTUAL Generated Column) không lưu trữ dữ liệu thật trên đĩa, SQLite vẫn cho phép chúng ta tạo chỉ mục (Index) lên trên nó.

Khi bạn tạo chỉ mục trên một cột ảo hoặc trực tiếp lên một biểu thức JSON:

CREATE INDEX idx_products_weight ON techmart_products(specs ->> '$.weight_kg');

SQLite sẽ trích xuất trước giá trị cân nặng từ JSON, xây dựng một cấu trúc cây chỉ mục B-Tree riêng và lưu trữ các khóa đó lên đĩa. Điều này mang lại một sự cải thiện hiệu năng khổng lồ:

• Trước khi có index: Hệ thống bắt buộc phải quét tuần tự toàn bảng (Full Table Scan). Độ phức tạp thời gian là \(O(N)\) phép parse và so sánh JSON.
• Sau khi có index: Hệ thống thực hiện tìm kiếm nhị phân trực tiếp trên B-Tree chỉ mục. Độ phức tạp giảm xuống mức tối ưu \(O(\log N)\).

Hãy cùng kiểm chứng sự tối ưu hóa này thông qua công cụ phân tích Query Plan dưới đây.