Thứ Năm, 27 tháng 7, 2023

Thao tác JSON trong Postgresql

MỤC LỤC:

  • Giới thiệu
  • Thực hành chi tiết
  • Tạo bảng chứa cột jsonb
  • Chèn dữ liệu
  • Truy vấn một key cụ thể trong trường jsonb
  • Chọn dòng xe có lựa chọn màu cam orange
  • Chọn dòng xe phải cả màu orange và blue
  • Chọn dòng xe có màu orange hoặc blue
  • Chọn dòng xe động cơ diesel
  • Chọn dòng xe có hộp số hơn 6 cấp
  • Cập nhật một trường trong jsonb dùng jsonb_set
  • Xoá một phần tử trong mảng
  • Thêm phần tử mảng
  • Kết luận

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.

JSONB

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"]}');
idnamedetails
1Triton 2021{"gear": 6, "colors": ["white", "silver", "black", "orange"], "engine": "mivec diesel 2.4l", "tranmission": "4WD"}
2Ford Ranger{"gear": 10, "colors": ["white", "silver", "black", "orange", "blue", "grey"], "engine": "diesel turbo 2.0", "tranmission": "4WD"}
3Vinfast Fadil{"gear": 15, "colors": ["white", "silver", "red", "blue"], "engine": "gasoline 1.5", "tranmission": "2WD CVT"}

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
colors
["white", "silver", "black", "orange"]
["white", "silver", "red", "blue"]
["white", "silver", "black", "orange", "blue", "grey", "brown", "yellow"]

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

select * from test.products p where details['colors'] ? 'orange'
idnamedetails
1Triton 2021{"gear": 6, "colors": ["white", "silver", "black", "orange"], "engine": "mivec diesel 2.4l", "tranmission": "4WD"}
2Ford Ranger{"gear": 10, "colors": ["white", "silver", "black", "orange", "blue", "grey"], "engine": "diesel turbo 2.0", "tranmission": "4WD"}

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']`
idnamedetails
2Ford Ranger{"gear": 10, "colors": ["white", "silver", "black", "orange", "blue", "grey"], "engine": "diesel turbo 2.0", "tranmission": "4WD"}

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

select * from test.products p where details['colors'] ?| array['orange', 'blue']
idnamedetails
1Triton 2021{"gear": 6, "colors": ["white", "silver", "black", "orange"], "engine": "mivec diesel 2.4l", "tranmission": "4WD"}
2Ford Ranger{"gear": 10, "colors": ["white", "silver", "black", "orange", "blue", "grey"], "engine": "diesel turbo 2.0", "tranmission": "4WD"}
3Vinfast Fadil{"gear": 15, "colors": ["white", "silver", "red", "blue"], "engine": "gasoline 1.5", "tranmission": "2WD CVT"}

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

select * from test.products p where details ->> 'engine' ilike '%diesel%'
idnamedetails
1Triton 2021{"gear": 6, "colors": ["white", "silver", "black", "orange"], "engine": "mivec diesel 2.4l", "tranmission": "4WD"}
2Ford Ranger{"gear": 10, "colors": ["white", "silver", "black", "orange", "blue", "grey"], "engine": "diesel turbo 2.0", "tranmission": "4WD"}

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

select * from test.products p where (details['gear'])::int > 6
idnamedetails
2Ford Ranger{"gear": 10, "colors": ["white", "silver", "black", "orange", "blue", "grey"], "engine": "diesel turbo 2.0", "tranmission": "4WD"}
3Vinfast Fadil{"gear": 15, "colors": ["white", "silver", "red", "blue"], "engine": "gasoline 1.5", "tranmission": "2WD CVT"}

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
idnamedetails
2Ford Ranger{"gear": 10, "colors": ["white", "silver", "black", "orange", "blue", "grey", "brown", "yellow"], "engine": "diesel turbo 2.0", "tranmission": "4WD"}

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

So sánh tốc độ cập nhật EAV với JSONB

So sánh tốc độ truy vấn EAV với JSONB


=============================
* KHOÁ HỌC ORACLE DATABASE A-Z ENTERPRISE trực tiếp từ tôi giúp bạn bước đầu trở thành những chuyên gia DBA, đủ kinh nghiệm đi thi chứng chỉ OA/OCP, đặc biệt là rất nhiều kinh nghiệm, bí kíp thực chiến trên các hệ thống Core tại VN chỉ sau 1 khoá học.
* CÁCH ĐĂNG KÝ: Gõ (.) hoặc để lại số điện thoại hoặc inbox https://m.me/tranvanbinh.vn hoặc Hotline/Zalo 090.29.12.888
* Chi tiết tham khảo:
https://bit.ly/oaz_w
=============================
KẾT NỐI VỚI CHUYÊN GIA TRẦN VĂN BÌNH:
📧 Mail: binhoracle@gmail.com
☎️ Mobile/Zalo: 0902912888
👨 Facebook: https://www.facebook.com/BinhOracleMaster
👨 Inbox Messenger: https://m.me/101036604657441 (profile)
👨 Fanpage: https://www.facebook.com/tranvanbinh.vn
👨 Inbox Fanpage: https://m.me/tranvanbinh.vn
👨👩 Group FB: https://www.facebook.com/groups/DBAVietNam
👨 Website: https://www.tranvanbinh.vn
👨 Blogger: https://tranvanbinhmaster.blogspot.com
🎬 Youtube: https://www.youtube.com/@binhguru
👨 Tiktok: https://www.tiktok.com/@binhguru
👨 Linkin: https://www.linkedin.com/in/binhoracle
👨 Twitter: https://twitter.com/binhguru
👨 Podcast: https://www.podbean.com/pu/pbblog-eskre-5f82d6
👨 Địa chỉ: Tòa nhà Sun Square - 21 Lê Đức Thọ - Phường Mỹ Đình 1 - Quận Nam Từ Liêm - TP.Hà Nội

=============================
oracle tutorial, học oracle database, Tự học Oracle, Tài liệu Oracle 12c tiếng Việt, Hướng dẫn sử dụng Oracle Database, Oracle SQL cơ bản, Oracle SQL là gì, Khóa học Oracle Hà Nội, Học chứng chỉ Oracle ở đầu, Khóa học Oracle online,sql tutorial, khóa học pl/sql tutorial, học dba, học dba ở việt nam, khóa học dba, khóa học dba sql, tài liệu học dba oracle, Khóa học Oracle online, học oracle sql, học oracle ở đâu tphcm, học oracle bắt đầu từ đâu, học oracle ở hà nội, oracle database tutorial, oracle database 12c, oracle database là gì, oracle database 11g, oracle download, oracle database 19c, oracle dba tutorial, oracle tunning, sql tunning , oracle 12c, oracle multitenant, Container Databases (CDB), Pluggable Databases (PDB), oracle cloud, oracle security, oracle fga, audit_trail,oracle RAC, ASM, oracle dataguard, oracle goldengate, mview, oracle exadata, oracle oca, oracle ocp, oracle ocm , oracle weblogic, postgresql tutorial, mysql tutorial, mariadb tutorial, ms sql server tutorial, nosql, mongodb tutorial, oci, cloud, middleware tutorial, hoc solaris tutorial, hoc linux tutorial, hoc aix tutorial, unix tutorial, securecrt, xshell, mobaxterm, putty
By lúc
Labels: , ,

ĐỌC NHIỀU
Trần Văn Bình - Oracle Database Master