PostgreSQL 3 — JSON/JSONB & truy vấn bán cấu trúc

PostgreSQL: vừa quan hệ, vừa document store

Một trong những lý do PostgreSQL được yêu thích là nó không bắt bạn phải chọn giữa "cơ sở dữ liệu quan hệ" và "kho tài liệu (document store)". Bạn có thể lưu một dòng với vài cột chuẩn chỉnh (id, created_at, user_id) và một cột JSON chứa nguyên khối dữ liệu bán cấu trúc — rồi truy vấn cả hai bằng chung một câu SQL, trong chung một transaction, dưới chung một ràng buộc toàn vẹn. Đây là điểm khiến nhiều đội ngũ chọn PostgreSQL thay vì phải vận hành song song thêm một MongoDB riêng.

Nhưng "lưu được JSON" và "dùng JSON đúng cách" là hai chuyện khác nhau. Bài này đi sâu vào cơ chế lưu trữ, bộ toán tử truy cập, ngôn ngữ jsonpath, các hàm dựng và biến đổi, cách đánh index GIN cho truy vấn nhanh, và — quan trọng nhất — ranh giới giữa "khi nào JSONB là lựa chọn đúng" và "khi nào JSONB là cái bẫy".

Nếu bạn chưa vững về các kiểu dữ liệu cơ bản của PostgreSQL, nên đọc trước Kiểu dữ liệu. JSON/JSONB đơn giản là hai kiểu dữ liệu nữa trong bộ sưu tập đó, chỉ là chúng có cả một hệ sinh thái toán tử riêng.

json vs jsonb: khác biệt cốt lõi

PostgreSQL cung cấp hai kiểu để lưu JSON, và việc chọn đúng ngay từ đầu quan trọng hơn bạn nghĩ.

json lưu nguyên văn bản JSON. Khi bạn ghi vào, PostgreSQL chỉ kiểm tra rằng chuỗi hợp lệ về cú pháp rồi lưu y nguyên byte-for-byte. Hệ quả:

• Giữ nguyên khoảng trắng, thứ tự khoá, và cả khoá trùng lặp ({"a":1,"a":2} được giữ nguyên cả hai).
• Ghi nhanh (chỉ validate cú pháp).
• Nhưng mỗi lần truy vấn một trường bên trong, PostgreSQL phải phân giải lại (re-parse) toàn bộ chuỗi. Rất chậm khi truy cập lặp lại.
• Không index được theo kiểu cấu trúc.

jsonb lưu dạng nhị phân đã phân giải (decomposed binary). Khi ghi, PostgreSQL parse JSON thành cây và lưu ở định dạng nội bộ. Hệ quả:

• Ghi hơi chậm hơn json một chút (phải parse + chuyển đổi).
• Truy vấn nhanh vì không phải parse lại — chỉ đọc trực tiếp.
• Index được bằng GIN, hỗ trợ toán tử containment và existence.
• Mất định dạng gốc: khoảng trắng bị bỏ, thứ tự khoá không được đảm bảo (thường sắp theo độ dài rồi theo tên), và khoá trùng bị loại bỏ (chỉ giữ giá trị cuối).

Quy tắc ngón tay cái: mặc định dùng jsonb. Chỉ dùng json khi bạn thực sự cần bảo toàn văn bản gốc chính xác (ví dụ lưu bản gốc để ký số, audit đối chiếu byte, hoặc cần giữ thứ tự/khoá trùng vì lý do pháp lý).

▶ Chạy được trong SQL Builder

SELECT '{"a": 1,   "b":2, "a":3}'::jsonb;

Kết quả trả về {"a": 3, "b": 2} — khoảng trắng đã biến mất và khoá a trùng chỉ còn giá trị cuối. Đổi ::jsonb thành ::json để thấy chuỗi được giữ nguyên vẹn.

Bộ toán tử truy cập

Đây là phần bạn dùng hằng ngày. Bảng dưới tổng hợp các toán tử quan trọng nhất:

Điểm hay gây nhầm nhất là -> vs ->>. -> giữ giá trị ở dạng jsonb (để bạn còn truy cập tiếp), còn ->> "kết thúc" bằng cách trả về text. Vì thế chuỗi -> có thể nối tiếp nhau, và phần tử cuối cùng thường dùng ->> nếu bạn muốn giá trị nguyên thuỷ để so sánh hoặc hiển thị.

▶ Chạy được trong SQL Builder

SELECT '{"a":1,"b":{"c":2}}'::jsonb -> 'b' ->> 'c';

Ở đây -> 'b' trả về jsonb {"c":2}, rồi ->> 'c' trả về text '2'. Cùng kết quả nếu dùng đường dẫn:

