Đã đăng vào khoảng 4 giờ trước 7 phút đọc

PostgreSQL Bài 11: Các hàm và toán tử chuyên biệt trên JSONB trong môi trường sản xuất

Ở Bài 10, bạn đã nắm vững cách lưu trữ, đánh chỉ mục và sử dụng các toán tử trích xuất (->, ->>) hay toán tử bao hàm (@>). Tuy nhiên, trong môi trường sản xuất (production), các bài toán thực tế thường phức tạp hơn:

Cập nhật một giá trị lồng sâu bên trong JSON document mà không ghi đè toàn bộ dữ liệu.
Xóa bỏ một trường nhạy cảm hoặc dọn dẹp thuộc tính rác.
"Làm phẳng" (flatten) một mảng JSON lồng nhau thành các hàng/cột quan hệ chuẩn để chạy báo cáo, tính toán tổng hợp.
Chuẩn hóa các truy vấn phức tạp bằng chuẩn SQL/JSON Path (jsonb_path_query).

Bài học này sẽ đi sâu vào các công cụ mạnh mẽ nhất giúp bạn thao tác với JSONB trực tiếp bằng SQL thuần mà không cần kéo dữ liệu về backend xử lý.

1. Kỹ thuật chỉnh sửa JSONB tại chỗ (In-Place Mutation)

Khi cần cập nhật một thuộc tính con trong tài liệu JSONB, thay vì select toàn bộ document về backend (Node.js/Laravel/Go), parse thành struct, gán giá trị rồi update ngược lại, PostgreSQL cung cấp các hàm và toán tử thao tác trực tiếp ở tầng database.

1.1. Hàm `jsonb_set()`: Cập nhật hoặc chèn thêm trường mới

Cú pháp:

SQL

jsonb_set(
    target jsonb, 
    path text[], 
    new_value jsonb, 
    create_if_missing boolean DEFAULT true
)

target: Cột hoặc tài liệu JSONB cần sửa.
path: Mảng đường dẫn trỏ tới trường cần sửa (ví dụ: '{specs, ram_gb}').
new_value: Giá trị mới (bắt buộc phải ép kiểu thành jsonb).
create_if_missing: Nếu đường dẫn chưa tồn tại, có tạo mới hay không (mặc định là true).

Ví dụ thực tế: Nâng cấp RAM cho máy tính từ 32 lên 64, đồng thời thêm trường hệ điều hành:

SQL

UPDATE product_catalog
SET attributes = jsonb_set(
    attributes,
    '{specs, ram_gb}',
    '64'::jsonb
)
WHERE sku = 'DELL-XPS-15';

Nếu muốn cập nhật nhiều trường lồng nhau cùng lúc, bạn có thể lồng các lệnh jsonb_set() hoặc dùng toán tử nối ||.

1.2. Toán tử nối gộp `||` (Concat / Shallow Merge)

Toán tử || thực hiện gộp hai JSONB Object ở tầng ngoài cùng (top-level):

Nếu key đã tồn tại: ghi đè giá trị mới.
Nếu key chưa tồn tại: chèn thêm trường mới.

SQL

-- Thêm trường bảo hành và cập nhật trạng thái kho hàng:
UPDATE product_catalog
SET attributes = attributes || '{"warranty_months": 24, "in_stock": false}'::jsonb
WHERE sku = 'DELL-XPS-15';

2. Kỹ thuật xóa trường và phần tử trong JSONB

PostgreSQL hỗ trợ hai toán tử xóa chuyên biệt: toán tử trừ - và toán tử xóa theo đường dẫn #-.

2.1. Toán tử `-`: Xóa theo Key hoặc Chỉ số mảng (Index)

Xóa một key ở top-level:

SQL

-- Xóa thuộc tính 'price' khỏi document
SELECT attributes - 'price' FROM product_catalog;

Xóa nhiều keys cùng lúc:

SQL

-- Xóa đồng thời 'price' và 'in_stock'
SELECT attributes - ARRAY['price', 'in_stock'] FROM product_catalog;

Xóa phần tử trong mảng theo chỉ số:

SQL

-- Xóa phần tử đầu tiên (index 0) trong mảng tags
SELECT ('["laptop", "ultrabook", "developer"]'::jsonb) - 0;
-- Kết quả: ["ultrabook", "developer"]

2.2. Toán tử `#-`: Xóa trường lồng sâu theo đường dẫn

Nếu muốn xóa một thuộc tính nằm sâu bên trong object con (ví dụ: xóa dung lượng pin nằm trong specs.battery), toán tử - không thể tiếp cận được. Lúc này hãy dùng #-:

SQL

