postgresql - 追加到 jsonb 数组中并根据键更新现有记录

Question

我有一个表 media，其中包含一个空数组的 jsonb 数组字段 pictures。

我的想法是，每次我追加一个新的 json 对象时，我都想将之前任何一个的默认属性从 true 切换为 false。

示例对象:

{"file": "file.jpg", "default": true}

我用 2 个不同的查询完成了这一点。

一个用于插入一条新记录:

update media
set pictures = jsonb_set(
    pictures,
    concat('{' , jsonb_array_length(pictures) , '}')::text[],
    jsonb_build_object('file', 'somepicture.jpg', 'default', true)
)
where user_id = 8

还有一个用于从 default:true 切换到 default:false

update media
set pictures =
(
    select
        jsonb_agg(
            case when value->>'default' = 'true' and value->>'file' != 'somepicture.jpg'
            then value || jsonb_build_object('default', false)
            else value
            end
        )
    from jsonb_array_elements(media.pictures)
)
where user_id = 8

我的最终图片数组:

[
 {
  "file": "previouspicture.jpg",
  "default": false
 },
 {
  "file": "somepicture.jpg",
  "default": true
 }
]

我怎样才能通过一个查询实现同样的事情？

Answer 1

使用额外的 jsonb_set() 来更新之前的对象。您必须使用 coalesce() 才能从空数组开始:

update media
set pictures = jsonb_set(
    coalesce(
        jsonb_set(
            pictures,
            array[(jsonb_array_length(pictures)-1)::text],
            pictures->jsonb_array_length(pictures)-1 || '{"default": false}'
            ), 
        pictures
    ),
    array[(jsonb_array_length(pictures))::text],
    jsonb_build_object('file', 'filename.jpg', 'default', true)
)
where user_id = 8
returning *;

DbFiddle.