How to update JSON array with PostgreSQL

Question 1

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 matchX 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    STRICTAS $function$SELECT concat('[', string_agg("element"::text, ','), ']')::jsonFROM (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_baseSET    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;

Question 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?

Question 3

@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.

Question 4

@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 ]

Question 5

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

Question 6

@S-Man I want to find all the"othern" = 5, and update them themselves to for example"othern" = 7, only that using postgresql.

Question 7

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 mytableSET myjson = s.json_arrayFROM (    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 aCASE 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 jsonbAS $$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 jsonbAS $$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';

Question 8

Excellent answer, not only did you give me the solution to my problem (with theupdate 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!

Question 9

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 theJSONB -> JSON data, and thus save it to the database? thanks

Question 10

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

Question 11

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

Question 12

That's why I usedjsonb and notjsonstackoverflow.com/questions/22654170/…

S-Man 24k9 gold badges51 silver badges78 bronze badges · Accepted Answer · 2019-08-22 08:02:50Z

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 mytableSET myjson = s.json_arrayFROM (    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 aCASE 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 jsonbAS $$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 jsonbAS $$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 theupdate 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!
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 theJSONB -> JSON data, and thus save it to the database? thanks
json is saved with spaces, there are no inconveniences with this?
That's why I usedjsonb and notjsonstackoverflow.com/questions/22654170/…

Movatterモバイル変換

Collectives™ on Stack Overflow

How to update JSON array with PostgreSQL

1 Answer1

7 Comments

Your Answer

Sign up orlog in

Post as a guest

Linked

Related

Hot Network Questions

Subscribe to RSS