[PostgreSQL] Cập nhật trường dữ liệu trong JSONB và bảo đảm tính toàn vẹn dữ liệu

Trong quá trình làm việc với cơ sở dữ liệu PostgreSQL, việc làm việc với kiểu dữ liệu JSONB cung cấp một khả năng linh hoạt để lưu trữ và truy xuất dữ liệu không cấu trúc. Trong bài viết này, chúng ta sẽ tìm hiểu cách cập nhật một trường dữ liệu trong JSONB một cách an toàn và bảo đảm tính toàn vẹn dữ liệu

Cập nhật 1 field trong jsonb

Để bắt đầu, chúng ta cần tạo một bảng trong cơ sở dữ liệu chứa một cột có kiểu dữ liệu JSONB. Ví dụ, ta tạo bảng "products" với cột "details" là kiểu JSONB.

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    details JSONB
);

Sau đó, chúng ta tiến hành thêm một dòng dữ liệu mẫu vào bảng:

INSERT INTO products (details) VALUES ('{"name": "Product A", "quantity": 5}');

Giả sử chúng ta muốn cập nhật trường "quantity" trong đối tượng JSONB và trừ đi 1 sản phẩm Cách thứ nhất là chúng ta query quantity ra trước, để so sánh số lượng cần trừ đi, và sau đó là cập nhật giá trị field đó lại như sau

UPDATE products
SET details = jsonb_set(details, '{quantity}', '4')
WHERE id = 1;

Cách này khi code rất nguy hiểm, do có thể 2 tiến trình đang cùng vào xử lý hàm này của bạn, khi ấy giá trị đọc ra sẽ như nhau ở cả 2 tiến trình, và giá trị update lại sẽ gây sai, để khắc phục việc này bạn phải lấy giá trị của field quanity ngay lúc đó và đem đi trừ như sau:

UPDATE products
SET details = jsonb_set(details, '{quantity}', (details->>'quantity')::int - 1)
WHERE id = 1;

Để chuyên nghiệp hơn chúng ta nên check số lượng sản phẩm phải lớn hơn hoặc bằng số lượng chúng ta muốn trừ đi, như vậy câu sql sẽ như này:

UPDATE products
SET details = jsonb_set(details, '{quantity}', (details->>'quantity')::int - 1)
WHERE id = 1 and details->>'quantity' >= 1;

khi đó bạn sẽ lấy result từ việc update xem data mình được update hay chưa, nếu chưa có thể throw lỗi, thêm log vào để các bạn trace lỗi

Cập nhật 1 field nằm trong 1 array trong jsonb

Cũng tương tự như trên nhưng nếu cột detail chứa 1 mảng dữ liệu sản phẩm thì sao, tiếp tục nhé các cậu )))

Thêm một dòng dữ liệu mẫu vào bảng, bây giờ ta có productDetails là 1 mảng các sản phẩm

INSERT INTO products (details)
VALUES ('{
  "name": "Product A",
  "productDetails": [
    {"id": 1, "quantity": 5},
    {"id": 2, "quantity": 3},
    {"id": 3, "quantity": 7}
  ]
}');

Bây giờ chúng ta sẽ trừ quantity của product details có id 1 đi 1 sản phẩm:

UPDATE products
SET details = (
  SELECT jsonb_set(
    details,
    '{productDetails}',
    (
      SELECT jsonb_agg(
        CASE 
          WHEN (value->>'id')::int = 1 THEN
            jsonb_set(value, '{quantity}', ((value->>'quantity')::int - 1)::text::jsonb)
          ELSE
            value
        END
      )
      FROM jsonb_array_elements(details->'productDetails') AS value
    )
  )
)
WHERE id = 1
  AND EXISTS (
    SELECT 1
    FROM jsonb_array_elements(details->'productDetails') AS value
    WHERE (value->>'id')::int = 1
      AND (value->>'quantity')::int >= 1
  );

Trong câu truy vấn UPDATE, chúng ta sử dụng câu lệnh CASE để kiểm tra điều kiện "id" là 1 Nếu điều kiện này đúng, chúng ta sử dụng hàm jsonb_set để cập nhật trường "quantity" bằng cách trừ đi 1

Ngược lại, nếu điều kiện này không đúng, chúng ta giữ nguyên giá trị ban đầu của phần tử trong mảng

Còn bên dưới hàm where, chúng ta dùng jsonb_array_elements để chuyển mảng sang phần tử jsonb riêng biệt để so sánh id và quantity chúng ta muốn trừ

Chúng ta đã đi qua 2 ví dụ về việc cập nhật dữ liệu trong trường JSONB. Thường thì chúng ta sử dụng trường này cho những dữ liệu phức tạp mà không thể trình bày trên nhiều cột riêng biệt. Tuy nhiên, khi cập nhật dữ liệu trong trường JSONB, đặc biệt là các dữ liệu số liệu, chúng ta cần cẩn trọng, hãy đảm bảo thực hiện kiểm tra và xử lý cẩn thận trước khi cập nhật dữ liệu, đặc biệt là đối với các số liệu.

Điều này giúp đảm bảo tính toàn vẹn và đúng đắn của dữ liệu trong trường JSONB

PostgreSQL

Cập nhật 1 field trong jsonb

Cập nhật 1 field nằm trong 1 array trong jsonb

Mục lục