sql - 更新对象数组中的特定对象 Postgres jsonb

Question

我正在尝试更新包含对象数组的表 Books 上的 jsonb 列 pagesRead。它的结构类似于这样:

[{
    "book": "Moby Dick",
    "pagesRead": [
    "1",
    "2",
    "3",
    "4"
    ]
},
{
    "book": "Book Thief",
    "pagesRead": [
    "1",
    "2"
    ]
}]

我想做的是在阅读本书的特定页面时更新 pagesRead，或者如果有人开始阅读新书，则在其中添加一个额外的条目。

我能够检索到特定图书的详细信息，但我不确定如何更新它。

编辑:所以我不得不使用来自 S-Man 的更新查询来添加一本书条目，但我使用来自 Barbaros Özhan 的插入查询来处理更新页面

Answer 1

之前的一些想法:

1. 您永远不应将结构化数据按原样存储在一列中。这会产生更新、索引(因此，搜索/性能)、过滤等所有问题。请将所有内容规范化为适当的表格和列
2. 你不应该存储数组。将其规范化。
3. 不要使用类型文本来存储整数(页数)
4. "pagesRead" 是您的过滤器元素 ("book") 的兄弟元素。这使得引用它比引用它作为 child 要复杂得多。因此，将书名(或更好:id)视为键，如 {"my_id_for_book_thief": {"name": "Book Thief", "pagesRead": [...]}}。在这种情况下，您可以使用路径来引用它。否则，我们需要提取数组，查看每个 book 属性并引用其兄弟

---

demo:db<>fiddle

添加 book 非常简单(假设您使用的是 jsonb 类型而不是 json 类型):

SELECT mydata || '{"book": "Lord Of The Rings", "pagesRead": []}'
FROM mytable

更新:

UPDATE mytable
SET mycolumn = mycolumn || '{"book": "Lord Of The Rings", "pagesRead": []}'

---

添加一个 pagesRead 值:

SELECT 
    jsonb_agg(                                                         -- 4
        jsonb_build_object(                                            -- 3
            'book', elem -> 'book',
            'pagesRead', CASE WHEN elem ->> 'book' = 'Moby Dick' THEN  -- 2
                             elem -> 'pagesRead' || '"42"'
                         ELSE elem -> 'pagesRead' END
        )
    ) as new_array
FROM mytable,
    jsonb_array_elements(mydata) as elem                               -- 1

1. 将数组提取为每个元素一条记录
2. 如果元素包含正确的书则添加一个页面
3. 重建对象
4. 重新聚合您的数组。

更新将是:

UPDATE mytable
SET mycolumn = s.new_array
FROM (
    -- <query above>
) s