Trích xuất dữ liệu lồng nhau: jsonb_to_recordset()

Tiếp theo tụi mình sẽ chuyển sang các tình huống phức tạp hơn khi làm việc với dữ liệu dạng JSONB — trích xuất dữ liệu lồng nhau và chuyển chúng thành các dòng bảng. Bạn hỏi tại sao phải làm vậy? Đơn giản thôi! Hãy tưởng tượng bạn được đưa cho một object JSON chứa mảng các món hàng và được nhờ tính tổng tiền tất cả các món hoặc xuất chúng ra dạng bảng cho báo cáo. Mình sẽ giải thích ngay bây giờ nhé!

Tại sao không làm việc với JSON như text hoặc cấu trúc bình thường? Cùng xem thử một tình huống. Trong nhiều ứng dụng thực tế, dữ liệu được lưu dưới dạng mảng JSON:

[
  { "id": 1, "product_name": "Laptop", "price": 1200 },
  { "id": 2, "product_name": "Smartphone", "price": 800 },
  { "id": 3, "product_name": "Tablet", "price": 400 }
]

Nhìn thì tiện, nhưng khi phân tích dữ liệu thì thường phải chuyển mảng này thành bảng để lọc, sắp xếp hay tổng hợp. Ví dụ: “Tất cả đơn hàng có tổng tiền lớn hơn 500 đô”. JSONB tự nó không làm được việc này một cách tiện lợi như mong muốn. Đó là lúc jsonb_to_recordset() phát huy tác dụng.

Làm việc với jsonb_to_recordset()

Hàm jsonb_to_recordset() cho phép chuyển mảng các object JSONB thành các dòng bảng. Nó literally biến mỗi phần tử trong mảng thành một dòng, còn các key thì thành cột. Hàm này cực kỳ hữu ích khi dữ liệu lồng nhiều tầng hoặc chứa mảng object.

Cú pháp

SELECT *
FROM jsonb_to_recordset('[ mảng JSONB ]') AS alias(cột1 KIỂU, cột2 KIỂU, ...);

• [ mảng JSONB ]: mảng các object JSON mà tụi mình sẽ trích xuất dữ liệu.
• AS alias: tạo tên tạm cho bảng kết quả.
• cột1 KIỂU, cột2 KIỂU: định nghĩa tên cột và kiểu dữ liệu cho từng cột (ví dụ INTEGER, TEXT, NUMERIC).

Ví dụ: chuyển mảng JSONB thành dòng

Giả sử tụi mình có bảng sau:

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_name TEXT,
    products JSONB
);

Và bảng này có dữ liệu như sau:

Bây giờ nhiệm vụ là: xuất danh sách tất cả sản phẩm của mọi đơn hàng ra dạng bảng. Đây là cách làm với jsonb_to_recordset():

SELECT
    o.id AS order_id,
    o.customer_name,
    p.id AS product_id,
    p.product_name,
    p.price
FROM
    orders AS o,
    jsonb_to_recordset(o.products) AS p(id INTEGER, product_name TEXT, price NUMERIC);

Kết quả:

Ví dụ: lọc dữ liệu

Làm khó hơn chút nhé. Muốn chỉ hiện các sản phẩm trong đơn hàng có giá trên 100 đô:

SELECT
    o.id AS order_id,
    o.customer_name,
    p.id AS product_id,
    p.product_name,
    p.price
FROM
    orders AS o,
    jsonb_to_recordset(o.products) AS p(id INTEGER, product_name TEXT, price NUMERIC)
WHERE
    p.price > 100;

Kết quả:

Ví dụ: tổng hợp dữ liệu

Sao không thử tính tổng tiền tất cả sản phẩm trong đơn hàng nhỉ? Dùng hàm tổng hợp là xong:

SELECT
    o.customer_name,
    SUM(p.price) AS total_amount
FROM
    orders AS o,
    jsonb_to_recordset(o.products) AS p(id INTEGER, product_name TEXT, price NUMERIC)
GROUP BY
    o.customer_name;

Kết quả:

Lưu ý quan trọng

Hãy chắc chắn cấu trúc mảng JSON giống nhau cho mọi object. Nếu object có key khác nhau hoặc lồng sâu khác nhau, bạn có thể gặp lỗi hoặc kết quả bất ngờ.

Đặt đúng kiểu dữ liệu cho các cột trích xuất. Ví dụ, nếu key chứa ngày tháng thì dùng DATE, số thì dùng NUMERIC hoặc INTEGER.

Nhớ là jsonb_to_recordset() chỉ chuyển đổi mảng JSONB; với object đơn lẻ thì không dùng được đâu nha.

Lỗi thường gặp và cách tránh

Dùng sai kiểu dữ liệu: nếu trong mảng JSONB có giá trị kiểu khác nhau (ví dụ string thay vì số), sẽ bị lỗi. Nên chuyển dữ liệu về đúng kiểu trước khi dùng hàm.

Truy cập sai key: nếu key không tồn tại trong một object nào đó của mảng, sẽ bị lỗi. Kiểm tra kỹ cấu trúc dữ liệu trước khi query nhé.

Không có dữ liệu: nếu cột JSONB rỗng (NULL), hàm sẽ không trả về kết quả. Nên thêm kiểm tra, ví dụ dùng COALESCE().

Ứng dụng thực tế

jsonb_to_recordset() được dùng rất nhiều trong thực tế, như xử lý đơn hàng, phân tích báo cáo, log hành động user và xử lý dữ liệu từ API ngoài. Ví dụ:

• Trong web bán hàng, dễ dàng chuyển mảng sản phẩm thành bảng để làm báo cáo.
• REST API có thể trả về dữ liệu dạng JSON, dùng PostgreSQL để phân tích rất tiện.
• App phân tích dữ liệu thường dùng hàm này để xử lý dữ liệu nhiều tầng phức tạp.