Postgres 使用 unnest 进行 JOIN

sql
CREATE TABLE users (
  user_id SERIAL PRIMARY KEY,
  purchased_items TEXT[]
);
INSERT INTO users (purchased_items)
VALUES (ARRAY['item1', 'item2']),
       (ARRAY['item3', 'item4']),
       (ARRAY['item5', 'item6']);

sql
CREATE TABLE orders (
  order_id SERIAL PRIMARY KEY,
  user_id INTEGER REFERENCES users(user_id),
  order_date DATE
);
INSERT INTO orders (user_id, order_date)
VALUES (1, '2021-01-01'),
       (2, '2021-02-01'),
       (3, '2021-03-01');

sql
SELECT u.user_id, o.order_id, o.order_date
FROM users u
JOIN orders o ON u.user_id = o.user_id
JOIN unnest(u.purchased_items) AS item ON TRUE;

 user_id | order_id | order_date 
---------+----------+------------
       1 |        1 | 2021-01-01
       2 |        2 | 2021-02-01
       3 |        3 | 2021-03-01