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
– S-Man, Commented Aug 22, 2019 at 7: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
– Mario Montano, Commented Aug 22, 2019 at 7: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
– Mario Montano, Commented Aug 22, 2019 at 7: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
– S-Man, Commented Aug 22, 2019 at 7: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
– Mario Montano, Commented Aug 22, 2019 at 7:22

S-Man · Accepted Answer · 2019-08-22 08:02:50Z

8

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';

edited Aug 22, 2019 at 8:02

answered Aug 22, 2019 at 7:57

S-Man

24k9 gold badges51 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Mario Montano Over a year ago

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 Over a year ago

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

S-Man Over a year ago

Just re-cast it: UPDATE... SET myjson = json_array::json

Mario Montano Over a year ago

json is saved with spaces, there are no inconveniences with this?

S-Man Over a year ago

That's why I used jsonb and not json stackoverflow.com/questions/22654170/…

|

Collectives™ on Stack Overflow

How to update JSON array with PostgreSQL

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related