How to update JSON array with PostgreSQL

Question

I have the following inconvenience, I want to update a key of an JSON array using only PostgreSQL. I have the following json:

[
   {
      "ch":"1",
      "id":"12",
      "area":"0",
      "level":"Superficial",
      "width":"",
      "length":"",
      "othern":"5",
      "percent":"100",
      "location":" 2nd finger base"
   },
   {
      "ch":"1",
      "id":"13",
      "area":"0",
      "level":"Skin",
      "width":"",
      "length":"",
      "othern":"1",
      "percent":"100",
      "location":" Abdomen "
   }
]

I need to update the "othern" to another number if the "othern" = X

(X is any number that I pass to the query. Example, update othern if othern = 5).

This JSON can be much bigger, so I need something that can iterate in the JSON array and find all the "othern" that match X number and replace with the new one. Thank you!

I have tried with these functions json of Postgresql, but I do not give with the correct result:

    SELECT * FROM jsonb_to_recordset('[{"ch":"1", "id":"12", "area":"0", "level":"Superficial", "width":"", "length":"", "othern":"5", "percent":"100", "location":" 2nd finger base"}, {"ch":"1", "id":"13", "area":"0", "level":"Skin", "width":"", "length":"", "othern":"1", "percent":"100", "location":" Abdomen "}]'::jsonb) 
AS t (othern text);

I found this function in SQL that is similar to what I need but honestly SQL is not my strength:

CREATE OR REPLACE FUNCTION "json_array_update_index"(
    "json"            json,
    "index_to_update" INTEGER,
    "value_to_update" anyelement
)
    RETURNS json
    LANGUAGE sql
    IMMUTABLE
    STRICT
AS $function$
SELECT concat('[', string_agg("element"::text, ','), ']')::json
FROM (SELECT CASE row_number() OVER () - 1
                 WHEN "index_to_update" THEN to_json("value_to_update")
                 ELSE "element"
                 END "element"
      FROM json_array_elements("json") AS "element") AS "elements"
$function$;


UPDATE plan_base
SET    atts = json_array_update_index([{"ch":"1", "id":"12", "area":"0", "level":"Superficial", "width":"", "length":"", "othern":"5", "percent":"100", "location":" 2nd finger base"}, {"ch":"1", "id":"13", "area":"0", "level":"Skin", "width":"", "length":"", "othern":"1", "percent":"100", "location":" Abdomen "}], '{"othern"}', '{"othern":"71"}'::json)
WHERE  id = 2;

Answer 1

The function you provided changes a JSON input, gives out the changed JSON and updates a table parallel.

For a simple update, you don't need a function:

demo:db<>fiddle

UPDATE mytable
SET myjson = s.json_array
FROM (
    SELECT 
        jsonb_agg(
             CASE WHEN elems ->> 'othern' = '5' THEN
                 jsonb_set(elems, '{othern}', '"7"')
             ELSE elems  END
        ) as json_array
    FROM
        mytable,
        jsonb_array_elements(myjson) elems
) s

1. jsonb_array_elements() expands the array into one row per element
2. jsonb_set() changes the value of each othern field. The relevant JSON objects can be found with a CASE clause
3. jsonb_agg() reaggregates the elements into an array again.
4. This array can be used to update your column.

---

If you really need a function which gets the parameters and returns the changed JSON, then this could be a solution. Of course, this doesn't execute an update. I am not quite sure if you want to achieve this:

demo:db<>fiddle

CREATE OR REPLACE FUNCTION json_array_update_index(_myjson jsonb, _val_to_change int, _dest_val int)
RETURNS jsonb
AS $$
DECLARE
    _json_output jsonb;
BEGIN
    SELECT 
        jsonb_agg(
             CASE WHEN elems ->> 'othern' = _val_to_change::text THEN
                 jsonb_set(elems, '{othern}', _dest_val::text::jsonb)
             ELSE elems  END
        ) as json_array
    FROM
        jsonb_array_elements(_myjson) elems
    INTO _json_output;

    RETURN _json_output;
END;
$$ LANGUAGE 'plpgsql';

---

If you want to combine both as you did in your question, of course, you can do this:

demo:db<>fiddle

CREATE OR REPLACE FUNCTION json_array_update_index(_myjson jsonb, _val_to_change int, _dest_val int)
RETURNS jsonb
AS $$
DECLARE
    _json_output jsonb;
BEGIN
    UPDATE mytable
    SET myjson = s.json_array
    FROM (
        SELECT 
            jsonb_agg(
                 CASE WHEN elems ->> 'othern' = '5' THEN
                     jsonb_set(elems, '{othern}', '"7"')
                 ELSE elems  END
            ) as json_array
        FROM
            mytable,
            jsonb_array_elements(myjson) elems
    ) s
    RETURNING myjson INTO _json_output;

    RETURN _json_output;
END;
$$ LANGUAGE 'plpgsql';