Thao tác JSON trong Postgresql

Giới thiệu

CSDL quan hệ bên cạnh những lợi ích dữ liệu cấu trúc bảng có tính ràng buộc dữ liệu rất chặt chẽ giúp việc truy vấn tìm kiếm dữ liệu bằng khai báo câu lệnh SQL thay vì phải lập trình duyệt mảng, tập hợp ....

Tuy nhiên với dữ liệu dạng struct hoặc struct chứa các phần tử khác nhau, cấu trúc thay đổi hơn là dạng bảng hàng và cột, CSDL quan hệ tỏ ra thua kém so với CSDL NoSQL như MongoDB. Bản chất của JSON là chuỗi. Nên thực tế CSDL nào cũng có thể lưu được JSON string. Tuy nhiên để tìm kiếm, thao tác đến từng phần tử trong JSON thì mới là điều đáng bàn.

Postgresql là cơ sở dữ liệu tiên phong trong việc hỗ trợ lưu các kiểu dữ liệu phi chuẩn như array, hstore, json và jsonb vào cột. Postgresql từ bản 9.4 bổ xung kiểu jsonb cho phép thực hiện các toán tử, thao tác trong câu lệnh SQL. Bài viết này chỉ ví dụ những tình huống thao tác thường xuyên gặp với cột JSONB. Ở đây JSONB có nghĩa là binary json, nó khác với json string ở chỗ hỗ trợ nhiều thao tác hơn.

Thực hành chi tiết

Tạo bảng chứa cột jsonb

CREATE TABLE test.products (
  id serial,
  name text,
  details jsonb
);

Chèn dữ liệu

insert into test.products (name, details) values ('Triton 2021',
'{"gear": 6,
"tranmission": "4WD",
"engine": "mivec diesel 2.4l",
"colors": ["white", "silver", "black", "orange"]}');

insert into test.products (name, details) values ('Ford Ranger',
'{"gear": 10,
"tranmission": "4WD",
"engine": "diesel turbo 2.0",
"colors": ["white", "silver", "black", "orange", "blue", "grey"]}');

insert into test.products (name, details) values ('Vinfast Fadil',
'{"gear": 15,
"tranmission": "2WD CVT",
"engine": "gasoline 1.5",
"colors": ["white", "silver", "red", "blue"]}');

Truy vấn một key cụ thể trong trường jsonb

Trước Postgresql 14

select p.details -> 'colors' colors from test.products p

Từ Postgresl 14 trở đi, chúng ta có thể dùng cú pháp jsonb_column['element'] nhìn dễ hiểu hơn.

select p.details ['colors'] colors from test.products p

Chọn dòng xe có lựa chọn màu cam orange

select * from test.products p where details['colors'] ? 'orange'

Chọn dòng xe phải cả màu orange và blue

sql select * from test.products p where details['colors'] ?& array['orange', 'blue']`

Chọn dòng xe có màu orange hoặc blue

select * from test.products p where details['colors'] ?| array['orange', 'blue']

Chọn dòng xe động cơ diesel

select * from test.products p where details ->> 'engine' ilike '%diesel%'

Chọn dòng xe có hộp số hơn 6 cấp

select * from test.products p where (details['gear'])::int > 6

Cập nhật một trường trong jsonb dùng jsonb_set

Nhìn kết quả Vinfast Fadil có hộp số 15 cấp có vẻ sai sai. Vậy cần cập nhật lại số cấp của hộp số Fadil là 5. Giả sử vậy đi, hình như Fadil dùng CVT dây đai thì phải.

update test.products set details = jsonb_set(details, '{gear}', '5') where id = 3

Xoá một phần tử trong mảng

Xoá màu grey trong mẫu xe Ford Ranger có id = 2 chú ý lệnh (details->'colors') - 'grey' là xoá phần tử grey ra khỏi mảng colors

update test.products set details = jsonb_set(details, '{colors}', (details->'colors') - 'grey' ) where id = 2

Thêm phần tử mảng

Thêm 2 mầu yellow và brown vào một mảng colors

update test.products set details = jsonb_set(details, '{colors}', (details->'colors') || '["brown", "yellow"]' ) where id = 2

Kết luận

Dù Postgresql hỗ trợ lưu dữ liệu JSON và thao tác truy vấn đến từng phần tử, thêm, sửa xoá... nhưng chúng ta tuyệt đối không lạm dụng cột lưu JSONB. Hãy luôn ưu tiên thiết kế dạng bảng, cột truyền thống để tìm kiếm tốt hơn, join các bảng, áp các quan hệ, ràng buộc dễ dàng và nhiều lập trình khác có thể hiểu được ngay code của bạn.

Tuy nhiên JSONB thực sự hữu ích khi lưu các thuộc tính đa dạng có ở bản ghi này, nhưng lại không có ở bản ghi kia. Với JSONB chúng ta vẫn có thể đánh index đến từng element cụ thể hoặc tất cả các element để tăng tốc độ tìm kiếm.

Việc sử dụng cột JSONB giúp chúng ta xử lý bài toán bảng product lưu mặt hàng có rất nhiều thuộc tính khác nhau cùng với một số thuộc tính chung, nhưng phải tìm kiếm được theo các thuộc tính riêng. Trước đây người ta dùng mô hình EAV, nhưng giờ có thể chuyển qua JSONB. Tham khảo Replacing EAV with JSONB in PostgreSQL