▶ Chạy được trong SQL Builder

SELECT '{"a":1,"b":{"c":2}}'::jsonb #>> '{b,c}';

Với mảng, -> và ->> nhận chỉ số nguyên (bắt đầu từ 0, số âm đếm từ cuối):

▶ Chạy được trong SQL Builder

SELECT '{"tags":["x","y","z"]}'::jsonb -> 'tags' ->> -1;

Trả về 'z' — phần tử cuối cùng của mảng.

Containment và existence

@> là toán tử có sức nặng nhất trong thực tế: nó hỏi "cấu trúc JSON bên trái có bao gồm toàn bộ cấu trúc bên phải không?". Đây là cách bạn lọc theo nội dung JSON, và nó là toán tử được GIN index tăng tốc.

▶ Chạy được trong SQL Builder

SELECT '{"tags":["x","y"]}'::jsonb @> '{"tags":["x"]}';

Trả về true: object bên trái chứa khoá tags là một mảng, và mảng đó chứa phần tử "x". Lưu ý ngữ nghĩa của containment với mảng là "chứa các phần tử", không cần đúng thứ tự hay đủ số lượng.

Còn ? chỉ kiểm tra tồn tại khoá ở cấp cao nhất (top-level), không đi sâu vào lồng nhau:

▶ Chạy được trong SQL Builder

SELECT '{"a":1}'::jsonb ? 'a';

Trả về true. Với mảng chuỗi, ? kiểm tra tồn tại phần tử text: '["x","y"]'::jsonb ? 'x' cũng cho true.

jsonpath: truy vấn có điều kiện bên trong JSON

Từ PostgreSQL 12, chuẩn SQL/JSON path (jsonpath) cho phép viết biểu thức truy vấn phức tạp bên trong một giá trị JSON — kiểu như XPath nhưng cho JSON. Cú pháp cốt lõi:

• $ — gốc của tài liệu.
• .key — truy cập khoá; [*] — mọi phần tử mảng; [0] — phần tử theo chỉ số.
• ? (điều kiện) — filter, @ bên trong filter tham chiếu phần tử hiện tại.

Hàm hay dùng: jsonb_path_query (trả về tập các giá trị khớp), jsonb_path_query_first (giá trị đầu tiên), jsonb_path_exists (boolean).

▶ Chạy được trong SQL Builder

SELECT jsonb_path_query(
  '{"items":[{"price":50},{"price":150}]}',
  '$.items[*].price ? (@ > 100)'
);

Biểu thức lấy mọi price trong items rồi lọc những giá trị > 100 — kết quả là 150. So với việc phải bung mảng ra bằng jsonb_array_elements rồi lọc, jsonpath gọn hơn nhiều cho các điều kiện lồng nhau.

▶ Chạy được trong SQL Builder

SELECT jsonb_path_exists(
  '{"user":{"roles":["admin","editor"]}}',
  '$.user.roles[*] ? (@ == "admin")'
);

Trả về true — có ít nhất một role bằng "admin". Toán tử @? và @@ (ở bảng trên) là dạng viết tắt của các phép jsonpath này, và cả hai đều tận dụng được GIN index với jsonb_ops.

Hàm dựng và biến đổi JSONB

Dựng JSON từ dữ liệu quan hệ

Rất thường xuyên bạn cần tạo JSON từ các cột — ví dụ trả về API. Các hàm chính:

• jsonb_build_object(k1, v1, k2, v2, ...) — dựng object từ cặp khoá-giá trị.
• jsonb_build_array(v1, v2, ...) — dựng mảng.
• to_jsonb(anyelement) — chuyển một giá trị (kể cả một row/record) thành jsonb.
• jsonb_agg(expr) — hàm tổng hợp, gom nhiều dòng thành một mảng jsonb.
• jsonb_object_agg(k, v) — gom thành object.

▶ Chạy được trong SQL Builder

SELECT jsonb_build_object('id', 1, 'name', 'An', 'active', true);

Trả về {"id": 1, "name": "An", "active": true}. Sức mạnh thật sự lộ ra khi kết hợp với jsonb_agg trong một truy vấn có GROUP BY — bạn có thể trả về một cấu trúc lồng (nested) hoàn chỉnh trong đúng một câu SQL, thay vì ghép ở tầng ứng dụng.

Biến đổi và cập nhật

• jsonb_set(target, path, new_value[, create_if_missing]) — thay giá trị tại một đường dẫn.
• jsonb_insert(target, path, new_value[, insert_after]) — chèn giá trị mới (không ghi đè).
• jsonb_strip_nulls(jsonb) — bỏ mọi khoá có giá trị null.
• Toán tử - — xoá một khoá (hoặc phần tử mảng): data - 'key'.
• Toán tử || — gộp (merge/concat) hai jsonb: a || b (khoá của b ghi đè a).
• #- — xoá theo đường dẫn: data #- '{user,temp}'.

