Postgresql - Renaming the key of all objects in an array of a JSON column

Question

PostgreSQL 13

The goal is to rename all src keys in the photos array to image.

I have a table plans which has a column json with a simplified structure similar to the below sample.

{
  "id": "some id",
  "name": "some name",
  "tags": [
    {
      "keyId": 123,
      "valueId": 123
    },
    {
      "keyId": 123,
      "valueId": 123
    }
  ],
  "score": 123,
  "photos": [
    {
      "src": "someString"
    },
    {
      "src": "someString"
    }
  ],
  "payment": true
}

The number of objects in the photos array varies, but in general, it is less than 10, so a non-iterating method would be fine, too.

I tried something like this, but it is only good for modifying the value of a key, not the name of the key itself.

UPDATE
    plans
SET
    json = jsonb_set(json::jsonb, '{photos, 0, src}', '"image"')
;

With the following attempt, I was actually able to rename the key but it overwrites everything else, so only an object with {"image": "someUrl"} is left:

UPDATE
    plans
SET
    json = (json -> 'photos' ->> 0)::jsonb - 'src' || jsonb_build_object ('image',
        json::jsonb -> 'photos' -> 0 -> 'src')
WHERE json::jsonb ? 'photos' = true;

Is there a way to rename keys as expected?

Answer 1

So in the end I used a variation of my initial jsonb_set method. The solution isn't elegant or efficient, but since it is a one-time operation, it was only important to work:

UPDATE
    plans
SET
    json = jsonb_set(json::jsonb, '{photos, 0, imageUrl}', (json->'photos'->0->'src')::jsonb)
WHERE
    json->'photos'->0->'src' IS NOT NULL
;

This query would add the imageUrl key with the existing value of the src key for the first object (position 0) in the photos array. So it left me with src and imageUrl key.

To remove the src key, I ran the following query

UPDATE
    plans
SET
    json = json::jsonb #- '{ photos, 0, src}'
;

Repeating this as many times as the maximum number of elements in a photos array eventually solved the issue for me.