Append values to nested jsonb array, the jsonb array may or may not exist

Question

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

{
  "a": "b"
}

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

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.

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

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.

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.

Ajax1234 · Accepted Answer · 2022-09-22 14:13:19Z

2

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'

answered Sep 22, 2022 at 14:13

Ajax1234

71.7k9 gold badges67 silver badges110 bronze badges

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

1 Answer 1