-- Xóa thuộc tính storage bên trong specs:
UPDATE product_catalog
SET attributes = attributes #- '{specs, storage}'
WHERE sku = 'DELL-XPS-15';

3. Chuyển đổi JSONB thành dạng Bảng quan hệ (Relational Flattening)

Một trong những tác vụ backend thường gặp là trích xuất mảng JSON lồng nhau để thực hiện các phép toán JOIN, COUNT, SUM hoặc GROUP BY.

3.1. Bung mảng thành từng dòng với `jsonb_array_elements()` / `jsonb_array_elements_text()`

jsonb_array_elements(jsonb): Bung mảng thành tập hợp các dòng kiểu JSONB.
jsonb_array_elements_text(jsonb): Bung mảng thành tập hợp các dòng kiểu TEXT.

Ví dụ: Thống kê xem mỗi tag đang xuất hiện ở bao nhiêu sản phẩm:

SQL

SELECT 
    tag,
    COUNT(*) AS total_products
FROM product_catalog,
     jsonb_array_elements_text(attributes -> 'tags') AS tag
GROUP BY tag
ORDER BY total_products DESC;

Kết quả:

Plaintext

    tag     | total_products 
------------+----------------
 laptop     |             45
 developer  |             28
 ultrabook  |             12

(Kỹ thuật dùng dấu phẩy , ở trên tương đương với cú pháp CROSS JOIN LATERAL - cho phép hàm xử lý từng dòng của bảng đứng trước).

3.2. Bung Key-Value thành từng dòng với `jsonb_each()` / `jsonb_each_text()`

Nếu JSON lưu trữ các cặp key-value động mà bạn không biết trước tên thuộc tính (ví dụ: các thông số kỹ thuật tùy biến):

SQL

SELECT 
    sku,
    key AS spec_name,
    value AS spec_value
FROM product_catalog,
     jsonb_each_text(attributes -> 'specs')
WHERE sku = 'DELL-XPS-15';

Kết quả:

Plaintext

     sku     | spec_name |    spec_value    
-------------+-----------+------------------
 DELL-XPS-15 | cpu       | Intel Core i7
 DELL-XPS-15 | ram_gb    | 32

3.3. Ánh xạ trực tiếp sang cấu trúc bảng với `jsonb_to_recordset()`

Khi mảng JSON chứa một danh sách các object và bạn muốn biến nó thành một bảng dữ liệu có kiểu rõ ràng:

SQL

CREATE TABLE user_orders (
    order_id BIGINT PRIMARY KEY,
    items JSONB
);

INSERT INTO user_orders VALUES (
    1001,
    '[
        {"sku": "KEYBOARD-RGB", "qty": 1, "price": 85.5},
        {"sku": "MOUSE-WIRELESS", "qty": 2, "price": 25.0}
    ]'::jsonb
);

-- Làm phẳng mảng items thành bảng quan hệ có schema xác định:
SELECT 
    order_id,
    item.sku,
    item.qty,
    item.price,
    (item.qty * item.price) AS total_item_amount
FROM user_orders,
     jsonb_to_recordset(items) AS item(sku TEXT, qty INT, price NUMERIC);

Kết quả:

Plaintext

 order_id |      sku       | qty | price | total_item_amount 
----------+----------------+-----+-------+-------------------
     1001 | KEYBOARD-RGB   |   1 | 85.50 |             85.50
     1001 | MOUSE-WIRELESS |   2 | 25.00 |             50.00

4. Chuẩn SQL/JSON Path (`jsonb_path_query` & `@@`)

Từ PostgreSQL 12+, engine bổ sung khả năng hỗ trợ chuẩn SQL/JSON Path language (tương tự như XPath cho XML hoặc JSONPath trong Javascript). Đây là công cụ hiện đại nhất để truy vấn các tài liệu JSON phân tầng phức tạp.

Cú pháp đường dẫn bắt đầu bằng ký tự $ đại diện cho root object.

Các hàm và toán tử chính:

jsonb_path_exists(target, path): Kiểm tra xem đường dẫn có tồn tại hay không (trả về boolean).
jsonb_path_query(target, path): Trích xuất tất cả các giá trị khớp với đường dẫn (trả về tập hợp dòng).
jsonb_path_match(target, path) (hoặc toán tử @@): Đánh giá biểu thức lọc điều kiện.

Kịch bản thực tế với JSONPath:

SQL

-- 1. Tìm tất cả sản phẩm có ram_gb lớn hơn hoặc bằng 16 bên trong specs:
SELECT sku 
FROM product_catalog 
WHERE attributes @@ '$.specs.ram_gb >= 16';

