Trino SQL Bài học 34: Khai phá và phân tích dữ liệu phi cấu trúc với kiểu JSON

Mục tiêu bài học:

• Hiểu cách Trino tiếp cận và xử lý định dạng JSON từ các nguồn dữ liệu phi cấu trúc (Log API, Tracking Events).

• Nắm vững cú pháp JSONPath để định vị chính xác vị trí dữ liệu.

• Phân biệt rõ ràng chức năng của json_extract và json_extract_scalar.

• Xử lý cạm bẫy cực kỳ nguy hiểm: Sự sai lệch chỉ mục (Index) giữa mảng SQL và mảng JSON.

1. Dữ liệu JSON trong Data Lake

Trong các hệ thống hiện đại, dữ liệu ghi nhận từ hành vi người dùng (Clickstream) hoặc Payload của API thường được đổ thẳng xuống Data Lake dưới dạng một chuỗi văn bản khổng lồ (Kiểu VARCHAR).

Một ô dữ liệu có thể chứa chuỗi JSON như sau:

JSON

{
  "user": {"id": 101, "name": "Alice"},
  "devices": ["iphone", "macbook"],
  "active": true
}

Để đếm số lượng người dùng truy cập bằng "iphone", bạn không thể dùng LIKE '%iphone%' vì nó rất dễ sai lệch logic (ví dụ chuỗi chứa iphone_case cũng sẽ bị tính). Bạn cần dùng các hàm chuyên dụng của Trino để "mổ xẻ" cấu trúc này.

2. Định vị dữ liệu với JSONPath

Trino sử dụng cú pháp JSONPath tiêu chuẩn để xác định vị trí của dữ liệu bên trong chuỗi JSON.

• $: Đại diện cho gốc (Root) của toàn bộ chuỗi JSON.

• .key: Truy xuất vào một thuộc tính (Key) bên trong Object.

• [n]: Truy xuất vào phần tử thứ n của một Mảng (Array).

CẢNH BÁO KỸ SƯ (Tử huyệt Index): Ở Bài 30, bạn đã biết Mảng nguyên bản của Trino SQL bắt đầu từ 1. Tuy nhiên, khi bạn đi vào thế giới của JSONPath, quy tắc chuẩn quốc tế áp dụng: Chỉ mục mảng của JSONPath bắt đầu từ 0.

3. Lấy giá trị vô hướng với json_extract_scalar

Đây là hàm được sử dụng nhiều nhất khi phân tích JSON. Hàm này truy xuất vào đường dẫn JSONPath bạn chỉ định, bóc lớp vỏ JSON đi và trả về một chuỗi văn bản thuần túy (Kiểu VARCHAR).

Nếu nó trỏ vào một Object hoặc Array, nó sẽ trả về NULL. Nó chỉ dùng để lấy các giá trị "lá" cuối cùng (chuỗi, số, boolean).

SQL

WITH tracking_logs AS (
    SELECT '{"user": {"id": 101, "name": "Alice"}, "devices": ["iphone", "macbook"]}' AS payload
)
SELECT 
    json_extract_scalar(payload, '$.user.name') AS user_name,      -- Lấy thuộc tính lồng cấp
    json_extract_scalar(payload, '$.user.id') AS user_id,          -- Kết quả là chuỗi '101'
    json_extract_scalar(payload, '$.devices[0]') AS first_device   -- Lưu ý: Index JSON bắt đầu từ 0
FROM tracking_logs;

(Mẹo: Vì json_extract_scalar luôn trả về VARCHAR, nếu bạn muốn dùng user_id để tính toán, bạn phải bọc thêm CAST(... AS INTEGER)).

4. Trích xuất khối dữ liệu với json_extract

Khác với scalar, hàm json_extract(json_string, json_path) sẽ giữ nguyên định dạng JSON. Nó thường được dùng khi bạn muốn lấy ra một Object con hoặc một Mảng con để tiếp tục xử lý ở các bước sau.

Kết quả trả về của hàm này là kiểu dữ liệu JSON (định dạng nội bộ của Trino), không phải chuỗi thông thường.

SQL

WITH tracking_logs AS (
    SELECT '{"user": {"id": 101, "name": "Alice"}, "devices": ["iphone", "macbook"]}' AS payload
)
SELECT 
    json_extract(payload, '$.user') AS user_object,     -- Kết quả: {"id":101,"name":"Alice"}
    json_extract(payload, '$.devices') AS device_array  -- Kết quả: ["iphone","macbook"]
FROM tracking_logs;

5. Kết hợp JSON và Mảng SQL (Chuẩn hóa dữ liệu)

Khi bạn đã dùng json_extract để lấy ra được một mảng JSON (như ["iphone", "macbook"]), bạn thường muốn dùng hàm UNNEST (Bài 31) để làm phẳng nó ra thành từng dòng.

Tuy nhiên, UNNEST chỉ hoạt động với kiểu ARRAY của Trino, không hoạt động với kiểu JSON. Bạn bắt buộc phải ép kiểu (CAST) từ JSON sang ARRAY.

Quy trình đập vỡ Mảng JSON:

1. Dùng json_extract để lấy ra Mảng JSON.

2. Ép kiểu CAST(... AS ARRAY(VARCHAR)).

3. Nhét vào CROSS JOIN UNNEST.

SQL

WITH tracking_logs AS (
    SELECT 1 AS log_id, '{"devices": ["iphone", "macbook", "ipad"]}' AS payload
)
SELECT 
    l.log_id,
    t.device_name
FROM tracking_logs AS l
-- Bước 1 & 2: Trích xuất mảng JSON và ép kiểu thành Mảng SQL
CROSS JOIN UNNEST(
    CAST(json_extract(l.payload, '$.devices') AS ARRAY(VARCHAR))
) AS t(device_name);

Kết quả trả về:

Với quy trình này, bạn đã biến một chuỗi log phi cấu trúc hỗn độn thành một bảng dữ liệu quan hệ chuẩn mực, sẵn sàng cho mọi thao tác thống kê truyền thống.