JSON trong SQLite: json_extract, json_set và json_each

SQLite không có kiểu JSON, và điều đó không sao cả

SQLite không có kiểu cột JSON riêng. JSON được lưu trong một cột TEXT thông thường, và một nhóm hàm có sẵn, gọi chung là extension JSON1, biết cách phân tích, truy vấn và sửa đổi nó. JSON1 có sẵn trong mọi bản build SQLite hiện đại, nên bạn không cần cài gì thêm.

Cách hình dung: lưu tài liệu dưới dạng văn bản, dùng hàm để nhìn vào bên trong.

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    data TEXT
);

INSERT INTO users (data) VALUES
    ('{"name": "Ada", "email": "ada@example.com", "tags": ["admin", "beta"]}'),
    ('{"name": "Boris", "email": "boris@example.com", "tags": ["beta"]}');

SELECT id, data FROM users;

Hai hàng, mỗi hàng chứa một tài liệu JSON trong một cột văn bản bình thường. Giờ ta cần cách để truy cập vào bên trong các tài liệu đó.

Lấy trường bằng json_extract và ->>

json_extract(column, path) lấy một giá trị ra khỏi tài liệu JSON. Đường dẫn bắt đầu bằng $ (gốc), dùng .field cho khóa của object và [i] cho chỉ số của mảng.

CREATE TABLE users (id INTEGER PRIMARY KEY, data TEXT);
INSERT INTO users (data) VALUES
    ('{"name": "Ada", "email": "ada@example.com", "tags": ["admin", "beta"]}'),
    ('{"name": "Boris", "email": "boris@example.com", "tags": ["beta"]}');

SELECT
    json_extract(data, '$.name')     AS name,
    json_extract(data, '$.tags[0]')  AS first_tag
FROM users;

Viết json_extract(data, '$.name') khắp nơi nhanh chóng trở nên nhàm chán, nên SQLite cung cấp hai toán tử:

• -> trả về giá trị đã mã hóa JSON (chuỗi trả về kèm dấu ngoặc kép).
• ->> trả về giá trị SQL (văn bản hoặc số, không có dấu ngoặc kép).

CREATE TABLE users (id INTEGER PRIMARY KEY, data TEXT);
INSERT INTO users (data) VALUES
    ('{"name": "Ada", "tags": ["admin", "beta"]}');

SELECT
    data -> '$.name'      AS name_json,
    data ->> '$.name'     AS name_text,
    data ->> '$.tags[0]'  AS first_tag
FROM users;

name_json trả về "Ada" (vẫn là JSON), còn name_text là Ada. Dùng ->> khi bạn cần một giá trị để so sánh hoặc hiển thị. Dùng -> khi bạn sẽ đưa kết quả vào một hàm JSON khác.

Lọc theo trường JSON

Khi đã lấy được giá trị, bạn có thể lọc. Biểu thức đặt trong mệnh đề WHERE như mọi biểu thức khác:

CREATE TABLE users (id INTEGER PRIMARY KEY, data TEXT);
INSERT INTO users (data) VALUES
    ('{"name": "Ada", "email": "ada@example.com", "active": 1}'),
    ('{"name": "Boris", "email": "boris@example.com", "active": 0}'),
    ('{"name": "Cleo", "email": "cleo@example.com", "active": 1}');

SELECT data ->> '$.name' AS name
FROM users
WHERE data ->> '$.active' = 1;

Cách này chạy được, nhưng với bảng có kích thước đáng kể thì sẽ chậm: mọi hàng đều phải được phân tích để tính điều kiện. Ta sẽ khắc phục bằng chỉ mục ngay sau đây.

Tạo JSON: json_object và json_array

Theo chiều ngược lại, bạn có thể tạo JSON ngay trong truy vấn:

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT);
INSERT INTO users (name, email) VALUES
    ('Ada', 'ada@example.com'),
    ('Boris', 'boris@example.com');

SELECT json_object(
    'id', id,
    'name', name,
    'email', email
) AS user_json
FROM users;

json_object('k1', v1, 'k2', v2, ...) tạo một object. json_array(v1, v2, ...) tạo một mảng. Chúng rất tiện để dựng response của API ngay trong SQL, và có thể lồng nhau thoải mái:

SELECT json_object(
    'user', json_object('name', 'Ada', 'id', 1),
    'roles', json_array('admin', 'beta')
) AS payload;

Cập nhật JSON: json_set, json_insert, json_replace

Ba hàm có liên quan chặt chẽ dùng để sửa một tài liệu JSON và trả về phiên bản mới:

• json_set(doc, path, value): đặt giá trị tại đường dẫn, tạo mới nếu chưa có, ghi đè nếu đã có.
• json_insert(doc, path, value): chỉ thêm nếu đường dẫn chưa tồn tại.
• json_replace(doc, path, value): chỉ cập nhật nếu đường dẫn đã tồn tại.

Các hàm này không sửa trực tiếp mà trả về một tài liệu mới, thường được ghi lại bằng UPDATE:

CREATE TABLE users (id INTEGER PRIMARY KEY, data TEXT);
INSERT INTO users (data) VALUES
    ('{"name": "Ada", "email": "ada@example.com"}');

UPDATE users
SET data = json_set(data, '$.email', 'ada@new.com', '$.verified', 1)
WHERE id = 1;

SELECT data FROM users;

Lưu ý json_set nhận nhiều cặp đường dẫn và giá trị trong một lần gọi. Để xóa một khóa, dùng json_remove(doc, path).

Tách mảng với json_each

json_each là một hàm trả về bảng: nó nhận một mảng (hoặc object) JSON và trả về mỗi phần tử một hàng. Nhờ vậy, việc "tìm người dùng có tag admin", vốn khá vất vả với SQL thuần, trở thành một phép join bình thường:

