Update a value in a nested json in postgresql

Question

i'm dealing with a column of a postgresql table containing jsons having this kind of structure:

{
    "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
        }
    }
}

My task is to update the value of the key "access" with the current timestamp, if and only if the previous value is null. My function obviously get the id ("743d774d-835a-436a-b7e8-0acb6af9f683", "453f0613-e1fb-41ef-bf35-5e0520ed8995", etc) as input and i'm able to reach the interesting row of the table.

I've tried this kind of syntax:

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

in which I was thinking about overwriting {"access": null} with {"access": timestamp}, but:
- I cannot point correctly at the participants jsonb,
- overwriting the whole key/value pair feels inelegant and somewhat dangerous.
the syntax that I found in this page: https://dev.to/deepika_banoth/how-to-use-jsonbset-function-in-postgresql-35eo

at the point 2:
```
UPDATE "json" SET "participants"=jsonb_set("participants"::jsonb, '{access}', '"timestamp"'
WHERE "details"::json->>'id'='"743d774d-835a-436a-b7e8-0acb6af9f683"' 
```
but still cannot make it work because i'm not able to correctly point at the participants json.
- other pointless syntaxes.

I will be grateful to anyone who wants to help me or give me clues on how to deal with the problem.

Laurenz Albe · Accepted Answer · 2021-09-01 10:05:15Z

5

You can use jsonb_set, just use the path to the desired attribute in the path parameter:

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

Be careful that you don't use NULL instead of the timestamp, because then the result of jsonb_set is also NULL.

Note that modifying large JSONs in the database is not efficient, because it will always write the whole value.

edited Sep 1, 2021 at 10:05

answered Sep 1, 2021 at 9:59

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

qmk Over a year ago

"modifying large JSONs in the database is not efficient, because it will always write the whole value" -- could you provide a source?

Laurenz Albe Over a year ago

@qmk Well, the source is open... A jsonb value is a single attribute.

Collectives™ on Stack Overflow

Update a value in a nested json in postgresql

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related