Sử dụng Array trong Postgres để thay thế cho bảng trung gian

Quốc Cường viết ngày 23/10/2015

Thông thường, khi sử dung đến các quan hệ 1: nhiều hoặc nhiều: nhiều trong bảng quan hệ để có thể truy vấn ta cần có bảng trung gian ở giữa. Với việc sử dụng kiểu Array trong Postgres bạn có thể loại bỏ được bảng trung gian này. Demo nhé :
Bạn có 2 bảng như sau

1. Users
   • id - serial - Primary Key
   • username - character varying[255]
   • email - character varying[255]
   • jobs - integer[] << Array
2. Jobs
   • id - serial - Primary Key
   • name - character varying[255]
   • description - character varying[255]

Để có thể kết nối dữ liệu giữa 2 bảng :

SELECT users.* , array_to_json(array_agg(jobs.*)) as detail_jobs FROM users
LEFT JOIN jobs ON jobs.id = ANY(users.jobs)
GROUP BY users.id;

Kết quả bạn nhận được :

Ta có thể chọn định danh tên côt để có 1 mảng trả về ví dụ :

SELECT users.* , array_to_json(array_agg(jobs.name)) as detail_jobs FROM users
LEFT JOIN jobs ON jobs.id = ANY(users.jobs)
GROUP BY users.id;

Thay vì sử dụng ANY bạn có thể sử dụng cú pháp IN và unnest

SELECT users.* , array_to_json(array_agg(jobs.*)) as detail_jobs FROM users
LEFT JOIN jobs ON jobs.id IN (SELECT unnest(users.jobs))
GROUP BY users.id;

Bạn có thể thêm giá trị vào trong Array bằng cú pháp

WITH u AS  (
  SELECT array_append( users.jobs, 3 ) AS jobs FROM users WHERE id = 1
)
UPDATE users SET jobs = u.jobs FROM u WHERE users.id = 1;

Bạn cũng có thể xóa 1 jobs khỏi Array bằng cú pháp

WITH new_jobs AS (
  SELECT array_agg( jobs.job ) AS jobs FROM (
    SELECT unnest( users.jobs ) AS job FROM users WHERE id = 1
  ) jobs WHERE jobs.job != 3
)

UPDATE users SET jobs = new_jobs.jobs FROM new_jobs WHERE id = 1;

link tham khảo Array