Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE users (
  settings jsonb
);

INSERT INTO users VALUES
('[{
         "id": 100,
         "name": "testOne",
         "settings": "settingOne"
     },
     {
         "id": 101,
         "name": "testTwo",
         "settings": "settingTwo"
     },
     {
         "id": 101,
         "name": "testThree",
         "settings": "settingThree"
     }]')
RETURNING *;

CREATE TABLE




settings


[{"id": 100, "name": "testOne", "settings": "settingOne"}, {"id": 101, "name": "testTwo", "settings": "settingTwo"}, {"id": 101, "name": "testThree", "settings": "settingThree"}]



INSERT 0 1


-- only remove the first match
UPDATE users u
SET    settings = settings
                - (SELECT a.ord::int-1
                   FROM   jsonb_array_elements(u.settings) WITH ORDINALITY a(elem, ord)
                   WHERE  a.elem ->> 'id' = '101'
                   LIMIT  1)
WHERE  u.settings @> '[{"id":101}]';

UPDATE 1


TABLE users;


settings


[{"id": 100, "name": "testOne", "settings": "settingOne"}, {"id": 101, "name": "testThree", "settings": "settingThree"}]



SELECT 1