Update jsonb with the new keys but preserve the value if the existing obj has the key-CodePudding

I need to update the existing object. The resulting object will own all and only the keys from the new one, but if the same key already exist, I need to copy the value. Something like a merge, but only for values if they have the same key.

Example:
Existing in db: {"a":1,"b":2, "c":3},
New object: {"a":999, "d": 4}
Result: {"a":1, "d":4} - "a" get the value from existing data, "b" is gone, "d" is added.

This is for my special 'upsert' query. What I've done so far is the simple:

INSERT ....
ON CONFLICT (project_id, other_stuf)
    DO UPDATE SET payload = excluded.payload || answers.payload

but it's not deleting the "b" in the above example.

Thoughts?

CodePudding user response：

You can combine jsonb_each with jsonb_object_agg:

SET payload = (
  SELECT jsonb_object_agg(
    key,
    (CASE WHEN answers.payload ? key THEN answers.payload->key ELSE value)
  )
  FROM jsonb_each(excluded.payload)
)

or shortened to

SET payload = (
  SELECT jsonb_object_agg(key, COALESCE(answers.payload->key, value))
  FROM jsonb_each(excluded.payload)
)