sql - 使用 Postgres，如果为 NULL，我如何使用 UPDATE 插入新的 jsonb 值，或者如果值已经存在，如何修改 json？

Question

我在 Postgres(版本 10)中有一个表，其中包含一个可为空的 jsonb 列:

CREATE TABLE j_play (
id                  serial PRIMARY KEY,
created_ts          TIMESTAMPTZ,
data                JSONB
); 

可能最初插入的记录可能没有 json 数据，因此该列将为空。

| id | created_ts                   | data
| 1  | 2020-09-11 18:18:37.47755+00 | [null]

我想更新这条记录，如果'null'添加了一个json对象，如果一个json对象已经存在，那么它会被修改。

UPDATE j_play SET data = '{"a": "12345"}'
| id | created_ts                   | data
| 1  | 2020-09-11 18:18:37.47755+00 | {"a": "12345"}

UPDATE j_play SET data = '{"b": "54321"}'   -- This is obviously bogus but the intention is to amend existing json
| id | created_ts                   | data
| 1  | 2020-09-11 18:18:37.47755+00 | {"a": "12345", "b": "54321"}

如果记录以空 json 文档开头，那么我可以使用 jsonb_set:

| id | created_ts                   | data
| 1  | 2020-09-11 18:18:37.47755+00 | {}
UPDATE j_play SET data = jsonb_set(rec_data, '{a}', '"12345"', TRUE) WHERE id = 1

但是当初始值为 NULL 时，我不知道如何执行此操作。我可能可以忍受将列初始化为 {}，但我想知道是否有一种优雅的方法可以将其从 NULL 更新。

Answer 1

I want to update this record so that if 'null' a json object is added, and if a json object already exists then it is amended.

使用coalesce():

update j_play set data = coalesce(data, '{}') || '{"a": "12345"}';

Demo on DB Fiddle :

insert into j_play (data) values(null);

update j_play set data = coalesce(data, '{}') || '{"a": "12345"}';
update j_play set data = coalesce(data, '{}') || '{"b": "54321"}';

select * from j_play;

id | created_ts | data                        -: | :--------- | :--------------------------- 1 | null       | {"a": "12345", "b": "54321"}