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;

Ok, you pass in a number. If number is 5, which elements should be updated? You meant: Could me much bigger. What does this mean? More elements in the array or deeper nested elements? — S-Man, Aug 22 '19 at 07:05
@S-Man If I pass it as number 5, all fields ("othern": "5") must be updated to the new number that I must also pass to the query. — Mario Montano, Aug 22 '19 at 07:11
@S-Man more elements like this one: [{ "ch":"1", "id":"13", "area":"0", "level":"Skin", "width":"", "length":"", "othern":"1", "percent":"100", "location":" Abdomen " }, { "ch":"1", "id":"13", "area":"0", "level":"Skin", "width":"", "length":"", "othern":"1", "percent":"100", "location":" Abdomen " }, etc ] — Mario Montano, Aug 22 '19 at 07:13
It's still not clear to me: You want to find all elements that contain othern = 5. So far, so good. What then? Which elements should be updated to what? Maybe you could change your complex example into something simpler with only two fields per element and show the expected output — S-Man, Aug 22 '19 at 07:17
@S-Man I want to find all the **"othern" = 5**, and update them themselves to for example **"othern" = 7**, only that using postgresql. — Mario Montano, Aug 22 '19 at 07:22
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/198330/discussion-between-mario-montano-and-s-man). — Mario Montano, Aug 22 '19 at 19:13

S-Man · Accepted Answer · 2019-08-22T08:02:50.507

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

jsonb_array_elements() expands the array into one row per element
jsonb_set() changes the value of each othern field. The relevant JSON objects can be found with a CASE clause
jsonb_agg() reaggregates the elements into an array again.
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';

Excellent answer, not only did you give me the solution to my problem (with the **update mytable** it worked perfect for me), but you also taught me the reason for things. PD: I didn't know the **"CASE WHEN elems - >> etc"** part, excellent that! Thank you! — Mario Montano, Aug 22 '19 at 16:18
I have a small inconvenience, this table the column is saved as type **"JSON"**. Before I did a **"SELECT to_jsonb (myjson) ..."**, to convert it to **"JSONB"** and thus do the operation of the Update. Now my question is, how can I do to return the **JSONB -> JSON** data, and thus save it to the database? thanks — Mario Montano, Aug 22 '19 at 19:09
json is saved with spaces, there are no inconveniences with this? — Mario Montano, Aug 22 '19 at 20:44
That's why I used `jsonb` and not `json` https://stackoverflow.com/questions/22654170/explanation-of-jsonb-introduced-by-postgresql — S-Man, Aug 23 '19 at 06:27
What happens is that I am making arrangements to an app that is very large and everything is ready, use **"appendgrid"** to show the `json` values, when doing the `json` update `the order of the keys was modified` and the app are not reading the `data of json in "appendgrid"`. Any advice to keep the json in the same order it was in? I would really appreciate it! thanks for all the help! — Mario Montano, Aug 23 '19 at 18:45
json stores the values as is, jsonb not. But if the app supports valid json, it should consider the order, as in JSON specification order of keys is not given, only in arrays. But nevertheless: Storing as json should help. — S-Man, Aug 23 '19 at 19:24

How to update JSON array with PostgreSQL

1 Answers1