How to update jsonb field in postgresql-CodePudding

i've been trying to update jsonb field in postgre sql. i tried below command but it did not work and did not give any error.although the command seems successful, there is no change in the field. how can i update the jsonb field.

the command that i executed:

UPDATE "MddPublisher" SET "CountriesAndGamesAppStore" = jsonb_set("CountriesAndGamesAppStore" , '{"AX"}', '["test"]');

NOTE: the values in the row i tried to update were null.

NOTE: the command is working on already filled rows but it isn't working on null rows.

the structure of jsonb field:

{"AD": ["Hello"], "AF": ["Hello"], "AL": ["Hello"], "AS": ["Hello"], "AX": ["Hello"], "DZ": ["Hello"]}

CodePudding user response：

You can use coalesce function, this function check each argument has value untill argument has not null

coalesce("CountriesAndGamesAppStore", '{}'::jsonb)

This function check your first argument is not null, if first argument is null then use second argument

UPDATE "MddPublisher" SET 
"CountriesAndGamesAppStore" = jsonb_set(coalesce("CountriesAndGamesAppStore", '{}'::jsonb) , '{"AX"}', '["test"]');