在postgresql中更新一个嵌套json中的值

Question

我正在处理一个列的postgresql表包含json具有这种结构:

{
"id": "a",
"user_id": " e",
"event_id": 1,
"last_snooze_timestamp": "2021-02-25T13:45:26.000000+00:00",
"number_of_participants": 3,
"participants": {
"743d774d-835a-436a-b7e8-0acb6af9f683":{
"nome": "abc",
"cognome": "abc",
"pdfURL": "indirizzoPDF",
"type": "Booker",
"access": null
},
"453f0613-e1fb-41ef-bf35-5e0520ed8995": {
"nome": "cde",
"cognome": "cde",
"pdfURL": "indirizzoPDF",
"type": "Minor",
"access": null
}
}
}

我的任务是更新键"access"的值。对于当前时间戳，当且仅当前一个值为空。我的函数显然得到Id ("743d774d-835a-436a-b7e8-0acb6af9f683"， "453f0613-e1fb-41ef-bf35-5e0520ed8995"等)作为输入，我就能到达表中感兴趣的行了。

我试过这种语法:

1. SELECT jsonb_set(json_to_modify, 'path', jsonb '{"key":value}')

   我正在考虑用{"access": timestamp}覆盖{"access": null}，但是:

   • 我不能正确指向participantsjsonb，
   • 覆盖整个键/值对感觉不优雅，而且有些危险。

2. 我在本页找到的语法:https://dev.to/deepika_banoth/how-to-use-jsonbset-function-in-postgresql-35eo

   在第2点:

   UPDATE "json" SET "participants"=jsonb_set("participants"::jsonb, '{access}', '"timestamp"'
   WHERE "details"::json->>'id'='"743d774d-835a-436a-b7e8-0acb6af9f683"' 
   

   ，但仍然不能使它工作，因为我不能正确地指向参与者json.

   • 其他无意义的语法。

我将非常感谢任何想帮助我或给我提示如何处理这个问题的人。

Answer 1

您可以使用jsonb_set，只需在path参数中使用所需属性的路径:

UPDATE ...
SET jsonb_col = jsonb_set(
jsonb_col,
'{participants,743d774d-835a-436a-b7e8-0acb6af9f683,access}',
JSONB '"2021-09-01 12:00:00"'
)

注意不要用NULL代替时间戳，因为那样jsonb_set的结果也是NULL。

请注意，修改数据库中的大型json是不高效的，因为它将始终写入整个值。