▶ Chạy được trong SQL Builder

SELECT jsonb_set('{"a":1,"b":2}'::jsonb, '{b}', '99') || '{"c":3}'::jsonb;

Kết quả {"a": 1, "b": 99, "c": 3}: jsonb_set sửa b thành 99, rồi || gộp thêm khoá c. Lưu ý các hàm này không sửa tại chỗ — chúng trả về một giá trị jsonb mới, nên trong UPDATE bạn viết SET data = jsonb_set(data, ...).

Bung JSON thành dòng (set-returning)

Ngược với jsonb_agg, đôi khi bạn cần "trải" JSON ra thành nhiều dòng để join hoặc lọc theo kiểu quan hệ:

• jsonb_array_elements(jsonb) — mỗi phần tử mảng thành một dòng (jsonb).
• jsonb_array_elements_text(jsonb) — như trên nhưng trả text.
• jsonb_each(jsonb) / jsonb_each_text(jsonb) — trải object thành các cặp (key, value).
• jsonb_object_keys(jsonb) — liệt kê các khoá.

▶ Chạy được trong SQL Builder

SELECT jsonb_array_elements('[1,2,3]'::jsonb);

Trả về 3 dòng: 1, 2, 3. Trong truy vấn thật, các hàm này thường xuất hiện trong FROM ... CROSS JOIN LATERAL jsonb_array_elements(t.data -> 'items') AS elem, biến JSON lồng thành bảng ảo để xử lý tiếp bằng SQL thông thường — chủ đề này nối tiếp trong SQL nâng cao.

▶ Chạy được trong SQL Builder

SELECT jsonb_object_keys('{"id":1,"name":"An","email":"[email protected]"}'::jsonb);

Liệt kê các khoá cấp cao nhất: id, name, email (mỗi khoá một dòng).

Index GIN cho JSONB

Không có index, mọi truy vấn @> hay ? đều phải quét tuần tự và giải nén jsonb từng dòng. Với bảng lớn, đây là điểm chết. PostgreSQL giải quyết bằng GIN (Generalized Inverted Index).

-- (Minh hoạ) GIN mặc định: opclass jsonb_ops
CREATE INDEX idx_events_data ON events USING gin (data);

-- (Minh hoạ) opclass nhỏ hơn, nhanh hơn — chỉ hỗ trợ @>
CREATE INDEX idx_events_data_pathops ON events USING gin (data jsonb_path_ops);

Có hai opclass cho GIN trên jsonb:

• jsonb_ops (mặc định): index cả khoá lẫn giá trị. Hỗ trợ @>, ?, ?|, ?&, và các phép jsonpath @? / @@. Index lớn hơn nhưng linh hoạt.
• jsonb_path_ops: chỉ index các cặp đường dẫn→giá trị dạng hash. Nhỏ hơn và thường nhanh hơn cho @>, nhưng không hỗ trợ các toán tử tồn tại khoá (?, ?|, ?&). Chọn cái này nếu bạn chỉ dùng containment.

Nếu bạn luôn lọc theo một trường cụ thể (ví dụ data->>'status' = 'paid'), thì GIN toàn cột là quá nặng. Tốt hơn là index biểu thức trích — một B-tree thông thường trên biểu thức:

-- (Minh hoạ) Index B-tree trên một trường trích xuất
CREATE INDEX idx_events_status ON events ((data ->> 'status'));

Index này nhỏ, hỗ trợ cả so sánh khoảng (>, <, BETWEEN) và ORDER BY — điều mà GIN không làm được. Quy tắc: GIN cho truy vấn "chứa gì đó" linh hoạt; index biểu thức cho truy vấn cố định theo một trường.

Use case thực tế: lưu event/audit dạng JSONB

Hãy xét một hệ thống ghi audit log hoặc event stream — tình huống JSONB toả sáng. Mỗi sự kiện có vài trường chung (thời điểm, actor, loại sự kiện) và một khối payload khác nhau tuỳ loại:

-- (Minh hoạ) Bảng lai: cột quan hệ + 1 cột jsonb payload
CREATE TABLE audit_events (
    id          bigserial PRIMARY KEY,
    occurred_at timestamptz NOT NULL DEFAULT now(),
    actor_id    bigint,
    event_type  text NOT NULL,
    payload     jsonb NOT NULL
);

CREATE INDEX idx_audit_payload ON audit_events USING gin (payload jsonb_path_ops);
CREATE INDEX idx_audit_type_time ON audit_events (event_type, occurred_at DESC);

