PostgreSQL 9.5-将NULL与JSON合并时更新不起作用 [英] PostgreSQL 9.5 - update doesn't work when merging NULL with JSON

问题描述

我的users表包含类型为json的metadata列. 现在，我想在保留现有值的同时向用户添加新的元数据. 因此，我正在使用||运算符合并2个JSON对象:

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 *;

当已经有一些现有的元数据时，一切都很好. 但是，当先前的值为NULL时，更新将不起作用.更新后的metadata仍然是NULL.

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.

如何改进查询，以便在先前值为NULL时设置新的JSON对象，否则将先前值与新值合并?

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?

推荐答案

添加合并:

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

它的工作方式类似于普通字符串NULL || something始终为NULL

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

这篇关于PostgreSQL 9.5-将NULL与JSON合并时更新不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！