SQL 12 — Làm việc với JSON trong SQL

Vì sao cần JSON trong một cơ sở dữ liệu quan hệ?

Mô hình quan hệ rất mạnh khi dữ liệu có cấu trúc cố định: mỗi khách hàng có một họ tên, một thành phố, một ngày tạo. Bạn khai báo cột một lần, đặt kiểu dữ liệu, ràng buộc, rồi cứ thế dùng. Nhưng thực tế không phải dữ liệu nào cũng "vuông vắn" như vậy. Có ba tình huống mà mô hình cột cứng nhắc bắt đầu gây khó chịu:

1. Dữ liệu bán cấu trúc (semi-structured). Bạn lưu một sự kiện (event) từ ứng dụng di động. Mỗi loại sự kiện có một tập thuộc tính khác nhau: sự kiện "thêm vào giỏ" có product_id và quantity, sự kiện "đăng nhập" có device và ip. Tạo một cột riêng cho từng thuộc tính của từng loại sự kiện sẽ tạo ra một bảng đầy cột NULL.

2. Schema linh hoạt / thay đổi nhanh. Khi sản phẩm còn đang dò đường, các trường metadata thay đổi liên tục. Mỗi lần thêm trường lại phải ALTER TABLE trên một bảng triệu dòng là tốn kém và rủi ro.

3. Lưu nguyên payload từ bên ngoài. Bạn nhận một response JSON từ API đối tác (cổng thanh toán, ngân hàng) và muốn lưu lại y nguyên để truy vết, đồng thời thỉnh thoảng truy vấn một vài trường bên trong.

PostgreSQL giải quyết những trường hợp này bằng cách cho phép một cột chứa một tài liệu JSON — và quan trọng hơn, cho phép bạn truy vấn vào bên trong tài liệu đó như thể nó là dữ liệu có cấu trúc. Đây là điểm khác biệt lớn so với việc nhét JSON vào một cột TEXT: với TEXT, JSON chỉ là một chuỗi vô nghĩa; với kiểu JSON, cơ sở dữ liệu hiểu cấu trúc và cung cấp toán tử, hàm, index riêng.

Cảnh báo sớm (sẽ nói kỹ ở cuối): JSON không phải là cái cớ để bỏ chuẩn hoá. Lạm dụng JSON là một trong những lỗi thiết kế phổ biến nhất.

JSON vs JSONB: chọn cái nào?

PostgreSQL có hai kiểu dữ liệu: json và jsonb. Nhìn giống nhau khi viết, nhưng cách lưu trữ và hiệu năng khác hẳn.

Quy tắc thực dụng: gần như luôn dùng jsonb. Bạn chỉ chọn json khi cần lưu nguyên xi văn bản đầu vào (ví dụ kiểm toán pháp lý cần giữ chính xác chuỗi gốc kể cả khoảng trắng) và hầu như không truy vấn vào trong. Trong mọi tình huống phân tích, lọc, đánh index — jsonb thắng tuyệt đối.

Một minh hoạ về sự khác biệt khi parse:

SELECT '{"b":1, "a":2, "a":3}'::json   AS as_json,
       '{"b":1, "a":2, "a":3}'::jsonb  AS as_jsonb;
-- as_json  : {"b":1, "a":2, "a":3}   (giữ nguyên, kể cả khoá a trùng)
-- as_jsonb : {"a": 3, "b": 1}          (sắp xếp, bỏ a:2, giữ a:3)

▶ Chạy được trong SQL Builder (chỉ SELECT, không phụ thuộc bảng).

Toán tử truy cập: ->, ->>, #>, #>>

Đây là phần "ngữ pháp" bạn dùng hằng ngày. Có hai trục cần nhớ:

