在 PostgreSQL 中更改 json 數組的單個元素

首先，我想說我沒有太多經驗，但是我在大學學習了一些與 dbs 相關的科目後開始學習 dbs（所以在閱讀本文時請記住這一點）。

我已經在 stackoverflow 上問過這個問題，但我沒有得到結論性的答案，所以我認為在這裡問同樣的問題可能是個好主意，因為這個網站只關注數據庫及其來龍去脈。

我想使用 UPDATE、DELETE 或 INSERT 命令更改嵌套數組的單個元素。在這種情況下，我想通過使用 UPDATE 命令將一個“lastName”值換成另一個來更新數組的單個元素（我使用的語法可能是錯誤的，但這是我可以從我的’已閱讀官方文件）。在將任何內容放入真實數據庫之前，我使用 dbfiddle 測試我正在做的事情是否可行，以免犯任何不必要的錯誤。這是我在 dbfiddle https://dbfiddle.uk/?rdbms=postgres_10&fiddle=ecc2329efea6af28636b3537d46b6c01中寫的

如果有人能澄清我做錯了什麼，我將不勝感激。如果您對如何完成此類事情有任何建議，我將非常樂意獲得一些建設性的回饋。

我認為這不能使用jsonb_set().

該函式的第二個參數需要一個“目標路徑”。由於podaci包含一個數組，您需要包含數組位置，例如jsonb_set(podaci, '{1,lastName}', '"Kirin"')- 但是，沒有簡單的方法（我能想到）來獲得您需要的索引位置。

因此，您將需要遍曆元素，更改您想要的元素並聚合回來。這可以通過 UPDATE 語句分配中的子查詢來完成：

update test_tablica1
  set podaci = (select jsonb_agg(case 
                                  when element @> '{"firstName": "Maria"}' 
                                    then element||'{"lastName": "Kirin"}' 
                                  else element
                                 end)
                from jsonb_array_elements(podaci) as x(element))
where podaci @> '[{"firstName": "Maria"}]';                 

jsonb_agg()將返回的元素聚合jsonb_array_elements()到一個數組中。然後 CASE 表達式將檢查每個元素，如果它包含名字 Maria，姓氏將被替換（將 JSONB 值附加到 JSONB 值，替換現有鍵）

WHERE 子句防止更新不需要更新的行

線上範例

---

但是，您的模型看起來確實像是在數據庫中誤用了 JSON。將名稱儲存在與基表具有一對多關係的適當關係表中會更有效（test_tablica1在您的範例中）

就像是：

create table test_tablica1 
(
 id int primary key generated always as identity, 
 ... other columns ...,
 datum_upisa timestamp
);

create table names
(
 id int not null, 
 tablica1_id int not null references test_tablica1,
 firstname text, 
 lastname text
);

insert into test_tablica1 (id) values (1);
insert into names (id, tablica1_id, firstname, lastname)
values
(1, 'Tom', 'Cruise'),
(2, 'Maria', 'Sharapova'),
(3, 'James', 'Bond')
;

然後更新變得如此簡單：

update names
  set lastname = 'Kirin'
where firstname = 'Maria'
 and tablica1_id = 1 
;

條件tablica1_id = 1只是一個例子。添加所需的任何條件，以將名稱更改限制為連結到父表中特定行的行。

引用自：https://dba.stackexchange.com/questions/315117