CREATE TABLE users (id INTEGER PRIMARY KEY, data TEXT);
INSERT INTO users (data) VALUES
    ('{"name": "Ada", "tags": ["admin", "beta"]}'),
    ('{"name": "Boris", "tags": ["beta"]}'),
    ('{"name": "Cleo", "tags": ["admin"]}');

SELECT u.data ->> '$.name' AS name, t.value AS tag
FROM users u, json_each(u.data, '$.tags') t
WHERE t.value = 'admin';

Mỗi hàng của users được join với các phần tử trong mảng tags của nó. json_each cung cấp các cột hữu ích như key, value, type và fullkey. Hàm anh em json_tree duyệt đệ quy toàn bộ tài liệu, bao gồm mọi nút lồng nhau, rất tiện khi tìm kiếm trong những tài liệu chưa rõ cấu trúc.

Tạo chỉ mục cho trường JSON

Truy vấn WHERE data ->> '$.active' = 1 ở trên chạy được, nhưng SQLite phải phân tích từng hàng để tính điều kiện. Với các trường hay được truy vấn, hãy tạo chỉ mục biểu thức:

CREATE TABLE users (id INTEGER PRIMARY KEY, data TEXT);

CREATE INDEX idx_users_email
    ON users(json_extract(data, '$.email'));

INSERT INTO users (data) VALUES
    ('{"email": "ada@example.com"}'),
    ('{"email": "boris@example.com"}');

EXPLAIN QUERY PLAN
SELECT * FROM users WHERE json_extract(data, '$.email') = 'ada@example.com';

Chỉ mục phải dùng đúng biểu thức giống hệt truy vấn. Dùng json_extract(data, '$.email') trong chỉ mục nhưng data ->> '$.email' trong truy vấn sẽ không khớp, và chỉ mục sẽ nằm đó không được dùng: hãy chọn một cách viết và giữ nguyên.

Với những trường được truy vấn liên tục, một generated column dễ đọc hơn:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    data TEXT,
    email TEXT GENERATED ALWAYS AS (data ->> '$.email') VIRTUAL
);

CREATE INDEX idx_users_email ON users(email);

INSERT INTO users (data) VALUES ('{"email": "ada@example.com"}');

SELECT email FROM users WHERE email = 'ada@example.com';

Với người viết truy vấn, email trông như một cột bình thường, nhưng nó luôn tự đồng bộ với JSON.

Kiểm tra JSON hợp lệ

json_valid(text) trả về 1 nếu văn bản là JSON hợp lệ, ngược lại trả về 0. Kết hợp nó với ràng buộc CHECK để từ chối dữ liệu sai ngay lúc ghi:

CREATE TABLE events (
    id INTEGER PRIMARY KEY,
    payload TEXT CHECK (json_valid(payload))
);

INSERT INTO events (payload) VALUES ('{"type": "click"}');
INSERT INTO events (payload) VALUES ('not json');

Lệnh insert đầu tiên thành công; lệnh thứ hai thất bại với lỗi ràng buộc. Nếu không có kiểm tra này, JSON lỗi sẽ nằm im trong bảng cho đến khi một lệnh gọi json_extract nào đó báo lỗi vài tháng sau.

JSON và JSONB

Từ SQLite 3.45 có một dạng biểu diễn nhị phân gọi là JSONB: cùng dữ liệu đó, nhưng đã được phân tích sẵn thành dạng nhị phân gọn gàng để các hàm không phải phân tích lại ở mỗi lần gọi. Nhóm hàm jsonb_* (jsonb_extract, jsonb_set, jsonb_object, ...) trả về JSONB thay vì văn bản, và cột JSONB có thể được truy vấn bằng cùng các toán tử.

CREATE TABLE docs (id INTEGER PRIMARY KEY, body BLOB);

INSERT INTO docs (body) VALUES
    (jsonb('{"name": "Ada", "score": 99}'));

SELECT body ->> '$.name' AS name FROM docs;

Dùng JSON thường (văn bản) khi bạn muốn tài liệu dễ đọc trong các bản dump và dễ kiểm tra. Chọn JSONB khi bảng lớn, được truy vấn thường xuyên, và chi phí phân tích thật sự hiện rõ khi đo hiệu năng. Đừng chuyển sang theo mặc định: khả năng đọc được của JSON thường rất đáng giá khi gỡ lỗi.

Khi nào nên dùng JSON

Cột JSON phát huy tác dụng khi:

• Cấu trúc thay đổi giữa các hàng (ví dụ payload của sự kiện, nhật ký audit, webhook tích hợp).
• Bạn đang lưu đệm response từ một API bên ngoài và muốn giữ nguyên nó.
• Một trường hiếm khi được truy vấn và gần như không bao giờ dùng để lọc.

Chúng không phù hợp khi:

• Bạn dùng JSON để né việc thiết kế schema. Nếu hàng nào cũng có cùng các trường, đó nên là các cột.
• Bạn cần lọc hoặc join theo một giá trị thường xuyên. Một cột thật có chỉ mục sẽ luôn nhanh hơn việc tra đường dẫn JSON.
• Bạn cần đến khóa ngoại. JSON không có tính toàn vẹn quan hệ.

Điểm cân bằng tốt nhất là kết hợp cả hai: cột vô hướng cho các trường quyết định truy vấn và ràng buộc, kèm một cột JSON cho phần dữ liệu đa dạng còn lại.

Tiếp theo: tìm kiếm toàn văn

JSON mang lại sự linh hoạt khi lưu trữ. Trang tiếp theo nói về FTS5, engine tìm kiếm toàn văn của SQLite, giúp bạn tìm kiếm văn bản đúng nghĩa với xếp hạng và tô sáng kết quả, vượt xa những gì LIKE làm được.