Postgres: Update object items on JSONB array
Problem:
We have a table with a JSONB array column data which contains many objects:
[
{"type": "dog", "id": "dog-1"},
{"type": "cat", "id": "cat-1"}
]
How do we change the name field from "type" into "kind" and keep all related data?
Let’s assume table animals has 2 columns: id and data.
Answer:
UPDATE animals
SET data = new.data
FROM (
SELECT id,
jsonb_agg(
jsonb_build_object(
'id', elem -> 'id',
'kind', elem -> 'type'
)
) AS data
FROM animals,
jsonb_array_elements(CASE jsonb_typeof(data) WHEN 'array' THEN data ELSE '[]' END) AS elem
WHERE data -> 0 -> 'kind' IS NULL
GROUP BY animals.id
) new
WHERE animals.id = new.id;