Supabase中Workout表JSONB列存储多Exercise ID的SQL实现问询

1. 表结构修改与数据迁移 #

需要将workout_TABLE的exercise_id列类型转换为JSONB，同时把现有单条关联ID转为JSON数组格式：

情况1：原exercise_id为整数类型 #

ALTER TABLE workout_TABLE
ALTER COLUMN exercise_id TYPE JSONB
USING ARRAY[exercise_id]::JSONB;

情况2：原exercise_id为文本类型（需确保文本可转换为整数） #

ALTER TABLE workout_TABLE
ALTER COLUMN exercise_id TYPE JSONB
USING ('[' || exercise_id || ']')::JSONB;

注意：操作前请务必备份表数据，避免意外数据丢失。

2. 插入包含多个Exercise ID的Workout记录 #

后续插入数据时，直接传入JSON数组格式的Exercise ID集合即可：

方式1：用JSON字符串转换 #

INSERT INTO workout_TABLE (val_1, exercise_id)
VALUES ('upper_body', '[1,2,3]'::JSONB);

方式2：用PostgreSQL数组转换 #

INSERT INTO workout_TABLE (val_1, exercise_id)
VALUES ('full_body', ARRAY[1,3]::JSONB);

3. 查询关联的Exercise数据 #

查询指定Workout关联的所有Exercise #

-- 方式1：将JSONB数组转为整数数组后匹配
SELECT e.*
FROM workout_TABLE w
JOIN exercise_TABLE e ON e.id = ANY(w.exercise_id::INT[])
WHERE w.id = 1;

-- 方式2：使用JSONB包含操作符
SELECT e.*
FROM workout_TABLE w
JOIN exercise_TABLE e ON w.exercise_id @> to_jsonb(e.id)
WHERE w.id = 1;

查询包含指定Exercise ID的所有Workout #

SELECT w.*
FROM workout_TABLE w
WHERE w.exercise_id @> to_jsonb(2);

4. 性能优化（可选） #

如果需要频繁基于exercise_id做查询，建议创建GIN索引提升检索效率：

CREATE INDEX idx_workout_exercise_id ON workout_TABLE USING GIN (exercise_id);

内容的提问来源于stack exchange，提问作者Shahad Alharbi