PostgreSQL 9.5 - update doesn't work when merging NULL with JSON

Question

My users table contains a metadata column of type json. Now, I want to add new metadata to a user while preserving existing values. So I'm using the || operator to merge 2 JSON objects:

UPDATE users
SET metadata = metadata::jsonb || '{"test": true}'::jsonb
WHERE id=...
RETURNING *;

Everything works fine when there are already some existing metadata. However, when the previous value is NULL then the update doesn't work. The metadata after update is still NULL.

How can I improve my query so that it sets the new JSON object when the previous value is NULL or merges the previous and new values otherwise?

score 14 · Accepted Answer · answered Dec 22 '16 at 13:53

14

add coalesce:

UPDATE users
SET metadata = coalesce(metadata::jsonb,'{}'::jsonb) || '{"test": true}'::jsonb
WHERE id=...
RETURNING *;

it works similar like with normal strings NULL || something is always NULL

answered Dec 22 '16 at 13:53

JosMac

2,164
1
17
23

Wow! It helped. Thank you so much! – Flushed Dec 22 '16 at 13:56
1

The coalesce part gets even simpler: `coalesce(metadata, '{}')::jsonb` – tom Sep 25 '20 at 01:16

PostgreSQL 9.5 - update doesn't work when merging NULL with JSON

1 Answers1

Linked