Below is the starting point of one of the DB entry.

{
  "a": "b"
}

Below is what I did to reach to the above point.

1. create table if not exists testing (val jsonb);
2. insert into testing values('{"a":"b"}'::jsonb);

I would like to append an array of values to a key, let's say the key name to be 'errors'. If the key does not exist, I can use the below query.

3. update testing set "val"="jsonb_set_recursive"(val, '{errors}'::text[], '["a","b"]'::jsonb) where val->>'a'='b';

and I will have

{
  "a": "b",
  "errors": [
    "a",
    "b"
  ]
}

If the key "errors" already exists with some values, then I can use the below query

4. update testing set "val"="jsonb_set_recursive"(val, '{errors}', val->'errors' || '["a","b"]'::jsonb) where val->>'a'='b'

and I will have.

{
  "a": "b",
  "errors": [
    "a",
    "b",
    "a",
    "b"
  ]
}

The problem:

I do not know beforehand if the key "errors" exists or not.

• If the key exists, I would like to append to the existing values
• If the key does not exist, I would like to create the key and insert the array.

Any pointers on how to achieve this?

If I run query 4 when the key 'errors' does not exist, then the whole row value (val) seems to vanish.

CodePudding user response：

You can use coalesce in the set function:

update testing set val = jsonb_set(val, '{errors}'::text[], 
    coalesce(val->'errors', '[]'::jsonb) || '["a","b"]'::jsonb) 
where val->>'a'='b'

See fiddle