Link to example that only changes first occurance: https://www.db-fiddle.com/f/wd6jJmDxGp3W1x6ptrEjB4/0
Example Data:
| id (int) | application_questions (JSONB) |
|---|---|
| 1 | [{"id":1,"type":"single-select"},{"id":2,"type":"multi-select-check"}, {"id":3,"type":"single-select"},{"id":4,"type":"multi-select-check"}, {"id":5,"type":"single-select"},{"id":6,"type":"multi-select-check"}] |
| 2 | [{"id":1,"type":"single-select"},{"id":2,"type":"multi-select-check"}, {"id":3,"type":"single-select"},{"id":4,"type":"multi-select-check"}] |
| 3 | [{"id":1,"type":"single-select"},{"id":2,"type":"multi-select-check"}, {"id":3,"type":"single-select"},{"id":4,"type":"multi-select-check"}] |
I have a JSONB column where each record is an array of objects. Each object has the key type.
What I want to do is update each type if it matches a condition. For example if type is multi-select-check then change it to multi-select-combo, but leave the types that are not multi-select-check unchanged.
What I have found is that it is possible to change the first occurrence of type C but the others in that same list are ignored (see my db-fiddle example).
Please enlighten me.