Đây là mẫu lai (hybrid) đáng nhớ: những gì bạn luôn lọc/join/sắp xếp (event_type, occurred_at, actor_id) là cột quan hệ thật; những gì đa dạng và tra cứu thưa thớt (payload) là một cột jsonb duy nhất, được GIN index để lọc theo nội dung.

Với thiết kế trên, câu truy vấn "tìm mọi sự kiện có payload chứa country = VN và amount xuất hiện" trở thành:

-- (Minh hoạ) Truy vấn tận dụng GIN index qua @>
SELECT id, occurred_at, event_type
FROM audit_events
WHERE payload @> '{"country":"VN"}'
  AND event_type = 'payment'
  AND occurred_at >= now() - interval '7 days';

Điều kiện payload @> '{"country":"VN"}' được GIN index tăng tốc, event_type và occurred_at được B-tree tăng tốc. Bạn có được sự linh hoạt của document store cho payload, đồng thời vẫn giữ hiệu năng và ràng buộc quan hệ ở phần "xương sống".

Sơ đồ tư duy khi thiết kế:

Khi nào JSONB, khi nào cột quan hệ?

Nghiêng về JSONB khi:

• Dữ liệu thưa (sparse): mỗi bản ghi chỉ có một tập nhỏ trong rất nhiều trường khả dĩ, tạo cột riêng sẽ toàn NULL.
• Schema đa dạng theo loại: mỗi event_type có payload khác nhau.
• Schema thay đổi thường xuyên trong giai đoạn phát triển nhanh, không muốn ALTER TABLE liên tục.
• Cần lưu nguyên payload thô từ API/đối tác để truy vết.

Nghiêng về cột quan hệ khi:

• Trường được truy vấn/lọc/sắp xếp/join thường xuyên — cột quan hệ + B-tree luôn nhanh và gọn hơn.
• Cần ràng buộc toàn vẹn: NOT NULL, CHECK, FOREIGN KEY, UNIQUE. JSONB gần như không cho bạn các bảo đảm này ở cấp trường.
• Cần kiểu dữ liệu mạnh: một số trong JSON chỉ là numeric, không phân biệt int/bigint, không có date thật (chỉ là chuỗi).

Cảnh báo lạm dụng JSONB — những cái giá dễ bị bỏ quên:

1. Mất ràng buộc. Không có gì ngăn payload lưu "amount": "một trăm". Lỗi dữ liệu chỉ lộ ra khi truy vấn hỏng.
2. Khó tối ưu. Planner ước lượng số dòng (statistics) trên biểu thức JSON kém hơn nhiều so với cột thật; kế hoạch truy vấn dễ đi chệch. Xem EXPLAIN để hiểu vì sao thống kê quan trọng.
3. JSONB không phải nơi chứa quan hệ. Nếu bạn thấy mình join giữa các phần tử trong hai mảng JSON của hai bảng khác nhau, đó là dấu hiệu dữ liệu đáng lẽ phải được chuẩn hoá thành bảng.
4. Toàn vẹn tham chiếu (foreign key) không hoạt động bên trong JSON. Một product_id nằm trong payload sẽ không được PostgreSQL kiểm tra.

Ngoài ra, có thể mở rộng khả năng của JSONB bằng các extension (ví dụ tìm kiếm gần đúng, chỉ mục đặc thù) — xem Extension.

Ghi nhớ

• Mặc định dùng jsonb, không dùng json, trừ khi bắt buộc phải bảo toàn văn bản gốc byte-for-byte.
• -> trả jsonb (nối tiếp được), ->> trả text (giá trị cuối cùng); #> / #>> dùng đường dẫn.
• @> (containment) là toán tử lọc quan trọng nhất và được GIN index tăng tốc; ? chỉ kiểm tra tồn tại khoá cấp cao nhất.
• jsonpath (jsonb_path_query, jsonb_path_exists) cho phép lọc lồng nhau với ? (@ > 100).
• Chọn opclass: jsonb_ops (mặc định, hỗ trợ @> và ?) hay jsonb_path_ops (nhỏ/nhanh hơn, chỉ @>). Với truy vấn cố định một trường, dùng index biểu thức ((data->>'k')).
• Mẫu thiết kế đáng nhớ: cột quan hệ cho xương sống + một cột jsonb cho metadata/payload linh hoạt.
• JSONB đổi lấy sự linh hoạt bằng việc mất ràng buộc, kiểu mạnh và độ chính xác thống kê. Đừng nhét vào JSONB những gì xứng đáng là cột quan hệ.

Bài liên quan: Kiểu dữ liệu · SQL nâng cao · Extension