Update Postgres JSONB column array values conditionally

Question

I have a basic Postgres table structure: CREATE TABLE PROPS(id integer, data jsonb);

and format of data column:

[
  {
    "name": "abc",
    "refKey": 123
  },
  {
    "name": "cba",
    "refKey": 124
  },
  {
    "name": "xyz",
    "refKey": 123
  }  
]

I am struggling to write a sql to update "refKey" from 123 to 125 for all data (entire PROPS table).

Answer 1

Here is a query that can do that:

WITH src AS (
    SELECT id, json_array_elements(data::JSON) as arr FROM Props   
), modified AS (
    SELECT id, arr::JSONB || '{"refKey":125}'::JSONB AS arr FROM src
), regrouped AS (
    SELECT id, json_agg(arr) AS data FROM modified GROUP BY id
) 
UPDATE Props 
SET "data" = regrouped."data"
FROM regrouped
WHERE regrouped.id = Props."id";

It's definitely a little cumbersome but I found it easier to show the steps with the intermediate CTEs (Common Table Expressions).

Here's what we do in order:

1. In the first CTE, I expand the JSON array elements so we can modify each one individually using the json_array_elements() function.
2. In the second CTE, I modify each element in the array to update refKey to 125 (I do this with json concatenation - ||)
3. In the third CTE, I recombine all the elements of the array into a single JSONB array using the json_agg function.
4. Finally, with all these CTEs combined, we have a simple UPDATE to update that data back into the Props table.

Obviously you can customize the WHERE segment of the UPDATE command to only update certain rows, and you can update the logic in the modified CTE to update differently (e.g. increment instead of just setting to 125).