• Một dấu mũi tên (->, #>) trả về JSON (vẫn là kiểu jsonb).
• Hai dấu mũi tên (->>, #>>) trả về text (chuỗi thường).
• Mũi tên đơn (->, ->>) đi xuống một cấp.
• Mũi tên có thăng (#>, #>>) đi xuống theo một đường dẫn nhiều cấp (path).

Vì sao phân biệt jsonb và text lại quan trọng? Vì bạn không thể so sánh trực tiếp một jsonb với một chuỗi thường, cũng như khi muốn ép kiểu số bạn phải đi qua text trước:

-- ĐÚNG: lấy ra text rồi ép kiểu
WHERE (data ->> 'age')::int > 30

-- SAI thường gặp: (data -> 'age') vẫn là jsonb, so sánh với 30 sẽ lỗi hoặc sai logic

Truy cập phần tử mảng dùng chỉ số (bắt đầu từ 0), số âm đếm từ cuối:

SELECT '["a","b","c"]'::jsonb -> 0    AS phan_tu_dau,   -- "a"
       '["a","b","c"]'::jsonb ->> -1  AS phan_tu_cuoi;   -- c (text)

▶ Chạy được trong SQL Builder.

Kiểm tra "chứa" và "tồn tại khoá": @>, ?

Khi cột là jsonb, bạn có những toán tử tập hợp rất tiện cho việc lọc.

• @> (chứa): trái có chứa toàn bộ phải hay không.
• <@ (bị chứa): hướng ngược lại.
• ? (tồn tại khoá): jsonb có khoá ở cấp trên cùng không.
• ?| (tồn tại bất kỳ khoá nào trong mảng), ?& (tồn tại tất cả khoá).

-- (minh hoạ, không chạy trong sandbox vì sandbox không có cột jsonb)
-- Tìm các đơn hàng có trạng thái 'paid' nằm trong cột metadata jsonb
SELECT id
FROM orders
WHERE metadata @> '{"status":"paid"}';

-- Tìm những bản ghi có khoá 'coupon'
SELECT id FROM orders WHERE metadata ? 'coupon';

-- Có ít nhất một trong hai khoá
SELECT id FROM orders WHERE metadata ?| array['coupon','gift'];

Điểm cốt lõi: @> so khớp theo cấu trúc lồng nhau, không phải so chuỗi. metadata @> '{"a":{"b":1}}' đúng khi metadata chứa đúng cây con đó ở bất kỳ đâu phù hợp. Đây cũng chính là toán tử mà index GIN tăng tốc — sẽ nói ở phần dưới.

Các hàm dựng và biến đổi JSON

PostgreSQL có một bộ hàm phong phú. Dưới đây là những hàm bạn sẽ dùng nhiều nhất, kèm ví dụ chạy được trên schema sandbox.

to_jsonb và row_to_json: biến dữ liệu quan hệ thành JSON

SELECT to_jsonb(c) AS khach_hang_json
FROM customers c;

▶ Chạy được trong SQL Builder. Mỗi dòng customers được gói thành một object JSON với khoá là tên cột.

row_to_json tương tự nhưng trả về kiểu json (không phải jsonb):

SELECT row_to_json(c) AS khach_hang_json
FROM customers c;

▶ Chạy được trong SQL Builder.

jsonb_build_object: dựng object với khoá tự chọn

Khi bạn muốn kiểm soát đúng khoá nào xuất hiện thay vì gói cả dòng:

SELECT jsonb_build_object(
         'so_tk', account_no,
         'so_du', balance,
         'tien_te', currency
       ) AS tai_khoan
FROM accounts;

▶ Chạy được trong SQL Builder. Cú pháp là cặp 'khoá', giá_trị, 'khoá', giá_trị, ....

jsonb_agg và json_agg: gom nhiều dòng thành một mảng JSON

Đây là hàm tổng hợp (aggregate), thường đi với GROUP BY. Rất hữu ích để "gấp" quan hệ một-nhiều thành một tài liệu.

SELECT json_agg(row_to_json(c)) AS tat_ca_khach_hang
FROM customers c;

▶ Chạy được trong SQL Builder. Toàn bộ bảng customers trở thành một mảng JSON.

Kết hợp với GROUP BY để gom giao dịch theo tài khoản:

SELECT account_id,
       jsonb_agg(
         jsonb_build_object('so_tien', amount, 'loai', kind)
         ORDER BY created_at
       ) AS danh_sach_giao_dich
FROM transactions
GROUP BY account_id;

▶ Chạy được trong SQL Builder. Lưu ý có thể đặt ORDER BY ngay trong hàm tổng hợp để mảng có thứ tự ổn định.

jsonb_set: cập nhật một field bên trong

-- (minh hoạ, không chạy trong sandbox)
UPDATE orders
SET metadata = jsonb_set(metadata, '{status}', '"shipped"', true)
WHERE id = 10;

Tham số: jsonb_set(target, path, new_value, create_if_missing). path là mảng text như '{status}' hay '{addr,city}'. Tham số cuối true cho phép tạo khoá nếu chưa có.

jsonb_array_elements: bung mảng JSON thành nhiều dòng

Đây là hàm sinh tập (set-returning), biến một mảng JSON thành nhiều dòng để bạn join hoặc tổng hợp tiếp:

SELECT elem ->> 'name' AS ten_san_pham,
       (elem ->> 'qty')::int AS so_luong
FROM (SELECT '[{"name":"Sữa","qty":2},{"name":"Bánh","qty":5}]'::jsonb AS items) t,
     jsonb_array_elements(t.items) AS elem;

▶ Chạy được trong SQL Builder (mảng JSON viết tay, không phụ thuộc bảng). Mỗi phần tử của mảng trở thành một dòng riêng.

Sơ đồ luồng: từ quan hệ sang JSON và ngược lại

Vòng lặp này tóm tắt cả chương: bạn có thể gói dữ liệu quan hệ thành JSON để truyền đi, rồi bung JSON ra để xử lý quan hệ. Mũi tên đứt nét nhắc rằng khi một trường JSON đã ổn định và hay được truy vấn, nên cân nhắc đưa nó trở lại thành cột thật.

Index GIN cho JSONB

Truy vấn metadata @> '{"status":"paid"}' trên bảng triệu dòng mà không có index sẽ quét toàn bảng (sequential scan). Index GIN (Generalized Inverted Index) được thiết kế đúng cho dữ liệu "nhiều giá trị trong một ô" như jsonb.

-- (minh hoạ, không chạy trong sandbox)
-- GIN mặc định: hỗ trợ @>, ?, ?|, ?&
CREATE INDEX idx_orders_meta ON orders USING gin (metadata);

-- GIN với toán tử lớp jsonb_path_ops: nhỏ và nhanh hơn cho @>,
-- nhưng KHÔNG hỗ trợ ? (kiểm tra tồn tại khoá)
CREATE INDEX idx_orders_meta_path ON orders USING gin (metadata jsonb_path_ops);

Hai lựa chọn:

• jsonb_ops (mặc định): đánh index cả khoá lẫn giá trị, hỗ trợ đầy đủ @>, ?, ?|, ?&. Index lớn hơn.
• jsonb_path_ops: chỉ tối ưu cho @> (và truy vấn theo path). Index nhỏ hơn, truy vấn @> nhanh hơn, nhưng không hỗ trợ toán tử ?.

Nếu bạn chỉ truy vấn một trường cụ thể thường xuyên (ví dụ luôn lọc theo status), một index biểu thức (expression index) trên đúng trường đó thường gọn và hiệu quả hơn cả GIN:

-- (minh hoạ, không chạy trong sandbox)
CREATE INDEX idx_orders_status ON orders ((metadata ->> 'status'));
-- rồi truy vấn: WHERE metadata ->> 'status' = 'paid';

Đây cũng là dấu hiệu rõ ràng: nếu bạn phải tạo index biểu thức cho một trường JSON, có lẽ trường đó nên là một cột thật.

Đánh đổi: khi nào dùng JSON, khi nào nên chuẩn hoá

Đây là phần quan trọng nhất của bài. JSON trong PostgreSQL rất mạnh, nhưng sức mạnh đó dễ bị lạm dụng thành nợ kỹ thuật.

Nên dùng JSON khi

• Dữ liệu thực sự bán cấu trúc, hình dạng thay đổi theo từng dòng. Ví dụ payload sự kiện đa dạng, cấu hình tuỳ biến theo người dùng.
• Bạn lưu nguyên payload bên ngoài để truy vết, chỉ thỉnh thoảng đọc vài trường.
• Schema còn đang thay đổi nhanh, chưa muốn cam kết cấu trúc cứng.
• Quan hệ một-nhiều nhỏ, luôn đọc cùng cha và không cần truy vấn độc lập (ví dụ vài thẻ tag của một bài viết) — gói vào JSON có thể đơn giản hơn một bảng phụ.

Nên chuẩn hoá thành cột/bảng khi

• Trường được lọc, join, hoặc tổng hợp thường xuyên. Cột thật + index B-tree gần như luôn nhanh và rẻ hơn truy vấn vào JSON.
• Cần ràng buộc dữ liệu: NOT NULL, CHECK, khoá ngoại (foreign key), UNIQUE. JSON gần như không cho bạn những bảo đảm này một cách tự nhiên.
• Cần kiểu dữ liệu mạnh: số, ngày, tiền tệ. Trong JSON mọi thứ thường về text rồi phải ép kiểu, dễ lỗi và chậm.
• Quan hệ một-nhiều lớn, cần truy vấn độc lập. Giao dịch của một tài khoản phải là bảng transactions riêng (như sandbox), không phải một mảng JSON khổng lồ trong accounts.

Những "mùi xấu" (anti-pattern) cần tránh

• EAV trá hình: dồn mọi thứ vào một cột data jsonb và xem bảng như một kho key-value. Bạn mất hết lợi ích của mô hình quan hệ.
• Cập nhật từng field JSON liên tục: mỗi lần jsonb_set thường ghi lại toàn bộ tài liệu (do MVCC), tốn kém nếu tài liệu lớn và cập nhật dày.
• JSON lồng quá sâu: truy vấn #>>'{a,b,c,d}' khó đọc, khó index, khó bảo trì.

Một nguyên tắc gọn để nhớ: Cấu trúc bạn biết trước và hay hỏi tới → cột. Cấu trúc bạn không kiểm soát hoặc hiếm hỏi tới → JSON.

Tóm tắt

• JSON trong CSDL quan hệ giải quyết dữ liệu bán cấu trúc và schema linh hoạt, nhưng không thay thế chuẩn hoá.
• Dùng jsonb trong gần như mọi trường hợp; chỉ dùng json khi cần giữ nguyên văn bản gốc và ít truy vấn.
• Toán tử: ->/#> trả jsonb, ->>/#>> trả text; nhớ ép kiểu qua text trước khi so sánh số.
• @>, ?, ?|, ?& để kiểm tra chứa và tồn tại khoá — và chúng được tăng tốc bằng index GIN.
• Hàm dựng/biến đổi: to_jsonb, row_to_json, jsonb_build_object, jsonb_agg/json_agg, jsonb_set, jsonb_array_elements.
• GIN (jsonb_ops vs jsonb_path_ops) cho truy vấn chứa; index biểu thức cho một trường cụ thể.
• Nguyên tắc vàng: trường hay lọc/join/ràng buộc → cột; dữ liệu đa dạng, ít truy vấn → JSON.

Tự kiểm tra

1. Khác biệt cốt lõi giữa json và jsonb về cách lưu trữ là gì, và vì sao jsonb hỗ trợ index GIN còn json thì không?
2. Cho cột data jsonb, viết biểu thức lấy data -> trường age ra dạng số nguyên để so sánh > 30. Vì sao không dùng -> trực tiếp?
3. Phân biệt @> và ?. Cho một ví dụ truy vấn dùng mỗi toán tử.
4. Dùng jsonb_agg và jsonb_build_object, viết một câu SELECT gom mỗi account_id thành một mảng các object {so_tien, loai} từ bảng transactions.
5. Bạn thường xuyên lọc WHERE metadata ->> 'status' = 'paid' trên bảng triệu dòng. Nêu hai cách đánh index và cho biết nên chọn cách nào.
6. Nêu ba dấu hiệu cho thấy một trường đang nằm trong JSON nên được chuyển thành cột quan hệ thật.

Đọc tiếp

SQL 13 — Mẫu phân tích thực tế