如何在PostgreSQL中删除数组元素？

SELECT array_remove(ARRAY[1, 2, 3, 2, 4], 2);

 array_remove 
--------------
 {1,3,4}
(1 row)

-- 删除数组中第二个元素（索引为1）
SELECT ARRAY[1, 2, 3, 4, 5][1:1] || ARRAY[1, 2, 3, 4, 5][3:5];

 array 
--------
 {1,3,4,5}
(1 row)

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    hobbies VARCHAR[]
);

INSERT INTO users (name, hobbies) VALUES ('Alice', ARRAY['reading', 'swimming', 'cycling']);

UPDATE users
SET hobbies = array_remove(hobbies, 'swimming')
WHERE id = 1;

-- 假设我们有一个表 users，其中有一个字段 hobbies 是 JSONB 类型
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    hobbies JSONB
);

INSERT INTO users (name, hobbies) VALUES ('Alice', '{"reading", "swimming", "cycling"}'::jsonb);

-- 删除 'swimming'
UPDATE users
SET hobbies = hobbies - 'swimming'
WHERE id = 1;

-- 假设我们要删除 'swimming' 并保持顺序
WITH positions AS (
    SELECT id, array_position(hobbies, 'swimming') AS pos
    FROM users
    WHERE id = 1
)
UPDATE users
SET hobbies = CASE
    WHEN pos IS NOT NULL THEN array_cat(
        ARRAY(SELECT * FROM unnest(hobbies) WHERE array_position(hobbies, 'swimming') < pos),
        ARRAY(SELECT * FROM unnest(hobbies) WHERE array_position(hobbies, 'swimming') > pos)
    )
    ELSE hobbies
END
FROM positions
WHERE users.id = positions.id;