-- 2. Trích xuất tất cả các tag kết thúc bằng chuỗi "book" (Sử dụng regex trong path):
SELECT 
    sku,
    jsonb_path_query(attributes, '$.tags[*] ? (@ like_regex ".*book$")') AS matched_tag
FROM product_catalog;

5. Bảng tổng hợp các hàm & toán tử JSONB quan trọng

Cú pháp / Hàm	Ý nghĩa	Ví dụ áp dụng
`jsonb_set()`	Cập nhật/thêm trường theo đường dẫn cụ thể	`jsonb_set(payload, '{user, age}', '30')`
`-`	Xóa key ở top-level hoặc xóa index mảng	`payload - 'token'`
`#-`	Xóa trường con lồng sâu theo path	`payload #- '{meta, debug_info}'`
`jsonb_array_elements()`	Bung mảng JSONB thành các dòng dữ liệu	`FROM tbl, jsonb_array_elements(tbl.arr)`
`jsonb_each()`	Phân rã object thành các cặp (key, value)	Dùng cho dynamic schemas
`jsonb_to_recordset()`	Chuyển đổi mảng các object thành bảng SQL hoàn chỉnh	Báo cáo chi tiết giỏ hàng, hóa đơn
`jsonb_path_query()`	Truy vấn phần tử theo biểu thức SQL/JSON Path	Lọc mảng/object lồng phức tạp bằng regex/phép so sánh

6. Tóm tắt & Bài tiếp theo

Đừng parse và cập nhật JSON ở tầng ứng dụng nếu chỉ cần đổi một vài giá trị: hãy dùng jsonb_set() hoặc toán tử nối ||.
Dọn dẹp các trường thừa hoặc nhạy cảm dễ dàng bằng toán tử - và #-.
Các hàm jsonb_array_elements* và jsonb_to_recordset cho phép bạn kết hợp sức mạnh linh hoạt của NoSQL với các câu lệnh báo cáo quan hệ (JOIN, GROUP BY, SUM) truyền thống.
SQL/JSON Path (jsonb_path_query, @@) là giải pháp hiện đại nhất cho các truy vấn điều kiện lồng sâu.

Bài 12 xem tiếp: Kiểu mảng (Arrays) và Range Types (tsrange, daterange): Trường hợp sử dụng tối ưu — chúng ta sẽ tìm hiểu lý do tại sao không phải lúc nào cũng cần tạo bảng trung gian quan hệ 1-N (One-to-Many), cách khai thác kiểu mảng nguyên bản của PostgreSQL cùng các kiểu khoảng giá trị (Range Types) để giải quyết triệt để bài toán đặt phòng, chồng lấn lịch trình (Overlapping Schedules).

PostgreSQL

1. Kỹ thuật chỉnh sửa JSONB tại chỗ (In-Place Mutation)

1.1. Hàm jsonb_set(): Cập nhật hoặc chèn thêm trường mới

1.2. Toán tử nối gộp || (Concat / Shallow Merge)

2. Kỹ thuật xóa trường và phần tử trong JSONB

2.1. Toán tử -: Xóa theo Key hoặc Chỉ số mảng (Index)

2.2. Toán tử #-: Xóa trường lồng sâu theo đường dẫn

3. Chuyển đổi JSONB thành dạng Bảng quan hệ (Relational Flattening)

3.1. Bung mảng thành từng dòng với jsonb_array_elements() / jsonb_array_elements_text()

3.2. Bung Key-Value thành từng dòng với jsonb_each() / jsonb_each_text()

3.3. Ánh xạ trực tiếp sang cấu trúc bảng với jsonb_to_recordset()

4. Chuẩn SQL/JSON Path (jsonb_path_query & @@)

Các hàm và toán tử chính:

Kịch bản thực tế với JSONPath:

5. Bảng tổng hợp các hàm & toán tử JSONB quan trọng

6. Tóm tắt & Bài tiếp theo

Mục lục

1.1. Hàm `jsonb_set()`: Cập nhật hoặc chèn thêm trường mới

1.2. Toán tử nối gộp `||` (Concat / Shallow Merge)

2.1. Toán tử `-`: Xóa theo Key hoặc Chỉ số mảng (Index)

2.2. Toán tử `#-`: Xóa trường lồng sâu theo đường dẫn

3.1. Bung mảng thành từng dòng với `jsonb_array_elements()` / `jsonb_array_elements_text()`

3.2. Bung Key-Value thành từng dòng với `jsonb_each()` / `jsonb_each_text()`

3.3. Ánh xạ trực tiếp sang cấu trúc bảng với `jsonb_to_recordset()`

4. Chuẩn SQL/JSON Path (`jsonb_path_query` & `@@`)